Sql Server Backup Certificate along with Private Key
- Posted by Sqltimes
- On May 14, 2014
- 0 Comments
Quick one today:
Certificates are used everywhere these days. In Sql Server, certificates are used in several features to make communication secure — with proper authentication. Certificates are asymmetric keys, so they have public and private keys.
Following are the steps to create and backup certificates and sometimes with its private key.
Create a Certificate
Following syntax allows to create a new certificate and specify an expiration date as well.
|
1
2
3
4
5
6
7
8
9
10
|
---- Create a new certificate--USE [master]GOCREATE CERTIFICATE Principal_Certificate_NewWITH SUBJECT = 'Principal Certificate New' , EXPIRY_DATE = '12/31/2040'GO |
Backup Certificate
This syntax below allows us to take certificate backup. This backups up the public key of the certificate (not private key).
|
1
2
3
4
5
6
|
---- Back up the certificate--BACKUP CERTIFICATE Mirror_Certificate_NewTO FILE = N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Mirror_Certificate_New.cer'GO |
Backup Certificate along with its private key
This syntax below allows us to take backup of the certificate with its private key. Private key is encrypted with a password.
|
1
2
3
4
5
6
7
8
9
10
11
|
---- Back up the certificate with private key--BACKUP CERTIFICATE Mirror_Certificate_NewTO FILE = N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Mirror_Certificate_New.cer'WITH PRIVATE KEY ( FILE = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Mirror_Certificate_New.Key' , ENCRYPTION BY PASSWORD = 'UseaRandomPasswordWithNumbers123' )GO |
Hope this helps,
_Sqltimes
0 Comments