In this two part series, we will talk about data encryption and its implementation in SQL Server 2005.

 

You may encrypt sensitive data like credit card numbers, client information etc. using SQL Server data encryption functionality.

 

Let’s take an example to demonstrate the different steps involved in encrypting and decrypting your data.

 

 

STEP I – Setting the stage for encryption. Steps below are needed to be done only once

 

--- Create test database

CREATE DATABASE encryption_test;

GO

USE encryption_test;

GO

 

--- Copy table from AdventureWorks DB for tests.

SELECT * INTO Employee FROM AdventureWorks.HumanResources.Employee

GO

 

--- Add a column of encrypted data.

ALTER TABLE Employee

    ADD EncryptedNationalIDNumber varbinary(128);

GO

 

/*Creating master key, certificate.*/

 

--- Create a master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';

GO

 

--- Open master key

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';

GO

 

--- Create certificate

CREATE CERTIFICATE HumanResources037

   WITH SUBJECT = 'Sammamish HR',

   EXPIRY_DATE = '10/31/2009';

 

--- Create symmetric key protected by certificate

CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = DES

    ENCRYPTION BY CERTIFICATE HumanResources037;

GO

 

 

STEP II – Encrypting your data

 

--- Open symmetric key

OPEN SYMMETRIC KEY SSN_Key_01

   DECRYPTION BY CERTIFICATE HumanResources037 ;

 

--- Encrypt data with symmetric key

UPDATE Employee

SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);

 

--- Close the key used to encrypt the data.

CLOSE SYMMETRIC KEY SSN_Key_01;

 

 

STEP III – Different ways to decrypt data

 

--- Option one. Require symmetric key to be opened explicitly

 

OPEN SYMMETRIC KEY SSN_Key_01

   DECRYPTION BY CERTIFICATE HumanResources037;

 

SELECT NationalIDNumber, EncryptedNationalIDNumber 

    AS 'Encrypted ID Number',

    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))

    AS 'Decrypted ID Number'

    FROM Employee;

 

CLOSE SYMMETRIC KEY SSN_Key_01;

 

--- Option two

 

USE encryption_test;

GO

SELECT NationalIDNumber, EncryptedNationalIDNumber

    AS 'Encrypted ID Number',

    CONVERT(nvarchar, DecryptByKeyAutoCert ( cert_ID('HumanResources037') , NULL ,EncryptedNationalIDNumber))

    AS 'Decrypted ID Number'

    FROM Employee

 

 

What’s next?

Next time, we will delve deep into key maintenance - How to backup and restore different types of keys.

 

 

Additional Resources

 

Improving Data Security by Using SQL Server 2005

 

Cryptographic functions

 

How to encrypt data

 

How to decrypt data

 

You may also check Laurentiu Cristofor's blog. Laurentiu works on security features of SQL Server. It is a great resource with technical details for data encryption.

 

POSTED BY : Sergey Pustovit