Using the ASP.NET Membership API with SQL Server / SQL Azure: The new “System.Web.Providers” namespace
- by Harish Ranganathan
The Membership API came in .NET 2.0 and was a huge enhancement in building web applications with users, managing roles, permissions etc., The Membership API by default uses SQL Express and until Visual Studio 2008, it was available only through the ASP.NET Configuration manager screen (Website – ASP.NET Configuration) or (Project – ASP.NET Configuration) and for every application, one has to manually visit this place to start using the Security and other settings. Upon doing that the default SQL Express database aspnet.mdf is created to store all the user profiles.
Starting Visual Studio 2010 and .NET 4.0, the Default Website template includes the Membership API controls as a part of the page i.e. When you create a “File – New – ASP.NET Web Application” or an “ASP.NET MVC Application”, by default the Login/Register controls are enabled in the MasterPage and they are termed under “ApplicationServices” setting in the web.config file with connection string pointed to the SQL Express database.
In fact, when you run the default website and click on “Logon” –> “Register”, and enter the details for registration and click “Register”, that is the time the aspnet.mdf file is created with the tables for Users, Roles, UsersInRoles, Profile etc.,
Now, this uses the default SQL Express database within the App_Data folder. If you want to move your Membership information to some other database such as SQL Server, SQL CE or SQL Azure, you need to manually run the aspnet_regsql command and specify the destination database name.
This would create all the Tables, Procedures and Views required to handle the Membership information. Thereafter you can change the connection string for “ApplicationServices” to point to the database where you had run all the scripts.
Now, enter “System.Web.Providers” Alpha. This is available as a part of the NuGet package library.
Scott Hanselman has a neat post describing the steps required to get it up and running as well as doing the basic changes at http://www.hanselman.com/blog/IntroducingSystemWebProvidersASPNETUniversalProvidersForSessionMembershipRolesAndUserProfileOnSQLCompactAndSQLAzure.aspx
Pretty much, it covers what the new System.Web.Providers do.
One thing I wanted to clarify is that, the new “System.Web.Providers” add a lot of new settings which are also marked as the defaults, in the web.config. Even now, they use SQL Express as the default database. But, if you change the connection string for “DefaultConnection” under connectionStrings to point to your SQL Server or SQL Azure, Membership API would now be able to create all the tables, procedures and views at the destination specified (i.e. SQL Server or SQL Azure).
In my case, I modified the DefaultConneciton to point to my SQL Azure database. Next, I hit F5 to run the application. The default view loads. I clicked on “LogOn” and then “Register” since I knew there are no tables/users as of then. One thing to note is that, I had put “NewDB” as the database name in the connection string that points to SQL Azure. NewDB wasn’t existing and I would assume it would be created before the tables/views/procedures for Membership are created.
Once I clicked on the “Register” to register my first username, it took a while and then registered as well as logged in me in. Also, I went to the SQL Azure Management Portal and verified that there exists “NewDB” which has just been created
I could also connect to the SQL Azure database “NewDB” from Management Studio and found that the tables now don’t have the aspnet_ prefix. The tables were simply Users, Roles, UsersInRoles, Profiles etc.,
So, with a few clicks and configuration change, I could actually set up the user base for my application on SQL Azure and even make the SessionState, Roles, Profiles being stored in SQL Azure database.
The new System.Web.Proivders also required MARS (MultipleActiveResultSets=true) setting since it uses Entity Framework for the DAL operations.
Also, the “Project – ASP.NET Configuration” screen can be used to further create/manage users/roles etc., although the data is stored on the remote database.
With that, a long pending request from the community to have the ability to configure and use remote databases for Application users management without having to run the scripts from SQL Express is fulfilled.
Cheers !!!
