Migrate from SQL Server 2008R2 (AspNetSqlMembershipProvider) to SQL Azure (Universal Providers)

Question

User-95190175 posted

I like to summarize my searches and final successful result regarding migration of ‘aspnetdb’ with data from SQL Server 2008R2 to SQL Azure. I have site http://ukrainebazar.com/ where for user identity I use AspNetSqlMembershipProvider. Unfortunately AspNetSqlMembershipProvider is not supported in SQL Azure. You have to install and use Universal Provider (this option will be proposed automatically when you try to deploy your site to Azure – only note that web.config will be changed!). But anyway if you have data base with (data) you also need to transfer data from ‘aspnetdb’ to new DB in SQL Azure.

 

1. How to make new clean ‘aspnetdb’ (for users identity) in SQL Azure – just make new project (ASP.NET Forms or MVC) in VS2012 from template and deploy it to your Azure account. Than register one user and data base will be created automatically. Use Azure tools to download initial project for Data base. Than you can use this project for deploying new clean DB into SQL Azure.
2. For this step you need new DB on SQL Azure with Tables ready (without data inside!) as follow:

• -        Applications
• -        Memberships
• -        Profiles
• -        Roles
• -        Users
• -        UsersInRoles
• -        UsersOpenAuthAccounts
• -        UsersOpenAuthData

Last two tables only if you use OAuth authenticationlike like Facebook, Google, OpenId, Yahoo etc.

Use ‘Import and Export Data’ utility from SQL Server 2008R2 to transfer the existing user accounts and passwords from the SqlMembershipProvider to the Universal Providers using the following sequence:

• -        1. Applications
• -        2. Users
• -        3. Memberships
• -        4. Profiles
• -        5. Roles
• -        6. UsersInRoles
• -        7. UsersOpenAuthData
• -        8. UsersOpenAuthAccounts

         

3.  Now your new data base on SQL Azure almost ready for use. If you have profile records in Table “Profile” than need to make last changes in column ‘PropertyNames’ -      to remove all strings like ‘:S’

     For example in original DB table ‘aspnet_Profile’ you have record like this:

               MailValid:S:0:4:UserIP:S:4:13:FirstName:S:17:8:LastName:S:25:12:

     Than in SQL Azure this record has to be like this:

               MailValid:0:4:UserIP:4:13:FirstName:17:8:LastName:25:12:

4. One last step – User’s Passwords. This step very important and depends from your initial setting in SqlMembershipProvider. Finally you will need to change        web.config file:

-first try to set passwordCompatMode="Framework20"  like this:

<membership defaultProvider="DefaultMembershipProvider">
      <providers>
         <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider" connectionStringName="DefaultConnection" applicationName="test" passwordCompatMode="Framework20" />
      </providers>
</membership>

-Check if you can login with existing user name and password, if not - try to set passwordCompatMode="Framework40" like this:

<membership defaultProvider="DefaultMembershipProvider">
      <providers>
        <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider" connectionStringName="DefaultConnection" applicationName="test" passwordCompatMode="Framework40" />
      </providers>
</membership>

-And finally like this:

<membership defaultProvider="DefaultMembershipProvider" hashAlgorithmType="SHA256">
      <providers>
        <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider" connectionStringName="DefaultConnection" applicationName="test" passwordCompatMode="Framework40" />
      </providers>
</membership>

If it does not help, than looks like no more options and all users have to reregister own passwords using option ‘Forget Password?’ On my site first option with ‘Framework20” was successful, and I transferred all users with all records to SQL Azure.