/*
This example script support the following Microsoft documentation PR:
https://github.com/MicrosoftDocs/sql-docs/pull/1976
That PR concerns the following documentation page:
https://github.com/MicrosoftDocs/sql-docs/edit/live/docs/t-sql/statements/backup-certificate-transact-sql.md
By: Solomon Rutzky
On: 2019-04-23
https://SqlQuantumLeap.com/
*/
USE [tempdb];
CREATE CERTIFICATE [_BackupRestoreCert]
ENCRYPTION BY PASSWORD = 'password for private key'
WITH SUBJECT = 'https://SqlQuantumLeap.com/';
-- Make sure private key is loaded:
SELECT [thumbprint], [cert_serial_number], [pvt_key_encryption_type_desc], [pvt_key_last_backup_date]
FROM sys.certificates
WHERE [name] = N'_BackupRestoreCert';
-- thumbprint: 0x5424DBDA16C2CB3F22630F2908DE57C0737B1886
-- cert_serial_number: 3f e6 9b 87 30 99 bd 8c 41 9a 36 a5 f3 ab 02 fb
-- pvt_key_encryption_type: ENCRYPTED_BY_PASSWORD
-- pvt_key_last_backup_date: NULL
-- 1) Backup the private key to a file:
BACKUP CERTIFICATE [_BackupRestoreCert]
TO FILE = '_BackupRestoreCert_Backup.cer' -- default folder = DefaultData directory
WITH PRIVATE KEY (
FILE = '_BackupRestoreCert_Backup.pvk', -- default folder = DefaultData directory
DECRYPTION BY PASSWORD = 'password for private key', -- current password
ENCRYPTION BY PASSWORD = 'backup pass' -- new password
);
-- 2) Remove private key:
ALTER CERTIFICATE [_BackupRestoreCert]
REMOVE PRIVATE KEY;
-- 3) Verify that private key has been removed:
SELECT [thumbprint], [cert_serial_number], [pvt_key_encryption_type_desc], [pvt_key_last_backup_date]
FROM sys.certificates
WHERE [name] = N'_BackupRestoreCert';
-- thumbprint: 0x5424DBDA16C2CB3F22630F2908DE57C0737B1886
-- cert_serial_number: 3f e6 9b 87 30 99 bd 8c 41 9a 36 a5 f3 ab 02 fb
-- pvt_key_encryption_type: NO_PRIVATE_KEY
-- pvt_key_last_backup_date: 2019-04-23 15:29:04.783 (in UTC)
-- 4) Restore private key:
ALTER CERTIFICATE [_BackupRestoreCert]
WITH PRIVATE KEY (
FILE = '_BackupRestoreCert_Backup.pvk', -- default folder = DefaultData directory
DECRYPTION BY PASSWORD = 'backup pass', -- current password
ENCRYPTION BY PASSWORD = 'password for private key' -- new password
);
-- 5) Verify that private key has been restored:
SELECT [thumbprint], [cert_serial_number], [pvt_key_encryption_type_desc], [pvt_key_last_backup_date]
FROM sys.certificates
WHERE [name] = N'_BackupRestoreCert';
-- thumbprint: 0x5424DBDA16C2CB3F22630F2908DE57C0737B1886
-- cert_serial_number: 3f e6 9b 87 30 99 bd 8c 41 9a 36 a5 f3 ab 02 fb
-- pvt_key_encryption_type: ENCRYPTED_BY_PASSWORD
-- pvt_key_last_backup_date: 2019-04-23 15:29:04.783 (in UTC)
Comments
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