Backup Certificate SQL Server and return bytes with a Select Statement

Question

I have a certificate in a database in Microsoft SQL Server 2008 R2. I want to get a copy of the certificate so I can burn it onto a USB device and put it in a safe. I don't want to have to go onto the sql server machine itself, I want to do all of this from my client application which I have written.

I know how to backup a certificate using BACKUP CERTIFICATE (http://msdn.microsoft.com/en-us/library/ms178578.aspx) but this only has options to save to a FILE.

Is there another technique (perhaps using sys.certificates) which I can use?

We are planning an upgrade to 2014, so if there is functionality specific to that version I can use that, but ideally the solution should work with 2008R2.

• blobbles: Might be best to design a sql server job which backs up the certificate to a specific location using the T-SQL in your link. Deploy the job on the server then execute the job from your application and pick up the file from the location the SQL Server job saved it.

Answer 1

New in SQL Server 2012 and also in 2014 are two new encryption functions that will return a binary stream as you are describing. CREATE CERTIFICATE was also updated to take BINARY input for public and private keys.

CERTENCODED will return the public key portion of the certificate in BINARY form. CERTPRIVATEKEY will return the private key portion of the certificate in BINARY form. this is encrypted so the password to decrypt it would also be needed.

The above could be saved in a binary form and written to a disk, or stored in textual representation of the binary form and written to disk.

Should you need to create it again, use the BINARY options of the CREATE CERTIFICATE function.

Edit: There is nothing in 2008R2 that can do the same that I know of through system functions.