Migration for user accounts from the SqlMembershipProvider to the Universal Providers

Xiaohong Tang

As you know ASP.Net SqlMembershipProvider / SqlRoleProvider only support Microsoft SQL Server and Microsoft SQL Server Express. There is no support for Microsoft SQL Azure and Microsoft SQL Server Compact. The ASP.NET Universal Providers have been created in order to add support for SQL Azure to be ready for cloud environments like Azure.

Here we will talk about how to migrate your existing project with the SqlMembershipProvider for your user accounts and passwords to the Universal Providers.

First, install the Universal Providers Nuget package. This will update the existing project to use Universal Providers. You can also migrate the existing user accounts and passwords from the SqlMembershipProvider to the Universal Providers using the instructions below.

Migrate all the accounts from the old tables to the new tables:

• For Microsoft ASP.NET Universal Providers 1.1 /1.2, below is sample SQL scripts for the membership and role providers (this doesn’t cover the profile provider):

INSERT INTO dbo.Applications (ApplicationName, ApplicationId, Description)

SELECT ApplicationName, ApplicationId, Description FROM dbo.aspnet_Applications

GO

INSERT INTO dbo.Roles (ApplicationId, RoleId, RoleName, Description)

SELECT ApplicationId, RoleId, RoleName, Description FROM dbo.aspnet_Roles

GO

INSERT INTO dbo.Users (ApplicationId, UserId, UserName, IsAnonymous, LastActivityDate)

SELECT ApplicationId, UserId, UserName, IsAnonymous, LastActivityDate FROM dbo.aspnet_Users

GO

INSERT INTO dbo.Memberships (ApplicationId, UserId, Password, 

PasswordFormat, PasswordSalt, Email, PasswordQuestion, PasswordAnswer, 

IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, 

LastLockoutDate, FailedPasswordAttemptCount, 

FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, 

FailedPasswordAnswerAttemptWindowStart, Comment) 

SELECT ApplicationId, UserId, Password, 

PasswordFormat, PasswordSalt, Email, PasswordQuestion, PasswordAnswer, 

IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, 

LastLockoutDate, FailedPasswordAttemptCount, 

FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, 

FailedPasswordAnswerAttemptWindowStart, Comment FROM dbo.aspnet_Membership

GO

INSERT INTO dbo.UsersInRoles SELECT * FROM dbo.aspnet_UsersInRoles

GO

After all the accounts are migrated from the old tables to the new tables, you could update the config setting for Universal Providers (if needed) to map to the appropriate settings on the SqlMembershipProvider. In this case, a password reset won’t be needed and existing users will still be able to logon

Here is a list of the settings for SqlMembershipProvider that should be mapped to the settings on the Universal Providers DefaultMembershipProvider:

1. Default setting in membership and SqlMembershipProvider (here are 2 examples for same results of settings):

In SqlMembershipProvider, by default passwordCompatMode is Framework20.

In DefaultMembershipProvider, by default passwordCompatMode is Framework40.

SqlMembershipProvider	DefaultMembershipProvider
e.g.	e.g.
<membership defaultProvider="AspNetSqlMembershipProvider">       <providers>         <clear />         <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices" applicationName="test" />       </providers> </membership>	<membership defaultProvider="DefaultMembershipProvider">       <providers>         <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider" connectionStringName="DefaultConnection" applicationName="test" passwordCompatMode="Framework20" />       </providers> </membership>
<membership defaultProvider="AspNetSqlMembershipProvider">       <providers>         <clear />         <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices" applicationName="test" passwordCompatMode="Framework20" />       </providers> </membership>	<membership defaultProvider="DefaultMembershipProvider">       <providers>         <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider" connectionStringName="DefaultConnection" applicationName="test" passwordCompatMode="Framework20" />       </providers> </membership>

2. Specified hashAlgorithmType setting in membership with SqlMembershipProvider (here are 2 examples for same results of settings):

In SqlMembershipProvider, specified hashAlgorithmType will be used,  no matter what passwordCompatMode.

In DefaultMembershipProvider, because of Medium trust security issue on reading hashAlgorithmType setting in membership, only when passwordCompatMode is Framework40, specified hashAlgorithmType will be used.

SqlMembershipProvider	DefaultMembershipProvider
e.g.	e.g.
<membership defaultProvider="AspNetSqlMembershipProvider" hashAlgorithmType="SHA256">       <providers>         <clear />         <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices" applicationName="test" passwordCompatMode="Framework20" />       </providers> </membership>	<membership defaultProvider="DefaultMembershipProvider" hashAlgorithmType="SHA256">       <providers>         <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider" connectionStringName="DefaultConnection" applicationName="test" passwordCompatMode="Framework40" />       </providers> </membership>
<membership defaultProvider="AspNetSqlMembershipProvider" hashAlgorithmType="SHA256">       <providers>         <clear />         <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices" applicationName="test" passwordCompatMode="Framework40" />       </providers> </membership>	<membership defaultProvider="DefaultMembershipProvider" hashAlgorithmType="SHA256">       <providers>         <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider" connectionStringName="DefaultConnection" applicationName="test" passwordCompatMode="Framework40" />       </providers> </membership>

   

3. Specified Framework40 passwordCompatMode in SqlMembershipProvider:

SqlMembershipProvider	DefaultMembershipProvider
e.g.	e.g.
<membership defaultProvider="AspNetSqlMembershipProvider">       <providers>         <clear />         <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices" applicationName="test" passwordCompatMode="Framework40" />       </providers>     </membership>	<membership defaultProvider="DefaultMembershipProvider">       <providers>         <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider" connectionStringName="DefaultConnection" applicationName="test" passwordCompatMode="Framework40" />       </providers>     </membership>

Xiaohong Tang

Follow