SQL Server Encryption Certificates Overview

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

Secure, Simple and Affordable SQL Server Database Encryption

Free MSSQLTips Webinar: Secure, Simple and Affordable SQL Server Database Encryption

Ensuring the security of your SQL Server database is priority number one. It can be a costly and time consuming project with upgrades, code changes, additional licensing and more. Nevertheless, as a SQL Server Professional you are still responsible for the protection of client data as a best practice and to meet various security regulations.


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 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:

select from sys certifcates

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:

select from sys certifcates

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. 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

Comments For This Article

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

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 (19453)

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 (19118)

Thanks - very helpful!


Recommended Reading

Storing passwords in a secure way in a SQL Server database

SQL Server Column Level Encryption Example using Symmetric Keys

Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions

Where Does SQL Server Store Its Certificates

Updating an expired SQL Server TDE certificate

get free sql tips
agree to terms

Learn more about SQL Server tools