Setting up the ASP.NET MVC Membership Starter Kit with SQL Server

I just got a chance to download and begin looking at the new ASP.NET MVC Membership bits from Troy Goode (www.squaredroot.com). The project is a starter kit that puts the standard ASPNETDB membership bits (the ones you can create with aspnet_regsql) in the MVC Framework.

 

When I first downloaded, I had a hard time getting things to work using his standard ASPNETDB instance when it was hooked up to SQL Server (not SQL Express). At first, I thought I was having a problem connecting to the database, but I created a little page called test.aspx and did the following:

 

namespace MvcMembership
{
    public partial class test : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            TryDatabaseConnection();
            TryMembership();
        }

        private void TryMembership()
        {
            try
            {
                Response.Write(string.Format("App name: {0}<br/>",Membership.Provider.ApplicationName));

                MembershipUser user = Membership.GetUser();
                Response.Write("Success on Membership<br/>");
            }
            catch (Exception ex)
            {
                Response.Write("Failed on Membership<br/>");
                Response.Write(string.Format("Error Type: {0}<br/>", ex.GetType().ToString()));
                Response.Write(string.Format("Message: {0}<br/>", ex.Message));
                Response.Write(string.Format("Source: {0}<br/>", ex.Source));
            }
        }

        private void TryDatabaseConnection()
        {
            string connString = ConfigurationManager.ConnectionStrings["ASPNETDBConnectionString"].ToString();
            SqlConnection connection = new SqlConnection(connString);

            try
            {
                connection.Open();
                Response.Write("Success on Database Connection<br/>");
            }
            catch (Exception ex)
            {
                Response.Write("Failed on Database Connection<br/>");
            }
            finally
            {
                connection.Dispose();
            }
        }
    }
}

I was able to connect to the database, but not the Membership bits. Rather than go through all of the steps, I will tell you what you need to do get this running quickly.

First, create your ASPNET Membership database. The easiest way to do this:

1. Open a VIsual Studio 2008 Comand Prompt
2. Type aspnet_regsql and hit enter
3. Click next
4. Keep the default "Configure SQL Server for application services" and click next
5. Choose the database and click next, next and then finish

If you like to be a maverick, the install scripts are also located at %windir%/Microsoft .Net/Framework/v2.0.50727. It does not really matter how you create the database.

You now need to go to the sample MVC Membership web application and edit the web.config. By default, it is implicit and only contains the following Membership bits.

    <connectionStrings>
        <add name="ASPNETDBConnectionString"
         connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"
         providerName="System.Data.SqlClient" />
    </connectionStrings>
…
<roleManager enabled="true" />

You need to fill this in. First, replace the connection string with the connection string to your database:

    <connectionStrings>
        <add name="ASPNETDBConnectionString"
         connectionString="Server=(local);Database=ASPNETDB;UID={Membership User};PWD={user password};"
         providerName="System.Data.SqlClient" />
    </connectionStrings>

You then need to fill in the implied bits with real bits. This means the <roleManager enabled="true" /> will become something like:

<membership userIsOnlineTimeWindow="20" defaultProvider="AspNetSqlProvider">
  <providers>
    <add connectionStringName="ASPNETDBConnectionString"
         minRequiredPasswordLength="8"
         minRequiredNonalphanumericCharacters="1"
         requiresQuestionAndAnswer="true"
         applicationName="/"
         passwordFormat="Encrypted"
         passwordAttemptWindow="10"
         enablePasswordReset="true"
         enablePasswordRetrieval="true"
         name="AspNetSqlProvider"
        type="System.Web.Security.SqlMembershipProvider" />
  </providers>
</membership>
<roleManager defaultProvider="RoleManagerProvider"
             enabled="true"
             cacheRolesInCookie="true"
             cookieName=".ASPROLES"
             cookieTimeout="30"
             cookiePath="/"
             cookieRequireSSL="false"
             cookieSlidingExpiration="true"
             cookieProtection="All">
  <providers>
    <add name="RoleManagerProvider"
         type="System.Web.Security.SqlRoleProvider"
         connectionStringName=" ASPNETDBConnectionString "
         applicationName="/" />
  </providers>
</roleManager>

Notice the highlighted section. It is important. The normal value here is hashed, but I want to be able to reverse engineer the passwords for admin purposes (a feature I will have to add to the MVC bits). To use encrypted, I will have to work with the machineKey section of the config. This looks like this:

    <machineKey validationKey="{Validation Key Here}"
        decryptionKey="{Decryption Key Here}"
        validation="3DES"/>

If you need to gen keys, you can use Ben Strackany’s key generator. You will need a Code Project account (free) to download the project:
http://www.codeproject.com/KB/aspnet/machineKey.aspx

If you do not have an account on Code Project, and don’t want one, Peter Bromberg has created a sample page that will create these keys, as long as you want to use SHA1 for validation (yes, you can alter this):
http://www.eggheadcafe.com/articles/20030514.asp

The final step is seeding the database. The easiest way is to open the web configuration tool. When it connects to the database, it will create a record in aspnet_applications for the / application. You will have to go through the wizards to create your administrator accounts.

