SQL2005 Express DB with custom SQL Membership Provider

Jul 5, 2007 at 10:54 PM
Hi,

Thought I would share my current efforts with the community. I have been working on getting the SQL datastore working with a proper SQL Membership Provider as I don't like using the XML files to store any information.

This is still early code so its a dirty hack to get working but if anyone wants to try it to build upon this should help. To set everything up you need to do the following:

1. Download the latest source-code 3314
2. Start up your SQL 2005 Express database and start SQL Management Studio Express
3. Once you are logged in using a Windows Account or SA (SQL/Mixed login preferred for this setup), create a new database
4. Call this database blogengine
5. Locate the Login section in the left hand list (at the bottom), and create a new user called beuser, assign a SQL password (not Windows Authentication), make it the same as the login or whatever secure password you want
6. At the bottom of the Create Login dialog box, ensure the blogengine database is set as the default schema for this user
7. Double-Click your blogengine database to expand the list, you will see a folder called Security
8. In the right hand side you should see some users like dbo
9. Right-Click in the right-hand side and select Create User
10. Set the user id as beuser and login as beuser
11. In the two lists below, you will see some user rights, ensure you tick dbo.owner in both lists, then hit OK to create the new user
12. Disconnect from your local database and reconnect but login with the beuser credentials
13. Double-Click the blogengine database so you are in its context
14. Click on New Query button to display a blank query window, ensure the toolbar is showing blogengine as the selected database
15. Locate the folder where you unzipped the 3314 source code and find the folder called Setup (in the Blogengine.NET folder)
16. Open the .sql file in notepad, copy the contents and paste it into the New Query window in SQL Management Studio Express
17. Press F5 to execute the query (or click on Execute)
18. If everything went okay you will have a some new Tables created in your Database

Now the annoying part

I have got my lazy arse round to scripting up Settings data into a INSERT script but I will try to get that done soon. You need to do the following now:

1. Double-Click the Table folder underneath your blogengine database container, you should now see the be_ tables
2. Right-Click the be_Settings table and select Open Table to display the datagrid (allows easy entry of data)
3. The datagrid will show two columns, first column is the name of the setting, the second column is the data for that setting
4. Open up the settings.xml file in VS or your favourite notepad/text editor
5. Working from the top of the list (underneath <settings> object) copy each element name e.g. storagelocation and paste it into the first column in your datagrid in SQL Management Express
6. Once you have added all of the elements you will need to paste in the values into the second column, ensuring the right value is entered for each element
7. You will also need to amend the element named mssqlconnectionstring, giving it the value of your connection string (see the web.config sample below)
8. Close the datagrid to save the information

This concludes setting up the BlogEngine.NET specific database stuff

Now you need to backup the web.config file in your 3314 source code folder and replace it with the following:

Jul 5, 2007 at 10:55 PM
<?xml version="1.0"?>
<configuration>

<configSections>
<sectionGroup name="BlogEngine">
<section name="blogProvider" requirePermission="false" type="BlogEngine.Core.Providers.BlogProviderSection, BlogEngine.Core" allowDefinition="MachineToApplication" restartOnExternalChanges="true"/>
</sectionGroup>
</configSections>

<BlogEngine>
<blogProvider defaultProvider="MSSQLBlogProvider">
<providers>
<!-- remarked out the XmlBlogProvider as it caused build errors on my machine -->
<!--<add name="XmlBlogProvider" type="BlogEngine.Core.Providers.XmlBlogProvider"/>-->
<add name="MSSQLBlogProvider" type="BlogEngine.Core.Providers.MSSQLBlogProvider"/>
</providers>
</blogProvider>
</BlogEngine>

<!-- my local SQL2005 Epxress database server -->
<connectionStrings>
<add name="BlogEngineDB" connectionString="Data Source=LOCALHOST\SQLEXPRESS;User ID=beuser;Password=beuser;persist security info=False;initial catalog=blogengine;" providerName="System.Data.SqlClient"/>
</connectionStrings>

<appSettings>
<add key="SecurityKey" value="thequickbrownfoxjumpedov"/>
</appSettings>

<system.web>

<compilation debug="true" />
<customErrors mode="Off" />
<globalization requestEncoding="utf-8" responseEncoding="utf-8" />
<httpRuntime enableVersionHeader="false" sendCacheControlHeader="true" />
<trust level="High" />

<!-- entered my own machine key, you can use the default machineKey below however -->
<machineKey
validationKey="D9F7287EFDE8DF4CAFF79011D5308643D8F62AE10CDF30DAB640B7399BF6C57B0269D60A23FBCCC736FC2487ED695512BA95044DE4C58DC02C2BA0C4A266454C"
decryptionKey="BDAAF7E00B69BA47B37EEAC328929A06A6647D4C89FED3A7D5C52B12B23680F4"
validation="SHA1" decryption="AES"
/>
<!--<machineKey
validationKey=
"7E90CE9426A34585CE01489FC93E693522E6945E08694CB782E9B3A6FA5653C0619C6F628BBE8CC6D01C69B067644FF8339DD15FBFD2AA913692D8B69E04BCBF"
decryptionKey=
"4AE6AE7D46FFF6B02E0592D1550B0DF46CEC4CD52E771BCAE020E5C434A57A8D"
validation="SHA1"
decryption="AES"/>-->


<authentication mode="Forms">
<forms timeout="129600" name=".AUXBLOGENGINE" protection="All" slidingExpiration="true" loginUrl="~/login.aspx" />
</authentication>

<pages enableSessionState="false" enableViewStateMac="true" enableEventValidation="true">
<controls>
<add namespace="Controls" tagPrefix="blog"/>
</controls>
</pages>

<!-- Left the XmlMembershipProvider incase you want to switch back to the XML datastore -->
<!--<membership defaultProvider="XmlMembershipProvider">
<providers>
<clear />
<add name="XmlMembershipProvider" type="BlogEngine.Core.Providers.XmlMembershipProvider" description="XML membership provider" xmlFileName="~/App_Data/users.xml"/>
</providers>
</membership>-->

<!-- Ensure passwordFormat is set to clear, we using clear as the code-behind in the Provider class takes
care of encrypting and decrypting the password. Password is always encrypted when stored in the database -->
<membership defaultProvider="MSSQLMembershipProvider">
<providers>
<clear />
<add name="MSSQLMembershipProvider"
type="BlogEngine.Core.Providers.MSSQLMembershipProvider"
description="Sql membership provider"
connectionStringName="BlogEngineDB"
passwordFormat="Clear" />
</providers>
</membership>

<!-- Use this Provider when using the ASP.NET Configuration web tool to setup your default user
You will need to disable the MSSQLMembershipProvider when enabling the SqlProvider.
Once your initial user is setup, disable this provider and re-enable the MSSQLMembershipProvider
-->
<!--<membership defaultProvider="SqlProvider"
userIsOnlineTimeWindow="15">
<providers>
<add
name="SqlProvider"
type="System.Web.Security.SqlMembershipProvider"
connectionStringName="BlogEngineDB"
applicationName="BlogEngine.NET"
passwordFormat="Hashed"
minRequiredPasswordLength="5"
minRequiredNonalphanumericCharacters="0"
passwordStrengthRegularExpression=""
requiresUniqueEmail="false"
requiresQuestionAndAnswer="true"
enablePasswordReset="true"
enablePasswordRetrieval="false" />
</providers>
</membership>-->

<httpModules>
<add name="UrlRewrite" type="BlogEngine.Core.Web.HttpModules.UrlRewrite"/>
<add name="CompressionModule" type="BlogEngine.Core.Web.HttpModules.CompressionModule"/>
<add name="ReferrerModule" type="BlogEngine.Core.Web.HttpModules.ReferrerModule"/>
</httpModules>

<httpHandlers>
<add verb="*" path="microsummary.axd" type="BlogEngine.Core.Web.HttpHandlers.MicroSummary" validate="false"/>
<add verb="*" path="file.axd" type="BlogEngine.Core.Web.HttpHandlers.FileHandler" validate="false"/>
<add verb="*" path="image.axd" type="BlogEngine.Core.Web.HttpHandlers.ImageHandler" validate="false"/>
<add verb="*" path="syndication.axd" type="BlogEngine.Core.Web.HttpHandlers.SyndicationHandler" validate="false"/>
<add verb="*" path="commentfeed.axd" type="BlogEngine.Core.Web.HttpHandlers.CommentFeedHandler" validate="false"/>
<add verb="*" path="sitemap.axd" type="BlogEngine.Core.Web.HttpHandlers.Sitemap" validate="false"/>
<add verb="*" path="trackback.axd" type="BlogEngine.Core.Web.HttpHandlers.TrackbackHandler" validate="false"/>
<add verb="*" path="pingback.axd" type="BlogEngine.Core.Web.HttpHandlers.PingbackHandler" validate="false"/>
<add verb="*" path="opensearch.axd" type="BlogEngine.Core.Web.HttpHandlers.OpenSearchHandler" validate="false"/>
<add verb="*" path="metaweblog.axd" type="BlogEngine.Core.API.MetaWeblog.MetaWeblogHandler" validate="false"/>
<add verb="*" path="rsd.axd" type="BlogEngine.Core.Web.HttpHandlers.RsdHandler" validate="false"/>
<add verb="*" path="css.axd" type="BlogEngine.Core.Web.HttpHandlers.CssHandler" validate="false"/>
<add verb="*" path="rating.axd" type="BlogEngine.Core.Web.HttpHandlers.RatingHandler" validate="false"/>
<add verb="*" path="opml.axd" type="BlogEngine.Core.Web.HttpHandlers.OpmlHandler" validate="false"/>
</httpHandlers>

</system.web>
</configuration>
Jul 5, 2007 at 10:57 PM
You will see the connection string above:

connectionString="Data Source=LOCALHOST\SQLEXPRESS;User ID=beuser;Password=beuser;persist security info=False;initial catalog=blogengine;" providerName="System.Data.SqlClient"

You need to enter this into the settings table in your database for the mssqlconnection element.

The web.config above disables XmlProvider and XmlMembersipProvider, replacing them with the MSSQLBlogProvider and a custom MSSQLMembershipProvider I created. This class was created by using the XmlMembershipProvider class as a template, inheriting from SqlMembershipProvider so we can override some of the features.
Jul 5, 2007 at 11:00 PM
In the BlogEngine.Core project, underneath Providers, create a new class called MSSQLMembershipProvider and replace the contents with the following:

#region Using

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Configuration.Provider;
using System.Web.Security;
using System.Web.Hosting;
using System.Web.Management;
using System.Security.Permissions;
using System.Web;
using System.Web.Configuration;
using System.Text;
using System.Security.Cryptography;
using System.Data.SqlClient;
using System.Data.SqlTypes;

#endregion

