How can I access table with Always Encrypted in SQL Server Authentication?

Question

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.

• https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver16
• https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-query-columns-ssms?view=sql-server-ver16
• https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-tutorial-getting-started?view=sql-server-ver16&tabs=ssms
• https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/overview-of-key-management-for-always-encrypted?view=sql-server-ver16

Answer 1

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
• And add Column Encryption Setting = Enabled in additional properties like below

Here I created SQL user and gave him permission to view the encrypted table.

here I am getting decrypted column values: