What is Always Encrypted and how it is applied to protect sensitive data

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024

In his time GDPR let's talk about another feature of SQL Server to protect sensitive data. It's nothing more than that Always Encrypted!

What is Always Encrypted

Its purpose is that the data is always encrypted by the application from and to the database. It contains a separation of rights between who owns the data and who can see it.

Let's see how it works

We can enable Always Encrypted for specific fields that contain sensitive data. It uses two of its keys  column encryption key  which encrypts the field and of column master key which encrypts the remaining keys.

Encryption is divided into two categories

Deterministic: is the creation of an encrypted value that remains the same for every identical value in the field. E.g. in country encryption, every customer that has the value Greece will have the exact same encrypted value in all records. This function enables this field to be used for indexing, joins and pivoting, but as a disadvantage it allows someone to guess the price. E.g. from the moment I know that a customer is in the country of Greece and I know the coded price so I also know which other customers are from Greece.

Randomized: is the creation of encrypted value that will be different every time even for the same value in the field. E.g. in country encryption every customer that has the value Greece will have a different encrypted value. This function leads to greater security but as a disadvantage it does not allow one to use the field for indexing, join and groups.

The example

First we make one  column encryption key and one master encryption key.

This can be done with T-SQL and with the SSMS GUI.

Using T-SQL

USE [bi_test]
GO

CREATE COLUMN MASTER KEY [CMK_Auto1]
WITH
(
       KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
       KEY_PATH = N’CurrentUser/my/44866B5D9463FDAB3BBF961C2BBE78E01CCD5C35′
)
GO

CREATE COLUMN ENCRYPTION KEY [CEK_Auto1]
WITH VALUES
(
       COLUMN_MASTER_KEY = [CMK_Auto1],
       ALGORITHM = 'RSA_OAEP',
       ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F003400340038003600360062003500640039003400360033006600640061006200330062006200660039003600310063003200620062006500370038006500300031006300630064003500630033003500555BAFF9D24FD8C7D15AD1E8850AA95921ABFD8213E80ED87D630EB3AD01AE60E758689FDC8F18ABCBC50AF48F927FBB22CBED7CFF871FE0579AB9B8F8A128AB5129AF5CA693C05AECE025EF02A344D36C4BD50AA665433D1F9C97B8FA605A79F4A54A0065A8252D78619A6D467547358033F31078326A75F8626FC7306611673B043A6E4D3AA99547E9B2A06967ABC488656229C19045A8145445A84338DDA807B40444E41093AA6BB0B5D29B16E9787EAD652042FFD73B264483CEE592B370233C51523BC843434D6201C5F3D51C5BE50B9EE03AE9A2C888C79FC4C5A09143625F55DD641359A14C21DD627FAEB13DD59AD019E8E92BE7638A171ADF8341326DC17EA3416F642FBCE8C2D34C54E5ED47F2E3D077C6D57907ADC54ED150193D7E6E605617A5D7EC53B7B21C8BBFBDF256CA7F16A41DB28821432454EA4EA976C07342060C2EFC07354BE3D7601A6E4591C0EEAAC563348444DA15CEEABB6AAF40E81C7303F66DA734630AEEAE0C9CFF02FB5EFA03985ABB21CA7AE52CE504802D17B2409226B18C8F3E9D6582ADADD512D285C533C58905B2097FD8BAEA143BCB159A03CE483040C19444ECD9E7D9C3C58897D98461988CA296A29F8C452CC110D65536CA36079C74AED3D7647A223F249BBA395E586E1A5DA4C31F1AD682C635ADEDB906B767780DBFF3598DAF20940E7C50101D2EE3FB1C3D91E4B6D89859
)
GO

We need to clear the cache:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

With SSMS

From base – security – Always Encrypted Keys

We turn it on  secure enclave for greater security:

SELECT [name], [value], [value_in_use] FROM sys.configurations
WHERE [name] ='column encryption enclave type';

EXEC sys.sp_configure 'column encryption enclave type', 1;
GO

RECONFIGURE;

We create a table with a deterministic and a randomized encrypted field:

With T-SQL

CREATE TABLE [dbo].[customer](
       [ID] [int] IDENTITY(1,1) NOT NULL,
       [name] [nvarchar](50) NULL,
       [surname] [nvarchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
       [sex] [nvarchar](20) NULL,
       [afm] [nvarchar](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
)

*In case the table already contains records, we should use an SSIS package to export and re-import the records, otherwise with the wizard in SSMS (right click on the encrypt columns base)

With SSMS

Right click on the encrypt columns database, it opens the wizard with which we can automate the whole process without the T-SQL steps:

Passing two same records from my app:

We notice that although we put the same price twice, the A.F.M is a different reason randomized  while the surname that is deterministic it's the same.

To see the real values from SSMS, we have to activate it before connecting enable always encrypt:

We run the query again:

How to insert into an encrypted table

DECLARE @surname nvarchar(50) = 'Nikolaou';
DECLARE @afm nvarchar(20) = '141280950';

INSERT INTO customer values('Georgios',@surname,'Male',@afm)

select * from customer

How to update an encrypted table

DECLARE @customer_S nvarchar(50) = 'Nikolaou';
DECLARE @customer_N nvarchar(50) = 'Papandreou';

UPDATE [dbo].customer SET surname=@customer_N
WHERE surname = @customer_S

select * from customer

How to select in an encrypted table (we don't have the possibility to like)

DECLARE @customer_S nvarchar(50) = 'Matzouranis';

SELECT * FROM [dbo].customer
WHERE surname = @customer_S

How to connect the database that uses always encrypt with a Python application

We load the libraries:

import pyodbc,time,easygui

We connect with the odbc we have created with the parameter Column Encryption Setting=Enabled :

conn = pyodbc.connect("DSN=sql_encrypt;"
                      "UID=user;"
                      "PWD=password;"
                      "Column Encryption Setting=Enabled;"
                      )

We create with the easygui library a form with the fields:

msg  = 'Insert Value'
title = 'Insert Value'

fieldNames = ['name','surname','sex','afm']
fieldValues = []
fieldValues = easygui.multenterbox(msg,title,fieldNames)

name,surname,sex,afm=fieldValues

Open the cursor on the connection and put ? in the insert parameters:

cursor = conn.cursor()

statement="""INSERT INTO customer values(?,?,?,?)"""

We call all parameters as dynamic query:

cursor.execute(statement,name,surname,sex,afm)

conn.commit();

cursor.execute('select * from customer')

print(cursor.fetchall())

conn.commit();
cursor.close();
conn.close();

exit();

In closing, we should not forget that when we do not want to decrypt fields we also need certificates, there is a good guide on how to backup them  here.

Sources:

• Microsoft Always Encrypted