Adding ASP.NET Authentication to SQL Azure

[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:

image

And the views:

image

And part of the listing of stored procedures:

image

The next step is to wire some applications to make use of this database, especially the project that started this whole idea.

GrapeCity

GrapeCity Developer Tools
comments powered by Disqus