Search results matching tags 'SQL Server 2008' and 'cryptography'

SSIS: Mo' Secure Configurations

Mike C — Mon, 18 Jan 2010 22:19:00 GMT

I ran into an issue the other day and I needed a solution for automically configuring my SSIS packages from securely stored DBMS connection strings. Problem is that most DBMSs don’t support Integrated Authentication—they require a username and password. Storing the username/password combo in the connection string in plain text is a security risk, so the question becomes an issue of storing this information securely.

There are several ways to store configuration data for SSIS packages. Probably one of the easiest and most popular methods is to use .dtsConfig files stored in the file system. This method presents some challenges when you want to secure usernames and passwords in the files. I won’t go into the details, since this post isn’t about .dtsConfig files, but Jamie Thompson does a great job of running down the .dtsConfig file options over at his old blog: http://consultingblogs.emc.com/jamiethomson/archive/2007/04/26/SSIS_3A00_-Storing-passwords.aspx.

After abandoning .dtsConfig files the idea of combining SSIS SQL Server configurations and cell-level encryption popped into my head. I created a quick proof of concept and tested it. Then SSIS guru Jamie Thompson pointed me over to a blog post from Larry Charlton (from way back in 2007!) at http://curionorg.blogspot.com/2007/05/encrypted-sql-server-ssis.html that is very similar to what I came up with. Kudos to Larry for a nice piece of code. My concept is very similar to Larry’s, but with one major difference in implementation that I’ll cover below.

So the idea is that SSIS, by default, looks for a table named [dbo].[SSIS Configurations] to get its configuration data. This script builds the table:

CREATE TABLE [dbo].[SSIS Configurations]

(

ConfigurationFilter nvarchar(255) NOT NULL,

ConfiguredValue nvarchar(255) NULL,

PackagePath nvarchar(255) NOT NULL,

ConfiguredValueType nvarchar(20) NOT NULL

);

Here’s a breakdown of the columns that SSIS is looking for:

ConfigurationFilter holds a name you assign to the configured value. In my sample package I named my entry “Password Connection String”.
ConfiguredValue is the actual value. In the sample package this is the actual connection string with username and password.
PackagePath is the SSIS package property path. In the example the connection string value is used to configure the connection string of an OLEDB Connection named “Password Login Connection”. The path is “\Package.Connections[Password Login Connection].Properties[ConnectionString]”.
ConfiguredValueType tells SSIS the data type of your ConfiguredValue. In this case the data type is “String”.

Once you create the [dbo].[SSIS Configurations] table you can populate it with a connection string either through the SSIS designer or using a SQL INSERT statement like the following:

INSERT INTO [dbo].[SSIS Configurations]

(

ConfigurationFilter,

ConfiguredValue,

PackagePath,

ConfiguredValueType

)

VALUES

(

'Password Connection String',

'Data Source=(local);User ID=TestLogin;Password=p@$$w0rd;Initial Catalog=Test;',

'\Package.Connections[Password Login Connection].Properties[ConnectionString]',

'String'

);

Note: if you use the SSIS designer in BIDS to populate the table with connection strings, it will strip out sensitive information like passwords.

You can use SQL Server security to restrict access to this table, but for my purposes that wasn’t good enough. Due to IT policies I specifically had to obscure the plain text of the connection string since it contained a password. For that I decided to turn to SQL Server cell-level encryption. The trick is that SSIS needs to be able to read the data directly from the table as plain text, but it shouldn’t be stored as plain text.

Larry’s implementation uses the T-SQL EncryptByPassPhrase and DecryptByPassPhrase functions, both of which require you to store a plain text passphrase somewhere to encrypt and decrypt your data and limit you to Triple-DES encryption. But the main problem I have with this method is that storing a plain text passphrase somewhere doesn’t solve the security issue—it just passes the buck. The same problem is evident when you try to encrypt SSIS packages by password.

Make no mistake, encryption key management is the hardest part of encryption—it’s a simple case of Turtles All The Way Down. Personally I don’t want to take on the responsibility for managing plain text passwords and passphrases—I’d rather let the server do that for me. So my solution needs to eliminate the need to store and pass plaintext passwords and passphrases to encrypt and decrypt the secure data.

To secure data using cell-level encryption first we need to do a little setup. The first step is to create a database master key (DMK), as shown below.

Note: always backup new encryption keys and certificates as soon as you create them!

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Ma5t3rK3yP@55';

Then we need to create a certificate that is secured by the database master key (DMK). For simplicity I named the certificate ConfigCert.

CREATE CERTIFICATE ConfigCert

AUTHORIZATION TestUser

ENCRYPTION BY PASSWORD = 'myT3stP@$$'

WITH SUBJECT = 'Configuration Encryption Certificate',

EXPIRY_DATE = '20201231';

ALTER CERTIFICATE ConfigCert

WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'myT3stP@$$');

Next we create a symmetric encryption key that’s protected by the certificate. I called the symmetric key ConfigSymKey.

