Let's Encrypt a BLOB

Mike C — Wed, 08 Apr 2009 04:01:00 GMT

SQL Server 2008 has an impressive array of encryption features -- cell-level symmetric and asymmetric encryption, key management, EKM, TDE, and more. But they do have some limitations. Symmetric encryption functions, for instance, can only encrypt slightly less than 8,000 bytes of data (the additional information like random IV and authenticator hash added to the result take up some space in the varbinary(8000) result). If the result of your encryption returns more than 8,000 bytes of data the encryption functions will return NULL.

If you run into a situation where you need to encrypt BLOB data in the database--graphics, documents, XML, or other sensitive data--the built-in functions will make your life a bit more difficult. When this happens you can either chop up your data into small chunks, all slightly less than 8K, and encrypt them separately; or you can use the CLR. Fortunately SQL CLR and the .NET System.Security.Cryptography namespace make it easy to encrypt your BLOB data.

In this post I'm going to write a couple of functions to encrypt and decrypt data by passphrase using CLR. These functions work similarly to the EncryptByPassPhrase and DecryptByPassPhrase functions, except for two differences:

These CLR functions use the AES algorithm with a 256-bit key to encrypt and decrypt your data
These functions can encrypt and decrypt BLOB data up to 2.1 GB in size

(See http://technet.microsoft.com/en-us/library/ms190357.aspx and http://technet.microsoft.com/en-us/library/ms188910.aspx for more information about EncryptByPassPhrase and DecryptByPassPhrase).

The CLR functions are EncryptAesByPassPhrase and DecryptAesByPassPhrase. The EncrytAesByPassPhrase function has this signature:

EncryptAesByPassPhrase (PassPhrase nvarchar(4000), Plaintext varbinary(max), AddAuthenticator bit, Authenticator nvarchar(4000))

The function generates a 256-bit AES encryption key and IV based on your PassPhrase (and the SHA-1 hash of your Authenticator if you include one and set the AddAuthenticator bit to 1). It then uses the encryption key and IV to encrypt your Plaintext. The result is a varbinary(max), and the first 16 bytes of the result are a salt/IV value required to regenerate your key on decryption calls. The DecryptAesByPassPhrase function looks like this:

DecryptAesByPassPhrase (PassPhrase nvarchar(4000), Ciphertext varbinary(max), AddAuthenticator bit, Authenticator nvarchar(4000))

The parameters mirror the EncryptAesByPassPhrase parameters, except that you need to pass DecryptAesByPassPhrase the encrypted Ciphertext in place of the Plaintext. The result is the Plaintext in varbinary(max) form. If the Plaintext should be another data type (varchar, nvarchar, etc.) you need to explicitly cast it.

Here's what the encryption function looks like in C#. I've added comments inline.

[Microsoft.SqlServer.Server.SqlFunction

(

IsDeterministic = false,

DataAccess = DataAccessKind.None

)]

[return: SqlFacet(MaxSize = -1)]

public static SqlBytes EncryptAesByPassPhrase

(

SqlString PassPhrase,

[SqlFacet(MaxSize = -1)] SqlBytes Plaintext,

SqlBoolean AddAuthenticator,

SqlString Authenticator

)

{

try

{

// Automatically return NULL if passphrase or plaintext is NULL

if (PassPhrase.IsNull || Plaintext.IsNull)

return SqlBytes.Null;

// Generate hash for authenticator

SHA1Managed Sha1 = new SHA1Managed();

string AuthHash = ""; // If authenticator not used, use empty string

// Convert the authenticator hash to Base64 to append it to passphrase without conversion problem

if (AddAuthenticator.IsTrue && !Authenticator.IsNull)

AuthHash = Convert.ToBase64String

(

Sha1.ComputeHash

(

Encoding.Unicode.GetBytes(Authenticator.Value)

)

);

string AuthPass = PassPhrase.Value + AuthHash; // Append authenticator to passphrase

// Next derive a key from the passphrase + authenticator, with random 16-bit Salt

Rfc2898DeriveBytes KeyGenerator = new Rfc2898DeriveBytes(AuthPass, 16);

// Create a Rijndael/AES encryption object

Rijndael Aes = Rijndael.Create();

Aes.IV = KeyGenerator.GetBytes(Aes.BlockSize >> 3); // Assign the IV

Aes.Key = KeyGenerator.GetBytes(Aes.KeySize >> 3); // Assign the Key

// Now get the raw plain text

byte[] rawData = Plaintext.Value;

// Use a MemoryStream wrapping a CryptoStream with a Rijndael encryptor to encrypt the data

using (MemoryStream memoryStream = new MemoryStream())

{

using

(

CryptoStream cryptoStream = new CryptoStream

(

memoryStream,

Aes.CreateEncryptor(),

CryptoStreamMode.Write

)

{

// First write out the 16-byte salt so we can regenerate the same key next time

memoryStream.Write(KeyGenerator.Salt, 0, 16);

// Now write out the encrypted data

cryptoStream.Write(rawData, 0, rawData.Length);

cryptoStream.Close();

// Convert the encrypted data in memory to an array and return as a SqlBytes object

byte[] encrypted = memoryStream.ToArray();

return new SqlBytes(encrypted);

}

catch

{

// Return NULL if an encryption error occurs

return SqlBytes.Null;

}

The DecryptAesByPassPhrase function looks like this:

[Microsoft.SqlServer.Server.SqlFunction

(

IsDeterministic = true,

DataAccess = DataAccessKind.None

)]

[return: SqlFacet(MaxSize = -1)]

public static SqlBytes DecryptAesByPassPhrase

(

SqlString PassPhrase,

[SqlFacet(MaxSize = -1)] SqlBytes Ciphertext,

SqlBoolean AddAuthenticator,

SqlString Authenticator

)

{

try

{

// Automatically return NULL if passphrase or plaintext is NULL

if (PassPhrase.IsNull || Ciphertext.IsNull)

return SqlBytes.Null;

// Get the ciphertext into a byte array

byte[] rawData = Ciphertext.Value;

// Get the 16-byte salt from the byte array

byte[] Salt = new byte[16];

for (int i = 0; i < 16; i++)

Salt[i] = rawData[i];

// Generate hash for authenticator

SHA1Managed Sha1 = new SHA1Managed();

string AuthHash = ""; // If no authenticator, use empty string

// Convert the authenticator hash to Base64 to append it to passphrase without conversion problem

if (AddAuthenticator.IsTrue && !Authenticator.IsNull)

AuthHash = Convert.ToBase64String(Sha1.ComputeHash(Encoding.Unicode.GetBytes(Authenticator.Value)));

string AuthPass = PassPhrase.Value + AuthHash; // Append authenticator to passphrase

// Next derive a key from the passphrase + authenticator, with 16-bit Salt

Rfc2898DeriveBytes keyGenerator = new Rfc2898DeriveBytes(AuthPass, Salt);

// Create a Rijndael/AES encryption object

Rijndael aes = Rijndael.Create();

aes.IV = keyGenerator.GetBytes(aes.BlockSize >> 3); // Assign the IV

aes.Key = keyGenerator.GetBytes(aes.KeySize >> 3); // Assign the key

// Wrap a CryptoStream in a MemoryStream to decrypt the data

using (MemoryStream memoryStream = new MemoryStream())

{

using

(

CryptoStream cryptoStream = new CryptoStream

(

memoryStream,

aes.CreateDecryptor(),

CryptoStreamMode.Write

)

{

// Decrypt and write out the decrypted data with the CryptoStream

// ...ignore the leading 16 bytes, the Salt

cryptoStream.Write(rawData, 16, rawData.Length - 16);

cryptoStream.Close();

// Put the decrypted MemoryStream in a byte array and return as SqlBytes

byte[] decrypted = memoryStream.ToArray();

return new SqlBytes(decrypted);

}

catch

{

// If there's an exception return NULL

return SqlBytes.Null;

}

Here's a quick sample that encrypts a 20,000 byte string and then decrypts it again using the functions:

-- Create a 20,000 byte string

DECLARE @c varchar(max) = REPLICATE(CAST('0123456789' AS varchar(max)), 2000);

DECLARE @v varbinary(max); -- a varbinary(max) variable to hold the result

-- First encrypt by passphrase

SET @v = dbo.EncryptAesByPassPhrase

(

'This is my passphrase',

CAST(@c AS varbinary(max)),

'MyAuthenticator'

);

-- Then decrypt by passphrase

DECLARE @d varchar(max);

SET @d = CAST

(

dbo.DecryptAesByPassPhrase

(

'This is my passphrase',

@v,

'MyAuthenticator'

) AS varchar(max)

);

-- Get the length of the result

SELECT DATALENGTH(@d);

-- Get the actual result

SELECT @d;

The sample project with these two functions in it for Visual Studio 2008 is in the attached file.

This is just a very simple example of the type of encryption functionality you can use from SQL CLR. I'll demonstrate some more SQL CLR-enabled cryptography in a future post.

Michael Coles: Sergeant SQL : encryption, SQL CLR

Let's Encrypt a BLOB