Updated ASP.net scripts for use with Microsoft SQL Azure

This article provides information about two specific limitations when using ASP.net in conjunction with a Microsoft SQL Azure database.  

Issue 1 - Problems with the aspnet_regsql tool:

The  aspnet_regsql.exe tool will fail to run against a SQL Azure database.  When trying to create a database with the aspnet_regsql.exe tool, the user will encounter one of the following errors:

An error occurred during the execution of the SQL file 'InstallSqlState.sql'. The SQL error number is 40510 and the SqlException message is: Statement 'CREATE DATABASE' is not supported in this version of SQL Server. 

-- or –

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'.

 

Issue 2 - Asp.Net Session State Management:

 

A user’s session will not expire if the ASP.net web application uses a  SQL Azure database for session state management.

 

Cause 1 -  Microsoft SQL Azure is a cloud-based relational database platform built on SQL Server technologies.  The aspnet_regql.exe tool works with the .sql scripts which are located in the .Net framework directory, however SQL Azure databases do not support some of the SQL statements used in these raw .sql scripts. 

Cause 2 -Asp.Net session expiration functionality is implemented through stored procedures that are scheduled through jobs in SQL Server.  SQL Agent manages these jobs.  However, SQL Azure databases do not support SQL Agent.

 

To configure SQL Azure databases for Asp.Net membership, role manager, profiles, personalization, and SQL Web event provide features, please do the following:

1. Download the modified raw .sql scripts from here.
2. Connect to the SQL Azure database.  For instructions on how to connect to a SQL Azure Database, go here.
3. Connect to the master database.
4. Run the modified InstallAzure.sql file to create the database.  The default is [aspnetdb].  NOTE: The database name can be changed to anything you want.
5. Connect to the database you created, and then run the scripts in InstallCommon.sql.
6. Run the corresponding .sql statement depending on the feature:

·         Membership – InstallMembership.sql

·         Personalization – InstallPersonalization.sql

·         Profile – InstallProfile.sql

·         Roles – InstallRoles.sql

·         SQL Web Event Provider – InstallWebEventSqlProvider.sql

Note: You may receive the following message while executing the scripts: "Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information schema, sys, or yourself."  This message can be ignored.

 

The workarounds provided here do not work for ASP.Net SQL Session State Management features.   Microsoft does not support SQL Session State Management using SQL Azure databases for ASP.net applications.

There is a modified aspnet_regAzure.exe tool that is available for use.  You can download this tool from here.  This tool only supports configuring a SQL Azure database for certain Asp.net features.  This tool does not support all the functionalities of aspnet_regsql.exe, and it is not supported by Microsoft.

You can find more documentation on Guidelines and Limitations for SQL Azure databases here.

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use (http://go.microsoft.com/fwlink/?LinkId=151500) for other considerations.