Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


SQL Server 2005 Encryption Certificates Overview

By:   |   Last Updated: 2007-08-29   |   Comments (3)   |   Related Tips: More > Encryption

I have a Database Master Key created in the database. Can I start encrypting data in my database now? The answer is "not yet". A Database Master Key per se' cannot encrypt data. You either need another key or a certificate. In this second tip of a four-part series we'll cover all things Certificates.

NOTE - For the first tip in these series, please reference - Managing SQL Server 2005 Master Keys for Encryption.


What Can Certificates Be Used For?

Certificates, which are second in line in the encryption hierarchy, can be used to create symmetric keys for data encryption or to encrypt the data directly. Information on certificates currently in the database can be retrieved through the sys.certificates DMV:

How Can I Create a Certificate?

There are a number of ways to create certificates. SQL Server 2005 shipped with the ability to create self- signed certificates, so if you're in a hurry to get things up and running then create one using the following syntax:

EXPIRY_DATE = '08/12/2012'

If an expiration date is not specified in the creation statement then a default expiration date of one year from the creation date will be applied. Also, the certificate created by the statement above will be encrypted using the Database Master Key. If you would prefer that a password be used instead of the Database Master Key then the statement ENCRYPTION BY PASSWORD='<Password>' can be used. You can see that the certificate is encrypted using the password in the sys.certificates view:

Another method of creating a certificate is by using .cer and .pvk files issued from a Certificate Authority. Since most, if not all, Active Directory domains have a Certificate Authority in the infrastructure, you can request a certificate by using an Advanced Request. Click here for additional information on requesting certificates from a Certificate Authority. Certificates can also be created by using signed executable files and dll's.

Can I Backup The Certificates?

Certificates can be backed up the same way as keys can be backed up. Remember that certificates and keys should always be backed up to secure media that can be stored offsite in case of disaster. The syntax for backing up certificates is:


How Do I Restore a Certificate From a Backup?

A certificate can be restored from a previous backup, but there is no "restore certificate" command. You must use the CREATE CERTIFICATE command and specify the file location and password used during the backup process.

What Permissions Does A User Need To Use The Certificate?

In order to use a certificate for decryption a user must have VIEW DEFINITION and CONTROL permissions on the certificate. These permissions can be assigned either individually or through Database Roles. To grant permissions to users and groups, use the following syntax:


This syntax differs slightly from traditional GRANT statements in that the scope qualifier (::) must be used.

Next Steps

Last Updated: 2007-08-29

get scripts

next tip button

About the author
MSSQLTips author Tim Cullen Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Thursday, October 18, 2012 - 1:42:12 PM - Jim Back To Top

Can you tell me the pros/cons between encrypting data with a certificate and encrypting data with a symmetric key?

Monday, September 10, 2012 - 12:17:44 PM - Rob Back To Top

For those of you using SQL Server 2012, the "Can I Backup The Certificates?" section is different.


See here: http://msdn.microsoft.com/en-us/library/ms178578.aspx


The code for 2012 should be:


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


Monday, August 20, 2012 - 3:15:28 AM - Ruchir Back To Top

Thanks - very helpful!

Learn more about SQL Server tools