DB-유

Always Encrypted in SQL Server: Overview and Setup

· mssql, always encrypted, security, encryption

The audit request always arrives the same way: "the DBAs must not be able to read the card number column." TDE doesn't help, since it only protects files on disk and anyone with db_datareader (me included) still sees clear text. Always Encrypted, new in SQL Server 2016 and in Standard edition since 2016 SP1, moves encryption into the client driver. The database only stores and receives ciphertext, and the keys live on the application side.

I'll cover the two-level key model, setting up an encrypted column in SSMS and T-SQL, and the limits you should know before promising the app team anything. You need SQL Server 2016 SP1 or 2017, SSMS 17.x on the workstation that holds the master key, and clients on .NET Framework 4.6.1+ with System.Data.SqlClient (or ODBC Driver 13.1+ / JDBC 6.0+). Older drivers can't decrypt and just see binary data.

The keys

It's easier once you see the two keys:

  • Column Master Key (CMK) – an asymmetric key stored outside SQL Server: in the Windows Certificate Store, Azure Key Vault, or an HSM through CNG. SQL Server only holds a pointer (the key path).
  • Column Encryption Key (CEK) – a symmetric key that actually encrypts the data. It is stored in the database, but encrypted with the CMK, so the server cannot use it.

The client driver retrieves the encrypted CEK from the server, decrypts it with the CMK it can reach locally, and then encrypts parameters and decrypts result columns transparently.

Create the Column Master Key

On the workstation, create a self-signed certificate (a CA-issued one is better for production) in the current user store:

$cert = New-SelfSignedCertificate -Subject "CN=AlwaysEncrypted CMK" -CertStoreLocation Cert:\CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange -KeyLength 2048
$cert.Thumbprint

Then register it in the database. In SSMS this is under Security → Always Encrypted Keys → Column Master Keys → New; the generated T-SQL is:

CREATE COLUMN MASTER KEY [CMK_Sales]
WITH (
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/My/2F5A1C9E0B7D4E3F8A6C1B2D3E4F5A6B7C8D9E0F'
);

Note that this statement stores only the path. The certificate itself must later be present on every application server that needs to read the data. Back up the certificate with its private key somewhere safe right now. Lose it and the data is gone for good.

Create the Column Encryption Key

The CEK has to be generated and encrypted with the CMK by a client, because SQL Server cannot do that part. SSMS does it for you (Column Encryption Keys → New), producing:

CREATE COLUMN ENCRYPTION KEY [CEK_Sales]
WITH VALUES (
    COLUMN_MASTER_KEY = [CMK_Sales],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075...
);

The same can be done from PowerShell with the SqlServer module (New-SqlColumnEncryptionKey), which is the way to go for scripted deployments.

Encrypt the columns

Choose the encryption type per column:

  • Deterministic – the same plaintext always produces the same ciphertext. Allows equality lookups, joins, GROUP BY and indexes on the column. Requires a _BIN2 collation.
  • Randomized – different ciphertext every time. More secure, but the column can only be stored and retrieved; no WHERE clause at all on the server.

For a new table:

CREATE TABLE dbo.Customers (
    CustomerID int IDENTITY PRIMARY KEY,
    Name nvarchar(100) NOT NULL,
    NationalID char(11) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Sales], ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    CardNumber varchar(19)
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Sales], ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
);

For an existing table with data, you cannot simply ALTER COLUMN; the data must be encrypted client-side. Use the Always Encrypted Wizard in SSMS (right-click the table → Encrypt Columns), or Set-SqlColumnEncryption from PowerShell. The wizard copies the table with the data encrypted and swaps it in, so plan for a maintenance window on large tables.

Connecting with encryption enabled

The client needs one connection string keyword:

Server=sqlprod01;Database=Sales;Integrated Security=true;Column Encryption Setting=Enabled;

In SSMS the same option is on the Additional Connection Parameters tab of the connect dialog (Column Encryption Setting=Enabled). Values must be passed as parameters, never as literals; the driver encrypts SqlParameter values before sending them:

-- works from a client with the CMK
DECLARE @id char(11) = '12345678901';
SELECT CustomerID, Name FROM dbo.Customers WHERE NationalID = @id;

With SSMS 17.x, parameterisation of DECLARE variables is enabled under Query Options → Execution → Advanced → Enable Parameterization for Always Encrypted.

Seeing both sides

Connect without the setting and select from the table. Encrypted columns come back as varbinary blobs. Connect with the setting from the machine holding the certificate and the same query returns clear text. Metadata for auditing:

SELECT c.name, c.encryption_type_desc, k.name AS cek
FROM sys.columns c JOIN sys.column_encryption_keys k ON k.column_encryption_key_id = c.column_encryption_key_id
WHERE c.object_id = OBJECT_ID('dbo.Customers');

Limits and errors

  • "Failed to decrypt a column encryption key. Invalid key store provider name: 'MSSQL_CERTIFICATE_STORE'." or "Certificate with thumbprint '...' not found in certificate store 'My' in certificate location 'CurrentUser'." The client machine or the account running the application does not have the certificate. Export it with the private key (Export-PfxCertificate) and import it under the identity that runs the app.
  • "Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', ...)" You compared the column with a literal or with a column encrypted under a different key or type. Use a parameter, and make both sides deterministic with the same CEK.
  • "Encryption scheme mismatch for columns/variables" on a stored procedure parameter. The procedure metadata was compiled before the column was encrypted; run sp_refresh_parameter_encryption 'dbo.usp_FindCustomer'.
  • LIKE, range predicates, arithmetic, and ORDER BY on encrypted columns are not possible in this version. Filter and sort on the client side or on non-encrypted columns.

Don't treat it as a general encryption switch. Use it on a few sensitive columns with simple access patterns.