namespace BlogEngine.Core.Providers
{
/// <summary>
/// An MSSQL Custom Provider implementation based on the BlogEngine.NET Membership Provider class
/// </summary>
public class MSSQLMembershipProvider : SqlMembershipProvider
{
private Dictionary<string, MembershipUser> _Users;
private SqlConnection providerConn;
private MachineKeySection machineKey;
private MembershipPasswordFormat pPasswordFormat = MembershipPasswordFormat.Clear;

#region Properties

// MembershipProvider Properties
// TODO: get/set application name
/// <summary>
///
/// </summary>
public override string ApplicationName
{
get { throw new NotSupportedException(); }
set { throw new NotSupportedException(); }
}

/// <summary>
///
/// </summary>
public override bool EnablePasswordRetrieval
{
get { return false; }
}

/// <summary>
///
/// </summary>
public override bool EnablePasswordReset
{
get { return false; }
}

/// <summary>
///
/// </summary>
public override int MaxInvalidPasswordAttempts
{
get { return 5; }
}

/// <summary>
///
/// </summary>
public override int MinRequiredNonAlphanumericCharacters
{
get { return 0; }
}

/// <summary>
///
/// </summary>
public override int MinRequiredPasswordLength
{
get { return 8; }
}

/// <summary>
///
/// </summary>
public override int PasswordAttemptWindow
{
get { throw new NotSupportedException(); }
}

/// <summary>
///
/// </summary>
/*public override MembershipPasswordFormat PasswordFormat
{
get { return MembershipPasswordFormat.Clear; }
}*/
public override MembershipPasswordFormat PasswordFormat
{
get { return pPasswordFormat; }
}

/// <summary>
///
/// </summary>
public override string PasswordStrengthRegularExpression
{
get { throw new NotSupportedException(); }
}

/// <summary>
///
/// </summary>
public override bool RequiresQuestionAndAnswer
{
get { return false; }
}

/// <summary>
///
/// </summary>
public override bool RequiresUniqueEmail
{
get { return false; }
}

#endregion

#region Supported methods

/// <summary>
/// Build the Configuration Collection
/// </summary>
/// <param name="name"></param>
/// <param name="config"></param>
public override void Initialize(string name, NameValueCollection config)
{
if (config == null)
throw new ArgumentNullException("config");

if (String.IsNullOrEmpty(name))
name = "MSSQLMembershipProvider";

if (string.IsNullOrEmpty(config"description"))
{
config.Remove("description");
config.Add("description", "SQL membership provider");
}

if (string.IsNullOrEmpty(config"passwordFormat"))
{
config.Remove("passwordFormat");
config.Add("passwordFormat", "Encrypted");
}

base.Initialize(name, config);

// Get encryption and decryption key information from the configuration.
Configuration cfg =
WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
machineKey = (MachineKeySection)cfg.GetSection("system.web/machineKey");

// Throw an exception if unrecognized attributes remain
if (config.Count > 0)
{
string attr = config.GetKey(0);
if (!String.IsNullOrEmpty(attr))
throw new ProviderException("Unrecognized attribute: " + attr);
}
}

/// <summary>
/// Returns true if the username and password match an exsisting user.
/// </summary>
public override bool ValidateUser(string username, string password)
{
if (String.IsNullOrEmpty(username) || String.IsNullOrEmpty(password))
return false;

try
{
ReadMembershipDataStore();

// Validate the user name and password
MembershipUser user;

if (_Users.TryGetValue(username, out user))
{
// call CheckPassword helper function to validate password
if (CheckPassword(password, user.Comment))
{
// check if user is approved
// update the date/time stamps
return true;
}
}

return false;
}
catch (Exception)
{
return false;
}
}

/// <summary>
/// Retrieves a user based on his/hers username.
/// the userIsOnline parameter is ignored.
/// </summary>
public override MembershipUser GetUser(string username, bool userIsOnline)
{
if (String.IsNullOrEmpty(username))
return null;

ReadMembershipDataStore();

// Retrieve the user from the data source
MembershipUser user;
if (_Users.TryGetValue(username, out user))
return user;

return null;
}

/// <summary>
/// Retrieves a collection of all the users.
/// This implementation ignores pageIndex and pageSize,
/// and it doesn't sort the MembershipUser objects returned.
/// </summary>
public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
{
ReadMembershipDataStore();
MembershipUserCollection users = new MembershipUserCollection();

foreach (KeyValuePair<string, MembershipUser> pair in _Users)
{
users.Add(pair.Value);
}

totalRecords = users.Count;
return users;
}

/// <summary>
/// Creates a new user store he/she in the database
/// </summary>
public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out MembershipCreateStatus status)
{
/*status = MembershipCreateStatus.Success;
MembershipUser user = new MembershipUser(Name, username, username, email, passwordQuestion, password, isApproved, false, DateTime.Now, DateTime.Now, DateTime.Now, DateTime.Now, DateTime.MaxValue);
_Users.Add(username, user);
return user;*/

string encPassword;

// validate the password
if (password == null)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
else
{
// encrypt the password
try
{
encPassword = Encrypt(password, true);
password = encPassword;
status = MembershipCreateStatus.Success;
}
catch
{
// write error to screen or to a log file?
status = MembershipCreateStatus.ProviderError;
return null;
}
}

// check if we have a providerkey, if not then generate one
if (providerUserKey == null)
{
providerUserKey = Guid.NewGuid();
}
else
{
if (!(providerUserKey is Guid))
{
status = MembershipCreateStatus.InvalidProviderUserKey;
return null;
}
}
Jul 5, 2007 at 11:00 PM
Edited Jul 5, 2007 at 11:44 PM
// try creating the user
if (status == MembershipCreateStatus.Success)
{
try
{
// create the new user
MembershipUser user = new MembershipUser(Name, username, providerUserKey, email, passwordQuestion, password, isApproved, false, DateTime.Now, DateTime.Now, DateTime.Now, DateTime.Now, DateTime.MaxValue);
_Users.Add(username, user);
return user;
}
catch
{
// log or display error
status = MembershipCreateStatus.ProviderError;
}
finally
{
status = MembershipCreateStatus.Success;
}
}
else
{
status = MembershipCreateStatus.UserRejected;
}

return null;
}

/// <summary>
/// Changes a users password.
/// </summary>
public override bool ChangePassword(string username, string oldPassword, string newPassword)
{
// not implemented yet
return false;
}

/// <summary>
/// Deletes the user from the database
/// removes him/her from the internal cache.
/// </summary>
public override bool DeleteUser(string username, bool deleteAllRelatedData)
{
// not implemented yet
return false;
}

/// <summary>
/// Get a user based on the username parameter.
/// the userIsOnline parameter is ignored.
/// </summary>
public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
{
// not implemented yet
return default(MembershipUser);
}

/// <summary>
/// Retrieves a username based on a matching email.
/// </summary>
public override string GetUserNameByEmail(string email)
{
// not implemented yet
return null;
}

/// <summary>
/// Updates a user. The username will not be changed.
/// </summary>
public override void UpdateUser(MembershipUser user)
{
// not implemented yet
}

#endregion

#region Helper methods

//
// A helper function to retrieve config values from the configuration file.
//

private string GetConfigValue(string configValue, string defaultValue)
{
if (String.IsNullOrEmpty(configValue))
return defaultValue;

return configValue;
}

// check password
private bool CheckPassword(string password, string dbpassword)
{
string pass1 = password; // user input
string pass2 = dbpassword; // db stored password

// encrypt the password and then compare it to the stored password
try
{
pass1 = Encrypt(password, true);
}
catch
{
// log this
return false;
}

if (pass1 == pass2)
{
return true;
}

return false;
}


#region crypto functions
/// <summary>
/// function to encrypt data
/// </summary>
public static string Encrypt(string toEncrypt, bool useHashing)
{
byte[] keyArray;
byte[] toEncryptArray = UTF8Encoding.UTF8.GetBytes(toEncrypt);

System.Configuration.AppSettingsReader settingsReader = new AppSettingsReader();
// Get the key from config file

string key = (string)settingsReader.GetValue("SecurityKey", typeof(String));
//System.Windows.Forms.MessageBox.Show(key);
//If hashing use get hashcode regards to your key
if (useHashing)
{
MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
//Always release the resources and flush data of the Cryptographic service provide. Best Practice

hashmd5.Clear();
}
else
keyArray = UTF8Encoding.UTF8.GetBytes(key);

TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
//set the secret key for the tripleDES algorithm
tdes.Key = keyArray;
//mode of operation. there are other 4 modes. We choose ECB(Electronic code Book)
tdes.Mode = CipherMode.ECB;
//padding mode(if any extra byte added)

tdes.Padding = PaddingMode.PKCS7;

ICryptoTransform cTransform = tdes.CreateEncryptor();
//transform the specified region of bytes array to resultArray
byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);
//Release resources held by TripleDes Encryptor
tdes.Clear();
//Return the encrypted data into unreadable string format
return Convert.ToBase64String(resultArray, 0, resultArray.Length);
}

