PART I - Data security enhancements in SQL Server 2005
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
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