Seeking help on storing keys in Azure Key vault for on premise database?

anil kumar 1,646 Reputation points
2021-11-30T11:05:03.267+00:00

Hello,

We want to implement Transparent Data Encryption (TDE) for some of our on premise databases and we want to store the keys/certificates in Azure Key Vault..

Given the below constraints, Is it really possible ?

  1. ON premise and Azure networks are not connected.
  2. Azure AD and on premise AD may not be connected in few cases, they might be federated in some cases.
  3. SQL Server service account credentials should not be stored in Azure key vault as it is a domain account with privileged access.
  4. Application connected to that DB should can not not be registered in Azure AD.
  5. Keys should be accessed using API

I understand TDE keys are needed during initial set up and db restoration and we want to store those keys in Azure Key Vault for security and backup reasons. Use case would be while implementing TDE, Keys are fetched from Azure Key Vault using APIs, same would be done for DB restore also.

Appreciate your insightful response, thank you !!

Azure Key Vault
Azure Key Vault
An Azure service that is used to manage and protect cryptographic keys and other secrets used by cloud apps and services.
1,451 questions
SQL Server | Other
{count} votes

Accepted answer
  1. Ronen Ariely 15,206 Reputation points
    2021-11-30T13:23:33.387+00:00

    hi,

    Your request to use Azure Key Vault as your on-premises certificate Does NOT make sense to me, as this mean that you will not be able to use your database if the connection is broken, but your requirement (needs) make totally sense - meaning to use the Azure Key Vault as your as your backup.

    I did not used this architecture as it not fit me but you can try it and use for yourself (assuming it is working as expetected)

    (1) Step one: exported the certificate directly from the SQL Server on premises instance or from the certificate store (create the files)

    (2) Step two: You can import the certificate file to the Azure SQL Managed Instance for backup or simply to Azure Key Vault

    For more info about import the certificate to the Azure Key Vault check this:
    https://learn.microsoft.com/en-us/azure/key-vault/certificates/tutorial-import-certificate

    Check this Doc. Most of the tasks you need are covered there.
    https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/tde-certificate-migrate?tabs=azure-powershell

    (3) Step 3: Once you will need to use the certificate locally, then you can Export the certificates from Azure Key Vault as explained here:
    https://learn.microsoft.com/en-us/azure/key-vault/certificates/how-to-export-certificate?tabs=azure-powershell

    Here is example using Get-AzureKeyVaultSecret command as well:

    # Replace these variables with your own values  
    $vaultName = "<KEY_VAULT>"  
    $certificateName = "<CERTIFICATE_NAME>"  
    $pfxPath = [Environment]::GetFolderPath("Desktop") + "\$certificateName.pfx"  
    $password = "<PASSWORD>"  
      
    $pfxSecret = Get-AzureKeyVaultSecret -VaultName $vaultName -Name $certificateName  
    $pfxUnprotectedBytes = [Convert]::FromBase64String($pfxSecret.SecretValueText)  
    $pfx = New-Object Security.Cryptography.X509Certificates.X509Certificate2  
    $pfx.Import($pfxUnprotectedBytes, $null, [Security.Cryptography.X509Certificates.X509KeyStorageFlags]::Exportable)  
    $pfxProtectedBytes = $pfx.Export([Security.Cryptography.X509Certificates.X509ContentType]::Pkcs12, $password)  
    [IO.File]::WriteAllBytes($pfxPath, $pfxProtectedBytes)  
    

1 additional answer

Sort by: Most helpful
  1. Seeya Xi-MSFT 16,586 Reputation points
    2021-12-01T06:57:49.927+00:00

    Hi @anil kumar ,

    Welcome to Microsoft Q&A!
    Please see these:
    https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/setup-steps-for-extensible-key-management-using-the-azure-key-vault?view=sql-server-ver15&tabs=portal
    http://www.bradleyschacht.com/transparent-data-encryption-with-azure-key-vault/
    Hope these links could help you.

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.