*This is NOT TDE*

New feature in SQL Server 2016 : a new capability that protects data at rest and in motion.

Always Encrypted  encrypts data of individual columns end -to -end(protects data at rest and in motion). This feature currently support Ado.net & Sql Client of ADO.Net 4.6.

Lets see how to configure this feature on New or Existing Database

• Create a user database if does not exists
• Expand the database and navigate to Security – Always Encrypted Keys

• Select Column Master Keys – Right Click – New Column Master Key
  • Give a meaningful name and Generate a Self-Signed Certificate. You can have a 3rd party certificate installed on the server.

By default the self-signed certificate will be created with a Expiry Date of a 365 days.

USE <Database Name>
CREATE COLUMN MASTER KEY [Name]
WITH
(
      KEY_STORE_PROVIDER_NAME=N'MSSQL_CERTIFICATE_STORE',
      KEY_PATH=N'CurrentUser/My/Certificate Thumbprint'
)
GO

•  Generate Column Encryption Keys

• Give a meaningful Key-name and Choose Column master Key 
• View Column Master Keys and Column Encryption Keys as shown below

• Create table and provide the column Encryption Key to the Columns which require encryption.

CREATE TABLE [dbo].[Patients](
 [PatientId] [int] IDENTITY(1,1), 
 [SSN] [char](11) COLLATE Latin1_General_BIN2 
 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, 
 ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
 COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL,
 [FirstName] [nvarchar](50) NULL,
 [LastName] [nvarchar](50) NULL, 
 [MiddleName] [nvarchar](50) NULL,
 [StreetAddress] [nvarchar](50) NULL,
 [City] [nvarchar](50) NULL,
 [ZipCode] [char](5) NULL,
 [State] [char](2) NULL,
 [BirthDate] [date] 
 ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, 
 ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
 COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL
 PRIMARY KEY CLUSTERED ([PatientId] ASC) ON [PRIMARY] )
 GO

• Insert some data into the table and later query it. The SSN and BirthDate column data is encrypted (in motion)

• Encryption verification from SQL Profiler