smithvoice.com
 Y'herd thisun? 

“Nothing is impossible on earth; one must only discover the means with which it can be carried out.”

from Ways of Spaceflight, 1928 (NASA F-622) by Hermann Oberth

ASP.Net Membership mdf setup without ASPNETDB.mdf

TaggedCoding, Database

It's true that Microsoft's ASP.Net Membership and Role security system isn't world-class bulletproof and has quite a few limitations so you'd not want to use it as-is for any project where money is involved, but for a lot of small projects it fits the bill fine.  

The only real problem with it (no flames please, I already stated that it has limitations <g>), is that for all of its history it has been a drag to set up.  Most of us get it into a project by banging on it, repeatedly getting that silly "automagic" ASPNETDB.mdf file autocreated, swearing a lot... then we forget about the pain till the next time.  

A decade after it was introduced, the web is still full of new posts and queries about how to get it rolling... with few of them being simple.

I just did my umpteenth setup and this time when my brain started saying 'next time I won't have to use this thing'... I figured I'd be smarter and write it down.  Also, because I am getting sick and tired of simple yes/no questions being answered by showing a stupid YouTube video, I decided that I won't even show a single darned image in these steps. Text only.  

Below are the steps to making a custom MDF file database attached with providers for Roles and Membership.  This is my usual way of starting development, to get the core down in local files that I can play with on the train and then I'll export to a real SQL Server instance when/if it's ready for growth. 

Why not just keep the ASPNETDB.mdf when you're exporting anyway?  You could, but personally I hate seeing that file name and from all the other posters over the years, I'm not alone.

These steps require SQL Express to be installed on your dev box (I'm using 12 but I tested on a 2008R2 also) because Express is needed for using attached mdfs.  I'm also showing with VS2012 Premium though at least Pro should work, I don't know about the Express versions of Visual Studio.  If you only have the free Express version of VS then don't yell at me if these steps don't fit your needs.

1) Start Visual Studio in admin mode (which should be your default all the time) and create your Web app.  I'm using an ASP.Net Empty Web Application so we can focus on the task without all the template baggage that the WebForms and MVC templates add by default.

2) In the Solution Explorer, right click on the project node and chose Add >> Add ASP.NET folder >> App_Data

3) Right click on the new App_Data folder in the Solution Explorer tree, and pick Add >> New Item.  When the New item form opens, click on the language of your project in the left hand tree (such as "Visual C#") and click on the "Data" subnode.  Select the "SQL Server Database" option, give the mdf a name that you like and press the Add button. The mdf file will be created in the App_Data folder.  

Note:  for this demo I'm creating a file named "svdemodb.mdf"

4) Save your solution and close Visual Studio (this helps release any locks on the file). Don't do anything else, just close VS down. The rote and temptation is to hit that Project>>Asp.Net Configuration but stop your fingers from that habit or you will create the dreaded ASPMETDB.mdf file.

5) Open Sql Server Management Studio. In the Connect to Server popup make sure that you're set to Server name = .\SQLEXPRESS with Windows Authentication.

6) In the left pane tree, open the Databases node and make sure that you don't see the mdf that you created in Visual Studio.  Most likely you won't see the new file already on a fresh project but if you're doing these steps repeatedly just for trying them out you might see one from a previous test that you'll want to use right-click >> Tasks >> Detach on to get back in step.

Right click on the Databases node and select "Attach" from the popup.

7) In the Attach Databases form, press the "Add" button and use the folder treeview to search for your mdf file.  

Selecting the mdf will put in the the Attach Databases form's top grid.  BEFORE YOU CLOSE THIS FORM, select the cell in "Attach As" column.  Sql Server will put a GUID string in this cell, but you will make your life easier by giving our file a friendly name.  Something short and unique-ish because it will have to be unique to your dev machine.  

For these steps I'm friendly-naming my file "secdemo2012". 

Exit the cell and confirm that your naming was accepted.  If so, close the form by clicking the Ok button at the bottom.

8) Close Sql Server Management Studio (this helps release any locks on the file).

9) Open a command line box (Run >> Command Prompt).  You can use the Admin command prompt option or use the Developer Command Prompt as admin in your Visual Studio install set if you're not signed on to Windows as an admin already.

10) Navigate to the latest full .net framework folder where an aspnet_regsql.exe file is located.  Right now "cd c:\windows\Microsoft.Net\Framework\v4.0.0313" gets you there.

11) Start the configuration app with the command "aspnet_regsql.exe".  Hit the "Next" button till it asks you if you want to Configure or Remove application services.  Choose the top option to "Configure SQLServer for Application Services" and press "Next."

12) At the "Select Server and Database" step your local machine name will likely be defaulted in the Server textbox.  Replace that value with ".\SQLEXPRESS"  (no quotes) which will automatically load the Database dropdown with the databases attached to your SQL Server Express instance and that list should include the friendly name that you gave to your attached mdf file.  Select that option.

If your friendly name is not listed, close the app and restart Management Studio to make sure the file is correctly attached then try again.

13) After selecting your friendly named database, press the "Next" button for the summary/confirm step and "Next" again to do the deed. In a couple of seconds you should be all set with the confirmation "The database has been created or modified".  Press "Finish" to close the app.

14) Restart Visual Studio and open your solution. 

You probably have the mdf file listed in the Data Connections node of your Server Explorer, I like to delete that at this point to prove that the next steps work but you can leave it if you want (but if you want to, right click and delete it from the Server Explorer > Data Connections, NOT the Solution Explorer > [Project] > App_Data folder.  You want to remove the connection, not the file).