/// <summary>
/// function to decrypt a phrase
/// </summary>
public static string Decrypt(string cipherString, bool useHashing)
{
byte[] keyArray;
//get the byte code of the string

byte[] toEncryptArray = Convert.FromBase64String(cipherString);

System.Configuration.AppSettingsReader settingsReader = new AppSettingsReader();
//Get your key from config file to open the lock!
string key = (string)settingsReader.GetValue("SecurityKey", typeof(String));

if (useHashing)
{
//if hashing was used get the hash code with regards to your key
MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
//release any resource held by the MD5CryptoServiceProvider

hashmd5.Clear();
}
else
{
//if hashing was not implemented get the byte code of the key
keyArray = UTF8Encoding.UTF8.GetBytes(key);
}

TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
//set the secret key for the tripleDES algorithm
tdes.Key = keyArray;
//mode of operation. there are other 4 modes. We choose ECB(Electronic code Book)

tdes.Mode = CipherMode.ECB;
//padding mode(if any extra byte added)
tdes.Padding = PaddingMode.PKCS7;

ICryptoTransform cTransform = tdes.CreateDecryptor();
byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);
//Release resources held by TripleDes Encryptor
tdes.Clear();
//return the Clear decrypted TEXT
return UTF8Encoding.UTF8.GetString(resultArray);
}

