aspnet_regsql.exe and SQLAzure?

Question

Hi,

I get a error when I run aspnet_regsql.exe against SQLAzure? Is their a fix or another way to populate SQLAzure with ASP.NET membership tables, sproc's etc.

Thanks

Answer 1

aspnet_regsql.exe uses a number of scripts that utilize features unsupported by SQL Azure.  We are looking into it. 

Basically, to get it working you need to update the scripts and manually run them to create the database.  Membership scripts are relatively easy to change.  All the scripts are located in a .NET folder (for example, C:\Windows\Microsoft.NET\Framework\v2.0.50727).

If this is a blocking issues for you and you need help getting past this, feel free to ping me offline.
Stan

---

Program Manager, SQL Azure

Answer 2

Hi,

I would really like a fix to this as well.

Thanks

Answer 3

Me too.

Answer 4

You can also try out this page http://www.sqlazureverifier.com/ for checking your scripts, to see if it uses unsupported commands.

Answer 5

Hi Stan,

Do you know of any example membership scripts that have been amended for the Azure environment.

Many thanks,

Tim

Answer 6

Hello all,
We can use the SqlAzure Migration Wizard http://sqlazuremw.codeplex.com/ using which you can easily create SQL Azure compatible script from your already-running-locally-created ASP.NET Membership database.

Answer 7

Thanks Anton, this is what I have tried already but I receive errors on the import relating to the scheme, I will try again in a bit and post the exact options selected and the result.

Answer 8

Hi Anton,

Here is what happens when I use the SQLAzureMW:

1.       Dropped all existing databases and created a fresh database.

2.       Using SQLAzureMW_v0.2.7

3.       Connected to local SQL Express 2008  instance

4.       Accept default script options (all true except for script headers)

5.       Select Schemas, Stored Procedures, Tables and Views

6.       Choose all aspnet membership and roles schemas

7.       Choose all aspnet membership, roles and users stored procedures

8.       Choose all applicable tables (aspnet membership tables plus custom app tables)

9.       Choose all aspnet views

10.   Script direct to Azure

11.   Connect to Azure DB via wizard and select Script

 

I then receive a number of errors, a selection of which are below (not comprehensive):

Error #: 15151 -- Cannot find the user 'aspnet_Membership_BasicAccess', 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_BasicAccess')

EXEC sys.sp_executesql N'CREATE SCHEMA [aspnet_Membership_BasicAccess] AUTHORIZATION [aspnet_Membership_BasicAccess]'

 

 

Error #: 15151 -- Cannot find the user 'aspnet_Membership_FullAccess', 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 [aspnet_Membership_FullAccess]'

 

Error #: 40512 -- Deprecated feature 'More than two-part column name' is not supported in this version of SQL Server.

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_aspnet_Applications]'))

EXEC dbo.sp_executesql @statement = N'

  CREATE VIEW [dbo].[vw_aspnet_Applications]

  AS SELECT [dbo].[aspnet_Applications].[ApplicationName], [dbo].[aspnet_Applications].[LoweredApplicationName], [dbo].[aspnet_Applications].[ApplicationId], [dbo].[aspnet_Applications].[Description]

  FROM [dbo].[aspnet_Applications

Error #: 40512 -- Deprecated feature 'More than two-part column name' is not supported in this version of SQL Server.

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_aspnet_MembershipUsers]'))

EXEC dbo.sp_executesql @statement = N'

  CREATE VIEW [dbo].[vw_aspnet_MembershipUsers]

  AS SELECT [dbo].[aspnet_Membership].[UserId],

            [dbo].[aspnet_Membership].[PasswordFormat],

            [dbo].[aspnet_Membership].[MobilePIN],

            [dbo].[aspnet_Membership].[Email],

            [dbo].[aspnet_Membership].[LoweredEmail],

            [dbo].[aspnet_Membership].[PasswordQuestion],

            [dbo].[aspnet_Membership].[PasswordAnswer],

            [dbo].[aspnet_Membership].[IsApproved],

            [dbo].[aspnet_Membership].[IsLockedOut],

            [dbo].[aspnet_Membership].[CreateDate],

            [dbo].[aspnet_Membership].[LastLoginDate],

            [dbo].[aspnet_Membership].[LastPasswordChangedDate],

            [dbo].[aspnet_Membership].[LastLockoutDate],

            [dbo].[aspnet_Membership].[FailedPasswordAttemptCount],

            [dbo].[aspnet_Membership].[FailedPasswordAttemptWindowStart],

            [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptCount],

            [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptWindowStart],

            [dbo].[aspnet_Membership].[Comment],

            [dbo].[aspnet_Users].[ApplicationId],

            [dbo].[aspnet_Users].[UserName],

            [dbo].[aspnet_Users].[MobileAlias],

            [dbo].[aspnet_Users].[IsAnonymous],

            [dbo].[aspnet_Users].[LastActivityDate]

  FROM [dbo].[aspnet_Membership] INNER JOIN [dbo].[aspnet_Users]

      ON [dbo].[aspnet_Membership].[UserId] = [dbo].[aspnet_Users].[UserId]

 

