question

SandeepPawar-8572 avatar image
0 Votes"
SandeepPawar-8572 asked NavtejSaini-MSFT commented

Error while querying 'Always encrypted' column in Azure SQL database

Hi Experts,

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.

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. Please help.

100437-image.png


azure-sql-database
image.png (24.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SandeepPawar-8572, welcome to Microsoft Q&A forum.

There was similar issue mentioned in Stack with the solution. Could you please check if it helps:

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


0 Votes 0 ·

1 Answer

SandeepPawar-8572 avatar image
0 Votes"
SandeepPawar-8572 answered NavtejSaini-MSFT commented

Hi @AnuragSharma-MSFT, Many thanks for the info but I have visited that page already. Anyway 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 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.

It would be good if the error message mention something about the key vault firewall thing then it would be easy to focus and fix the issue. Hope it helps.

Thanks,
Sandeep

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@SandeepPawar-8572 Thank you for posting the answer and helping who will have the same issue.

1 Vote 1 ·