Categories
Sql Server

Always Encrypted SQL Server 2016

Always Encrypted SQL Server 2016

Always Encrypted is a new feature introduced in SQL Server 2016. Always Encrypted SQL Server 2016 main objective is to protect sensitive data from unauthorized access. Unlike TDE, as well, Always Encrypted allows you to encrypt only certain columns, rather than the entire database. Always Encrypted option enables data encryption at application level with the help of ADO.NET. By this way our data is secure over the network.

An Always Encrypted-enabled driver installed on the client computer ensures that plain text is only revealed in the application, and nowhere in between the application and the database.

Always Encrypted limitations:

  • Columns using one of the following datatypes: xml, timestamp/rowversion, image, ntext, text, sql_variant, hierarchyid, geography,geometry, alias, user defined-types.
  • FILESTREAM columns
  • Columns with ROWGUIDCOL property
  • Columns with default constraints
  • Sparse columns set
  • String (varchar, char, etc.) columns with non-bin2 collations
  • Partitioning columns

SQL Server 2016 Always Encrypted Concepts:

Column Master Key – This protects the column encryption key(s). You must have at least one master key before encrypting any columns.
Column Encryption Key – This is the encryption key that actually protects that encrypted columns.
Column-level encryption setting – A column must be set to encrypted, with a specific column encryption key, the algorithm (currently only one algorithm is supported), and the type of encryption to be used:
Deterministic – always encrypts to the same cipher text, so can be used for certain          operations (point lookups, distinct, group by) and can be indexed.
Randomized – more secure, but cannot be evaluated or used in any operations (write/display only) and cannot be indexed.
Connection string – In order for the client driver to understand that column encryption is in use. Make sure .NET Framework version 4.6 or higher is installed on your development machine.

Always Encrypted Example Query:

----CREATE DATABASE ALENC_DEMO----
CREATE DATABASE ALENC_DEMO
GO

----USE ALENC_DEMO----
USE ALENC_DEMO
GO

----NOW CREATE COLUMN MASTER KEY-----
CREATE COLUMN MASTER KEY CMK  
WITH (  
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
	KEY_PATH = N'LocalMachine/My/87FC3FFF276BBB7CC2A19D3397FD22624D9176F9'
   );
  
----NOW CREATE COLUMN ENCRYPTION KEY----  
CREATE COLUMN ENCRYPTION KEY CEK   
WITH VALUES  
(  
    COLUMN_MASTER_KEY = [CMK],
	ALGORITHM = 'RSA_OAEP',
	ENCRYPTED_VALUE = 0x01700000016C006F00630061006C006D0061006300680069006E0065002F006D0079002F0038003700660063003300660066006600320037003600620062006200370063006300320061003100390064003300330039003700660064003200320036003200340064003900310037003600660039001765C438C24249AB67982ACBFD574CAB0A1D8F98A50BED0A99CC05FDC8F1F1FA6061068E6D2900BED6754FB7196EC88DE7DC8A0FEF71F4FB21342F964575CCF66BA9D132C87270530936120ED0623AD0803956DE720AE4C203B6A1A575A080CCB0D28B584EC0FE3F12B186526DEE5CBDFAA3D128F353AFEBA0FDD895C7913F022DEC79035C9290FFE6AADB2300284EB0A55F44438E208ABC639FD2D9D5BC375DC15BCEB9B37E3CA8C9948C192A1456E5A9D8AF0A3E500C2EBC07467583A7EF31E5E3901FE46FF18311419274945CD79CE742B72E0F69DD844AAF31BB744FEBBF9FA996CB1E1F0F6C39D51FA6D92FF45F535A6E20AE45C9F3E4D253AE306D24E8412F4A25D299BAAE56603617A2AA4DC8CC527BC1FEC346D29426462247D683B65664A24A9484AF0406D46DF14DB1E3C3713FF10B0FB98B25D4238BBBDF4FD9A288DB48657A9119573485CDE547294E5BB5D9150FCB2E7513D1DF3D02871FACF58BFC7D8E16E4DEF8E7E6CD07F0527F9FBF863D78206E83B2AE4BCD5A02D8AA24F3DB5A948410F58A743AD04B842D61414AEC778B6FEFA613355D639384EEA02545FD880C65F29083A99D7FD7FE7099D8880E54C79DF4C0FED417C7A8A352F1B0352AB685D9E447510B47F7891EAEB03A39E589AA4C54718DF8727E8850F54268D474C54023604D349A7224B05521D51E1096136A4962284556EA8BFCD622283C
);  

----CREATE TABLE WITH ENCRYPTED COLUMNS----
CREATE TABLE ENCRYPTED_TABLE
(
ID INT IDENTITY(1,1) PRIMARY KEY,
FIRST_NAME NVARCHAR(50) COLLATE Latin1_General_BIN2
ENCRYPTED WITH 
    (
       ENCRYPTION_TYPE = RANDOMIZED, 
       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
       COLUMN_ENCRYPTION_KEY = CEK
    ),
LAST_NAME VARCHAR(20),
CREDIT_CARD NVARCHAR(50) COLLATE Latin1_General_BIN2
ENCRYPTED WITH 
    (
       ENCRYPTION_TYPE = DETERMINISTIC , 
       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
       COLUMN_ENCRYPTION_KEY = CEK
    ),
)

Create Column Master key and Column Encryption Key SSMS:

1. Open SQL Server Management Studio, Expand User Database > Security > Always Encrypted Keys.

2. Right click on Always Encrypted Keys and click on New Column Master Key. Enter the key name and choose Key Store.Always Encrypted SQL Server 2016

ALWAYS_ENC2

3. Right Click on Always Encrypted Keys and click on New Column Encryption Key. Enter name and choose column master key.

Always Encrypted SQL Server

Always Encrypted SQL Server 2016

4. Now create your table with these Column Master key and Column Encryption Key.

Please refer to following link to develop application for Always Encryption feature with .NET: https://msdn.microsoft.com/en-in/library/mt757097.aspx