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

About these ads

4 thoughts on “Using ASP.NET Membership in SQL Azure

  1. Hi!

    The main problem here, as far as I remember, is that as there is no SQL Agent in SQL Azure, so there is no session clean up. Somebody came up with a solution here: http://blogs.msdn.com/b/sqlazure/archive/2010/08/04/10046103.aspx Also, as far as I remember, use SQL Azure as session state provider was not supported by Microsoft, maybe that has changed now…

    But I think nowadays, the best option is using AppFabric Caching Service now that it has been released :) It is probably even faster.

    Cheers!

  2. Morning Lluis,

    Just looked through this post and thought I’d send you a question / info.

    I have been having terrible trouble deploying (from Visual Studio 2010) to Azure.

    I have built an MVC 3 site with EF 4.3 using POCO – code first with DBContext.

    When I deployed I could see my site. When I used the built in logon stuff in mvc and registered a new user I could see that the membership tables were created for me. (this would mean I would not need to use your scripts mentioned in this post.) I think the difference is that I am using http://nuget.org/packages/System.Web.Providers so maybe this is a new library that does it all for me?

    The first problem I encountered was that my custom tables were not created automatically even though I was using the DropCreateDatabaseIfModelChanges code and it all was working locally. I spent 2 days playing with connection string. All I could see on the web page was “an error has occurred”, no event log info etc…

    Anyway, last night I deleted everything out of Azure and redeployed. So my site was up there so then I created an Azure database with the name that my Azure connection string would use. I did all the right things and in the end, still no luck.

    I was annoyed. I deleted my Azure database again, ready to go to bed. I thought I’d try the site to see if I got a new error message like, “cannot find database”. But the site loaded. My custom tables were loaded and when I registered a new user with the built in logon stuff in mvc the membership tables were created. Amazing. It all works now. But after 2 days of hell I was asking my self, if it was this easy, why did I have so much trouble.

    Anyway, I am not sure if you kept up to date with http://nuget.org/packages/System.Web.Providers but, if you did, I would like your comments on what happened to me and how Azure and membership and http://nuget.org/packages/System.Web.Providers now works together.

    Hope to hear from you soon,

    RuSs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s