Always Encrypted

*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

AE_1

  • 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.

AE_2

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

AE_3

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

AE_4

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

AE_6

  • 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

AE_7.png

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

AE_8

  • Encryption verification from SQL Profiler
    AE_9

 

Leave a Reply

Your email address will not be published. Required fields are marked *