Installing .NET membership in SQL azure database

Hi All,

I am getting the below error when trying to install membership provider on the AZURE database. 

Exception:
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.

Have gone through a similar post in this forums which suggest an alternate approach of installing SQL scripts for AZURE. But the links provided in the below post to download the scripts are dead.

http://forums.asp.net/t/1892284.aspx?Unable+to+install+membership+in+SQL+azure+database+

http://www.dotnetzone.gr/cs/blogs/dpantontech/archive/2013/05/22/how-to-run-aspnet-regsql-on-azure-sql-database.aspx

Below links to download scripts for AZURE are not active.

http://archive.msdn.microsoft.com/KB2006191/Release/ProjectReleases.aspx?ReleaseId=3539

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

https://support.microsoft.com/kb/2006191

Also gone through a blog that suggest to make use of ASP.NET Universal provider for SQL AZURE. Just wanted to quickly check if there are any other alternative for this before proceeding with the below universal provider.

http://www.hanselman.com/blog/IntroducingSystemWebProvidersASPNETUniversalProvidersForSessionMembershipRolesAndUserProfileOnSQLCompactAndSQLAzure.aspx

Another option that i tried was to use the SQLAzureMW tool that would help in migrating the SQL Server 2005/2008/2012 databases to Azure SQL Database, but it didnt help either

http://sqlazuremw.codeplex.com/

Appreciate your help, suggestions in getting this res

January 5th, 2015 2:21pm

Hi Sriram,

What issues are you getting using SQL Server Migration Assistant?

Free Windows Admin Tool Kit Click here and download it now
January 5th, 2015 2:39pm

Hi SRIRAM,

According to my knowledge, besides modifying raw .sql scripts (InstallAspSchema.zip) and using the ASP.NET Universal provider for SQL AZURE, there is no other alternative to work around this issue.

As you cannot download the .sql scripts (InstallAspSchema.zip), I recommend you submit a feedback at http://feedback.azure.com/forums/34192--general-feedback. Your feedback is valuable for us to improve our products and increase the level of service provided.

Besides, from your description, you fail to migrate database with SQL Database Migration Wizard, please help to post the full error message for analysis. Also there are various tools available for migration database to Azure SQL Database, you can use DAC Package, DAC BACPAC Import Export and so on. For more details, please review the following link.
http://msdn.microsoft.com/en-us/library/azure/hh694043.aspx


Thanks,
Lydia Zhang


January 6th, 2015 9:45am

Hi SRIRAM,

According to my knowledge, besides modifying raw .sql scripts (InstallAspSchema.zip) and using the ASP.NET Universal provider for SQL AZURE, there is no other alternative to work around this issue.

As you cannot download the .sql scripts (InstallAspSchema.zip), I recommend you submit a feedback at http://feedback.azure.com/forums/34192--general-feedback. Your feedback is valuable for us to improve our products and increase the level of service provided.

Besides, from your description, you fail to migrate database with SQL Database Migration Wizard, please help to post the full error message for analysis. Also there are various tools available for migration database to Azure SQL Database, you can use DAC Package, DAC BACPAC Import Export and so on. For more details, please review the following link.
http://msdn.microsoft.com/en-us/library/azure/hh694043.aspx


Thanks,
Lydia Zhang


Free Windows Admin Tool Kit Click here and download it now
January 6th, 2015 9:45am

First thanks to DE, Subhendu & Lydia Zhang for your response.

  1. I was trying to Migrate the ASP.NET membership tables from existing database on local server to Azure SQL DB.
  2. The tool generate the script from the local database and try to execute the same on the SQL Azure DB.
  3. Migration status
  4. Roles and Stored Procedures related to membership tables get created successfully.
  5. Issue occurs in the schema and table creation process.
  6. However the custom tables, SPs that exist in the local db are migrated successfully to SQL Azure DB
  7. I created a new VM in Azure that has the SQL web version and i was able to execute the membership related scripts successfully without any issues.

Attaching the Migration Error log screenshot for reference. Since there is no provision to upload the error log file.

Migration Tool - Error Log

Error Log1

error log 2

January 6th, 2015 3:11pm

Lydia,

I even tried the approach suggested by you. "Migration using DAC Package", but still it does not succeed.

Getting the below error. Looks like SQL AZURE does not support some of the membership features. 

DAC Migration Error

Free Windows Admin Tool Kit Click here and download it now
January 7th, 2015 1:02pm

You could try a simpler approach. Create a new local database without any tables and add the membership features to that db. Script the database. Edit the script to point to your SQL Azure database and run it there. That should create the necessary tables, views and stored procedures.

January 14th, 2015 2:29pm

Hi Paul,

I already tried creating a database locally, then created membership tables and generated the script and executed the same on the SQLAZURE database, it didn't work either.

Free Windows Admin Tool Kit Click here and download it now
January 27th, 2015 10:10am

Did you include the membership views and stored procedures along with the tables? I think that's all there is to the membership system, along with the necessary web.config settings. What does it mean when you say 'it didn't work'? Was there a specific error message?
January 27th, 2015 3:38pm

The ASP.NET universal provider is the right way to go here.  Your welcome to reach out to me at guyhay@microsoft.com if that doesn't work.  I've posted the same response to your voting forum idea.  Happy to talk offline.

Thanks Guy

Free Windows Admin Tool Kit Click here and download it now
February 4th, 2015 1:02am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics