CODEWITHSUNDEEP
sql-serverazure-keyvaultalways-encrypted
David Christopher Reynolds
David Christopher Reynolds

Reputation: 1451

SQL Server Always Encrypted with Azure Key Vault and Client Secret - can I view the unencrypted data in SSMS

I am using SQL Server Always Encrypted to encrypt some columns of sensitive data. I am using an Azure Key Vault to store the keys. Access to the key vault is granted via client secrets, not certificates. I would like to be able to view the unencrypted data in SQL Server Management Studio on my machine. I know that if we were using a certificate I could import the certificate into my machine and all would be good. I can't find a way to do this if you are using client secrets. Is this possible, and if so how? Thanks

Upvotes: 0

Views: 1148

Answers (2)

Joseph Xu
Joseph Xu

Reputation: 6043

Add to @MichaelHoward-MSFT's answer, your account should have the permission to access the Azure Key Vault.

  1. In the SQL Server Management Studio, we should select the account which has the permission to access the Azure Key Vault. Then click the Options button.

enter image description here

  1. Add the string Column Encryption Setting=enabled to the tab as follows:

enter image description here

  1. When we connected to the server, then we can execute the sql query, it will pop up prompt box, then we click the Enable. Then we can see the Decrypted column.

enter image description here

Upvotes: 1

MichaelHoward-MSFT
MichaelHoward-MSFT

Reputation: 319

The only requirement is that your account has access to the KeyVault that has the cert/key. So make sure the account using SMSS has an account that has access...You actually do not need to use the same authn mechanism as SQL

Upvotes: 2

Related Questions