Home
%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%20noopener%20noreferrer%20noopener%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
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
Occasional Visitor

I'm wondering if the line

    print 'create certificate MY_Certificate from binary = '

should have been

    print 'create certificate ''' + @certName + ''' from binary = '

 

Also the line with CERTPRIVATEKEY, should have printed something, I think.

In my case, it does not. The docs states that 3 parameters must be used. 

Have I found a problem?