In the previous article we manage to import into Azure SQL Database a certificate so we could improve the security of our system.
But there can be situations where we need to backup the certificate to be used in another Azure SQL Database or for any other reason.
So how can we backup an already existing certificate in our Azure SQL Database?
We can execute this script and we will get the SQL query that we can store and execute to recreate the certificate.
declare @pwd VARCHAR(Max) declare @cer VARBINARY(MAX) declare @pvk VARBINARY(MAX) declare @certName sysname select @certName = 'Example_Certificate' select @pwd = '<Certificate Password>' print 'create certificate MY_Certificate from binary = ' print CERTENCODED(cert_id(@certName)) print ' with private key ( binary = ' print CERTPRIVATEKEY(cert_id(@certName),@pwd) print ', decryption by password = ''' + @pwd + ''')' print 'go'
The output of this query will be something like this.
create certificate MY_Certificate from binary = 0x308201D53082013EA00302010202103E4700E2E02E819A4419B7B55D... with private key ( binary = 0x1EF1B5B000000000010000000100000010000000540200002966AC361... , decryption by password = '<Certificate Password>') Go
And as you can see this is the SQL Query to create a certificate like we see in the previous article.
Now we can store this command and recreate the certificate if we need to.