MSFTSEC03-BP02 Enable Always Encrypted feature for SQL Server
Microsoft SQL Server's Always Encrypted feature provides robust data protection using client-side encryption with certificates. This technology creates a clear separation between data owners who can view the information and data managers who shouldn't have access. It effectively safeguards sensitive data by encrypting it in the database, during transit, and even while being processed. Always Encrypted is available not only in on-premises SQL Server deployments but also in cloud environments, including Amazon RDS for SQL Server and SQL Server instances running on Amazon EC2. By implementing Always Encrypted, organizations can enhance their data security posture, particularly when handling sensitive information in Microsoft SQL Server environments on AWS.
Desired outcome: Implement client-side encryption capabilities that protect sensitive data throughout its entire lifecycle, keeping data encrypted even during processing and is only accessible to authorized applications and users with proper decryption keys.
Common anti-patterns:
-
Processing sensitive data in plaintext within applications or databases, exposing it to potential compromise during computation or memory access by unauthorized processes.
-
Implementing encryption in use inconsistently across different data types or applications, leaving some sensitive information vulnerable during processing operations.
-
Using client-side encryption without proper key management or secure key distribution mechanisms, potentially compromising the security benefits of the encryption implementation.
Benefits of establishing this best practice:
-
Maximum data protection through encryption that maintains data confidentiality even during processing operations, verifying that sensitive information is never exposed in plaintext to unauthorized systems or users.
-
Enhanced separation of duties between data owners and data managers, allowing database administrators to manage systems without accessing sensitive business data.
-
Improved regulatory capabilities for highly regulated industries that require the highest levels of data protection, including protection during data processing operations.
Level of risk exposed if this best practice is not established: High
Implementation guidance
When implementing Always Encrypted, first identify which sensitive data elements truly need this protection layer. Then map out where to apply based on your data flows and access patterns. Evaluate feature limitations and potential performance impacts before proceeding, as encryption/decryption operations can affect response times and resource usage.
Implementation steps
-
Identify highly sensitive data elements that require protection during processing, such as personally identifiable information (PII), financial data, or healthcare records.
-
Configure SQL Server Always Encrypted for identified sensitive columns, choosing appropriate encryption types (deterministic or randomized) based on query requirements.
-
Set up certificate-based key management for Always Encrypted, and properly store and distribute keys to authorized client applications.
-
Modify client applications to handle encrypted data operations, including proper connection string configuration and query modifications.
-
Implement secure key provisioning mechanisms that allow authorized applications to access encryption keys while blocking unauthorized access.
-
Configure column master keys and column encryption keys with appropriate permissions and access controls to maintain separation of duties.
-
Test application functionality with encrypted data for proper operation and performance while maintaining data protection.
-
Establish monitoring and auditing procedures to track key usage, encryption operations, and access to sensitive encrypted data.
Resources
Related documents:
Related tools:
-
Azure Key Vault
(for hybrid scenarios)
