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

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

image

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

image

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

image

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

image

 

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

About these ads

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 111 other followers

%d bloggers like this: