Migrating Optimizely 11 to 12: SQL Membership & Legacy Hashes (Part 2)

/*
================================================================================
ASP.NET Membership to ASP.NET Core Identity Migration Script
================================================================================
*/
SET NOCOUNT ON;

-- 1. Add LegacyPasswordSalt column to store old hash/salt data
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name = N'LegacyPasswordSalt' AND Object_ID = Object_ID(N'dbo.AspNetUsers'))
BEGIN
    PRINT 'Adding column [LegacyPasswordSalt] to [dbo].[AspNetUsers]...';
    ALTER TABLE [dbo].[AspNetUsers] ADD [LegacyPasswordSalt] NVARCHAR(MAX) NULL;
END

GO

BEGIN TRANSACTION;
BEGIN TRY
    -- 2. INSERT USERS
    PRINT 'Migrating users...';

    INSERT INTO dbo.AspNetUsers (
        Id, UserName, NormalizedUserName, Email, NormalizedEmail, 
        EmailConfirmed, PasswordHash, SecurityStamp, ConcurrencyStamp, 
        PhoneNumber, PhoneNumberConfirmed, TwoFactorEnabled, 
        LockoutEnd, LockoutEnabled, AccessFailedCount, 
        LegacyPasswordSalt, -- <--- The Key Column
        IsApproved, CreationDate, LastLoginDate, LastLockoutDate, IsLockedOut
    )
    SELECT
        u.UserId,
        u.UserName,
        UPPER(u.UserName),
        m.Email,
        UPPER(m.Email),
        1, -- EmailConfirmed
        -- We store the formatted legacy string in PasswordHash strictly as a placeholder
        (m.Password + '|' + CAST(m.PasswordFormat AS VARCHAR(10)) + '|' + m.PasswordSalt), 
        NEWID(), NEWID(), NULL, 0, 0, 
        m.LastLockoutDate, 1, 0,
        -- Actual Legacy Data storage: Hash|Format|Salt
        (m.Password + '|' + CAST(m.PasswordFormat AS VARCHAR(10)) + '|' + m.PasswordSalt), 
        1, m.CreateDate, m.LastLoginDate, m.LastLockoutDate, 0
    FROM dbo.aspnet_Users u
    LEFT JOIN dbo.aspnet_Membership m ON u.UserId= m.UserId
    WHERE NOT EXISTS (SELECT 1 FROM dbo.AspNetUsers anp WHERE anp.Id = u.UserId)
      AND m.Password IS NOT NULL;

    PRINT 'Users migrated successfully.';

    -- 3. INSERT ROLES
    PRINT 'Migrating roles...';
    INSERT INTO dbo.AspNetRoles (Id, Name, NormalizedName)
    SELECT RoleId, RoleName, UPPER(RoleName)
    FROM dbo.aspnet_Roles r
    WHERE NOT EXISTS (SELECT 1 FROM dbo.AspNetRoles anr WHERE anr.Id = r.RoleId);

    -- 4. INSERT USER ROLES
    PRINT 'Migrating user-role relationships...';
    INSERT INTO dbo.AspNetUserRoles (UserId, RoleId)
    SELECT UserId, RoleId FROM dbo.aspnet_UsersInRoles ur
    WHERE NOT EXISTS (SELECT 1 FROM dbo.AspNetUserRoles anur WHERE anur.UserId = ur.UserId AND anur.RoleId = ur.RoleId);

    COMMIT TRANSACTION;
    PRINT 'Migration script completed successfully.';
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    PRINT 'Error: ' + ERROR_MESSAGE();
    THROW;
END CATCH

using Microsoft.AspNetCore.Identity;
using System.ComponentModel.DataAnnotations.Schema;

public class MyCustomUser : IdentityUser // Or ApplicationUser depending on your setup
{
    [Column(TypeName = "nvarchar(max)")]
    public string? LegacyPasswordSalt { get; set; }
}

using Microsoft.AspNetCore.Identity;
using System;
using System.Security.Cryptography;
using System.Text;

public class FallbackPasswordHasher : PasswordHasher<MyCustomUser>
{
    // The legacy field format: Hash|Format|Salt
    private const int LegacyHashIndex = 0;
    private const int LegacyFormatIndex = 1; 
    private const int LegacySaltIndex = 2;
    private const int ExpectedLegacyPropertyCount = 3;
    private const int HashedPasswordFormat = 1; // 1 = Hashed

    public override PasswordVerificationResult VerifyHashedPassword(MyCustomUser user, string hashedPassword, string providedPassword)
    {
        // 1. Modern Hash Check
        // If LegacyPasswordSalt is empty, the user has already been migrated or is new.
        if (string.IsNullOrEmpty(user.LegacyPasswordSalt))
        {
            return base.VerifyHashedPassword(user, hashedPassword, providedPassword);
        }

        // 2. Legacy Hash Fallback
        string[] passwordProperties = user.LegacyPasswordSalt.Split('|');
        if (passwordProperties.Length < ExpectedLegacyPropertyCount)
        {
            return PasswordVerificationResult.Failed;
        }

        string legacyHashBase64 = passwordProperties[LegacyHashIndex];
        string saltBase64 = passwordProperties[LegacySaltIndex];
            
        // We assume format is '1' (Hashed).
        // If you have users with format '0' (Clear), handling that here is a security risk.
        byte[] providedHashBytes = HashLegacyPassword(providedPassword, HashedPasswordFormat, saltBase64);
        
        if (providedHashBytes == null) return PasswordVerificationResult.Failed;

        byte[] storedHashBytes;
        try
        {
            storedHashBytes = Convert.FromBase64String(legacyHashBase64);
        }
        catch (FormatException)
        {
            return PasswordVerificationResult.Failed;
        }

        // 3. Secure Comparison
        if (CryptographicOperations.FixedTimeEquals(providedHashBytes, storedHashBytes))
        {
            // Success! Return 'SuccessRehashNeeded' to trigger an automatic database update.
            return PasswordVerificationResult.SuccessRehashNeeded;
        }

        return PasswordVerificationResult.Failed;
    }

    public override string HashPassword(MyCustomUser user, string password)
    {
        // When creating a NEW hash (e.g. Change Password, or Re-hashing after login),
        // we must clear the legacy field to ensure future logins use the modern standard.
        user.LegacyPasswordSalt = null;
        return base.HashPassword(user, password);
    }

    /// <summary>
    /// Replicates the specific HMACSHA512 logic used by the legacy SqlMembershipProvider.
    /// </summary>
    private byte[] HashLegacyPassword(string password, int passwordFormat, string salt)
    {
        if (passwordFormat != HashedPasswordFormat) return null;
        if (string.IsNullOrEmpty(password) || string.IsNullOrEmpty(salt)) return null;

        byte[] passwordBytes;
        byte[] saltBytes;
            
        try
        {
            // IMPORTANT: Membership provider used Unicode (UTF-16), not UTF-8.
            passwordBytes = Encoding.Unicode.GetBytes(password);
            saltBytes = Convert.FromBase64String(salt);
        }
        catch
        {
            return null;
        }

        using (var hmac = (KeyedHashAlgorithm)HashAlgorithm.Create("HMACSHA512"))
        {
            // --- Replicated ASP.NET Membership Key Logic ---
            // Do not refactor this block. It handles specific key padding used by the old provider.
            if (hmac.Key.Length == saltBytes.Length)
            {
                hmac.Key = saltBytes;
            }
            else if (hmac.Key.Length < saltBytes.Length)
            {
                var key = new byte[hmac.Key.Length];
                Buffer.BlockCopy(saltBytes, 0, key, 0, key.Length);
                hmac.Key = key;
            }
            else 
            {
                var key = new byte[hmac.Key.Length];
                for (var i = 0; i < key.Length;)
                {
                    var len = Math.Min(saltBytes.Length, key.Length - i);
                    Buffer.BlockCopy(saltBytes, 0, key, i, len);
                    i += len;
                }
                hmac.Key = key;
            }
            // -----------------------------------------------

            return hmac.ComputeHash(passwordBytes);
        }
    }
}

services.AddScoped(typeof(IPasswordHasher<>), typeof(FallbackPasswordHasher<>));
services.AddCmsAspNetIdentity<MyCustomUser>();