Share via

PART I - Data security enhancements in SQL Server 2005

  • Article

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

Comments

  • Anonymous
    November 02, 2006
    We recently posted a blog about SQL 2005 Security enhancements, focussing on encryption and decryption.

  • Anonymous
    February 16, 2007
    In the previous edition ( Data security enhancements in SQL Server 2005) , we talked about data encryption/decryption

  • Anonymous
    April 26, 2007
    When the certificate expires in 2009 would we no longer have access to the data?

  • Anonymous
    May 09, 2015
    CREATE CERTIFICATE HumanResources037   WITH SUBJECT = 'Sammamish HR',   EXPIRY_DATE = '10/31/2060'; make update