Create SQL Server certificate on multiple servers

Question

I have created a certificate on SQL server for encrypting my database backups. I used the following article for instructions.

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption?view=sql-server-2016

I need to restore this database to multiple servers. I can use the following code to import/create the certificate on another server.

-- Copy Certificate to target then create
CREATE CERTIFICATE myCertificate   
    FROM FILE = 'C:\Temp\CertBackup.cer'   
    WITH PRIVATE KEY (FILE = 'C:\Temp\CertBackup.pvk',   
    DECRYPTION BY PASSWORD = 'myPassword'); 
GO

I would like to be able to import this certificate to many servers at once instead of running this command on each server. Should I script this with Powershell or is there a better way?

Ben Thul · Accepted Answer · 2018-07-11 20:38:37Z

DECLARE @password VARCHAR(40) = 'f00bar!23'
select name, 'create certificate ' + QUOTENAME(name) + ' from binary = ' 
    + CONVERT(VARCHAR(MAX), CERTENCODED(CERT_ID(name)), 1)
    + ' with private key ( binary = ' 
    + CONVERT(VARCHAR(MAX), CERTPRIVATEKEY(CERT_ID(name), @password), 1)
    + ', decryption by password = ''' + @password + ''')'
FROM sys.[certificates] AS [c]
WHERE name = '«cert name here»';

Run that and it will generate a T-SQL statement that will allow you to create the certificate without having to use a certificate backup. The above assumes that the private key for the certificate is protected by a database master key. If it's protected by a password, you'll have to modify the code accordingly (left as an exercise for the reader).

Note: @password is only used to protect the certificate in transit. That is, it is not the password for the certificate itself.

Collectives™ on Stack Overflow

Create SQL Server certificate on multiple servers

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related