How can I tell where my Database encryption keys are kept

  • Hello, can someone please help me with the following question, thanks in advance

    I am not a DBA, but am learning Azure

    One of the exercises was the use Azure Key Vault to store the encryption key (key encryption key) to encrypt the column encryption key, and store this KEK in Azure Key Vault (rather than on the local SQL Server).

    I performed this action OK (using SQL 2019 free trial running  on an Azure VM) following this blog article

    https://docs.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

    What I would like to know is although I can see basic details on the encryption keys is SQL Management Studio under Database > Security > Always Encrypted Keys   subnode, it does not state where these keys are stored (e.g. master database or Azure Key Vault) unless I am missing something?

    Is there a command I can run on the SQL server (PowerShell for example) of sqlcmd.exe to see where these encryption keys are stored and therefore see if the SQL server is making using of Azure Key Vault and if so the name of the Key Vault?

    Thanks very much

    Charlie

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • This was removed by the editor as SPAM

  • Well, I received a reply to my initial question about SQL encryption and key location, but it was not quite what I was expecting. Hopefully, someone else can help me with my specific technical query

    Thanks in advance

     

     

  • For TDE, the symmetric encryption key, the DEK, is in the database. This is always the case. The certificate that is used to encrypt/decrypt this is stored in Azure Key Vault.

    I'm guessing something in sys.dm_database_encryption_keys has a link with the thumbprint.

    Are you looking to find which dbs are encrypted with key vault and which aren't? I think for local TDE, there is a link to a cert in master.

  • I am not sure why you marked the previous answer as spam when it (as you said) answered your "initial question" but was not what you were expecting.

    If you followed the tutorial you stated, then you are using the Azure key vault as you specified a provider (FROM PROVIDER [AzureKeyVault_EKM]).

    I don't have this set up so I am not certain how to find out if SQL is using it or not, but I imagine that if you do a "select * from sys.asymetric_keys", you should get some information about the key which would PROBABLY contain something related to the key.

    If you wanted to test that it was using the AKV, you could try shutting off that VM or deleting the key you used or change the secret.  Basically, break the SQL Server<->Azure Key Vault linking to that key and see if the encrypted content is still accessible.

  • Mr. Brian Gale wrote:

    I am not sure why you marked the previous answer as spam ...

    It may be that no one marked the post as SPAM.  It may have been the automatic system that marked it because of the "pattern" and the fact that the person who created the post currently has a low number of points.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks, everyone for taking the time to reply,

    I will try this one,

    select * from sys.asymetric_keys

    and get back to you, I am thinking of this from the perspective of an admin that did not set up the SQL server initially, and just wanted a quick way to check if Azure Key Vault is being used or not.

    Thanks again

    Charlie

     

  • Hello All

    Thanks again for taking the time to reply, I selected the database that is using Always Encrypted and ran the following command

    select * from sys.asymetric_keys

    However, I received the following error message

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'sys.tde_keys'.

    I can see under

    Database (e.g. MyDataseName) > Security > Always Encrypted Keys > Column Master Keys 

    a key called CMK_Auto1

    and under

    Database (e.g. MyDataseName) > Security > Always Encrypted Keys > Column Encryption Keys

    a key called CEK_Auto1

    However right-clicking and looking at the properties of these keys does not tell me where they are stored (locally or Azure Key Vault), unless I am missing something, hence reaching out to this forum

    Perhaps there is a different query that can be run?

    Thanks, everyone

    Chralie

     

     

     

     

     

     

  • I've not yet learned how to even spell "Azure" but a quick web search led me to the following article.  From your description, I'm thinking it contains the information you want to know.  Of course, my preamble here is to also explain that I could be wrong. 😀

    https://azure.microsoft.com/en-us/blog/announcing-transparent-data-encryption-tde-with-customer-managed-keys-for-managed-instance/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hello Jeff

    Thanks for taking the time to reply,

    I set up SQL Always Encrypted using  and Azure Key Vault to store the key, and all this works OK

    What I would like to know is assuming I am an SQL admin looking at an SQL server/database is there an easy way to determine if any of the keys are stored in Azure Key Vault. Assuming you have no previous knowledge of the installation.

    Thanks

    Charlie

     

  • Ah.  Got it.  It's an interesting question and, hopefully, someone will come back with the answer.  If nothing else, this post will act as a "bump" for it. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hello Steve

    Thanks very much for taking the time to reply, this has resolved my issue 🙂

    Running

    select * from sys.column_master_keys

    returns a number of fields one called "key_store_provider_name" which as the value "AZURE_KEY_VAULT"

    and another field called "key_path" which contains the full path (URL) to the key in the key vault

    Thanks again

    Charlie

     

  • Awesome... thanks Steve.  We don't use any of the SQL versions of encryption at work (the hardware guys have it all down pat including non-database data and all "at rest" data, as well) and I also have zero Azure experience (heh... hopefully, I'll be able to retire before then 😀 ).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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