Database Table Encryption Using Symmetric Key in SQL Server 2008 R2

The purpose of this article is to provide security to a database column's value so that no one can understand what the actual value is.



Scenario: Actually a few days ago I had the situation to add one more layer of security, like encryption and decryption of some keys (for example SaltKey or RSAKey), that were being used in my C# code base file. These keys were being used to encrypt and decrypt the username and password, however I don't want to make it public these keys (for example SaltKey or RSAKey) because anyone can read this easily using reflector if obfuscation is not applied to the C# code base file (.cs file). So I thought to put these keys into the database and put up all the columns values in encrypted mode and decrypt it since I needed the actual values of these keys.

I studied about this from various portals before implementing. In this article I'll also share some findings that may be helpful to you to create rudimentary queries.

There are many approaches to implement encryption but I've chosen column encryption of a database table. I just encrypted and decrypted the data placed in the table. This approach takes little time to do the required procedure than the other approach.

So let's start with encryption and decryption process executed with a Symmetric Key. I've created a database with the name DBEncrypt with a table “TestTable” that has three columns in it. Kindly look at the screenshot given below:





If you notice in the image shown above there are three columns with some text value. My main objective is to encrypt those values and decrypt accordingly.

Create a master key, each database can have one master key and this master key used to protect the private keys of the certificates. After this we'll create a certificate to encrypt the data in the database and symmetric key (the symmetric key used by the sender and the receiver of a message, it's a common key used to encrypt and decrypt the message). We've created a symmetric key using the certificate (EncryptTestCert).

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'Support@123'

CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'SupportCert'

CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert.

Now it's time to create another three columns that will keep encrypted data of these three columns (SaltKey, RSAKey and PrivateKey) of table respectively. I've also added one more Column SaltKeyEnCol2 as nvarchar(max). I will share the reason to create an extra column later in this document.

ALTER TABLE TestTable
ADD SaltKeyEnCol VARBINARY(256),RSAKeyEnCol VARBINARY(256),PrivateKeyEnCol VARBINARY(256)

ALTER TABLE TestTable
ADD SaltKeyEnCol2 nvarchar(max)

The code given below encrypts three columns (SaltKey, RSAKey and PrivateKey) values to the newly created columns above with an alter commnad. Also note that we are using the same certificate(EncryptTestCert) to open the symmetric key and make it available for use. Kindly have a look at the syntax given below.

OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert,
UPDATE TestTable
SET SaltKeyEnCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SaltKey),RSAKeyEnCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),RSAKey),PrivateKeyEnCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),PrivateKey)

Kindly manually close the symmetric key otherwise it will remain open for the current session.

After the execution of the preceding query you can have a look and see the affects made into the database. Kindly have a look at the image given below:



If you notice in the image above, it only shows you <Binary data> in encrypted Columns. Which was a little tricky for me initially, after looking at these values I thought I made some mistake and the data doesn't look OK.

To cater this issue, I again created another column with the name “SaltKeyEnCol2” as I have shared this in the preceding in the document and is also shown below. It helped me to see the actual value.

ALTER TABLE TestTable
ADD SaltKeyEnCol2 nvarchar(max)

Please execute the SQL query given below and see the effect.

OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
UPDATE TestTable SET SaltKeyEnCol2 = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SaltKey)

The column SaltKeyEnCol2 has some encrypted value in SaltKeyEnCol2 as depicted below in the screen shot rather than the <Binary Data> value.



As we can see above, we've achived the encryption of the required columns. Now the turn is to decrypt the data placed in the encrypted columns. Kindly refer to the image given below with the outcome.



Kindly find in the following the complete database query script.

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'Support@123'

CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'SupportCert'

CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert

ALTER TABLE TestTable
ADD SaltKeyEnCol VARBINARY(256),RSAKeyEnCol VARBINARY(256),PrivateKeyEnCol VARBINARY(256)

ALTER TABLE TestTable
ADD SaltKeyEnCol3 nvarchar(max)
--drop COLUMN SaltKeyEnCol3


OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
UPDATE TestTable SET SaltKeyEnCol3 = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SaltKey)


OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert,
UPDATE TestTable
SET SaltKeyEnCol2 = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SaltKey),RSAKeyEnCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),RSAKey),PrivateKeyEnCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),PrivateKey)

CLOSE SYMMETRIC KEY TestTableKey;

OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
SELECT CONVERT(nvarchar(max),DECRYPTBYKEY(SaltKeyEnCol2)) AS DecryptSaltKeyEnCol FROM TestTable

OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
SELECT CONVERT(nvarchar(max),DECRYPTBYKEY(SaltKeyEnCol2)) AS DecryptSaltKeyEnCol,CONVERT(nvarchar(max),DECRYPTBYKEY(RSAKeyEnCol)) AS DecryptRSAKeyEnCol,
CONVERT(nvarchar(max),DECRYPTBYKEY(PrivateKeyEnCol)) AS DecryptPrivateKeyEnCol FROM TestTable

CLOSE SYMMETRIC KEY TestTableKey

I hope it will help you somewhere. I took the idea from: Introduction to SQL Server Encryption and Symmetric Key Encryption.

I hope you understand the practical scenario.