//
// HexToByte
// Converts a hexadecimal string to a byte array. Used to convert encryption
// key values from the configuration.
//
private byte[] HexToByte(string hexString)
{
byte[] returnBytes = new bytehexString.Length / 2;
for (int i = 0; i < returnBytes.Length; i++)
returnBytesi = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
return returnBytes;
}

#endregion

/// <summary>
/// Handles Opening the SQL Connection
/// </summary>
private bool OpenConnection()
{
bool result = false;

// Initial if needed
if (providerConn == null)
providerConn = new SqlConnection(ConfigurationManager.ConnectionStrings"BlogEngineDB".ConnectionString);
// Open it if needed
if (providerConn.State == System.Data.ConnectionState.Closed)
{
result = true;
providerConn.Open();
}

return result;
}
Jul 5, 2007 at 11:01 PM
/// <summary>
/// Builds the internal cache of users.
/// </summary>
private void ReadMembershipDataStore()
{
lock (this)
{
if (_Users == null)
{
_Users = new Dictionary<string, MembershipUser>(16, StringComparer.InvariantCultureIgnoreCase);

// connect to the SQL database and retreive the membership data for each user record
bool connClose = OpenConnection();
string sqlQuery = "SELECT u.UserId, u.UserName, m.Email, m.PasswordQuestion,m.Password, m.IsApproved, m.IsLockedOut, m.CreateDate,m.LastLoginDate, u.LastActivityDate, u.LastActivityDate,m.LastPasswordChangedDate, m.LastLockoutDate FROM aspnetUsers u INNER JOIN aspnetMembership m ON m.UserId = u.UserId";
SqlCommand cmd = new SqlCommand(sqlQuery, providerConn);
SqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{

MembershipUser user = new MembershipUser(
Name, // Provider name
rdr.GetString(1), // Username
rdr.GetGuid(0), // providerUserKey
rdr.GetString(2), // Email
rdr.GetString(3), // passwordQuestion
rdr.GetString(4), // using comment to store the password, how dumb is that!
rdr.GetBoolean(5), // isApproved
rdr.GetBoolean(6), // isLockedOut
rdr.GetDateTime(7), // creationDate
rdr.GetDateTime(8), // lastLoginDate
rdr.GetDateTime(9), // lastActivityDate
rdr.GetDateTime(10), // lastPasswordChangedDate
rdr.GetDateTime(11) // LastLockoutDate
);

_Users.Add(user.UserName, user);
}
}
}
}

