THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Ben Miller

SQL 2005 Encryption with Certificates

OK, I found out that my age is creeping up on me.  I have implemented some encryption using Certificates in SQL Server and had some interesting experiences I thought I would share.

I have a Database Master key and planned out my encryption strategy at least for the near future as follows:

         |________SYMMETRIC KEY encrypted by Certificate above.

So I created my DB Master Key with a Password, then I created my certificate and then created my Symmetric Key with decryption by certificate.  What I found out was that currently we had implemented encryption and decryption by using an external encrypter/decrypter which was function based.  So you would use something like:

SELECT dbo.fn_EncryptMyString('41111111111111111')

I would get the character value back with it encrypted.  Then I would be able to decrypt with

SELECT dbo.fn_DecryptMyString(fieldInDatabase)

When moving to SQL Server encryption using SYMMETRIC KEY en/decryption, doing it inside a Function is not supported for using a DATABASE MASTER KEY because of the nature of a function in a query.  So you cannot open a Symmetric Key inside a function, you must use Certificates.

This is what led me to this experience.  First you Create or Open your DATABASE MASTER KEY and then you would create the Certificate with all the information that is necessary to create it:

ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'This is My Subject',
EXPIRY_DATE = '10/31/2009';

When you get done with that, you will want to backup the key (the Private key will be encrypted with the database master key) in order to recover if you lose the DB Master Key. Having the Private Key of the Certificate will allow you to still decrypt the data after you reestablish the DB Master Key in the database. 

BACKUP CERTIFICATE NameOfCertificate TO FILE = 'c:\storedcerts\NameOfCertificatecert'
WITH PRIVATE KEY ( FILE = 'c:\storedkeys\NameOfCertificatekey' ,
ENCRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh' );

This will basically backup the Certificate Private Key to the file named above and you can then restore it to the database after moving the database to another server or whatever.

This is all well and good, but when testing it as SA or Sysadmin you will always be able to use the Certificate, but as a normal user that does not have DBO privileges or other privileges that give you rights to the certificate, there are 2 permissions that you must set in order to use the Certificate as a non-DBO or higher privilege user.

I found this nice explanation on MSSQL Tips that is very easy to understand and is the same information as in BOL.
It is found here.

The permissions are:

If you do not grant these permissions, you will just get a NULL value back from either DecryptByCert or EncryptByCert, no error messages or anything, just a NULL.

So have fun, I certainly will remember again next time I need to create this stuff again, as once you have some pain in forgetting the last step, you tend to stay a little more aware of it in the future occurences.

Published Monday, February 02, 2009 3:41 PM by dbaduck


No Comments
New Comments to this post are disabled

About dbaduck

Ben Miller is a Senior Database Administrator for HealthEquity in Draper, UT. He has been working with SQL since SQL Server 6.0 (1998) and has had a variety of roles in his career, including SQL Support and MVP Lead at Microsoft.
Privacy Statement