{"id":1159,"date":"2020-10-06T07:00:00","date_gmt":"2020-10-06T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1159"},"modified":"2025-06-12T18:10:37","modified_gmt":"2025-06-12T15:10:37","slug":"always-encrypted-gia-prostasia-eyaisthiton-dedomeno","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/always-encrypted-gia-prostasia-eyaisthiton-dedomeno\/","title":{"rendered":"What is Always Encrypted and how it is applied to protect sensitive data"},"content":{"rendered":"

In his time GDPR <\/strong>let's talk about another feature of SQL Server to protect sensitive data<\/strong>. It's nothing more than that Always Encrypted<\/strong>!<\/p>\n\n\n\n

What is Always Encrypted<\/h4>\n\n\n\n

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.<\/p>\n\n\n\n

Let's see how it works<\/h4>\n\n\n\n

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

Encryption is divided into two categories<\/h4>\n\n\n\n

Deterministic<\/strong>: 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<\/strong>, joins <\/strong>and pivoting<\/strong>, 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.<\/p>\n\n\n\n

Randomized: <\/strong>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.<\/p>\n\n\n\n

The example<\/h2>\n\n\n\n

<\/p>\n\n\n\n

First we make one  column encryption key<\/strong> and one master encryption key<\/strong>.<\/p>\n\n\n\n

This can be done with T-SQL and with the SSMS GUI.<\/p>\n\n\n\n

Using T-SQL<\/h5>\n\n\n\n
USE [bi_test]\nGO\n\nCREATE COLUMN MASTER KEY [CMK_Auto1]\nWITH\n(\n       KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',\n       KEY_PATH = N\u2019CurrentUser\/my\/44866B5D9463FDAB3BBF961C2BBE78E01CCD5C35\u2032\n)\nGO\n\nCREATE COLUMN ENCRYPTION KEY [CEK_Auto1]\nWITH VALUES\n(\n       COLUMN_MASTER_KEY = [CMK_Auto1],\n       ALGORITHM = 'RSA_OAEP',\n       ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F003400340038003600360062003500640039003400360033006600640061006200330062006200660039003600310063003200620062006500370038006500300031006300630064003500630033003500555BAFF9D24FD8C7D15AD1E8850AA95921ABFD8213E80ED87D630EB3AD01AE60E758689FDC8F18ABCBC50AF48F927FBB22CBED7CFF871FE0579AB9B8F8A128AB5129AF5CA693C05AECE025EF02A344D36C4BD50AA665433D1F9C97B8FA605A79F4A54A0065A8252D78619A6D467547358033F31078326A75F8626FC7306611673B043A6E4D3AA99547E9B2A06967ABC488656229C19045A8145445A84338DDA807B40444E41093AA6BB0B5D29B16E9787EAD652042FFD73B264483CEE592B370233C51523BC843434D6201C5F3D51C5BE50B9EE03AE9A2C888C79FC4C5A09143625F55DD641359A14C21DD627FAEB13DD59AD019E8E92BE7638A171ADF8341326DC17EA3416F642FBCE8C2D34C54E5ED47F2E3D077C6D57907ADC54ED150193D7E6E605617A5D7EC53B7B21C8BBFBDF256CA7F16A41DB28821432454EA4EA976C07342060C2EFC07354BE3D7601A6E4591C0EEAAC563348444DA15CEEABB6AAF40E81C7303F66DA734630AEEAE0C9CFF02FB5EFA03985ABB21CA7AE52CE504802D17B2409226B18C8F3E9D6582ADADD512D285C533C58905B2097FD8BAEA143BCB159A03CE483040C19444ECD9E7D9C3C58897D98461988CA296A29F8C452CC110D65536CA36079C74AED3D7647A223F249BBA395E586E1A5DA4C31F1AD682C635ADEDB906B767780DBFF3598DAF20940E7C50101D2EE3FB1C3D91E4B6D89859\n)\nGO<\/pre>\n\n\n\n
We need to clear the cache:<\/p>\n\n\n\n
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;<\/pre>\n\n\n\n
With SSMS<\/h5>\n\n\n\n
From base \u2013 security \u2013 Always Encrypted Keys<\/p>\n\n\n\n
\"\"
1<\/figcaption><\/figure>\n\n\n\n
We turn it on  secure enclave<\/strong> for greater security:<\/p>\n\n\n\n
SELECT [name], [value], [value_in_use] FROM sys.configurations\nWHERE [name] ='column encryption enclave type';\n\nEXEC sys.sp_configure 'column encryption enclave type', 1;\nGO\n\nRECONFIGURE;<\/pre>\n\n\n\n
We create a table with a deterministic and a randomized encrypted field:<\/p>\n\n\n\n
With T-SQL<\/h5>\n\n\n\n
CREATE TABLE [dbo].[customer](\n       [ID] [int] IDENTITY(1,1) NOT NULL,\n       [name] [nvarchar](50) NULL,\n       [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,\n       [sex] [nvarchar](20) NULL,\n       [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,\n)<\/pre>\n\n\n\n
*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)<\/em><\/p>\n\n\n\n
With SSMS<\/h5>\n\n\n\n
Right click on the encrypt columns database, it opens the wizard with which we can automate the whole process without the T-SQL steps:<\/p>\n\n\n\n
\"\"
2<\/figcaption><\/figure>\n\n\n\n
Passing two same records from my app:<\/p>\n\n\n\n
\"\"
3<\/figcaption><\/figure>\n\n\n\n
We notice that although we put the same price twice, the A.F.M is a different reason randomized  <\/strong>while the surname that is deterministic <\/strong>it's the same.<\/p>\n\n\n\n
\"\"
4<\/figcaption><\/figure>\n\n\n\n
To see the real values from SSMS, we have to activate it before connecting enable always encrypt<\/strong>:<\/p>\n\n\n\n
\"\"
5<\/figcaption><\/figure>\n\n\n\n
\"\"
6<\/figcaption><\/figure>\n\n\n\n
We run the query again:<\/p>\n\n\n\n
\"\"
7<\/figcaption><\/figure>\n\n\n\n
How to insert into an encrypted table<\/h5>\n\n\n\n
DECLARE @surname nvarchar(50) = 'Nikolaou';\nDECLARE @afm nvarchar(20) = '141280950';\n\nINSERT INTO customer values('Georgios',@surname,'Male',@afm)\n\nselect * from customer<\/pre>\n\n\n\n
\"\"
8<\/figcaption><\/figure>\n\n\n\n
How to update an encrypted table<\/h5>\n\n\n\n
DECLARE @customer_S nvarchar(50) = 'Nikolaou';\nDECLARE @customer_N nvarchar(50) = 'Papandreou';\n\nUPDATE [dbo].customer SET surname=@customer_N\nWHERE surname = @customer_S\n\nselect * from customer<\/pre>\n\n\n\n
\"\"
9<\/figcaption><\/figure>\n\n\n\n
How to select in an encrypted table (we don't have the possibility to like)<\/h5>\n\n\n\n
DECLARE @customer_S nvarchar(50) = 'Matzouranis';\n\nSELECT * FROM [dbo].customer\nWHERE surname = @customer_S<\/pre>\n\n\n\n
\"\"
10<\/figcaption><\/figure>\n\n\n\n
How to connect the database that uses always encrypt with a Python application<\/h3>\n\n\n\n
<\/p>\n\n\n\n
We load the libraries:<\/p>\n\n\n\n
import pyodbc,time,easygui<\/pre>\n\n\n\n
We connect with the odbc we have created with the parameter Column Encryption Setting=Enabled :<\/p>\n\n\n\n
conn = pyodbc.connect(\"DSN=sql_encrypt;\"\n                      \"UID=user;\"\n                      \"PWD=password;\"\n                      \"Column Encryption Setting=Enabled;\"\n                      )<\/pre>\n\n\n\n
We create with the easygui library a form with the fields:<\/p>\n\n\n\n
msg  = 'Insert Value'\ntitle = 'Insert Value'\n\nfieldNames = ['name','surname','sex','afm']\nfieldValues = []\nfieldValues = easygui.multenterbox(msg,title,fieldNames)\n\nname,surname,sex,afm=fieldValues<\/pre>\n\n\n\n
Open the cursor on the connection and put ?<\/strong> in the insert parameters:<\/p>\n\n\n\n
cursor = conn.cursor()\n\nstatement=\"\"\"INSERT INTO customer values(?,?,?,?)\"\"\"<\/pre>\n\n\n\n
We call all parameters as dynamic query:<\/p>\n\n\n\n
cursor.execute(statement,name,surname,sex,afm)\n\nconn.commit();\n\ncursor.execute('select * from customer')\n\nprint(cursor.fetchall())\n\nconn.commit();\ncursor.close();\nconn.close();\n\nexit();<\/pre>\n\n\n\n
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<\/a>.<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n