如何为SQL Always Encrypted妥善保护加密密钥并控制解密权限

Great question—this is a super common concern when rolling out Always Encrypted, and you absolutely can lock down decrypted access to only your target AD group while blocking DBAs and other SSMS users from seeing plaintext. Let’s break down how to do this, including addressing your question about IIS server separation.

Core Context: Always Encrypted’s Key Hierarchy #

First, a quick recap to ground this: Always Encrypted uses two layers of keys, and access to both is required for decryption:

• Column Master Key (CMK): Encrypts the Column Encryption Keys (CEKs). This lives outside the database (e.g., Azure Key Vault, local machine certificate store, HSM)—never store it in the SQL Server itself.
• Column Encryption Key (CEK): Encrypts your sensitive table columns. This is stored in the database, but it’s encrypted by the CMK, so it’s useless without access to the CMK.

To decrypt data, a user/app needs:

1. Database permissions to read the CEK metadata
2. Access to the CMK (to decrypt the CEK)

We’ll lock down both to only your allowed AD group.

Step-by-Step Configuration #

1. Store the CMK in an External Key Store (Critical!) #

Don’t use SQL Server’s built-in CMK storage. Pick an external store where you can tightly control access:

• On-prem: Use the Local Machine Certificate Store on your IIS server (this aligns with your idea of separating IIS and SQL Server).
• Cloud: Use Azure Key Vault (easier for centralized access control).

For either option:

• Grant only your target AD group (the one your IIS app pool runs under) permission to access the CMK. For example:
  • If using a local certificate: Add the AD group to the certificate’s "Read" permissions in the Local Machine > Personal certificate store.
  • If using Azure Key Vault: Assign the Key Vault Crypto User role to the AD group (this allows decrypting the CEK).
• Explicitly deny access to DBA groups in this external store—they should have zero permissions here.

2. Lock Down Database-Level CEK Permissions #

Even if DBAs can’t access the CMK, you should tighten database permissions to be safe:

• Grant your target AD group these two permissions (required for the app to decrypt):

  GRANT VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO [YourAllowedADGroup];
  GRANT VIEW ANY COLUMN MASTER KEY DEFINITION TO [YourAllowedADGroup];
  

• Deny these permissions to DBA groups (e.g., sysadmin):

  DENY VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO [DBAAdGroup];
  DENY VIEW ANY COLUMN MASTER KEY DEFINITION TO [DBAAdGroup];
  

This ensures DBAs can’t even see the CEK/CMK metadata, let alone use it.

3. Your IIS + SQL Separation Idea: Does It Work? #

Yes, separating your IIS server (where the CMK is stored) from the SQL Server is a key part of this setup—but it’s not the only piece. Here’s why it works:

• DBAs don’t have access to your IIS server, so they can’t get to the CMK stored there. Even if they enable Column Encryption Setting=Enabled in SSMS, they’ll get an error when trying to decrypt data because they can’t access the CMK.
• Make sure your IIS app pool runs under an AD account that’s part of your allowed group, and that account has the necessary permissions to read the CMK from the IIS server’s certificate store.

4. Extra Guardrails to Block DBA Workarounds #

• Deny DBAs permissions to modify Always Encrypted keys:

  DENY ALTER ANY COLUMN ENCRYPTION KEY TO [DBAAdGroup];
  DENY ALTER ANY COLUMN MASTER KEY TO [DBAAdGroup];
  

• If you’re on a supported SQL Server version, consider Always Encrypted with Secure Enclaves if you need to run queries on encrypted columns (like WHERE clauses). This adds another layer of security by keeping decryption within a secure enclave, but it’s optional for basic decryption access control.

Final Verdict: Is IIS Separation + CMK on IIS Enough? #

It’s the foundation, but you need to pair it with:

• Proper AD group permissions on the CMK store
• Tightened database-level permissions for CEK/CMK metadata

When you combine all these, DBAs (or anyone not in your allowed group) won’t be able to decrypt sensitive data—even if they enable Column Encryption Setting=Enabled in SSMS. They’ll only see ciphertext, while your ASP.NET app will work seamlessly with plaintext data.

内容的提问来源于stack exchange，提问作者the Ben B