CREATE SYMMETRIC KEY ConfigSymKey

AUTHORIZATION TestUser

WITH ALGORITHM = AES_256,

KEY_SOURCE = 'I am the very model of the modern major general',

IDENTITY_VALUE = 'Stella! Stella!'

ENCRYPTION BY CERTIFICATE ConfigCert;

Note: it’s a best practice to always specify a KEY_SOURCE and IDENTITY_VALUE in the CREATE SYMMETRIC KEY statement so you can recreate the same symmetric key when necessary. There is no BACKUP SYMMETRIC KEY statement.

Then we need to create a slight variant of the [dbo].[SSIS Configurations] table designed to store the encrypted configuration values, as shown below.

CREATE TABLE [dbo].[SSIS_Configurations_Encrypted]

(

ConfigurationFilter nvarchar(255) NOT NULL,

ConfiguredValue varbinary(512) NULL, -- This column changed

PackagePath nvarchar(255) NOT NULL,

ConfiguredValueType nvarchar(20) NOT NULL

);

The only difference between this table and the [dbo].[SSIS Configurations] table is the ConfiguredValue column. In the encrypted version of the table this column is varbinary instead of varchar, and it’s bigger than it’s unencrypted version. The reasons are that (1) all SQL Server encryption functions return varbinary data, and (2) the encrypted data will always be longer than the plain text.

The next step is to drop the [dbo].[SSIS Configurations] table and create a view with the same name.

DROP TABLE [dbo].[SSIS Configurations];

CREATE VIEW [dbo].[SSIS Configurations]

SELECT ConfigurationFilter,

CAST

(

DecryptByKeyAutoCert

(

Cert_ID(N'ConfigCert'), NULL, ConfiguredValue

) AS nvarchar(255)

) AS ConfiguredValue,

PackagePath,

ConfiguredValueType

FROM [dbo].[SSIS_Configurations_Encrypted];

The view uses the DecryptByKeyAutoCert function to automatically decrypt the ConfiguredValue column. This works because SSIS does not differentiate between the view and the table of the same name. This is a good thing, because it simplifies our process quite a bit. Our next step is to make sure that inserts and updates to the view properly update the underlying table with encrypted data. For this we create an INSTEAD OF INSERT, UPDATE trigger.

CREATE TRIGGER SSIS_Configurations_Trigger

ON [dbo].[SSIS Configurations]

INSTEAD OF INSERT, UPDATE

BEGIN

SET NOCOUNT ON;

-- SQL treats updates as a logical delete followed by an insert

-- So look at the deleted virtual table to see if anything was deleted

DECLARE @IsUpdate char(1) = CASE WHEN (SELECT COUNT(*) FROM deleted) = 0 THEN 'N'

ELSE 'Y'

END;

-- Open the symmetric key

OPEN SYMMETRIC KEY ConfigSymKey

DECRYPTION BY CERTIFICATE ConfigCert;

-- Do a merge

MERGE [dbo].[SSIS_Configurations_Encrypted] AS target

USING inserted AS source

(

target.ConfigurationFilter = source.ConfigurationFilter

AND target.PackagePath = source.PackagePath

AND target.ConfiguredValueType = source.ConfiguredValueType

)

WHEN MATCHED AND @IsUpdate = 'Y' THEN

UPDATE SET target.ConfiguredValue = EncryptByKey(Key_Guid(N'ConfigSymKey'), source.ConfiguredValue)

WHEN NOT MATCHED AND @IsUpdate = 'N' THEN

INSERT

(

ConfigurationFilter,

ConfiguredValue,

PackagePath,

ConfiguredValueType

)

VALUES

(

source.ConfigurationFilter,

EncryptByKey(Key_Guid(N'ConfigSymKey'), source.ConfiguredValue),

source.PackagePath,

source.ConfiguredValueType

);

-- Close the symmetric key

CLOSE SYMMETRIC KEY ConfigSymKey;

END;

This trigger intercepts INSERT and UPDATE requests to the [dbo].[SSIS Configurations] view to automatically encrypt the ConfiguredValue column, which is then used to update the encrypted data in the underlying [dbo].[SSIS_Configurations_Encrypted] table. We can re-run the previous INSERT statement from the beginning of the article to test it.

INSERT INTO [dbo].[SSIS Configurations]

(

ConfigurationFilter,

ConfiguredValue,

PackagePath,

ConfiguredValueType

)

VALUES

(

'Password Connection String',

'Data Source=(local);User ID=TestLogin;Password=p@$$w0rd;Initial Catalog=Test;',

'\Package.Connections[Password Login Connection].Properties[ConnectionString]',

'String'

);

A couple of quick SELECT queries reveal the results:

SELECT *

FROM dbo.[SSIS_Configurations_Encrypted];

SELECT *

FROM dbo.[SSIS Configurations];

The results from the first query show the ConfiguredValue is stored unreadable/encrypted. Querying the view returns the unencrypted plain text.

