Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Encrypting Column Values (SQL 2005+)

From Wiki

Jump to: navigation, search

Whenever sensitive data is stored in your system, it can be beneficial to encrypt it. Starting w/ SQL 2005, there was a native method to do it. This can be useful in addressing concerns about what happens when somehow a backup of your database finds its way into the wrong hands.

Creating Keys/Certificates

First keys and certificates must be set up. SQL Server is capable of using symmetric or asymmetric keys, for more discussion on which to use see here: [1]. In my case symmetric worked just fine.

  1. If Not Exists (Select * From sys.symmetric_keys Where symmetric_keys.symmetric_key_id = 101)
  2.  Create Master Key Encryption By Password = 'll5gty765asdflkajsdfqwoerwqer'
  3. Go
  5. If Not Exists (Select * from sys.certificates Where name = 'MyCertificate')
  6.  Create Certificate MyCertificate With Subject = 'Encryption Cert My Application'
  7.    , Expiry_Date '20100431'
  8. Go
  10. If Not Exists (Select * From sys.symmetric_keys Where name = 'MyKey')
  11.  Create Symmetric Key MyKey With Algorithm = AES_256 Encryption By Certificate MyCertificate
  12. Go
  14. --set up permissions for application account
  15. Grant References On Symmetric Key :: MyKey To MyApplication
  16. Grant Control On Certificate :: MyCertificate To MyApplication

This is all pretty straightforward, we've gotta create a Master Key, a certificate, and finally the key that we use to encrypt and decrypt. Then we need to grant the account that will be doing the encryption and decryption sufficient permissions to use these newly created objects.

Encrypting Data

Once we've got the keys setup, its' time to put them to use. Lets assume a table TEST with a varbinary column is what you're using to store social security numbers.

  1. Open Symmetric Key MyKey Decryption By Certificate MyCertificate
  3. Insert Into TEST (Name, SSN)
  4. Values ('SomeName', EncryptByKey(Key_Guid('MyKey'), '999999999')

It is kind of a nuisance to have to open the key each time you add a value, but not such a big deal if you're using stored procs for data access.

Decrypting Data

Decrypting data is a bit easier, because it can be done without opening the key.

  1. Select Name, Convert(VarChar(9), DecryptByKeyAutoCert(Cert_Id('CmsCertificate'), null, EncryptedSSN))

The null parameter is for 'password', which is not necessary if using the master key. If the user issuing the query has sufficient permissions, the decrypted SSN will be shown correctly, otherwise NULL will be returned.

This has some limitations, for example it does not seem to work when called from another database on the same server. It is also terrifically slow if you need a where clause on this column - if you needed to do SSN lookups it would probably be best to store a hash to use in the lookup.

726 Rating: 2.0/5 (39 votes cast)