Keeping track of certificates and private keys in SQL Server

Question

Keeping track of certificates and private keys in SQL Server

David.Deegan

Grasshopper

Points: 20
More actions
April 10, 2018 at 1:00 pm

#334206

Hello
I am looking to implement a recovery routine for encrypted databases and wanted to know if the location of the certificate and private keys are stored in any SQL Server table.
The *.cer & *.pvk were copied to the file system years ago but if a new DBA did not know which certificate and private key were used, recovery wouldn't be possible on another server (without knowing which files to copy to the new server)
If this was the command originally used...
backup certificate [SAMPLE_CERTIFICATE]
to file = 'E:\SQLDBA\Certificates\SAMPLE_CERTIFICATE.cer'
with private key (file = 'E:\SQLDBA\Certificates\SAMPLE_CERTIFICATE.pvk' , encryption by password = 'S3cr3t!' )
Is there a command like "select * from Unknown_table " that would show...
Certificate directory = 'E:\SQLDBA\Certificates\SAMPLE_CERTIFICATE.cer'
Private key directory = E:\SQLDBA\Certificates\SAMPLE_CERTIFICATE.pvk
Password = S3cr3t
Thanks
Dave

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

David.Deegan - Tuesday, April 10, 2018 1:00 PM
Hello
I am looking to implement a recovery routine for encrypted databases and wanted to know if the location of the certificate and private keys are stored in any SQL Server table.
The *.cer & *.pvk were copied to the file system years ago but if a new DBA did not know which certificate and private key were used, recovery wouldn't be possible on another server (without knowing which files to copy to the new server)
If this was the command originally used...
backup certificate [SAMPLE_CERTIFICATE]
to file = 'E:\SQLDBA\Certificates\SAMPLE_CERTIFICATE.cer'
with private key (file = 'E:\SQLDBA\Certificates\SAMPLE_CERTIFICATE.pvk' , encryption by password = 'S3cr3t!' )
Is there a command like "select * from Unknown_table " that would show...
Certificate directory = 'E:\SQLDBA\Certificates\SAMPLE_CERTIFICATE.cer'
Private key directory = E:\SQLDBA\Certificates\SAMPLE_CERTIFICATE.pvk
Password = S3cr3t
Thanks
Dave

In your example, you can query sys.certificates to see the information available. It will tell you the last time it was backed up but not the location. It wouldn't be able to keep track of that - you can move it anywhere and SQL Server won't know. The password isn't stored - it would be a bit of a security hole.
Setting some type of standards around how this is managed and then documenting it should really cover things needed. A lot of the enterprise password management software has ways to document this per server/instance if you are using one. The bigger problem I've run into is when it's handled in different ways for different servers/instances.

Sue