Adding ASP.NET Authentication to SQL Azure

November 21 2010

[update 2011-01-27]

If you are creating a new authentication database on SQL Azure, there is an updated set of scripts you can use to create it from scratch: http://code.msdn.microsoft.com/KB2006191.

Thanks to Dennis Burton for pointing these out. Dennis has another post, Azure Migration: From on-premises SQL Server to SQL Azure, which discusses migrating user data between an existing database and a newly created on on SQL Azure.

[/update]

To set the stage for some future projects, I wanted to see if I could install ASP.NET Authentication into a SQL Azure database. Looking at the actual objects, it seemed a simple enough database to work. My first attempt was to try a direct installation with aspnet_regsql.exe. That failed, there are too many administrative commands in the installation scripts.

Time for Plan B: default installation on a local database and attempting a transfer with the SQL Azure Migration Wizard (SAMW). Again using aspnet_regsql.exe, I performed a default installation on my local SQL Server instance.

After the installation was complete, I ran the SAMW. In the analysis, two stored procedures were flagged as having features incompatible with SQL Azure:

StoredProcedure [dbo].[aspnet_Membership_GetNumberOfUsersOnline] -- Deprecated feature 'Table hint without WITH' is not supported in this version of SQL Azure.

StoredProcedure [dbo].[aspnet_Setup_RemoveAllRoleMembers] -- sp_droprole is not supported in current version of SQL Azure

Not too bad. I can get by without these two procedures, or probably easily create suitable workarounds if I do need them (and since aspnet_Setup_RemoveAllRoleMembers uses a cursor, I may want to rewrite that on principal alone). Running the migration was successful-the only issue flagged was for aspnet_Membership_GetNumberOfUsersOnline, which was not created.

Connecting to the database in SSMS, I can see the objects were all created (except the one procedure mentioned above). Here are the tables: