Bulk Import of User Passwords into Insite

To encrypt the user password using batch SQL scripts will require to create a SQL Server Custom User Defined Function. The first step will be to create a SQL Server Database Project in Visual Studios. The database project will be named XCommerce.DB.

After the project has been created set the Target Framework to .Net Framework 4.5.2. In addition add a reference to Microsoft AspNet.Identity.Core

The next step is to create SQL CLR C# User Defined Function that is named GetEncryptedPassword. The function will call the Rfc2898DeriveBytes method to encrypt the password.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Cryptography;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetEncryptedPassword(string password)
{
byte[] salt;
byte[] buffer2;
if (password == null)
{
throw new ArgumentNullException("password");
}
using (Rfc2898DeriveBytes bytes = new Rfc2898DeriveBytes(password, 0x10, 0x3e8))
{
salt = bytes.Salt;
buffer2 = bytes.GetBytes(0x20);
}
byte[] dst = new byte[0x31];
Buffer.BlockCopy(salt, 0, dst, 1, 0x10);
Buffer.BlockCopy(buffer2, 0, dst, 0x11, 0x20);
string encryptPassword = Convert.ToBase64String(dst);

return new SqlString(encryptPassword);
}
}

After the class has been created the function needs to be deployed to the SQL Server. To deploy the project right click on the Project in Solution Explorer and select publish. In the publish dialog box click the Target database connection then publish.

The next step is to enable SQL Server to call the user defined function. Run the following script to enable the functionality

sp_configure 'clr enabled', 1;
GO

Finally run the following insert to add the user password into the AspNetUsers table.

INSERT [dbo].[AspNetUsers] ([Id], [Email], [EmailConfirmed], [PasswordHash], [SecurityStamp], [PhoneNumber], [PhoneNumberConfirmed], [TwoFactorEnabled], [LockoutEndDateUtc], [LockoutEnabled], [AccessFailedCount], [UserName]) VALUES
(NEWID(), N'testUser@example.com', 0, dbo.GetEncryptedPassword('HelloWorldPassword'), NEWID(), NULL, 0, 0, NULL, 1, 0, N'TestUser')