Error #: 40512 -- Deprecated feature 'Table hint without WITH' is not supported in this version of SQL Server.

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 u(NOLOCK),

            dbo.aspnet_Applications a(NOLOCK),

            dbo.aspnet_Membership m(NOLOCK)

    WHERE   u.ApplicationId = a.ApplicationId                  AND

            LastActivityDate > @DateActive                     AND

            a.LoweredApplicationName = LOWER(@ApplicationName) AND

            u.UserId = m.UserId

    RETURN(@NumOnline)

END'

END

 

Error #: 468 -- Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_AddUsersToRoles]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles

                @ApplicationName  nvarchar(256),

                @UserNames                           nvarchar(4000),

                @RoleNames                           nvarchar(4000),

                @CurrentTimeUtc   datetime

AS

BEGIN

                DECLARE @AppId uniqueidentifier

                SELECT  @AppId = NULL

                SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName

                IF (@AppId IS NULL)

                                RETURN(2)

                DECLARE @TranStarted   bit

                SET @TranStarted = 0

 

                IF( @@TRANCOUNT = 0 )

                BEGIN

                                BEGIN TRANSACTION

                                SET @TranStarted = 1

                END

 

                DECLARE @tbNames            table(Name nvarchar(256) NOT NULL PRIMARY KEY)

                DECLARE @tbRoles              table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)

                DECLARE @tbUsers             table(UserId uniqueidentifier NOT NULL PRIMARY KEY)

                DECLARE @Num                   int

                DECLARE @Pos                    int

                DECLARE @NextPos            int

                DECLARE @Name                 nvarchar(256)

 

                SET @Num = 0

                SET @Pos = 1

                WHILE(@Pos <= LEN(@RoleNames))

                BEGIN

                                SELECT @NextPos = CHARINDEX(N'','', @RoleNames,  @Pos)

                                IF (@NextPos = 0 OR @NextPos IS NULL)

                                                SELECT @NextPos = LEN(@RoleNames) + 1

                                SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))

                                SELECT @Pos = @NextPos+1

 

                                INSERT INTO @tbNames VALUES (@Name)

                                SET @Num = @Num + 1

                END

 

                INSERT INTO @tbRoles

                  SELECT RoleId

                  FROM   dbo.aspnet_Roles ar, @tbNames t

                  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId

 

                IF (@@ROWCOUNT <> @Num)

                BEGIN

                                SELECT TOP 1 Name

                                FROM   @tbNames

                                WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)

                                IF( @TranStarted = 1 )

                                                ROLLBACK TRANSACTION

                                RETURN(2)

                END

 

                DELETE FROM @tbNames WHERE 1=1

                SET @Num = 0

                SET @Pos = 1

 

                WHILE(@Pos <= LEN(@UserNames))

                BEGIN

                                SELECT @NextPos = CHARINDEX(N'','', @UserNames,  @Pos)

                                IF (@NextPos = 0 OR @NextPos IS NULL)

                                                SELECT @NextPos = LEN(@UserNames) + 1

                                SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))

                                SELECT @Pos = @NextPos+1

 

                                INSERT INTO @tbNames VALUES (@Name)

                                SET @Num = @Num + 1

                END

 

                INSERT INTO @tbUsers

                  SELECT UserId

                  FROM   dbo.aspnet_Users ar, @tbNames t

                  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId

 

                IF (@@ROWCOUNT <> @Num)

                BEGIN

                                DELETE FROM @tbNames

                                WHERE LOWER(Name) IN (SELECT LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE au.UserId = u.UserId)

 

                                INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)

                                  SELECT @AppId, NEWID(), Name, LOWER(Name), 0, @CurrentTimeUtc

                                  FROM   @tbNames

 

                                INSERT INTO @tbUsers

                                  SELECT  UserId

                                  FROM   dbo.aspnet_Users au, @tbNames t

                                  WHERE   LOWER(t.Name) = au.LoweredUserName AND au.ApplicationId = @AppId

                END

 

                IF (EXISTS (SELECT * FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr WHERE tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId))

                BEGIN

                                SELECT TOP 1 UserName, RoleName

                                FROM                     dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr, aspnet_Users u, aspnet_Roles r

                                WHERE                   u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId

 

                                IF( @TranStarted = 1 )

                                                ROLLBACK TRANSACTION

                                RETURN(3)

                END

 

                INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId)

                SELECT UserId, RoleId

                FROM @tbUsers, @tbRoles

 

                IF( @TranStarted = 1 )

                                COMMIT TRANSACTION

                RETURN(0)