///// <summary>
///// Encrypts a string using the SHA256 algorithm.
///// </summary>
//private static string Encrypt(string plainMessage)
//{
// byte[] data = Encoding.UTF8.GetBytes(plainMessage);
// using (HashAlgorithm sha = new SHA256Managed())
// {
// byte[] encryptedBytes = sha.TransformFinalBlock(data, 0, data.Length);
// return Convert.ToBase64String(sha.Hash);
// }
//}

#endregion

#region Unsupported methods

/// <summary>
///
/// </summary>
/// <param name="username"></param>
/// <param name="answer"></param>
/// <returns></returns>
public override string ResetPassword(string username, string answer)
{
throw new NotSupportedException();
}

/// <summary>
///
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public override bool UnlockUser(string userName)
{
throw new NotSupportedException();
}

/// <summary>
///
/// </summary>
/// <param name="emailToMatch"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalRecords"></param>
/// <returns></returns>
public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
{
throw new NotSupportedException();
}

/// <summary>
///
/// </summary>
/// <param name="usernameToMatch"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalRecords"></param>
/// <returns></returns>
public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
{
throw new NotSupportedException();
}

/// <summary>
///
/// </summary>
/// <returns></returns>
public override int GetNumberOfUsersOnline()
{
throw new NotSupportedException();
}

/// <summary>
///
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <param name="newPasswordQuestion"></param>
/// <param name="newPasswordAnswer"></param>
/// <returns></returns>
public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPasswordQuestion, string newPasswordAnswer)
{
throw new NotSupportedException();
}

/// <summary>
///
/// </summary>
/// <param name="username"></param>
/// <param name="answer"></param>
/// <returns></returns>
public override string GetPassword(string username, string answer)
{
throw new NotSupportedException();
}

#endregion
}
}
Jul 5, 2007 at 11:36 PM
I am hoping this discussion engine doesn't garble the above code!

Paste in all three parts above in your new MSSQLMembershipProvider class and then save the file (VS should auto format the contents). Its essentially the same as the XmlMembershipProvider class however I have changed it slightly to deal with the SQL Database, namely the datastore method is different and I have changed the way the user validation works.

I had trouble implementing the built-in SQLMembershp password encryption/decryption/hashing as I was getting some random results so I ditched the Microsoft way and implemented my own version which encrypts passwords securely and decrypts them correctly. The crypto functions are provided to get around the password encryption problem, instead of relying on the SqlProvider I decided to go the easier route and just have the application treat passwords as plain text.

That way when a new user is created, their password is initially in plain-text however I created a custom method of CreateUser which encrypts the input password and then stores this encrypted password into the database. Result? Allows the password to be decrypted properly meaning the validation process works as it should now.

There are some caveats with the above which I will explain now. Firstly to get this code working you will need to do the following:

1. Get your database setup with the new Sql Membership Schema
2. Modify your web.confg temporarily to enable you to create your first default user

Locate the web.config file and review the Provider sections, I have commented what you need to do, simple enough. Comment out the custom MSSQLProvider and uncomment the SqlProvider provider - you need to do this in order to be able to uset the ASP.NET Configuration web tool that we need to initially setup our database.

Now you need to build the new schema into your database:

1. Open Visual Studio Command Prompt (START > Programs > Visual Studio.NET 2005 > Visual Studio Command Prompt)
2. Type the following:

aspnet_regsql.exe

3. This will start up a dialog box advising you its going to setup the SqlMembership Schema for your database
4. Click Next
5. Choose the default option Configure SQL Server for application services
6. You will see a screen asking for your Server name, login details and database
7. Enter your SQLEXPRESS database server name e.g. LOCALHOST\SQLEXPRESS (or whatever you called your SQL Server Instance)
8. Choose SQL Server Authentication
9. Enter your beuser login details
10. Click on the drop down list, if the connection worked you should see blogengine listed, select it to continue

now this process can fail a few times so you need to repeat the above until it works properly, common problems are with the format of the Server name

You should see some messages indicating the schema's where created, click Next/Close to exit. Thats your database schema setup now.

1. Now you need to go to Visual Studio.NET 2005 with the 3314 source open and then click on the BlogEngine.NET project (set this as the default project)
2. Click on Website > ASP.NET Configuration to launch the built-in web server and site configuration
3. You will see a IE page displayed with 3 options
4. Click on Security to display the security page and then click on Create User
5. Enter your default user details e.g. Administrator / <password> etc and create the new user
6. Close the ASP.NET Configuration web tool

Nearly done, your default user is now setup and your site is almost ready to handle everything via the SQL database. Unfortunately I haven't had the time to sort this bit out so its bit of a pain to do. Make sure you site builds correctly with the above changes in place.

1. In SQL Managment Studio, open the table aspnet_Membership (right-click > Open Table)
2. You will see one row and a colum labelled Password with an encrypted password in there, we need to edit this shortly
3. In Visual Studio.NET 2005 open the file MSSQLMembershipProvider and create a break point at line 391 (CheckPassword function)
4. Make sure BlogEngine.NET is your startup project, click on Debug to start debugging
5. Once your BlogEngine app is running, go to the login page (login.aspx)
6. Enter the user details you created earlier in the ASP.NET Configuration tool and hit login
7. The app will break at line 391 (private bool CheckPassword.....)
8. In your Watch List window create a watch for the following items:

pass1
pass2

9. Step through the code, you will see the code going through into the Encrypt function and returning back to the CheckPassword function
10. the string var pass1 will now have the password you entered in Encrypted format
11. Now for the crap bit - in your watch list, double-click the pass1 value and copy it without the " quotes
12. In your SQL Management Studio window, paste this encrypted string into the Password field and then press enter to commit the change
13. You will notice in your watch list the database encrypted password is different to the encrypted password, you can inject new values in here (the fun of debugging)
14. Double-Click the watch item pass2 and copy its value
15. Paste this value into the watch item pass1
16. Now hit run on the debug toolbar

Hey presto your logged in!

Create a new user now from the users page, this bit works fine as I have ensured when a new user is created, the CreateUser method encrypts the password and then stores this in the database in encrypted format (hence why we leave the PasswordFormat as Clear text).

In SQL Management Studio Express, if you close the datagrid and re-open the aspnetMembership table, you will see two rows now, one for the default user created earlier and a new row with your new user you just created. You can delete the default user if you want to now. You will also need to open the table *aspnetUsers* and delete the corresponding row in there.

One more thing, I just noticed I pasted the wrong CheckPassword function, the correct one is:

private bool CheckPassword(string password, string dbpassword)
{
string pass1 = password; // user input
string pass2 = dbpassword; // db stored password

// encrypt the password and then compare it to the stored password
try
{
pass1 = Encrypt(password, true);
}
catch
{
// log this
return false;
}

if (pass1 == pass2)
{
return true;
}

return false;
}

TODO:

1. Automate the database/user setup process
2. Move any/all SQL commands into SQL Stored Procedures
3. Future Security Feature: User SQL 2005's built-in encrypt/decrypt function instead of the code-behind to handle the passwords
4. Finish off the MSSQLMembershipProvider class implementing all the unsupported features
5. Find out why the hell the MSDN example to encrypt/decrypt using SqlMembership is a pile of poo

Its a bit code kung-fu but I only just got it working properly, gonna clean it up some more soon


Oct 31, 2007 at 6:16 PM
Edited Oct 31, 2007 at 6:17 PM
I do not understand. Why no to just change memebership provider in web.config to standard SQL Provider????
Please explain

I do not worry about migration of existing data.

Also what groups do I need to create?