SQL Server 2008至2016列级加密与客户端数据访问方案咨询

Got it, let’s break down your options for column-level encryption in SQL Server 2008 through 2014 (since you can’t upgrade to 2016 yet) and how they work with ADO.NET/Entity Framework, plus the extra effort you’ll need to put in compared to Always Encrypted.

1. Database-Side Column Encryption Options #

These are built into SQL Server, but unlike Always Encrypted, they don’t handle encryption/decryption automatically for clients—you’ll need to manage key access and adjust your code.

a. Symmetric Key Encryption (Most Common for Columns) #

This is the most performant option for encrypting large volumes of column data. You’ll create a symmetric key (protected by a certificate or asymmetric key) and use it to encrypt specific columns.

Example SQL setup:

-- Create a master key first (if not exists)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongMasterKeyPassword123!';

-- Create a certificate to protect the symmetric key
CREATE CERTIFICATE CustomerDataCert WITH SUBJECT = 'Customer PII Certificate';

-- Create the symmetric key
CREATE SYMMETRIC KEY CustomerDataKey 
    WITH ALGORITHM = AES_256 
    ENCRYPTION BY CERTIFICATE CustomerDataCert;

-- Encrypt an existing column (e.g., SSN)
ALTER TABLE Customers 
ADD SSN_Encrypted VARBINARY(128);

OPEN SYMMETRIC KEY CustomerDataKey DECRYPTION BY CERTIFICATE CustomerDataCert;
UPDATE Customers 
SET SSN_Encrypted = EncryptByKey(Key_GUID('CustomerDataKey'), SSN);
CLOSE SYMMETRIC KEY CustomerDataKey;

Client Access (ADO.NET/EF):

• Before querying the encrypted column, you need to run OPEN SYMMETRIC KEY CustomerDataKey DECRYPTION BY CERTIFICATE CustomerDataCert; in the same database session.
• For ADO.NET: You can execute this command before your SELECT/UPDATE queries, or wrap logic in stored procedures that handle key opening internally.
• For EF: You’ll need to either use stored procedures for CRUD operations on encrypted columns, or use EF interceptors to inject the key-opening command before relevant queries.

Extra Workload:

• Manage key backups, permissions (only grant VIEW DEFINITION on certificates/keys to authorized users), and key rotation.
• Code changes to handle key opening—no "set it and forget it" like Always Encrypted.
• Report tools (e.g., SSRS) need to include the key-opening command in their dataset queries.

b. Asymmetric Key Encryption #

More secure but slower than symmetric keys—best for encrypting small, high-value data (like encryption keys themselves, not large column datasets).

Example setup:

CREATE ASYMMETRIC Key HighValueDataKey
    WITH ALGORITHM = RSA_2048;

ALTER TABLE Customers
ADD CreditCard_Encrypted VARBINARY(256);

UPDATE Customers
SET CreditCard_Encrypted = EncryptByAsymKey(AsymKey_ID('HighValueDataKey'), CreditCardNumber);

Client Access:

• Decryption requires access to the asymmetric key (or its private key). You’ll need to run DecryptByAsymKey(AsymKey_ID('HighValueDataKey'), CreditCard_Encrypted) in queries.
• For EF, this means writing custom LINQ queries or using SQL functions, since EF doesn’t natively handle asymmetric key decryption.

Extra Workload:

• Higher CPU overhead on the database server.
• More complex key management (private key security is critical—never store it in plaintext).
• Code changes to explicitly call decryption functions in queries.

c. Certificate-Based Encryption (Indirect) #

You can encrypt columns directly with a certificate, but this is inefficient for large datasets—most often, certificates are used to protect symmetric keys (as shown in the symmetric key example above). Direct certificate encryption works similarly to asymmetric keys but uses SQL Server certificates instead of asymmetric keys.

2. Client-Side Encryption (Full Control, More Work) #

If you want the database to never see plaintext data (like Always Encrypted’s client-side model), you can handle encryption/decryption entirely in your application code before sending data to the database.

How to Implement:

• Use .NET encryption libraries (e.g., System.Security.Cryptography.AES) to encrypt sensitive data in your ADO.NET/EF code before saving it to a VARBINARY column.
• For EF, you can encapsulate encryption/decryption in entity property getters/setters:

  public class Customer
  {
      private string _ssn;
      public byte[] SSN_Encrypted { get; set; }
  
      public string SSN
      {
          get => Decrypt(SSN_Encrypted);
          set => SSN_Encrypted = Encrypt(value);
      }
  
      private byte[] Encrypt(string plaintext)
      {
          // Implement AES encryption with your secure key
      }
  
      private string Decrypt(byte[] ciphertext)
      {
          // Implement AES decryption with your secure key
      }
  }
  

Extra Workload:

• Full responsibility for encryption logic, key storage (never hardcode keys—use secure storage like Windows Credential Manager or a custom key vault), and key rotation.
• All applications/report tools accessing the data need to implement the same encryption/decryption logic—no out-of-the-box support for tools like SSRS (you’ll need custom code in reports to decrypt data).
• No database-side querying of plaintext data (e.g., you can’t run WHERE SSN = '123-45-6789' on the database; you have to encrypt the value client-side first).

3. How This Compares to SQL Server 2016 Always Encrypted #

Always Encrypted is designed to minimize effort:

• You set up column encryption in SQL Server, then just add Column Encryption Setting=Enabled to your connection string.
• The .NET driver handles encryption/decryption automatically—no code changes needed for ADO.NET/EF.
• Keys are stored client-side or in Azure Key Vault, with built-in key management.

Pre-2016 options lack this automation:

• Database-side encryption requires manual key management and code changes to handle key access.
• Client-side encryption requires full custom implementation across all apps/reports.

4. Summary of Extra Workload #

• Key Management: No built-in secure key storage like Always Encrypted—you’ll need to implement or use a third-party key management system, plus handle backups, permissions, and rotation.
• Code Changes:
  • ADO.NET: Add key-opening commands or wrap logic in stored procedures.
  • EF: Use interceptors, stored procedures, or custom property logic to handle encryption/decryption.
• Report Tool Adaptation: Modify reports to include key-opening commands (for database-side encryption) or custom decryption code (for client-side encryption).
• Performance Testing: Database-side encryption adds server CPU load; client-side encryption adds client CPU and increases data transfer size.
• Compliance Validation: Manually verify that key access is restricted and encryption logic meets your compliance requirements (Always Encrypted handles much of this automatically).

内容的提问来源于stack exchange，提问作者bitshift