Failed to decrypt a column encryption key using key store provider: 'AZURE_KEY_VAULT'

Question

I've used Always encrypted for a database table column on a DB on Azure. I've gone through this article - https://learn.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault - followed it very carefully. I have encrypted the columns, and that was done successfully. I can see that the column is encrypted.

When I try to follow the same article to now try to view the data from an application, I get error stating:

Failed to decrypt a column encryption key using key store provider: 'AZURE_KEY_VAULT'. Verify the properties of the column encryption key and its column master key in your database. The last 10 bytes of the encrypted column encryption key are: '54-36-01-E0-5C-A1-82-80-B1-B4'.

This is the error in the code, but then after I let that error go, I get:

Access denied. Caller was not found on any access policy. Caller: appid=afd26169-bbac-4a45-ad3c-2b4492d19c6e;oid=dabbc750-5601-442b-9809-3a17f74d5aa2;numgroups=0;iss=https://sts.windows.net/bd8eb048-c497-4576-80eb-99e763b83ffd/ Vault: AWKeyVault2;location=eastus2

It's worth noting that when I use SSMS, and I set the connection string options to Column Encryption Setting=Enabled, I can see the decrypted data in SSMS.

For the most part, I think it's a permissions problem, but I'm at a loss. I've given the App Registration full access to the Azure Subscription. I've generated a 'Secret' for the App Registration also, and using that in the application. If I can see the data from SSMS, I think it means the encryption was successful, so it must be something to do with the fact that the application just cannot get to the key in the azure key vault. What am I missing?

Rosenbaum

Answer 1

OK, so figured this out. It definitely was a permissions thing. In order to completely verify that you have the correct permissions, go to Key Vault Blade:

• Select Access Policies from the Key Vault resource blade menu on theleft
• Click the "add new" link/button at the top
• Select Principal to select the application that you are using (i.e. the app registration from which you got the client ID from)
• From the Key permissions drop down, make sure you give it "Decrypt", "Sign", "Get", "UnwrapKey" permissions
• Make sure to save changes

This is one of the last things - you still have to make sure your app registration has the correct permissions for your subscription.

Rosenbaum

You can accept yourself as the answer. This can be beneficial to other community members. Thank you. – Retrorse

Thank you so much for this answer. – Operose

Answer 2

Maybe a little late to the party, but I recently publihed an article about the topic and it might help better: https://www.codeproject.com/Articles/5355073/Full-Tutorial-on-using-Always-Encrypted-with-Azure

Basically, there is a mix of settings and coding that will allow you to use Always Encrypted with Azure Key Vault and it is not only related Azure settings and permissions. You need to register your Web App in Azure Active Directory, take the according Application ID, then create a new Secret for it, take the Secret value - then write some code to authenticate to AKV using these two. But that is also with a bit of skepsis - please read the article.

Pani