Configuring Your SQLExpress Membership Provider
Prologue
In the process of writing the last series of posts to create ‘A Better ASP.Net Member/Role Management Page’ (here’s a link to the 1st part), I used a SQL Express database located in my App_data folder. This was the first time I’ve used one. I have access to a full blown external SQL Server for development (since that’s what I’ll be doing in production). Due to my lack of experience with this form of database, I had a heck of time figuring out how to configure my membership provider. In part 4 of the series, I looked at enabling/disabling some functionality of the page based on how the Membership provider was configured. Obviously, to check that the functionality of the page is working correctly, I needed to change the configuration of the provider. Therein lied the problem.
Problem
When I created my database in the App_Data folder for this project, VS.Net 2008 did a lot of the work for me. While this isn’t bad, what I found is that I had a dickens of time figuring out how to get my Membership provider configured to use something besides the default settings. So I thought I’d create an entry on how to do it since I had a tough time with it.
We’ll start from the very beginning (i.e. from scratch) for this post, but the basic problem is that when VS.Net does all the work, the web.config does NOT contain any membership provider configuration information, so it isn’t readily apparent how to change the provider’s configuration. Once we figure out what’s going on and how to rectify it, it actually becomes pretty easy though. (Note, giving credit where credit is due, I got a lot of information from this site, but have pared it down to the very basics).
Solution
First off, let’s start with a new website. We only need one page in the site, it comes with a default.aspx and we’ll just use that. We’ll begin by setting up the page to display a couple things. Add the following to your front-end code:
<div>
Configuration: <br />
<asp:TextBox ID="txtCurrentConfig" runat="server"
TextMode="MultiLine" Width="400" Height="200" />
<br /><br />
Members:<br />
<asp:GridView ID="gvMembers" runat="server">
</asp:GridView>
</div>
We’re creating a TextBox to hold some configuration information that we’ll retrieve in our back-end, and then a GridView that we’ll use to pull members from the database. This will help us to see if we’ve done something horribly wrong ;-).
We’ll need also to create our database before we can pull any membership from it. Right-click your App_Data folder and select ‘Add New Item…’ From this list, you want to select SQL Server Database. Name it ‘aspnetdb.mdf’ and click ‘Add’. Now we should have a database file in our App_Data folder. If you go to the Server Explorer panel, you can open the database, you’ll notice that it doesn’t have ANY tables in it, including the membership and roles tables that we want to be able to access. We need to add these.
Adding the membership and roles information basically amounts to running the aspnet_regsql.exe against our database and the the functionality will be added to the database. The article I referenced above has a great walkthrough on this, however I found that if I ran the aspnet_regsql tool in GUI mode, somehow the SQL service on my machine didn’t have rights to access the DB file (as it was in my My Documents folder under the VS 2008 folder, and somehow the GUI – despite being run under my account – couldn’t see my profile). Also, I couldn’t get my SQL Server to attach to the file for the same reason. What I did find that worked though, was an article that detailed how to run aspnet_regsql by command line (hey, and the article’s got a classy color scheme too ;-) ). This is how I’ll do our database. Open a command prompt and run the following:
aspnet_regsql -A all -C "Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True" -d "C:\<Filepath>\APP_DATA\aspnetdb.mdf"
You should notice that if you have the filepath to your database file correct, it will add a few a few different things to your database and then report it is finished. If you refresh your database in the Server Explorer, you’ll see that now we have some tables (and stored procedures). We’re ready to do some stuff with our membership provider now.
Next, we’ll add a user to our database so that we can do some testing using our GridView. We’ll do it the easy way, open the Website Administration Tool by clicking the button on the toolbar in your Solution explorer. Once the page comes up, Click the ‘Security’ link. Click the link to ‘Select Authentication Type’, and we’ll set it to forms authentication. Select ‘From the internet’ and click ‘Done’. Also, we’ll click the link to ‘Enable Roles’. Now, let’s add a user to our database, click the ‘Create User’ link. On the next screen, let’s create a user, just to prove a point, use ‘password’ for the password. When you click ‘Create User’. You’ll notice that the user creation fails because we don’t have a non-alphanumeric character in the password. Go ahead and create a new password that meets the criteria and then create the user.
Ok, now we’ll add some back-end code so we can see what’s happening with our provider. Add the following to your page_load event handler:
gvMembers.DataSource = Membership.GetAllUsers()
gvMembers.DataBind()
txtCurrentConfig.Text = "Password format:" & Membership.Provider.PasswordFormat
What we’re doing is retrieving all our members, and binding them to the GridView. We also pull the PasswordFormat specified for the membership provider and display that in our textbox. If you run your page, you’ll see that we successfully pull our lone member into the GridView, and that our PasswordFormat is 1. This PasswordFormat correlates with the enumeration for PasswordFormats that is defined in the .Net Framework as follows (ok, I’m interpreting what they’ve done but it still stands):
Enum MembershipPasswordFormat
Clear = 0
Encrypted = 2
Hashed = 1
End Enum
You can see that the 1 indicates that we have Hashed passwords. Herein lies our dilemma (yes, we’re finally to it). Let’s say we wanted to use Encrypted passwords instead? (I needed to change this so I could test the different PasswordFormats against the code in the aforementioned post). Normally, we’d just go and change the Membership provider’s details in the web.config. So let’s go do it. Go look in your web.config for the membership provider’s definition… go on, I’ll wait. Haven’t found it? Keep looking. While you’re at it, see if you can find the connectionString for the membership database… Can’t find it either? There must be one, otherwise how would it connect to the database? You’re right, we’ve mysteriously created a provider we don’t know how to configure.
The solution lies in this: our Machine.config has a default membership provider defined. When we created our membership stuff, this default provider is used by… default. We have to override this if we want to customize it. So how do we do this? First, let’s open our machine.config and see if we can find our Membership provider’s definition there. The machine.config can be found at C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config (assuming you’re %system% folder is Windows). Search down and you’ll find that there is in fact a provider defined here:
<membership>
<providers>
<add name="AspNetSqlMembershipProvider"
type="System.Web.Security.SqlMembershipProvider, System.Web,
Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a"
connectionStringName="LocalSqlServer"
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="true"
applicationName="/"
requiresUniqueEmail="false"
passwordFormat="Hashed"
maxInvalidPasswordAttempts="5"
minRequiredPasswordLength="7"
minRequiredNonalphanumericCharacters="1"
passwordAttemptWindow="10"
passwordStrengthRegularExpression=""/>
</providers>
</membership>
There it is. The current definition of our membership provider. So how do we override it? Simple, we need to define a new one in our web.config code. Somewhere inside your system.web section of the web.config, add a membership provider section and define a new provider as such:
<membership>
<providers>
<add name="myCustomProvider"
type="System.Web.Security.SqlMembershipProvider, System.Web,
Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a"
passwordFormat="Encrypted"
connectionStringName="LocalSqlServer" />
</providers>
</membership>
Notice that we changed our passwordFormat to be Encrypted (so when we run the application, our format should now show as 2. How do we know what connectionStringName to use? We’ll, for one we could just use the same string as the one defined in the provider in the machine.config, but we can also look through the machine.config and see that we have a connectionString defined there too.
<connectionStrings>
<add name="LocalSqlServer"
connectionString="data source=.\SQLEXPRESS;Integrated
Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User
Instance=true"
providerName="System.Data.SqlClient"/>
</connectionStrings>
You’ll see, that the default connectionString setup in the machine.config is ‘LocalSqlServer’. We could redefine it, but this will work just fine for us. So run your application and see what we get. The member binds fine to the GridView, but we’re still showing that our password is hashed (1). What’s the deal? Well, what’s happening, is we’re still using the default provider in the machine.config. We need to specify that we want to use ours instead. to do this, modify your membership element opening tag with the following attribute addition:
<membership defaultProvider="myCustomProvider">
Here we define that OUR provider should be the default provider. We also can take an additional step recommended my many people on the internet, and that is to remove the default provider from the machine.config. To do that, you can just add the clear element before your <add> provider element as follows:
<providers>
<clear/>
<add name=”…..
Alternatively, we could also remove the provider specifically by using the following in place of the <clear/> element:
<remove name="AspNetSqlMembershipProvider" />
We’re simply telling .Net in to remove the default provider (named AspNetSqlMembershipProvider). Now run your application again. Notice that our passwordFormat is now 2 – Encrypted. BUT, also notice that we no longer are successfully binding our GridView to the member we created. What gives? Add the following line to the end of your page_load event handler and we’ll see:
txtCurrentConfig.Text &= vbCrLf & "Application Name:" & _
Membership.Provider.ApplicationName
Now run your application again. Notice that the applicationName is the name of your website. If you look in your machine.config, what is the applicationName being used? Right, “/” or root. Effectively, by leaving out the applicationName in our membership provider definition, we’ve changed to a different application. Our user is assigned to the root application, and we’re pulling members from a different application (our website name). To fix that, just add the following to your provider’s definition:
applicationName="/"
Run your application again, and you’ll see that now we have our member back. Or better yet, we could create a unique application name for our application and start creating users in that instead. This helps to ensure that our users are unique to our application and not being reused from some other application that used the root application name.
Epilogue
Well, there it is. Now you can configure your SQLExpress membership provider as you desire. There are a load of other settings that we didn’t define in ours (as witnessed by the settings in the machine.config) and defining them is easy as adding the settings.
Funny how something so simple could cause me so much searching all over the internet looking for the solution. Honestly, it took me quite a long time to find the answer, I probably wasted 2 hours or more before I stumbled into the answer. Maybe I’m just dense, it and it should be common knowledge, but it wasn’t to me.
Anyhow, it’s there, and if either of us ever need it, we’ll know where to look for the answer.