Migrate Microsoft LightSwitch application

In this blog post I will show you how to migrate a Microsoft LightSwitch application with Radzen.

1. Create а new Microsoft LightSwitch application that displays the Products table from the Northwind database:

2. Enable Forms authentication for application access control:

3. Publish the app to IIS using an admin user as the application administrator:

This will create the ASP.NET membership tables. LightSwitch uses those to store users, passwords, roles, etc:

4. Create a new database as a copy of the existing one used in the LightSwitch application:

5. Create a new Radzen application that displays the Products table:

6. Enable security with email confirmation:

7. Deploy the application to IIS from Radzen:

We now have the ASP.NET Core 2.0 Identity tables in our new database:

and we can use the following SQL script to migrate our users, passwords, roles, etc. to the new database:

-- THIS SCRIPT NEEDS TO RUN FROM THE CONTEXT OF THE MEMBERSHIP DB
BEGIN TRANSACTION MigrateUsersAndRoles
USE Northwind

-- INSERT USERS
INSERT INTO NorthwindNew.dbo.AspNetUsers
          (Id,
           UserName,
           NormalizedUserName,
           PasswordHash,
           SecurityStamp,
           EmailConfirmed,
           PhoneNumber,
           PhoneNumberConfirmed,
           TwoFactorEnabled,
           LockoutEnd,
           LockoutEnabled,
           AccessFailedCount,
           Email,
           NormalizedEmail)
SELECT aspnet_Users.UserId,
     aspnet_Users.UserName,
     -- The NormalizedUserName value is upper case in ASP.NET Core Identity
     UPPER(aspnet_Users.UserName),
     -- Creates an empty password since passwords don't map between the 2 schemas
     '',
     /*
      The SecurityStamp token is used to verify the state of an account and
      is subject to change at any time. It should be initialized as a new ID.
     */
     NewID(),
     /*
      EmailConfirmed is set when a new user is created and confirmed via email.
      Users must have this set during migration to reset passwords.
     */
     1,
     aspnet_Users.MobileAlias,
     CASE
       WHEN aspnet_Users.MobileAlias IS NULL THEN 0
       ELSE 1
     END,
     -- 2FA likely wasn't setup in Membership for users, so setting as false.
     0,
     CASE
       -- Setting lockout date to time in the future (1,000 years)
       WHEN aspnet_Membership.IsLockedOut = 1 THEN Dateadd(year, 1000,
                                                   Sysutcdatetime())
       ELSE NULL
     END,
     aspnet_Membership.IsLockedOut,
     /*
      AccessFailedAccount is used to track failed logins. This is stored in
      Membership in multiple columns. Setting to 0 arbitrarily.
     */
     0,
     aspnet_Membership.Email,
     -- The NormalizedEmail value is upper case in ASP.NET Core Identity
     UPPER(aspnet_Membership.Email)
FROM   aspnet_Users
     LEFT OUTER JOIN aspnet_Membership
                  ON aspnet_Membership.ApplicationId =
                     aspnet_Users.ApplicationId
                     AND aspnet_Users.UserId = aspnet_Membership.UserId
     LEFT OUTER JOIN NorthwindNew.dbo.AspNetUsers
                  ON aspnet_Membership.UserId = AspNetUsers.Id
WHERE  AspNetUsers.Id IS NULL

-- INSERT ROLES
INSERT INTO NorthwindNew.dbo.AspNetRoles(Id, Name)
SELECT RoleId, RoleName
FROM aspnet_Roles;

-- INSERT USER ROLES
INSERT INTO NorthwindNew.dbo.AspNetUserRoles(UserId, RoleId)
SELECT UserId, RoleId
FROM aspnet_UsersInRoles;

IF @@ERROR <> 0
BEGIN
  ROLLBACK TRANSACTION MigrateUsersAndRoles
  RETURN
END

COMMIT TRANSACTION MigrateUsersAndRoles

We are done!

Now we only need to reset the password from the deployed Radzen application. Microsoft LightSwitch apps needed either a desktop app or custom pages for user management. For the purpose of this blog post I'll take the shortcut and I will enter my admin user email directly in the database:

Now we can reset the password from the deployed Radzen application:

You will receive email with a link to generate new password and you can use it to login:

If you don't want your users to reset their passwords you can use a custom password hasher which makes ASP.NET Membership (LightSwitch) passwords work with ASP.NET Core Identity (Radzen):

We need first to copy both Password and PasswordSalt from old aspnet_Membership table:

-- THIS SCRIPT NEEDS TO RUN FROM THE CONTEXT OF THE MEMBERSHIP DB
BEGIN TRANSACTION MigrateUsersAndRoles
USE Northwind

