Using Certificates in Azure SQL Database : Backup

Published Mar 13 2019 06:49 PM 1,392 Views
Not applicable
First published on MSDN on Oct 03, 2017
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.
1 Comment
%3CLINGO-SUB%20id%3D%22lingo-sub-368952%22%20slang%3D%22en-US%22%3EUsing%20Certificates%20in%20Azure%20SQL%20Database%20%3A%20Backup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-368952%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Oct%2003%2C%202017%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20In%20the%20previous%20%3CA%20href%3D%22https%3A%2F%2Fblogs.msdn.microsoft.com%2Fazuresqldbsupport%2F2017%2F09%2F21%2Fusing-certificates-in-azure-sql-database-import%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20article%20%3C%2FA%3E%20we%20manage%20to%20import%20into%20Azure%20SQL%20Database%20a%20certificate%20so%20we%20could%20improve%20the%20security%20of%20our%20system.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20But%20there%20can%20be%20situations%20where%20we%20need%20to%20backup%20the%20certificate%20to%20be%20used%20in%20another%20Azure%20SQL%20Database%20or%20for%20any%20other%20reason.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20So%20how%20can%20we%20backup%20an%20already%20existing%20certificate%20in%20our%20Azure%20SQL%20Database%3F%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20We%20can%20execute%20this%20script%20and%20we%20will%20get%20the%20SQL%20query%20that%20we%20can%20store%20and%20execute%20to%20recreate%20the%20certificate.%20%3CBR%20%2F%3E%20declare%20%40pwd%20VARCHAR(Max)%20%3CBR%20%2F%3E%20declare%20%40cer%20VARBINARY(MAX)%20%3CBR%20%2F%3E%20declare%20%40pvk%20VARBINARY(MAX)%20%3CBR%20%2F%3E%20declare%20%40certName%20sysname%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20select%20%40certName%20%3D%20'Example_Certificate'%20%3CBR%20%2F%3E%20select%20%40pwd%20%3D%20'%3CCERTIFICATE%20password%3D%22%22%3E'%20%3CBR%20%2F%3E%20print%20'create%20certificate%20MY_Certificate%20from%20binary%20%3D%20'%20%3CBR%20%2F%3E%20print%20CERTENCODED(cert_id(%40certName))%20%3CBR%20%2F%3E%20print%20'%20with%20private%20key%20(%20binary%20%3D%20'%20%3CBR%20%2F%3E%20print%20CERTPRIVATEKEY(cert_id(%40certName)%2C%40pwd)%20%3CBR%20%2F%3E%20print%20'%2C%20decryption%20by%20password%20%3D%20'''%20%2B%20%40pwd%20%2B%20''')'%20%3CBR%20%2F%3E%20print%20'go'%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20The%20output%20of%20this%20query%20will%20be%20something%20like%20this.%20%3CBR%20%2F%3E%3CBLOCKQUOTE%3E%3CBR%20%2F%3E%20create%20certificate%20MY_Certificate%20from%20binary%20%3D%20%3CBR%20%2F%3E%200x308201D53082013EA00302010202103E4700E2E02E819A4419B7B55D...%20%3CBR%20%2F%3E%20with%20private%20key%20(%20binary%20%3D%20%3CBR%20%2F%3E%200x1EF1B5B000000000010000000100000010000000540200002966AC361...%20%3CBR%20%2F%3E%20%2C%20decryption%20by%20password%20%3D%20'%3CCERTIFICATE%20password%3D%22%22%3E')%20%3CBR%20%2F%3E%20Go%20%3CBR%20%2F%3E%3C%2FCERTIFICATE%3E%3C%2FBLOCKQUOTE%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20And%20as%20you%20can%20see%20this%20is%20the%20SQL%20Query%20to%20create%20a%20certificate%20like%20we%20see%20in%20the%20previous%20article.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Now%20we%20can%20store%20this%20command%20and%20recreate%20the%20certificate%20if%20we%20need%20to.%3C%2FCERTIFICATE%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-368952%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Oct%2003%2C%202017%20In%20the%20previous%20article%20we%20manage%20to%20import%20into%20Azure%20SQL%20Database%20a%20certificate%20so%20we%20could%20improve%20the%20security%20of%20our%20system.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-368952%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eazure%20sql%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20SQL%20Database%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Ecertificates%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESecurity%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1399343%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Certificates%20in%20Azure%20SQL%20Database%20%3A%20Backup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1399343%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20wondering%20if%20the%20line%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20print%20'create%20certificate%20MY_Certificate%20from%20binary%20%3D%20'%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eshould%20have%20been%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20print%20'create%20certificate%20'''%20%2B%20%40certName%20%2B%20'''%20from%20binary%20%3D%20'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20the%20line%20with%26nbsp%3BCERTPRIVATEKEY%2C%20should%20have%20printed%20something%2C%20I%20think.%3C%2FP%3E%3CP%3EIn%20my%20case%2C%20it%20does%20not.%20The%20docs%20states%20that%203%20parameters%20must%20be%20used.%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20I%20found%20a%20problem%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Mar 13 2019 06:49 PM
Updated by: