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" 
    -pe 
    -a sha1 
    -b "01/01/2013" 
    -e "12/31/2013" 
    -len 2048 
    -r 
    -n "CN=SQL Server 2012 - Encryption Demo Certificate" 
    C:\Workspace\Cert1_AdventureWorks2012.cer

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'
WITH PRIVATE KEY
(
    FILE = N'C:\Workspace\Cert1_AdventureWorks2012.pvk',
    DECRYPTION BY PASSWORD = N'p@ssw0rd'
);

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'
WITH PRIVATE KEY
(
    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

SELECT 
    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 !!!