Issues with porting ASP.NET Membership tables, procs, users and roles to Azure with SQLAzureMW?

Dec 29, 2013 at 12:19 AM
Edited Dec 29, 2013 at 12:25 AM
Hi,

Great tool.

However I have had a lot of problems trying to deploy the membership tables to my Azure SQL Database instance.

These are some of the script errors when run against the Azure SQL Database instance:

28/12/2013 14:12:54 --> Error #: 15151 -- Cannot find the user 'IIS APPPOOL\DefaultAppPool', because it does not exist or you do not have permission.
      CREATE SCHEMA failed due to previous errors.
       IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'aspnet_Membership_FullAccess')
       EXEC sys.sp_executesql N'CREATE SCHEMA [aspnet_Membership_FullAccess] AUTHORIZATION [IIS APPPOOL\DefaultAppPool]'
28/12/2013 14:12:59 --> Error #: 40512 -- Deprecated feature 'Table hint without WITH' is not supported in this version
of SQL Server.
   SET ANSI_NULLS ON
   SET QUOTED_IDENTIFIER OFF
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. 
   [aspnet_Membership_GetNumberOfUsersOnline]') AND type in (N'P', N'PC'))
   BEGIN
      EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
   @ApplicationName            nvarchar(256),
   @MinutesSinceLastInActive   int,
   @CurrentTimeUtc             datetime
AS
   BEGIN
       DECLARE @DateActive datetime
       SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive), @CurrentTimeUtc)

        DECLARE @NumOnline int
        SELECT  @NumOnline = COUNT(*)
        FROM    dbo.aspnet_Users uWITH (NOLOCK),
                     dbo.aspnet_Applications aWITH (NOLOCK),
                     dbo.aspnet_Membership mWITH (NOLOCK)
        WHERE   u.ApplicationId = a.ApplicationId                  AND
        LastActivityDate > @DateActive                     AND
        a.LoweredApplicationName = LOWER(@ApplicationName) AND
        u.UserId = m.UserId
        RETURN(@NumOnline)
   END' 
   END
Should I change anything with the membership tables to help SQLAzureMW succeed in taking across the Membership tables and data?

I have heard that SQLAzureMW has deployment the membership Provider table very successfully for some folk, but not me.

My source DB is SQL Server Standard 2008 SP2 targeting an Azure SQL Dtabase instance.

I would appreciate any help with the above.

Many thanks,

Ed
Coordinator
Dec 29, 2013 at 3:36 AM
Hi Ed,

The first error is because of "AUTHORIZATION [IIS APPPOOL\DefaultAppPool]". SQLAzureMW does not migrate users / logins for you. That is something that you will have to do by hand first or just delete "AUTHORIZATION [IIS APPPOOL\DefaultAppPool]" before you run the script and it will create the schema.

Now, the second problem. There is an error that you can fix. In the folder you put SQLAzureMW, you will find a file called "NotSupportedByAzureFile.Config. You need to edit the file and look for "NOLOCK" (around line 49). You need to add a space "ReplaceWith="WITH ("" with "ReplaceWith=" WITH ("". Basically, here is the line:
    <NotSupported Text="\w+\s?\(\s?(FASTFIRSTROW[,\s]*|FORCESEEK[,\s]*|HOLDLOCK[,\s]*|NOLOCK[,\s]*|NOWAIT[,\s]*|PAGLOCK[,\s]*|READCOMMITTED[,\s]*|READCOMMITTEDLOCK[,\s]*|READPAST[,\s]*|READUNCOMMITTED[,\s]*|REPEATABLEREAD[,\s]*|ROWLOCK[,\s]*|SERIALIZABLE[,\s]*|TABLOCK[,\s]*|TABLOCKX[,\s]*|UPDLOCK[,\s]*|XLOCK[,\s]*)+\s?\)" SearchReplace="\(" ReplaceWith=" WITH (" NotSubStr="WITH" SeverityLevel="1" ReplaceString="true" DisplayWarning="true" DefaultMessage="false" WarningMessage="Deprecated feature 'Table hint without WITH'.  Automatically added WITH for you." />
Then save the file.

Let me know if you run into any other issues.

Best Regards,
George
Dec 30, 2013 at 9:02 AM
Hi George,

Thank you for this.

On further investigation, I found there were more fundemental issues with how the database is implemented in local SQL Server compared to what Azure SQL Database supports. Microsoft reissued some tweaked membership scripts, see: http://support.microsoft.com/kb/2006191/de which ran fine. I then did a data compare using SSDT in Visual Studio which worked fine. My old ASP.NET .NET4 membership code then ran fine in Azure websites which surprised me, since it requires me to use .NET4.5. Anyway so far so good.

Thank you again.

Ed