There are a number of steps you have to take to do what you want. First if you can find something out there that uses the same encryption algorithm you're using in your C# code then you should just use the existing library (make sure it's a secure implmentation anyway). The following answer will get you where you want to go, however please make sure to change the salt value to a random salt that you can use, and/or look up how to pass in an IV and key without using the Rfc2898DeriveBytes class.
First you need a class that will do the encryption, this class can be used in both your project that you have .net code in, as well as the project you will create to get it on SQL Server.
internal class AES
{
static readonly byte[] u8_Salt = new byte[] { 0x26, 0x19, 0x81, 0x4E, 0xA0, 0x6D, 0x95, 0x34, 0x26, 0x75, 0x64, 0x05, 0xF6 };
public static string EncryptString(string plainText, string password)
{
Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(password, u8_Salt);
using (RijndaelManaged i_Alg = new RijndaelManaged { Key = pdb.GetBytes(32), IV = pdb.GetBytes(16) })
{
using (var memoryStream = new MemoryStream())
using (var cryptoStream = new CryptoStream(memoryStream, i_Alg.CreateEncryptor(), CryptoStreamMode.Write))
{
byte[] data = Encoding.UTF8.GetBytes(plainText);
cryptoStream.Write(data, 0, data.Length);
cryptoStream.FlushFinalBlock();
return Convert.ToBase64String(memoryStream.GetBuffer(), 0, (int)memoryStream.Length);
}
}
}
public static string Decrypt(string cipherText, string password)
{
Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(password, u8_Salt);
using (RijndaelManaged i_Alg = new RijndaelManaged { Padding = PaddingMode.Zeros, Key = pdb.GetBytes(32), IV = pdb.GetBytes(16) })
{
using (var memoryStream = new MemoryStream())
{
using (var cryptoStream = new CryptoStream(memoryStream, i_Alg.CreateDecryptor(), CryptoStreamMode.Write))
{
byte[] data = Convert.FromBase64String(cipherText);
cryptoStream.Write(data, 0, data.Length);
cryptoStream.Flush();
return Encoding.UTF8.GetString(memoryStream.ToArray());
}
}
}
}
}
Next you will need to create a CLR class for SQL Server. It's a fairly simple process, just create a new project and add that class to your project. After your project is created, add a new class that will wrap the encryption class.
public class AESManagedProc
{
[Microsoft.SqlServer.Server.SqlFunction]
public static string Encrypt(string plainText, string password)
{
return AES.EncryptString(plainText, password);
}
[Microsoft.SqlServer.Server.SqlFunction]
public static string Decrypt(string cipherText, string password)
{
return AES.Decrypt(cipherText, password);
}
}
Your project should look like this now
The next step is to get it onto SQL Server. You will need to first make sure that the clr is enabled on the SQL SErver, so to enable it use the master database and do the following:
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
Next create the assembly
CREATE ASSEMBLY AESEncryption FROM 'C:\PATH_TO_YOUR_DLL\SqlExtensions.dll' WITH PERMISSION_SET = SAFE;
Finally create two wrapper functions, one for encrypt and one for decrypt:
CREATE FUNCTION EncryptString
(
@plainText nvarchar(max), @password nvarchar(max)
)
RETURNS nvarchar(max)
AS
EXTERNAL NAME AESEncryption.[SqlExtensions.AESManagedProc].Encrypt
GO
CREATE FUNCTION DecryptString
(
@cipherText nvarchar(max), @password nvarchar(max)
)
RETURNS nvarchar(max)
AS
EXTERNAL NAME AESEncryption.[SqlExtensions.AESManagedProc].Decrypt
GO
Now to encrypt on SQL Server you just call the encryption/decryption functions.
SELECT dbo.EncryptString('test','test');
SELECT dbo.DecryptString(dbo.EncryptString('test','test'), 'test');
Desclaimer Make sure you don't use the u8_salt value in the code I gave you, that value is all over the internet in examples. Secondly make sure that your password is stored somewhere safe. Choose your password very wisley it will be used to generate your key so it's important that it's a safe choice.
