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?
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:
This is one of the last things - you still have to make sure your app registration has the correct permissions for your subscription.