How to implement SQL Server 2016 Always Encrypted

Environment :

Client Machine ( CM ) - Windows 7 Professional - SSMS 17 SQL Server Machine ( SM ) - Windows Server 2012 R2 Datacenter - SQL Server 2016 - SSMS 17
Step : 1 Create Database On SM Connect to database via SSMS Create testing database named 'Demo' Step : 2 Creating Column Master Key and Column Encryption Key
Create Master Key
Open DEMO -> Security -> Always Encrypted Keys -> Column Master Keys -> Right Click -> New Column Master Key

Name : CMK1 Key Store : Windows Certificate Store - Current User

Click Generate Certificate to create the cert

Select the new certificate and Click OK to create the key pointed to the Cert Store of Current User (CMK1 only store the meta data which point the location of cert. The Cert is the Key to decrypt the Column encryption key.)

Create Column Encryption Keys Open DEMO -> Security -> Always Encrypted Keys -> Column Encrytion Keys Right Click -> New Column Encryption Key
Name : CEK1
Column Master Key : CMK1

Click OK

Step 3 : Creating Always Encrypted Table
Config BirthDate and SSN Field Encrypted by CEK1

CREATE TABLE dbo.Demo_Always_Encrypted 
(
   ID INT IDENTITY(1,1) PRIMARY KEY,
   LastName NVARCHAR(45),
   FirstName NVARCHAR(45),
   BirthDate DATE ENCRYPTED WITH 
    (
        ENCRYPTION_TYPE = RANDOMIZED, 
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
        COLUMN_ENCRYPTION_KEY = CEK1
    ),
   SSN CHAR(11) COLLATE Latin1_General_BIN2 
   ENCRYPTED WITH 
   (
     ENCRYPTION_TYPE = DETERMINISTIC, 
     ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
     COLUMN_ENCRYPTION_KEY = CEK1
   )
);

Step 4 : Insert testing Data via SSMS

DECLARE @SSN CHAR(10) = '795-73-983'
DECLARE @BirthDate Date = '2010-10-10'

INSERT INTO dbo.Demo_Always_Encrypted
(LastName, FirstName, BirthDate, SSN) 
VALUES ('Larsen','Gregory',@BirthDate, @SSN);

Step 5 : Query in Server SSMS

SELECT [ID]
      ,[LastName]
      ,[FirstName]
      ,[BirthDate]
      ,[SSN]
  FROM [DEMO].[dbo].[Demo_Always_Encrypted]

Step 6 : Export the Private key from Server for client access data Run Cert Management Console (reference to Additional Information) in SM Open Certificates -Current - > Personal -> Certificates

Right Click "Always Encrypted Certificate" -> All Task -> Export -> Next

Checked "Export as Private Key" -> Next

Checked "Include all certificates in the certification path if possible" -> Next

Key in password ex: 12345678 -> Next

Key in Filename for the certificate and save path -> Next -> Finish

This exported certificate is the master key for decrypt the CMK1. If this cert is lost, all encrypted column will not able to decrypt.

Step 7 : Import the Private key to client machine. Run Cert Management Console in CM Open Certificates - Current User -> Personal -> Certificates Right Click "All Task" -> Import -> - Include all extended properties - Mark this key as exportable ( optional ) Step 8 : Delete Server Cert

Delete the server cert is to prevent the system administrator or DBA have a means to view the encrypted data.


Testing :
Step 9 : Verify Query the encrypted data in Server machine is deniable Step 10 : Verify Query the encrypted data in Client machine is successful ( with the certificate )


Additional Information:

A: Login Database via with Column Encryption Setting Enable

1. Run SSMS V17 or above.
2. Connect Database
3. Config Parameter "Column Encryption Setting=Enabled"

B: To add Certificate Manager to Microsoft Management Console

1. Click Start, click Run, type mmc, and then click OK.
2. In the File menu, click Add/Remove Snap-in.
3. In the Add/Remove Snap-in box, click Add.
4. In the Available Standalone Snap-ins list, click Certificates, and then click Add.
5. Click Computer Account, and then click Next.
6. Click the Local computer (the computer this console is running on) option, and then click Finish.
7. Click Close, and then click OK.

Reference:

Tutorial http://www.databasejournal.com/features/mssql/exploration-of-sql-server-2016-always-encrypted-part-1.html http://www.databasejournal.com/features/mssql/exploration-of-sql-server-2016-always-encrypted-part-1-2.html Always Encrypted Limitation https://blogs.sentryone.com/aaronbertrand/t-sql-tuesday-69-always-encrypted-limitations/