I also tend to right click on the Solution Explorer > [Project] > App_Data folder and choose "Open Folder in File Explorer" to pop up a window of the folder so I can verify that no ASPNETDB.mdf file is created.

15) Open the project's Web.config file in the editor.  Paste this connectionStrings entry under the "configuration" start tag and above the system.web start tag:  

<connectionStrings>
    <add name="SecDemoConn" 
        connectionString="Integrated Security=SSPI;Data Source=.\SQLEXPRESS;Database=secdemo2012;" providerName="System.Data.SqlClient" />
</connectionStrings>

Notice that my example uses "SecDemoConn" as the connection string name.  Set your name="" to something you want for your project.

Notice that my example uses "secdemo2012" as the Database, that was the friendly name I set up in SQL Server Management Studio.  

Change your entry to the friendly name that you attached the file with.

16) Add the authentication, roleManager and membership nodes in your system.web section, under the default compilation and httpRuntime nodes.  To make it clear I'll show the complete web.config but be careful with your pasting:

<configuration>
    <connectionStrings>
        <add name="SecDemoConn" 
            connectionString="Integrated Security=SSPI;Data Source=.\SQLEXPRESS;Database=secuFriendly;" 
            providerName="System.Data.SqlClient" />
    </connectionStrings>
    <system.web>
    <compilation debug="true" targetFramework="4.5" />
    <httpRuntime targetFramework="4.5" />
    <authentication mode="Forms" />
    <roleManager enabled="true" defaultProvider="SecRoleProvider">
        <providers>
            <add name="SecRoleProvider" 
                type="System.Web.Security.SqlRoleProvider" 
                connectionStringName="SecDemoConn" applicationName="SecDemo" />
        </providers>
    </roleManager>
    <membership defaultProvider="SecProvider">
         <providers>
         <clear/>
         <add name="SecProvider" 
              type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" 
              connectionStringName="SecDemoConn" 
              enablePasswordRetrieval="false" 
              enablePasswordReset="true" 
              requiresQuestionAndAnswer="true" 
              applicationName="SecDemo" 
              requiresUniqueEmail="true" 
              passwordFormat="Hashed" 
              maxInvalidPasswordAttempts="5" 
              minRequiredPasswordLength="7" 
              minRequiredNonalphanumericCharacters="1" 
              passwordAttemptWindow="10" 
              passwordStrengthRegularExpression=""/>
          </providers>
      </membership>
    </system.web>
</configuration>

Ok.  Now you have to make some changes to that pasting.  

First, pick a name you like for the roleManager and set it in the defaultProvider and roleManager >> providers >> name= slots.  In other words replace the value "SecRoleProvider" in the two places you see it in my example.

Next, do the same type of thing with the membership defaultProvider and provider name. In the two places in my example that it says "SecProvider", pick a name you like and replace my value with yours. Don't use the exact same word that you used for the role provider name.

Replace the example applicationName attribute value "SecDemo" with a value of your choice.  Make sure that you set the applicationName attribute to something correct for your project and that it is exactly the same value in both the membership and roleManager providers sections.

If you changed the connection string name from "SecDemoConn" then change the related connectionStringName attributes in the membership and role provider sections to match your connectionStrings >> name value.

Don't forget the <clear/> tag before the add section of the membership provider.  Not putting that command in is one of the most common errors we all encounter when we do this from memory instead of by pasting.

There are a lot of attributes in the membership provider, I've included the most common ones and set them to the most common starting values.  They're pretty self-evident, just be sure to check that they are what you want. The main list is documented here.

17) Save your web.config.  Save your solution. 

18) If you now open your Server Explorer Data Connections node you will probably see your mdf listed.  Clicking it open should show you the list of tables and views and so forth of the database.

19)  Big Finish!  Open the Visual Studio PROJECT menu and down a the bottom click the option "ASP.NET Configuration"

If all was followed correctly then you should be able to click on the Security option to create new Roles and new users and link users to the roles.

After adding a couple of roles and users, close the Configuration tool (look at your taskbar!  On many of my machines that tool tends to stay active after closing it AND it starts new instances each time you start it so you have to manually stop it from the "show hidden icons" option on the Windows taskbar).  Then save and close Visual Studio.  Then restart Visual Studio and open your solution and open the Configuration tool again to check that what you added is indeed still there.

In the Server Explorer >> Data Connections, open you friendly named connection and right click on some tables to make sure you can show the table data (such as look at the Users table to see your users are in THIS database instead of some magically created location on your machine.  You can now us this same database for your application tables and objects or just use it for the security by adding another mdf to the App_Data folder and another connectionString to the web.config.

Also check that App_Data folder - browse to it, don't just trust what Visual Studio shows in the Solution Explorer - and verify that your mdf is there and that no ASPNETDB.mdf file was created.  If you do see an ASPNETDB.mdf file then it's usually because your web.config connectionString attributes don't all match up and Visual Studio was being "helpful" by creating a new file instead of using the mdf you did all the work for.

Issues?  Go into SQL Server Management Studio as .\SQLEXPRESS and right click on the attached database in the Databases tree node.  Delete the node for the mdf that you made and close Management Studio.  Now delete the whole Solution folder and start over doing each step slowly one at a time.

The main thing is to do the security database setup first in your development because it can so easily get messed up in the making.  If you do it first and verify it before doing anything else then you at least have one headache checked off.



home     who is smith    contact smith     rss feed π
Since 1997 a place for my stuff, and it if helps you too then all the better smithvoice.com