How can I tell where my Database encryption keys are kept

Question

Post reply

How can I tell where my Database encryption keys are kept

cxmelga

SSC Enthusiast

Points: 177
More actions
May 16, 2021 at 7:02 am
Go to Answer
#3883976

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
May 17, 2021 at 7:23 am

This was removed by the editor as SPAM
cxmelga

SSC Enthusiast

Points: 177
More actions
May 27, 2021 at 9:32 am
Answer
#3888640

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

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

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

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

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

cxmelga SSC Enthusiast Points: 177 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 3

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.

Mr. Brian Gale SSC-Insane Points: 24690 More actions · Answer 4

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.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

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.

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

cxmelga SSC Enthusiast Points: 177 More actions · Answer 6

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

cxmelga SSC Enthusiast Points: 177 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

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.

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

cxmelga SSC Enthusiast Points: 177 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

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.

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

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 11

Always Encrypted is different than TDE. If you are using ALways Encrypted, the keys are in sys.column_encryption_keys and sys.column_master_keys.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

Steve Jones - SSC Editor wrote:

Always Encrypted is different than TDE. If you are using ALways Encrypted, the keys are in sys.column_encryption_keys and sys.column_master_keys.
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-column-encryption-keys-transact-sql?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-column-master-keys-transact-sql?view=sql-server-ver15

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.

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