END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     '

END

 

Answer 9

Here is the parsed script

Provided as is :)

http://www.dotmim.com/sitefiles/SqlAzureScriptAspnetDb.zip

Feel free to comment :)

---

Sébastien Pertus. Bewise

Answer 10

Hi Sebastian,

Thanks a lot! It works perfectly.

Best regards,
Nicolai

Answer 11

you 're welcome :)

---

Sébastien Pertus. Bewise

Answer 12

Sébastien,

Like you, I was able to create the ASPNET Membership and Role tables & sprocs  in Sql Azure by fixing the script for creating those tables & procedures.
But I haven't been able to create an ASPNET web application on Azure which can use the .NET membership and role providers to use those  tables. 

If I try a non-Azure application to access Sql Azure thru the membership and role provider it works, but not if the web application is located on Azure also.

Did you successfully have an Azure application use the Sql Azure role and membership tables you created?  If so, what did you do to make it work?

Answer 13

Do you get an exception on Azure? I had to insert some records in the SchemaVersions table before it worked.

/Nicolai

Answer 14

Yes, it works perfectly for me with a website in azure and the membership database on sql azure.
You have to perform an insert in schemaversions table some records.

I dont notify this issue, because i have transfered all my database records through SSIS packages.

---

Sébastien Pertus. Bewise

Answer 15

Hello!

What do I need to do to insert records into the schema versions table?

Totally new to this and really stuck :-(

Help greatly appreciated

Answer 16

I had to insert these records:

INSERT INTO aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion) VALUES('common', 1, 1)
INSERT INTO aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion) VALUES('health monitoring', 1, 1)
INSERT INTO aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion) VALUES('membership', 1, 1)
INSERT INTO aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion) VALUES('personalization', 1, 1)
INSERT INTO aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion) VALUES('profile', 1, 1)
INSERT INTO aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion) VALUES('role manager', 1, 1)

/nico

Answer 17

Thanks Nico! You and Anton have the combination that seems to work very nicely.

Answer 18

Sebastien -

Thanks so much - used in conjunction with Nico's (nlundgaard) bit below this worked like a charm.

Cheers,
Nick

Answer 19

Hello Sébastien,

The parsed script worked like a charm. Thanks a ton! Before I found your file, I was trying to recreate the InstallCommon.sql file for Azure, spent at least two hours trying to get rid of every error that I encountered. Finally found your script and voila, it worked. Compared my file to your file and found that I was close enough. That's a good sign. :)


Regards,
Gopinath

Answer 20

Hey nlundgaard,

Your script to initialize the Azure Membership tables worked perfectly. Thanks a lot. It saved me a lot of time.

Regards,
Gopinath

Answer 21

Will there be an Azure-compatible aspnet_regsql?  I'm now using the final release of VS2010 and there are still issues.  Please advise.

Answer 22

I did not see the scripts there. Can create a database in SQL Azure and then migrate the four tables in the 'aspnet' db in SQLExpress?

BTW in the latest update couple of days ago has this changed (I mean modified version of aspnet_regsql)?

Looks like aspnet-regsql.exe starts off with creating a database and then  bunch of other things like tables. Since creating the database has to be in one line and context change using USE not allowed, the tool basically fails. If we just remove this and get a new tool it may just work.

---

mysorian

Answer 23

This links points to scripts to create ASP.NET Membership:

KB2006191 - Updated ASP.NET scripts for use with SQL Azure

http://code.msdn.microsoft.com/KB2006191

---

mysorian

Answer 24

The locally running ASP.NET Membership database 'aspnetdb' has four tables.

Is it sufficient to create these tables in SQL Azure? 

Are there more tables for a fully functional authentication scheme?

---

mysorian

Answer 25

Hi Jayaram,

Yes, you need a bit more than that. You need some records in these tables (see another post in this thread with the neede insert statements) and there is also a bunch of stored procedures and views that you need to have in your database before Forms Authentication will work.

If you want to migrate from a "regular" SQL Server database, you can do so with different tools, like Red Gate SQL Compare 9 (8 and before don't support Azure) or with the SQL Azure Migration Wizard (http://sqlazuremw.codeplex.com/). But before you can, you'll have to fix the GetNumberOfUsersOnline stored procedure. You can read this blogpost for details).

---

----- http://jepsonsblog.blogspot.com -----