Removing the ASP.net membership provider from sql azure

May 19, 2011

Currently I’m working on an article for MSDN that discusses the various ways you can secure ASP.NET running in Windows Azure with forms based authentication. The second part of the article discusses how you can use the SqlMembershipProvider and SQL Azure for forms based authentication.

As you may be aware, to prep an on-premises SQL Server database you need to use the Aspnet_regsql.exe utility with a command line similar to the following to install the membership and role services:

aspnet_regsql -S [your server name].database.windows.net -d [your database name] -U [your user name]@[your server name] -P [your password] -A mr

However, if you attempt to run this same command line against a SQL Azure database you will receive the following error message:

An error occurred during the execution of the SQL file ‘InstallCommon.sql’. The SQL error number is 40508 and the SqlException message is: USE statement is not supported to switch between databases. Use a new connection to connect to a different Database.

SQL Server: [your server name].database.windows.net

Database: [your database name]

SQL file loaded:

InstallCommon.sql

This error is due to the fact that SQL Azure does not support the USE statement to switch database context when executing commands against SQL Azure databases. To work around this SQL Azure limitation, Microsoft created a hotfix to enable developers to prepare SQL Azure databases for use by the SqlMembershipProvider and SqlRoleProvider. You can download the hotfix here. To prepare the SQL Azure database you simply run the following command line:

aspnet_regsqlazure -s [your server name].database.windows.net -d [your database name] -u [your user name]@[your server name] -p [your password] -a mr

As I was writing the article, I wanted to test the command line several times, so I thought I would just remove the components from the SQL Azure database with the following command line:

aspnet_regsqlazure -s [your server name].database.windows.net -d [your database name] -u [your user name]@[your server name] -p [your password] -R mr

Unfortunately the hotfix does not support the removal of the services from the SQL Azure database. I could have just deleted the database and created a new one, but what if I had data other than just the role and membership information required by ASP.NET? In that scenario, dropping the database is not an option. So I decided to write a quick script to clear out the services for me. You can get the script here if you need it.