Usefulness of SQL column encryption

Question

I have followed http://msdn.microsoft.com/en-us/library/ms179331.aspx for encrypting a column in SQL Server.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'T3stP@ssword'

CREATE CERTIFICATE TestCert WITH SUBJECT = 'SSN Encryption'

CREATE SYMMETRIC KEY HRKey
    WITH ALGORITHM = DES
    ENCRYPTION BY CERTIFICATE TestCert;

OPEN SYMMETRIC KEY HRKey
   DECRYPTION BY CERTIFICATE TestCert;

UPDATE [dbo].[Person]
   SET [EncryptedSSN] = EncryptByKey(Key_GUID('HRKey'), CAST(SSN AS varchar(10)));


SELECT TOP 10 * FROM Person

1   Tom Thomson 111111111 0x0026DA624DBCA04CBCBF621FD5F......
2   Steve   Stephenson  222222222 0x0026DA624DBCA04CBCBF6.....

Shows I have encrypted data, which is what I wanted.

OPEN SYMMETRIC KEY HRKey
   DECRYPTION BY CERTIFICATE TestCert;

SELECT PersonID, Firstname, Lastname [SSN], 
    CONVERT(VARCHAR, DecryptByKey([EncryptedSSN])) 
    AS 'Decrypted SSN'
    FROM [dbo].[Person]


1   Tom Thomson     111111111   111111111
2   Steve   Stephenson  222222222   222222222

   Close SYMMETRIC KEY HRKey

Ignoring that I still have an unencrypted column, the question is what has this really improved, it is simple for me to open the key and use it to decrypt the data. I presume I'm missing some piece of understanding.

The idea here is that the key is tied to a user account, so you can only decrypt the data if you have the credentials for that user account. Someone who had the database, but not the login credentials, would be unable to decrypt the data. — Eric Petroelje, Dec 13 '12 at 18:49

Eric J. · Accepted Answer · 2012-12-13T20:20:42.490

The key is associated with a user account, meaning one needs credentials to access the key. If a hacker can hijack that identity, your concerns apply. However, simply copying or stealing the IO subsystem is insufficient to break the encryption, nor is accessing the IO subsystem with a different account.

Additionally, for complete security, backup media have to be protected as well. Backing up unencrypted data creates another attack vector. If you run a SaaS for larger clients, or if you must comply with standards such as HIPAA or PCI, you may be required to ensure backups area encrypted.

Usefulness of SQL column encryption

1 Answers1