none
aspnet_regsql.exe and SQLAzure? RRS feed

  • 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
    Thursday, August 20, 2009 7:13 PM

Answers

  • 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
    Saturday, August 22, 2009 12:35 AM

All replies

  • 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
    Saturday, August 22, 2009 12:35 AM
  • Hi,

    I would really like a fix to this as well.

    Thanks
    Wednesday, August 26, 2009 1:29 PM
  • Me too.
    Thursday, August 27, 2009 6:05 PM
  • You can also try out this page http://www.sqlazureverifier.com/ for checking your scripts, to see if it uses unsupported commands.
    Monday, August 31, 2009 2:54 PM
  • Hi Stan,

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

    Many thanks,

    Tim
    Tuesday, September 29, 2009 1:18 PM
  • 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.
    • Proposed as answer by nick_nyc Thursday, October 8, 2009 8:41 PM
    Tuesday, September 29, 2009 1:23 PM
  • 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.
    Tuesday, September 29, 2009 1:41 PM
  • 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

     

    Tuesday, September 29, 2009 5:31 PM
  • Here is the parsed script

    Provided as is :)

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

    Feel free to comment :)


    Sébastien Pertus. Bewise
    Tuesday, September 29, 2009 6:45 PM
  • Hi Sebastian,

    Thanks a lot! It works perfectly.

    Best regards,
    Nicolai
    Tuesday, September 29, 2009 8:01 PM
  • you 're welcome :)
    Sébastien Pertus. Bewise
    Tuesday, September 29, 2009 9:12 PM
  • 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?

    Wednesday, September 30, 2009 11:45 PM
  • Do you get an exception on Azure? I had to insert some records in the SchemaVersions table before it worked.

    /Nicolai
    Thursday, October 1, 2009 8:01 AM
  • 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
    Thursday, October 1, 2009 8:23 AM
  • 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
    Thursday, October 8, 2009 11:26 AM
  • 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
    • Proposed as answer by nick_nyc Thursday, October 8, 2009 8:43 PM
    Thursday, October 8, 2009 11:39 AM
  • Thanks Nico! You and Anton have the combination that seems to work very nicely.
    Thursday, October 8, 2009 8:43 PM
  • Sebastien -

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

    Cheers,
    Nick
    • Proposed as answer by nick_nyc Thursday, October 8, 2009 8:46 PM
    Thursday, October 8, 2009 8:46 PM
  • 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
    Wednesday, November 18, 2009 10:59 AM
  • Hey nlundgaard,

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

    Regards,
    Gopinath

    Wednesday, November 18, 2009 11:00 AM
  • Will there be an Azure-compatible aspnet_regsql?  I'm now using the final release of VS2010 and there are still issues.  Please advise.
    Thursday, May 13, 2010 9:03 PM
  • 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
    • Edited by Jayaram Krishnaswamy Thursday, June 10, 2010 8:38 PM Cursor jumps all over and creates faux typos
    Thursday, June 10, 2010 7:46 PM
  • 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
    Thursday, June 10, 2010 8:37 PM
  • 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
    Wednesday, June 23, 2010 4:01 AM
  • 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 -----
    Friday, February 25, 2011 10:04 PM