Moving ASP.NET roles/membership from Test/Local to Production

ASP.NET roles/membership providers are incredibly simple and convenient, but if you move you site straight to a production environment (SQL Server, etc.), it's probably not going to work without a couple changes

Believe it or not, I had never needed to use ASP.NET roles in any large production environment.  But recently at work, that changed.  Everything was working great on my local machine, couldn't be smoother, but then I moved everything to our web server with a SQL 2005 backend... no worky.


When you enable roles in ASP.NET within VS/VWD it automatically makes an MDF that resides in your App_Data folder.  And most likely, your machine is not running full-fledged SQL, just Express.  Which is just fine, but when you migrate to the production environment, even if you bring the MDF with you and place it in your App_Data file, your roles will not work.


What VS is forgetting to tell you is that your membership provider is using an 'invisible' ConnectionString called 'LocalSqlServer' and that that connectionString uses SQL Express and Windows credentials to access it.  Which is why, in most cases, that that connectionString will not work in your production setting.  What you need to do is two things:


  1. Move your MDF to you production SQL Server instance
  2. Make sure your role provider references that instance by changing the connection string


This is very easy to do, and here is how you do it:

Move your MDF to your Production SQL

  1. Copy your ASPNETDB.mdf file from your App_Code folder to Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data on your SQL machine
  2. Open SQL Server Management Studio and connect to your database
  3. Right click on the 'Databases' folder in the navigation pane, click 'Attach...'
  4. Click the 'Add...' button and navigate to your ASPNETDB.mdf on that machine, click 'OK'
  5. Click 'OK'
  6. You now have it attached and ready to use, I recommend renaming it as the name will be very long (I renamed it simply ASPNETDB.mdf - I'm creative)

Make sure your program references the new database location

  1. Go into your web.config file for your program, locate the connectionStrings section
  2. Add the following 2 lines:
    <remove name="LocalSqlServer"/>
    <add name="LocalSqlServer" connectionString="CONNECTION_STRING_TO_MEMBERSHIP_DB" providerName="System.Data.SqlClient"/>
  3. Upload and you should be all set

The important thing here is that you cleared out the 'invisible' connectionString that .Net uses and replaces it with one that is going to run off of your newly implemented database.

Comments (3) -

  • Thank you so much for your info...
    Really helpful...
  • Glad to help out, I figure my hours of confusion might help someone out Smile
  • tom
    Brilliant!  This really helped.
Comments are closed