MSSQL TDE Certificates in Key Vault

Category: azure sqlazure

Question

Sidney L on Wed, 12 Dec 2018 16:58:03


Hello,

I'm pretty new to the Azure space and have some questions regarding Key Vault.

I recently setup SQL Server 2017 with TDE (Transparent Data Encryption) enabled for all databases in an on-prem sql server.  I have been playing with Azure.  I'm able to perform BACKUP/RESTORE between on-prem and Azure.  Now, I'm poking around with Azure Key Vault.  Because my databases are TDE enabled, I have previously backed the TDE certificate from the source server.  I have now converted my .cert and .pvk files to .pfx.   Uploaded to Azure -> Key Vault -> Certificate and downloaded it as a backup to my on-prem machine.   My question is this: If I had a disaster on my on-prem sql server, how would restore this downloaded certificate (in pfx format) onto say, a stand-by sql server or if I had to rebuild my sql server 2017 from scratch?

Thanks!

Replies

angoyal-msft on Thu, 13 Dec 2018 15:07:45


Hi Sidney,

Thanks for your feedback. Could you please refer below documents if that helps:

https://docs.microsoft.com/en-us/azure/sql-database/transparent-data-encryption-byok-azure-sql

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault

Please let us know if you still have some concerns.

Thanks

Sidney L on Thu, 13 Dec 2018 20:55:53


Hi Angoyal-msft,

The two links you provided do not address what I was looking for.  Good information to know though.

My scenario is this:

BACKUP CERTIFICATE -> PVK2PFX -> Azure Key Vault

Let me explain briefly what the above means.  I ran the BACKUP CERTIFICATE on SQL Server machine.  This created the .CER and .PVK files.  Then I ran the "pvk2pfx" tool (part of Windows SDK) to convert the .cer and .pvk files to PFX format.  Finally I uploaded the PFX file to Azure Key Vault because KV requires PFX format.  It does not take .CER or .PVK files.  So all I have now is what is in Key Vault.

How do I use what's in Key Vault to restore my "original" TDE certificate if I'm building a new (or standby) SQL Server?  That's the question.   Or am I doing it entirely wrong.  I shouldn't upload my TDE certificate to Key Vault to begin with?  Maybe I should just burn these files on CD and keep them in a safe place??

pituach on Fri, 14 Dec 2018 22:08:25


Hi Angoyal-msft,

The two links you provided do not address what I was looking for.  Good information to know though.

My scenario is this:

BACKUP CERTIFICATE -> PVK2PFX -> Azure Key Vault

Let me explain briefly what the above means.  I ran the BACKUP CERTIFICATE on SQL Server machine.  This created the .CER and .PVK files.  Then I ran the "pvk2pfx" tool (part of Windows SDK) to convert the .cer and .pvk files to PFX format.  Finally I uploaded the PFX file to Azure Key Vault because KV requires PFX format.  It does not take .CER or .PVK files.  So all I have now is what is in Key Vault.

How do I use what's in Key Vault to restore my "original" TDE certificate if I'm building a new (or standby) SQL Server?  That's the question.   Or am I doing it entirely wrong.  I shouldn't upload my TDE certificate to Key Vault to begin with?  Maybe I should just burn these files on CD and keep them in a safe place??

Good day ,

>> How do I use what's in Key Vault to restore my "original" TDE certificate if I'm building a new (or standby) SQL Server?

You do not.

You should simply store the certificate which you create in the local machine for future DR cases :-)

Sidney L on Sat, 15 Dec 2018 00:55:07


Ronen Ariely,

To me, it's much easier to manually manage my TDE certificates after I run BACKUP CERTIFICATE from the sql server source machine.  I can burn them onto CD's or upload to BOX or something similar.  When you said "You do not."   Does it mean you can't do what I did in Key Vault or it is not recommended but you could do it?


pituach on Sat, 15 Dec 2018 08:57:32


Ronen Ariely,

To me, it's much easier to manually manage my TDE certificates after I run BACKUP CERTIFICATE from the sql server source machine.  I can burn them onto CD's or upload to BOX or something similar.  When you said "You do not."   Does it mean you can't do what I did in Key Vault or it is not recommended but you could do it?

Hi,

First of all let's emphasize that we only spoke about the certificate and not the DR of he database. If you want to re-use the same Certificate on-prem then you should backup the original one from the On-Prem. This is the simplest open. You do not need to store it in CD but in the Azure. You can use your storage place in the Azure to store files for backup. 

* The links that angoyal-msft exactly point this topic and focus only on the certificate (I intended to propose his response as answer, before you respond that this is not what you are looking for)

Now... regarding your real full needs, which is actually not the certificate, but a solution for DR:, Microsopft do not support officially a solution for DR from Azure to On-Prem (it make no sense to them) but only from On-Prem to the Azure. Assuming that this discussion is about Azure SQL Database then there is not built-in option for DR to On-Prem but there are built-in solutions for automatic DR to another Azure SQL Database. 

To save my self time, Please read my answer in this question where I gave some solutions which might fit your needs and explanation: https://social.msdn.microsoft.com/Forums/en-US/8adbef83-673c-4074-8f19-d94dcb3bc326/is-there-any-way-to-migrate-an-azure-sql-database-to-an-sql-managed-instance?forum=ssdsgetstarted

Sidney L on Mon, 17 Dec 2018 19:05:50


Hi Ronen,

Thanks so much for responding.

For now, I've saved the original tde cert's on a secure network share.  This is probably the simplest for now.