I have also included a script for you to run, if you are not big on using the web configuration tool. If you use the script, you will have to set up the following machineKey section in your web.config, as the passwords are encrypted using these settings (NOTE the validation key is on two lines as spaces truncates things — you must fix that in your web.config when you copy and paste this):

<machineKey
validationKey=
"2AB9BB5084E7E8E34ECAA89E37B542E50D0C7E486D606804021812400A6E2EEDAF80F5E4
798B44EC71B0403F91D33B3208C170A34453B2A51A9EDA27839C6552"
decryptionKey="B2F58C16DEBB63B7CE25D5CD239CCDB0D71E160FE7FD65BC6CD1D0C98BAD29FA"
validation="SHA1" decryption="AES" />

Here is the script:

/***********************************************
*     ASP.NET MVC Application Seeder
*   ——————————————–
*     User Name:  Administrator
*     Password:   password!1
*
*     Security Question:
*     What is the velocity of an unladen sparrow?
*
*     Security Question Answer:
*     European or African?
***********************************************/

— Create the application
insert into aspnet_applications (ApplicationName
                                , LoweredApplicationName
                                , ApplicationId
                                , [Description])
values (‘/’
        , ‘/’
        , ‘3A168FD5-07E8-49FB-9C95-F8D15A9DA6E7’
        , ‘Default application for the sample ASP.NET MVC Applicatio’)
GO

— Create the administrator role
insert into aspnet_roles (ApplicationId
                                          , RoleId
                                          , RoleName
                                          , LoweredRoleName
                                          , [Description])
values (‘3A168FD5-07E8-49FB-9C95-F8D15A9DA6E7’
            , ‘072EA006-37E8-42DE-8FCA-BE790BF61BFA’
            , ‘Administrator’
            , ‘administrator’
            , ‘Administrator role for the sample ASP.NET MVC Applicatio’)
GO

— Create the Administrator user
insert into aspnet_users (ApplicationId
                                    , UserId
                                    , UserName
                                    , LoweredUserName
                                    , IsAnonymous
                                    , LastActivityDate)
values (‘3A168FD5-07E8-49FB-9C95-F8D15A9DA6E7’
            , ‘DE313B04-D60B-4E6B-B790-8F56FDB96C17’
            , ‘Administrator’
            , ‘administrator’
            , 0
            , GetUtcDate())
GO

— Create the user membership bits
insert into aspnet_membership(ApplicationId
                              , UserId
                              , [Password]
                              , PasswordFormat
                              , PasswordSalt
                              , MobilePIN
                              , Email
                              , LoweredEmail
                              , PasswordQuestion
                              , PasswordAnswer
                              , IsApproved
                              , IsLockedOut
                              , CreateDate
                              , LastLoginDate
                              , LastPasswordChangedDate
                              , LastLockoutDate
                              , FailedPasswordAttemptCount
                              , FailedPasswordAttemptWindowStart
                              , FailedPasswordAnswerAttemptCount
                              , FailedPasswordAnswerAttemptWindowStart
                              , Comment)
VALUES (‘3A168FD5-07E8-49FB-9C95-F8D15A9DA6E7’
            , ‘DE313B04-D60B-4E6B-B790-8F56FDB96C17’
    , ‘e4esufQBWoB7uWAAaCMH5TgeqPmozkIp+YAYDrq9NInu+1ovT+GWfuDjLZPMMAiT’
    , 2, ‘EzaAKgyIzgckUbbEzmW0lQ==’
    , NULL
    , ‘admin@company.com’
    , ‘admin@company.com’
    , ‘What is the velocity of an unladen sparrow?’
    , ‘e4esufQBWoB7uWAAaCMH5W3g/LF9xTjDpDr0yA88cwd53dNpxV8JGkzVZPQV0a+cxrJ1v+osHSg7RLqTRU4Vaw==’
    , 1
    , 0
    , GetUtcDate()
    , CAST(0x00009AB8015CEC03 AS DateTime)
    , CAST(0x00009AB8015CEBD4 AS DateTime)
    , CAST(0xFFFF2FB300000000 AS DateTime)
    , 0
    , CAST(0xFFFF2FB300000000 AS DateTime)
    , 0
    , CAST(0xFFFF2FB300000000 AS DateTime)
    , ‘Administrator for the sample ASP.NET MVC Application’)
GO

— Link the Administrator user to the Administrator role
insert into aspnet_usersinroles (UserId
                                                , RoleId)
values (‘DE313B04-D60B-4E6B-B790-8F56FDB96C17’
            , ‘072EA006-37E8-42DE-8FCA-BE790BF61BFA’)
GO

You should now be able to log in with the admin credentials (listed in the flower box at the top of the SQL script).

Beyond learning how to hook the ASP.NET MVC site into SQL Server Standard, Dev or Enterprise, you have learned how to fix membership bits. Just follow the steps in this blog post to fix an errant membership database. Here is the order of troubleshooting:

1. Can I connect to the database?
   If no, you have to fix the connection string.
2. Can I connect to membership? Things to check if not
   a) Do I have web.config set up correctly for membership?
   b) Do I have web.config set up correctly for roles? (if you are using roles)
   c) Do I have an application node in aspnet_membership?
   d) Am I using the correct machine keys?

Hope this helps!

Peace and Grace,
Greg