0
  1. Working on Azure SQL Server
  2. I approached Key Vault with Azure AD and generated a column master key and column encryption key to apply Always Encrypted to a specific table
  3. Therefore, if login the Always Encrypted Allowance setting from SSMS to Azure Active Directory - Universal with MFA, the encrypted table will be decrypted and inquired normally.
  4. And I am adding and managing specific users for SQL Server authentication. This is for connection with the backend.
  5. The following permissions were granted to the added user. VIEW ANY COLUMN MASTER KEY DEFINITION, VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
  6. The following error occurs when checking by accessing SQL Server Authentication to test with an added user in SSMS
Failed to decrypt column 'UserId'.
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: 'XX-XX-XX-XX-XX-XX-XX-XX-XX-XX'.

Key Vault already has Key permissions for my account, so there is no error, but the user account created for SQL Server Authentication does not have Key Vault access, so it seems to be error, but I don't know the exact error.

All the links below were referenced.

1
  • 1
    Azure Key Vault access policies primarily work with service principals, managed identities, or Azure AD users. To grant Azure SQL Server access to Key Vault, use managed identity authentication, create a service principal, or leverage an Azure AD user and associated service principal for authentication. Can you please provide your backend so, it would be more helpful to provide the answer.
    – Balaji
    Jan 8 at 12:17

1 Answer 1

0

How to access table with Always Encrypted in SQL Server Authentication?

To access table with Always Encrypted in SQL Server Authentication

  • Make sure the AD user with you are storing the keys in key vault is added in Microsoft Entra ID.
  • Make sure you give it "Decrypt", "Sign", "Get", "UnwrapKey" permissions to the AD user which you are using to store the keys in key vault.
  • While connecting with SQL authentication check Enable always Encrypted enter image description here
  • And add Column Encryption Setting = Enabled in additional properties like below https://i.imgur.com/pBJDtK1.png

Here I created SQL user and gave him permission to view the encrypted table. enter image description here

here I am getting decrypted column values: enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.