Introduction
Always Encrypted is a feature in Windows SQL Server designed to protect sensitive data both at rest and in flight between a client application server and Azure or SQL Server database(s).
Data protected by Always Encrypted remains in an encrypted state until it has reached the client application server. This effectively mitigates man-in-the-middle attacks and provides assurances against unauthorized activity from rogue DBAs or admins with access to Azure or SQL server databases.
The nShield HSM secures the key used to protect the Column Master Key, stored in an encrypted state on the client application server.
Product configurations
Supported nShield hardware and software versions
Entrust has successfully tested with the following nShield hardware and software versions:
| HSM | Security World Software | Firmware | Netimage |
|---|---|---|---|
Connect 5c |
13.6.12 (LTS 4) |
13.6.12 (LTS 4) |
|
nShield XC |
13.6.12 (LTS 4) |
13.6.11 |
|
nSaaS |
12.80.4 |
12.80.5 |
Role separation
The generation of keys and the application of these keys for encryption or decryption are separate processes. The processes can be assigned to users with various access permissions, or Duty Roles. The table below shows the processes and duty roles with reference to the Security Administrator and the database Administrator.
| Entrust recommends that you allow only unprivileged connections unless you are performing administrative tasks. |
| Process | Duty Role |
|---|---|
Generating the Column Master Key (CMK) and Column Encryption Key (CEK) |
Security Administrator |
Applying the CMK and CEK in the database |
Database Administrator |
Four database permissions are required for Always Encrypted.
| Operation | Description |
|---|---|
|
Required to generate and delete a column master key |
|
Required to generate and delete a column encryption key |
|
Required to access and read the metadata of the column master keys to manage keys or query encrypted columns |
|
Required to access and read the metadata of the column encryption key to manage keys or query encrypted columns |
Multiple Windows user accounts on a single client server
The Entrust nShield HSM solution for Microsoft SQL Always Encrypted enables keys that are associated with one user to be used by other users, providing secure access to a common database. Ask Entrust Support for hotfix-TAC1266 patch to allow multiple users to use the same always encrypted key.
Multiple client servers
Each client server wanting access to the content of the encrypted data with a given CEK must have:
-
An HSM in the same Security World.
-
Hotfix-TAC1266 to allow multiple users to use the same always encrypted key.
-
A copy of the CMK key token stored on its local drive.
Requirements
-
Knowledge of your organization Certificate Practices Statement and a Security Policy / Procedure in place covering administration of the HSM.
-
Access to the Entrust TrustedCare Portal.
-
An Entrust nShield HSM.
-
Network environment with usable ports 9004 and 9005 for the HSM.
Familiarize yourself with the nShield Documentation.
-
The importance of a correct quorum for the Administrator Card Set (ACS).
-
Whether Operator Card Set (OCS) protection or Softcard protection is required.
-
If OCS protection is to be used, a 1-of-N quorum must be used.
-
Whether your Security World must comply with FIPS 140 Level 3 or Common Criteria standards. If using FIPS 140 Level 3, it is advisable to create an OCS for FIPS authorization. For more information see FIPS 140 Level 3 compliance.
-
Whether to instantiate the Security World as recoverable or not.