SQL Server 2005 introduced native database encryption capabilities. This, simply put, makes it easy for you to protect sensitive data from outside attacks. This article looks at how to set up and use encryption on a simple database table.

First, create a table for testing purposes in your existing database:

CREATE TABLE [dbo].[CreditCards] (CardId INT PRIMARY KEY , CardNumber varbinary(256) )
GO

This creates a simple table with two columns.

Now, you will create a Symmetric Key that uses a password for authentication. Another option is to use a certificate, but that won’t be covered here.

CREATE SYMMETRIC KEY [PasswordProtectedKey] 
WITH ALGORITHM = TRIPLE_DES 
ENCRYPTION BY PASSWORD = ‘12345′
GO

You can now enter some test data into the CreditCard table. Note that you must first open the key.

OPEN SYMMETRIC KEY [PasswordProtectedKey] DECRYPTION BY PASSWORD = ‘12345′

GO

– Insert some data into your test table
DECLARE @KeyGuid AS UNIQUEIDENTIFIER
SET @KeyGuid = key_guid(‘PasswordProtectedKey’)

INSERT INTO [CreditCards] VALUES ( 1, encryptbykey( @KeyGuid, N‘1111-0000-0000-0000′))
INSERT INTO [CreditCards] VALUES ( 1, encryptbykey( @KeyGuid, N’2222-0000-0000-0000′))
CLOSE SYMMETRIC KEY [PasswordProtectedKey]
GO

Now you can try retrieving data from your table. First, try running an ordinary SELECT statement and note that the returned data is encrypted and unuseable. In order to return unencrypted data, you must use the key:

OPEN SYMMETRIC KEY [PasswordProtectedKey] DECRYPTION BY PASSWORD = ‘12345′
GO

SELECT CardId, convert( NVARCHAR(100), decryptbykey( CardNumber )) AS ‘Card Number’
FROM [dbo].[CreditCards]
GO

And what if you want to find a specific credit card number?

OPEN SYMMETRIC KEY [PasswordProtectedKey] DECRYPTION BY PASSWORD = ‘12345′
GO

SELECT CardId, convert( NVARCHAR(100), decryptbykey( CardNumber )) AS ‘Card Number’
FROM [dbo].[CreditCards]
WHERE convert( NVARCHAR(100), decryptbykey( CardNumber )) = ‘2222-0000-0000-0000′

GO

If you need to change the password:

OPEN SYMMETRIC KEY [PasswordProtectedKey] DECRYPTION BY PASSWORD = ‘12345′

GO

ALTER SYMMETRIC KEY PasswordProtectedKey
ADD ENCRYPTION BY PASSWORD = ‘67890′
GO

ALTER SYMMETRIC KEY PasswordProtectedKey
DROP ENCRYPTION BY PASSWORD =‘12345′
GO

And to remove your key entirely:

CLOSE SYMMETRIC KEY PasswordProtectedKey
DROP SYMMETRIC KEY PasswordProtectedKey