Recently took ownership of a platform that consists of multiple Azure and non-Azure components. There is a lack of documentation. After successfully testing E2E for one general scenario, I'm spending some time with the integration tests. One of the data repository tests queries a SQL table with an encrypted column. After some sleuthing, I was able to recreate the needed Azure KeyVault configuration that would allow me to pull down the SQL connection string secret using the service principal (SPN) for that environment.

Test method SomeTest threw exception: Microsoft.Data.SqlClient.SqlException: 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: 'F1-5C-6D-9F-6E-09-6A-0F-7F-B1'. One or more errors occurred. (Operation returned an invalid status code 'Forbidden') ---> Microsoft.Azure.KeyVault.Models.KeyVaultErrorException: Operation returned an invalid status code 'Forbidden'

Google for the error and the first search results are listed in the references. The SPN is added to the KeyVault that the service should reference. All other components reference this KeyVault in the resource group. That SPN has Decrypt, Unwrap Key, Verify permissions under Cryptographic Operations and at least Get and List under Key Management Operations.

I also verify that my IP Address is whitelisted through the KV Firewall. Also tested with Firewall disabled. Same error.

I decided to check the names of the actual Column Master Keys and Column Encryption Keys:

Note that I'm using Azure Data Studio (v.1.39.1) here and I can see the names of the keys and the CMKAuto1 corresponds with the one in KeyVault:

With Azure Data Studio, you can't script the keys like you can in SQL Server Management Studio (SSMS):

After this verification I still had issues, so I decided to add myself also to the KeyVault with the proper permissions. The integration tests run through Visual Studio 2022 still fails with the same error.

Next, I wanted to make sure that I was part of a DB role that had access to the CMK and CEK:

DROP ROLE IF EXISTS  db_privilegeddatareader
CREATE ROLE db_privilegeddatareader
GRANT VIEW ANY COLUMN MASTER KEY DEFINITION TO db_privilegeddatareader
GRANT VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO db_privilegeddatareader
GRANT SELECT ON DATABASE::<YOUR_DATABASE_SERVER> TO db_privilegeddatareader
EXEC db_privilegeddatareader 'db_owner', db_privilegeddatareader

Still no luck. I added myself as db_owner. Still no dice.

I debug through the test and verify that an access token/JWT was retrieved correctly.

I inspected the JWT jq and base64: sh jq -R 'split(".") | .[1] | @base64d | fromjson' <<< $jwt

The claims look correct and each token is good for an hour. The exception raised is:

Response status code indicates server error: 401 (Unauthorized).

The next step was to use Fiddler as a proxy to inspect the actual requests out but was contacted by a data analyst that needed to see the decrypted column values for one of these tables in production. Interesting coincidence...

I ensured that these analysts were added to the relevant Azure Active Directory group and ensured that this group had access control permissions in the production KeyVault. I decided to also add myself to this group to see if I can confirm what they would see.

Msg 0, Level 11, State 0, Line 0 Failed to decrypt column 'SomeEncryptedColumn'. Msg 0, Level 11, State 0, Line 0 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: 'B9-1A-CE-AE-B6-8D-FD-7C-E1-AD'. Msg 0, Level 11, State 0, Line 0 One or more errors occurred.

Note that the query will return if the encrypted column is excluded.

Even after the following settings:

Column Encryption Setting=Enabled

At this point, I decide to take a look at the keys in the Production KeyVault. There are no keys. I check to see if the keys have been deleted:

az keyvault key list-deleted "YOUR_KEY_VAULT_NAME"

No results ([]). Let's see what the Column Master Key is referenced on the SQL Server...

CREATE COLUMN MASTER KEY [CMK_Auto1]
WITH
(
    KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',
    KEY_PATH = N'https://production_key_vault_name.vault.azure.net:443/keys/CMKAuto1/some_guid'
)
GO

The KeyVault URI is pointing to some other KeyVault that isn't under the resource group that the rest of the services use. The production_key_vault_name belongs in some other resource group. Check that KeyVault and the keys exist there, however, the current version is marked Disabled while the one that SQL Server references is enabled. With the proper KV ACLs and firewall exclusion, I could view the encrypted columns only after opening a new instance of SMSS and re-authenticating.

Now back to the test environment. Looks like the KeyVault referenced here is also a part of a different resource group. Once I gave myself the proper KV ACLs and disabled the firewall (exclusion didn't work through Visual Studio, perhaps a re-opening it would have worked, but it did work with SSMS). I was able to run the integration tests successfully.

Much...

Takeaways

• Don't make assumptions about the Column Master Key location. The name can be autogenerated and identical. Use SSMS to get the details.

• Azure Data Studio does not have feature parity with SQL Server Management Studio. You won't be able to see the master database either.

• You need proper Access Control settings for the AAD user/group in your KeyVault.

• You need to ensure that the KeyVault Firewall has your IP Address allowed.

• You need to make sure that the AAD user/group is added to the appropriate database role (existing or created) that is granted COLUMN MASTER KEY DEFINITION and COLUMN ENCRYPTION KEY DEFINITION privileges.

• It's easy to mix up sessions between different logins.

• Only a login with an AAD account will allow you to add other AAD logins.

• Use Azure CLI with identities in question (SPN or AAD account) to check access. sh az keyvault key list --vault-name "your_key_vault"

• SSMS seems to cache or not refresh ACLs. If you've made changes to KeyVault permissions, it's best to open a new or another instance of SSMS and log in with the intended user.

Update: 01-05-2022

The expectation was that AAD users are added to a specific AAD Group. That group would be a user specified in the SQL databases (including master). This group was also added to the KeyVault containing the COLUMN MASTER KEY (CMKAuto2) and COLUMN ENCRYPTION KEY (CEKAuto1). The AAD Group added did not have the grants to those keys:

GRANT VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO [YOUR_AAD GROUP]
GRANT VIEW ANY COLUMN MASTER KEY DEFINITION TO [YOUR_AAD_GROUP]

References

• stackoverflow.com/questions/57735293/failed..

• dba.stackexchange.com/questions/292393/erro..

• learn.microsoft.com/en-us/answers/questions..

• learn.microsoft.com/en-us/cli/azure/keyvaul..

• learn.microsoft.com/en-us/sql/relational-da..

• shudnow.io/2020/01/22/sql-always-encrypted-..

• dontpaniclabs.com/blog/post/2020/06/16/conf..