Microsoft SQL Server 2005 Database Encryption Step-by-Step

This is a how-to guide which will aims to help ms sql server developers and ms sql server administrators to implement Microsoft SQL Server 2005 Encryption methodologies.
This tutorial is a step-by-step guide for encryption and decryption in MS SQL Server 2005 and later (MS SQL2008 aka Katmai)

Creating Master Key

Before using encryption algoritms in SQL Server 2005 and SQL Server 2008, a master key should be created in the database where encryption is going to be used.
Note that master key is created seperately for each database on a SQL Server database instance
Before creating a master key, sql developers or sql server database administers that has the required permissions can run the below t-sql select query to see if a master key is created before.

SELECT * FROM sys.symmetric_keys

If there has been created a master key, you will see a result that is similar to below if you are running the t-sql select from sys.symmetric_keys view in MS SQL Server 2005,

Note that in SQL2008 (CTP6 or February CTP) sys.symmetric_keys view has additional columns:

name
principal_id
symmetric_key_id
key_length
key_algorithm
algorithm_desc
create_date
modify_date
key_guid
key_thumbprint
provider_type
cryptographic_provider_guid
cryptographic_provider_algid

It is important that for a database in MS SQL Server, there can be only one master key in other words a single master key can be created on a database. A second master key can not be created in a sql server database.

You can use the below t-sql script code in order to create a master key in the sql database.

/*************** CREATE MASTER KEY *********************************/
IF NOT EXISTS (
SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##'
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$EncryptionPassword12'
GO


It is important that you keep the encryption password in a safe place or keep backups of your sql server database master key.
I'm going to deal with master key backup later in this tutorial.

You can drop or remove an existing master key using the DROP MASTER KEY t-sql command.
If you try to drop a master key which has been used for creating other database objects like certificates the DROP MASTER KEY sql command will fail.

If you run the DROP MASTER KEY sql server statement after a certificate is defined which we will see in the next step, the following error message is going to be informing the sql server programmer about the dependent certificate.

Msg 15580, Level 16, State 1, Line 2
Cannot drop master key because certificate 'PasswordFieldCertificate' is encrypted by it.

Creating a Certificate

The second step for using encryption in a SQL Server database is creating the certificates that will be used for creating symmetric keys and encrypting database table column fields.
So creating a certificate is still a preparation step in the encryption process.
Encrypting table column values or sql variables is still a few steps ahead.
You can also view existing certificates in a MS SQL Server database by running a select query over sys.certificates view.

SELECT * FROM sys.certificates

You can run the below t-sql script to create a certificate.

/*************** CREATE CERTIFICATE *********************************/
IF NOT EXISTS (
SELECT * FROM sys.certificates WHERE name = N'PasswordFieldCertificate'
)
CREATE CERTIFICATE PasswordFieldCertificate WITH SUBJECT = 'Password Fields';
GO


You can also drop or remove an existing certificate from a database using the DROP CERTIFICATE certificate_name tsql syntax.

If you try to drop a certificate that is used during the creation of a symmetric key, etc. the following error message is going to be thrown by the SQL Server engine.

Msg 15352, Level 16, State 1, Line 2
The certificate cannot be dropped because one or more entities are either signed or encrypted using it.

Creating a Symmetric Key

After the certifates are created in the sql database, next the symmetric key is being generated by executing a CREATE SYMMETRIC KEY SQL Server command.
Again, you can check sys.symmetric_keys view name fields whether a key already exists and declared in the current database.
The following sql code script is enough to create a symmetric key which will be used for encryption and for decryption in the sql database.

/*************** CREATE SYMMETRIC KEY *********************************/
CREATE SYMMETRIC KEY PasswordFieldSymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE PasswordFieldCertificate;


If you try to create symmetric key with a name that exists among the sys.symmetric_keys system view, the following error message will be displayed:

Msg 15282, Level 16, State 1, Line 1
A key with name 'PasswordFieldSymmetricKey' or user defined unique identifier already exists or you do not have permissions to create it.

In the above CREATE SYMMETRIC KEY t-sql statement one important point is the algorithm parameter that is specified in the command.
Microsoft SQL Server can use the following algorithms in encryption sensitive data.
DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192 and AES_256
As system administrators or database admins, one important note for AES_128, AES_192 and AES_256 is AES encryption algorithm can not be used on Microsoft Windows 2000 Servers and Windows XP operating systems. If you have a MS SQL Server instance running on a Win2k server, then it is better to create the symmetric keys using the TRIPLE_DES algorithm, for instance. Otherwise, your script will fail when it is run on a sql server which is installed on Windows 2000 servers and Windows XP computers since AES is not supported on those operating systems. You should consider this point while choosing an encryption algorithm for your SQL Server database applications.

Preparing Database Tables to Store Encrypted Data

Now it is time to create a table column field which will store or keep the encrypted values in it for us.
Since I'm working on AdventureWorks database which I have downloaded and installed as a sample database for MS SQL Server 2008 CTP 6, I'll try to alter a table in AdventureWorks db and add a new column for storing encrypted data.
Since password fields is not open or visible in Person.Contact table, I give up searching for a password field in any of tables in AdventureWorks. I want to encrypt EmailAddress data column values in Person.Contact table.
I'm adding a table column named EncryptedEmailAddress which is declared as varbinary data type and 256 bytes in length using the below ALTER TABLE sql command.
If this varbinary field is not enough in length or size to store the resultant encrypted data, the encryption sql commands will generate an error.

ALTER TABLE Person.Contact
ADD EncryptedEmailAddress varbinary(256);
GO


Now run the below select query and observe that the Encrypted e-mail address column is null. We have not updated this encrypted field yet.

SELECT EmailAddress, EncryptedEmailAddress FROM Person.Contact

Encrypting Sensitive Data

Here is the final step for encrypting a table field using MS SQL Server encryption algoritms and methods.
An encryption can be done on a string or binary value (in nvarchar, varchar, varbinary, nchar, char, binary sql data types) in SQL Server using the EncryptByKey t-sql function.
EncryptByKey encrypts a given data by using a symmetric key.
The necessary symmetric key information can be passed to the EncryptByKey function using the Key_GUID Transact-SQL function. Key_GUID returns the uniqueidentifier (GUID) of the symmetric key whose key name is specified in the Key_GUID function.
The output of the encryption function EncryptByKey is a varbinary with a maximum lenth 8000 bytes.

Since EncryptByKey t-sql command requires a symmetric key, if you execute the EncryptByKey command without openning the symmetric key, the EncryptByKey function will return NULL values during the encrypting calls.
Once the symmetric key is opened in a session, EncryptByKey will function properly in that session. This means as a sql server developer after opening the symmetric key in a session, you can then call the encryption and also the decryption functions more than once successfully.
Also, if you plan to encrypt data and write it on a table or store encrypted data in a stored procedure or in a user defined function (udf), you can open the related symmetric key once at the beginning of the stored procedure or user-defined function t-sql codes, and then in the following lines of sql codes you can execute the EncryptByKey and the DecryptByKey Transact-SQL commands.

/*************** ENCRYPT SENSITIVE DATA *********************************/
OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;

UPDATE Person.Contact SET EncryptedEmailAddress = EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), EmailAddress);

