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

Question

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

Answer 1

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.

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

3. 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.

Answer 2

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