Making a clear fashion statement with the classic belt and suspenders, you can deny permissions to the underlying table and the view to those that don’t need to see it, and you can deny access to the symmetric key and certificate to those same folks. If someone gains access to the view or the table, they still need access to the encryption key and certificate to decrypt the contents. Without this access, anyone querying the view will see NULL in the ConfiguredValue column.

Sample scripts and SSIS package are attached to this entry.

UPDATE: Andy L. pointed out that [dbo].[SSIS Configurations] is just the default name SSIS uses for your configuration entries table. You can actually change it in the designer at design time, or in SQL Server at build time. The designer lets you choose which table to point at. The important thing is that the columns need to have the correct names.

"Cloning" Symmetric Keys

Mike C — Thu, 18 Jun 2009 01:40:00 GMT

It's well-known by now that SQL Server 2005 and 2008 include new encryption-related statements that allow you to create and administer encryption keys. You can use CREATE CERTIFICATE to create or import a certificate or DROP ASYMMETRIC KEY to remove an asymmetric key from the database, for instance. One of the interesting ommissions from the T-SQL encryption statements is the statements necessary to backup and restore a symmetric key. Why would you want to do this? I can think of a couple of reasons off the top of my head:

You need to backup symmetric keys (and all other encryption keys, in fact) as part of an overall disaster recovery (DR) program. If a server needs to be rebuilt you obviously need a way to restore all encryption keys.
You need to implement the same symmetric keys on multiple servers. There could be a couple of reasons for this -- you might encrypt data on one server and decrypt it on another, or you might be load-balancing across a server farm and need identical encryption keys on multiple servers simultaneously.

It seems like a bit of an oversight to not include BACKUP and RESTORE SYMMETRIC KEY options in T-SQL, but in practice you can effectively achieve the same end results with the standard CREATE SYMMETRIC KEY statement. Basically the CREATE SYMMETRIC KEY statement gives you an option to "clone" the exact same symmetric key on any SQL Server 2005 or 2008 instance, anywhere, at any time. To create a cloneable symmetric key you need to specify two special CREATE SYMMETRIC KEY options:

The IDENTITY_VALUE option, which SQL Server uses to generate a GUID (uniqueidentifier) for the key
The KEY_SOURCE option, which SQL Server uses as key material to generate the actual key

As long as you specify the same values for the IDENTITY_VALUE and KEY_SOURCE options (and the same ALGORITHM), your symmetric key will be exactly the same no matter where, when, or how many times you create it. To be honest, if I were creating a list, always use IDENTITY_VALUE and KEY_SOURCE would be listed in the top 10 list of SQL Server encryption best practices.

Here's a quick sample demonstrating the CREATE SYMMETRIC KEY statement with IDENTITY_VALUE and KEY_SOURCE specified:

CREATE SYMMETRIC KEY test_aes128_key
WITH KEY_SOURCE = 'I am the very model of a modern major general',
IDENTITY_VALUE = 'E pluribus unum',
ALGORITHM = AES_128
ENCRYPTION BY PASSWORD = 'p@$$w0rd';

This CREATE SYMMETRIC KEY statement will create the same symmetric encryption key on any SQL Server 2005 or 2008 instance on which you run it. This brings up another point, about security. If the same KEY_SOURCE and IDENTITY_VALUE options can create the exact same encryption key on any of your servers, they will create the exact same encryption key on any of my servers, or any server owned by any hacker anywhere in the world. So once you've run your CREATE SYMMETRIC KEY statement, the IDENTITY_VALUE and KEY_SOURCE need to be handled like any other secure information. Don't leave them lying around where just anyone can access them. Store them with your certificates, key backups, and other confidential materials in a secure off-site location.

So what happens when you don't specify the IDENTITY_VALUE and KEY_SOURCE options? Well, basically SQL Server generates an unpredictable GUID to identify the symmetric key and the encryption key source material is randomly generated. Basically you'll never regenerate the exact same key again. Ever. There could be situations where this would be handy. The concept of "session keys" comes to mind. A session key is basically a "temporary" key that's only required to encrypt data for a user during a single session. Since it only exists for the life of the session, a totally randomly-generated key is just fine.

For my tastes, it would make more sense to require IDENTITY_VALUE and KEY_SOURCE options by default. If you didn't want to specify both of these options there should be another option/indicator specifically to say that you want these options generated randomly. At any rate, it's a good idea to get into the habit of treating these options as if they are mandatory unless they have a very specific special-purpose requirement (e.g., "session keys").

Protecting Your Data @ Rest Presentation (Tuesday)

Mike C — Sun, 17 May 2009 01:01:00 GMT

Tuesday night I'll be presenting on SQL Server encryption to the NJSQL user's group in Parsippany. More information about this event and NJSQL can be found here: http://njsql.org/Default.aspx.

UPDATE: Uploaded the encryption presentation, attached to this post.

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.