-- INSERT USERS
INSERT INTO NorthwindNew.dbo.AspNetUsers
          (Id,
           UserName,
           NormalizedUserName,
           PasswordHash,
           SecurityStamp,
           EmailConfirmed,
           PhoneNumber,
           PhoneNumberConfirmed,
           TwoFactorEnabled,
           LockoutEnd,
           LockoutEnabled,
           AccessFailedCount,
           Email,
           NormalizedEmail)
SELECT aspnet_Users.UserId,
     aspnet_Users.UserName,
     -- The NormalizedUserName value is upper case in ASP.NET Core Identity
     UPPER(aspnet_Users.UserName),
     -- Copy both Password and PasswordSalt
     aspnet_Membership.Password + ':' + aspnet_Membership.PasswordSalt,
     /*
      The SecurityStamp token is used to verify the state of an account and
      is subject to change at any time. It should be initialized as a new ID.
     */
     NewID(),
     /*
      EmailConfirmed is set when a new user is created and confirmed via email.
      Users must have this set during migration to reset passwords.
     */
     1,
     aspnet_Users.MobileAlias,
     CASE
       WHEN aspnet_Users.MobileAlias IS NULL THEN 0
       ELSE 1
     END,
     -- 2FA likely wasn't setup in Membership for users, so setting as false.
     0,
     CASE
       -- Setting lockout date to time in the future (1,000 years)
       WHEN aspnet_Membership.IsLockedOut = 1 THEN Dateadd(year, 1000,
                                                   Sysutcdatetime())
       ELSE NULL
     END,
     aspnet_Membership.IsLockedOut,
     /*
      AccessFailedAccount is used to track failed logins. This is stored in
      Membership in multiple columns. Setting to 0 arbitrarily.
     */
     0,
     aspnet_Membership.Email,
     -- The NormalizedEmail value is upper case in ASP.NET Core Identity
     UPPER(aspnet_Membership.Email)
FROM   aspnet_Users
     LEFT OUTER JOIN aspnet_Membership
                  ON aspnet_Membership.ApplicationId =
                     aspnet_Users.ApplicationId
                     AND aspnet_Users.UserId = aspnet_Membership.UserId
     LEFT OUTER JOIN NorthwindNew.dbo.AspNetUsers
                  ON aspnet_Membership.UserId = AspNetUsers.Id
WHERE  AspNetUsers.Id IS NULL

-- INSERT ROLES
INSERT INTO NorthwindNew.dbo.AspNetRoles(Id, Name)
SELECT RoleId, RoleName
FROM aspnet_Roles;

-- INSERT USER ROLES
INSERT INTO NorthwindNew.dbo.AspNetUserRoles(UserId, RoleId)
SELECT UserId, RoleId
FROM aspnet_UsersInRoles;

IF @@ERROR <> 0
BEGIN
  ROLLBACK TRANSACTION MigrateUsersAndRoles
  RETURN
END

COMMIT TRANSACTION MigrateUsersAndRoles

Just create a server\Startup.Custom.cs file in the Radzen application with the following content:

namespace NorthwindNew
{
    public partial class Startup
    {
        partial void OnConfigureServices(IServiceCollection services)
        {
            services.AddScoped<IPasswordHasher<ApplicationUser>, CustomPasswordHasher>();
        }
    }

    public class CustomPasswordHasher : PasswordHasher<ApplicationUser>
    {
        public static string GenerateHash(string providedPassword, byte[] saltAsByteArray)
        {
            var sha = new System.Security.Cryptography.SHA1CryptoServiceProvider();

            byte[] p1 = saltAsByteArray;
            byte[] p2 = System.Text.Encoding.Unicode.GetBytes(providedPassword);

            byte[] data = new byte[p1.Length + p2.Length];

            p1.CopyTo(data, 0);
            p2.CopyTo(data, p1.Length);

            return Convert.ToBase64String(sha.ComputeHash(data));
        }

        public override PasswordVerificationResult VerifyHashedPassword(ApplicationUser user, string hashedPasswordAndSalt,
           string providedPassword)
        {
            if (hashedPasswordAndSalt.IndexOf(":") != -1)
            {
                var hashedPassword = hashedPasswordAndSalt.Split(':')[0];
                var salt = hashedPasswordAndSalt.Split(':')[1];

                var hashedProvidedPassword = GenerateHash(providedPassword, Convert.FromBase64String(salt));

                return hashedPassword == hashedProvidedPassword
                    ? PasswordVerificationResult.SuccessRehashNeeded
                    : base.VerifyHashedPassword(user, hashedPassword, providedPassword);
            }
            else
            {
                return base.VerifyHashedPassword(user, hashedPasswordAndSalt, providedPassword);
            }
        }
    }
}

Enjoy!