Using ASP.NET Membership in SQL Azure

The scenario:

Sometimes we forget SQL Azure is not SQL Server. This morning in the project I’m working, we needed to create a small ASP.NET membership database in SQL Azure.

It seems easy! Would you expect troubles with this? Nope? Ok, me neither… until I tried to execute aspnet_regsql to create the database:

An error occurred during the execution of the SQL file ‘InstallCommon.sql’. The SQL error number is 208 and the SqlException message is: Invalid object name ‘master.dbo.sysdatabases’.

Notice that this error is absolutely normal, because in SQL Azure we can’t access to sysdatabases table. In fact, if we try to execute this script:

SELECT * FROM master.dbo.sysdatabases

We will receive this error:

Reference to database and/or server name in ‘master.dbo.sysdatabases’ is not supported in this version of SQL Server.

The solution:

When using SQL Azure we cannot create the database with aspnet_regsql. Instead, we should use a new set of scripts optimized for SQL Azure.

There are a couple of files to download, but we only need InstallAspSchema.zip which contains all the needed scripts separated by themes (memberships, roles, etc.) which I think is great! So we will install only the parts that we will use:

aspnet_scripts

The most important step is installing the scripts in the correct order. The first one must be InstallCommon.sql, and after (in my particular case) installing the InstallMemberShip.sql and InstallRoles.sql scripts is -by far- enough.

Note: Be aware with the scripts, I’ve had to execute the membership script twice, because the first one some tables haven’t been created. If you have the same problem please send me feedback about it.

Hope you enjoy it! :-)

More info:

ASP.NET Membership scripts for SQL Azure:

http://archive.msdn.microsoft.com/KB2006191

General Guidelines and Limitations (SQL Azure Database)

http://msdn.microsoft.com/en-us/library/ee336245.aspx