Michael Coles: Sergeant SQL : SQL CLR

Find a Hash Collision, Win $100

Mike C — Sat, 17 Apr 2010 21:38:00 GMT

Margarity Kerns recently published a very nice article at SQL Server Central on using hash functions to detect changes in rows during the data warehouse load ETL process. On the discussion page for the article I noticed a lot of the same old arguments...(read more)

Mission Accomplished: NJSQL Saturday Event

Mike C — Sun, 25 Oct 2009 00:03:00 GMT

Thanks to all those who made the NJSQL Saturday event held in Iselin, NJ today a success! Those lucky enough to attend were given a sneak preview of one of the best young presenters out there--MVP Jacob Sebastian--who will also be speaking at PASS. I...(read more)

Let's Hash a BLOB

Mike C — Sun, 12 Apr 2009 20:50:00 GMT

In my last post I talked about how to work around a couple of the limitations of SQL Server encryption by using SQL CLR and the .NET Framework to encrypt a BLOB value (up to 2.1 GB in size), using any supported algorithm you choose. In the example I used AES to encrypt data using a passphrase. SQL Server 2008 also allows you to generate cryptographic hashes of binary data using the MD2, MD4, MD5 or SHA-1 hash algorithms. The HashBytes function provides this functionality, as shown in the sample below:

SELECT HashBytes(N'SHA1', 'This is a test');

The result is a 160-bit binary hash value:


0xA54D88E06612D820BC3BE72877C74F257B561B19

The MD2, MD4 and MD5 algorithms all produce 128-bit binary hash values. There's a problem: the MDn-series of 128-bit hashes are currently considered insecure by cryptographers, and it's not advisable to use them for cryptographically secure applications. SHA-1 is considered more secure, but it's hash value length of 160 bits is considered relatively small by today's standards. The National Institute of Standards and Technology has already taken steps to require all federal agencies to stop using SHA-1 in 2010. Instead they are requiring federal agencies to use SHA-2 family of algorithms, which includes SHA-256, SHA-384 and SHA-512, which generate more cryptographically secure hash values that are 256, 384 and 512 bits in length.

SQL Server's HashBytes function doesn't give you the ability to access these more secure hash functions, but they are available through the .NET Framework and SQL CLR. The SQL CLR GetHash function below mirrors the functionality of the built-in HashBytes function. This function accepts an Algorithm name, which can be SHA256, SHA384, or SHA512. You also need to pass the Plaintext value to be hashed to the function. While HashBytes is limited to an 8,000 byte plaintext value (it truncates everything past 8,000 bytes), the GetHash function accepts a varbinary(max) up to 2.1 GB in size.


[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlBytes GetHash
(
  SqlString Algorithm, 
  [SqlFacet(MaxSize = -1)] SqlBytes Plaintext
)
{
  if (Algorithm.IsNull || Plaintext.IsNull)
    return SqlBytes.Null;
  bool HashDefined = true;
  HashAlgorithm Hash = null;
  switch (Algorithm.Value.ToUpper())
  {
    case "SHA256":
      Hash = new SHA256Managed();
      break;
 
    case "SHA384":
      Hash = new SHA384Managed();
      break;
 
    case "SHA512":
      Hash = new SHA512Managed();
      break;
 
    default:
      HashDefined = false;
      break;
  }
  if (!HashDefined)
    throw new Exception("Unsupported hash algorithm - use SHA256, SHA384 or SHA512");
  byte[] HashBytes = Hash.ComputeHash(Plaintext.Value);
  // Convert result to a SqlBytes value
  return new SqlBytes(HashBytes);
}

As you can see, accessing .NET Framework hash functionality through the SQL CLR is very simple. In a future post I'll talk about making your hash values even more secure to protect against so-called "rainbow table" attacks.

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.