SELECT EmailAddress, EncryptedEmailAddress FROM Person.Contact
GO


Decrypting Encrypted Data

Of course, if you are encrypting your data in Microsoft SQL Server 2005 or the new version SQL2008 aka Katmai, you may also want to decrypt the encrypted data.
As well as encrypting, decrypting can be handled using t-sql commands and functions in SQL Server. DecryptByKey t-sql function decrypts data using a symmetric key definen in the current sql database.
You can call the DecryptByKey function passing the encrypted data in varbinary data type. Just as similar to EncryptByKey requires an opened symmetric key in the current session, DecryptByKey function also requires a symmetric key that has been opened in the current session before it is called.

Therefore, the t-sql codes below are executed before making any call to EncryptByKey or DecryptByKey t-sql functions.

OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;
/*************** DECRYPT *********************************/
OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;

SELECT
EmailAddress,
EncryptedEmailAddress,
CONVERT(nvarchar, DecryptByKey(EncryptedEmailAddress)) AS 'Decrypted Email Address'
FROM Person.Contact;
GO


One last important point for decrypting encrypted data on SQL Server is that as a sql programmer or administrator, you should take care for the original data type that is encrypted and the target data type that the decrypted data is going to be converted.
Since DecryptByKey function returns data in varbinary data type up to 8000 bytes, if you convert this decrypted varbinary data to nvarchar sql data type you get different result when compared to decrypted varbinary data converted to varchar.
So, if you are encrypting nvarchar data, decrypt and convert it back to nvarchar. Same for varchar data type also. if you are encrypting varchar data, decrypt and convert it back to varchar. Otherwise, you will not get expected results from implemented decryption algorithm.

Let's look at the following select script where encryption and decryption takes place for both varchar and nvarchar data types.
You will see if nvarchar data is converted back to nvarchar then result is correct. Same is true for also varchar data.

SELECT

-- Encrypt 'Varchar' string and then decrypt encrypted data
CONVERT(varchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), 'Varchar'))),
CONVERT(nvarchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), 'Varchar'))),

-- Encrypt N'NVarchar' string and then decrypt encrypted data
CONVERT(varchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), N'NVarchar'))),
CONVERT(nvarchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), N'NVarchar')))


Using Parameters in EncryptByKey and DecryptByKey T-SQL Functions

I have used string values or database table column names in the previous examples. But sql variables can also be used for encryption and decryption functions.

DECLARE @sample_data nvarchar(MAX)
SET @sample_data = N'This is a step-by-step guide summarizing SQL Server Encryption'

SELECT
CONVERT(nvarchar(MAX), DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), @sample_data)))


In this step-by-step guide or SQL Server encryption and decryption tutorial, we have implemented SQL Server data encryption and encryption metodologies with sample t-sql codes in a simple application in Microsoft SQL Server 2005 and MS SQL Server 2008. Encryption and Decryption is an important aspect for database security in sql database development.