Dec 29, 2013 at 1:19 AM
Edited Dec 29, 2013 at 1:25 AM
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'))
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
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
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.
Dec 29, 2013 at 4:36 AM
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.
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:
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.