Encryption in SQL Server 2012–X 509 Certificates Generation, Registering, Backup and Removing of Certificates

Internal use X-509 Certificates can be generated using MakeCert.exe utility, Please find the below syntax


makecert.exe -sv "C:\Workspace\Cert1_AdventureWorks2012.pvk" 
    -a sha1 
    -b "01/01/2013" 
    -e "12/31/2013" 
    -len 2048 
    -n "CN=SQL Server 2012 - Encryption Demo Certificate" 

The details of the parameters are as follows

-sv        Private Key file
-pe        Private Key Exportable
-a         Signature Algorithm (md5 [Default] or sha1)
-b        Beginning Date 
-e        Expiring Date
-len      Length of Private Key
-r        Self-Signed Certificate
-n        Name of Certificate (Subject of Certificate)


You can run the above utility from Command Prompt, it will prompt you to create Password for accessing Private Key


Once you have created the Password, it prompt you again to confirm and create the certificate


Once the certificate is created, you will get the “Succeeded” message


Also you can open the created Certificate file to view the details



Register Certificates

Once we create the X-509 certificate, we can use this to Register Certificates in SQL Server using the below T-SQL command, Please make sure you have created the Service Master Key and Database Master Key before this step


USE AdventureWorks2012
-- Register Certificate in SQL Server

CREATE CERTIFICATE Cert1_AdventureWorks2012
FROM FILE = N'C:\Workspace\Cert1_AdventureWorks2012.cer'
    FILE = N'C:\Workspace\Cert1_AdventureWorks2012.pvk',

Backup Certificates

To Backup Certificate from SQL Server, Please use the below T-SQL Command

USE AdventureWorks2012

--Backup Certificates

BACKUP CERTIFICATE Cert1_AdventureWorks2012
TO FILE = N'C:\Workspace\Cert1_AdventureWorks2012_BACKUP.cer'
    FILE = N'C:\Workspace\Cert1_AdventureWorks2012_BACKUP.pvk',
    ENCRYPTION BY PASSWORD = N'p@ssw0rd~123'

View Certificates registered in Database

You  can view the certificates registered in a database using the below query

    name, certificate_id, principal_id, pvt_key_encryption_type
    pvt_key_encryption_type_desc, is_active_for_begin_dialog, issuer_name
    cert_serial_number, sid, string_sid, subject, expiry_date, 
    start_date, thumbprint, attested_by, pvt_key_last_backup_date
FROM sys.certificates


Deleting Certificates registered in Database

To delete a certificate registered in database, please use the below query

-- Deleting Certicates

DROP CERTIFICATE Cert1_AdventureWorks2012

In SQL Server, Certificates can be used to

1. Encrypt data using EncryptByCert and DecryptByCert functions

2. Encrypt Symmetric Keys

3. To Enable TDE based Encryption for Database

4. To digitally sign data using SignByCert() function

Hope this helps !!!


Author: Arunraj

I am a Microsoft Certified Technology Specialist (Database Developer). I work on SQL Server programming since SQL Server 7.0 specializes in SQL Server Programming and Performance Tuning and has 14 years of hands-on experience. I hold a Master Degree in Computer Applications. I am also part of NJSQL User Group and Northern New Jersey .Net User Group.

5 thoughts on “Encryption in SQL Server 2012–X 509 Certificates Generation, Registering, Backup and Removing of Certificates”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: