*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)