THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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:

DATABASE MASTER KEY
|_____Certificate
         |________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:

CREATE CERTIFICATE NameOfCertificate
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:
GRANT VIEW DEFINITION ON CERTIFICATE :: certMSSQLTIPS TO <Database user/role>
GRANT CONTROL ON CERTIFICATE :: certMSSQLTIPS TO <Database user/role>

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

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.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement