Error while querying 'Always encrypted' column

All we need is an easy explanation of the problem, so here it is.

I have set up ‘Always encrypted’ on one of the column in Azure SQL DB. Everything worked well except when a user is trying to access he is getting below error. ‘
I have tried some of the rememdies as below.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’

  1. User is mapped in Azure AD group which is also mapped in Azure SQL server. Keys are stored in Azure key vault and the AD group is given ‘Contributor’ access and ‘decrypt’, ‘encrypt’, ‘get’ , unwrap’ access is given through access policy.
    ‘GRANT VIEW ANY COLUMN MASTER KEY DEFINITION’ granted to AD group in SQL DB.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I have managed to fix the issue. It was related with the Azure key vault firewall. The member who wants to ‘decrypt’ the data should have white listed their IP address in Azure key vault as well along with other accesses in access policy mentioned here
‘https://stackoverflow.com/questions/57735293/failed-to-decrypt-a-column-encryption-key-using-key-store-provider-azure-key-v’

Once I added member’s IP address in key vault firewall he is able to see encrypted data. Hope it helps.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply