Updated ASP.net scripts for use with Microsoft SQL Azure

Article translations Article translations
Close Close
Article ID: 2006191 - View products that this article applies to.
Expand all | Collapse all

Symptoms

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

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.

 

Resolution

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.

 

More Information

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 for other considerations.

Properties

Article ID: 2006191 - Last Review: November 17, 2009 - Revision: 6.0
APPLIES TO
  • Microsoft ASP.NET 2.0
Keywords: 
KB2006191

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com