Always Encrypted is a new security feature which was introduced in SQL Server 2016. Always Encrypted is a technology to ensure the data stored in a database remains encrypted at all times during SQL Server query processing. Always Encrypted allows clients to encrypt sensitive data, such as credit card numbers and national identification numbers, inside the client application and never reveal the encryption key to the database engine.
As a result, Always Encrypted provides separation between those who own the data and those who manage it. Even DBAs, system admins, and cloud admins cannot access the data. Always Encrypted is a client side encryption and makes encryption transparent to the application. Data is transparently encrypted inside a client driver, and the client manages the encryption key. The key can be stored in either the Windows certificate store or Azure Key Vault.
Always Encrypted supports two types of encryption: randomized and deterministic. Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption generates a different encrypted value for the same plain text each time. Randomized encryption is more secure, but prevents equality searches, grouping, indexing, and joins on encrypted columns. Deterministic Encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows equality searches, grouping, filtering by equality, and joining tables based on the encrypted value. However, it also may enable unauthorized users to guess information about encrypted values by using patterns in the encrypted column
With Always Encrypted, the client application handles the actual data encryption and decryption outside of SQL Server. In order to encrypt and decrypt the data application must use an Always Encrypted Enabled driver that interfaces with SQL Server.
In order to implement Always Encrypted on a column, we need to generate a Column Encryption Key (CEK) and a Column Master Key (CMK). The CEK is used to protect/encrypt the column data, while the CMK is used to store and protect/encrypt the CEK. The CEK is stored in the SQL Server database engine. For the Column Master Key, the database engine stores only metadata that points to the key location. The actual master key is stored in a trusted external key store.
How Always Encrypted Works
Always Encrypted is a client side encryption technology in which the SQL Server client driver does the work. Basically, we move out encryption / decryption outside of SQL server, moving the keys as well as encryption/decryption of data to the application level. Always Encrypted relies on a client side application to automatically encrypt and decrypt sensitive data.
This involves a special driver that encrypts the data in sensitive columns before passing it to the Database Engine and automatically rewrites queries so that the semantics are preserved. When the client application retrieves the encrypted data from the database, the same driver transparently decrypted returning plain text to the client application. Consequently SQL Server never sees the sensitive information in plain text. The server does not have access to the key, either. The keys are managed entirely on the client side.
SQL Server 2016 Always Encrypted Setup
Here are the steps to encrypting data in a table. First, connect to your database using SSMS. Next, right click on the targeted table, which contains sensitive data, and select Encrypt Columns. In this scenario, we are encrypting columns for table 'tblEmployee'.
This action starts Always Encrypted wizard which will guide you through the process of encrypting table column.
Once you click Next button, you will proceed to the column selection step. In this scenario, we are selecting the NationalID, Birthdate, and HireDate columns to be encrypted.
Once you click Next, the wizard moves on to the Master Key configuration step
Our option here includes a decision on whether we want to auto-generate the CMK or use an existing one. We also need to decide where we going to store the CMK, either locally in the Windows Certificate Store or in Azure Key Vault. For this demo, I’m selecting the Windows Certificate Store for the Local Machine.
Once we click Next, we are presented with the Run Settings dialogue. We either can execute our configuration steps immediately or generate a PowerShell script to run later.
On the Summary page we just reviewing the selections. Click Finish
As we selected the Windows certificate store for our certificate, we can open the Certificates snap-in (certmgr.msc) for the local computer and find our Always Encrypted Auto Certificate in Personal certificates, as shown here.
We can find the Column Encryption Keys and Column Master Keys under Database - Security - Always Encrypted Keys
Now, execute a SELECT statement to list the data from the tblEmployee table.
All the values are encrypted for the columns we selected. We have all the prerequisites in place and the certificate, so we are supposed to see the data just fine. In order to do this, you need to specify special a connection string parameter. For SSMS, you just need to open the connection dialog and click on the Options button.
Choose the Additional Connection Parameters tab. Here you enter the "Column Encryption Setting = Enabled" string.
After you type the setting, click Connect, and the re-run the select statement. Your result should now look similar to the figure below.
Configuring the Client Application for Always Encrypted
To configure the encryption in the client application, you need to add additional connection setting in the application .config file. Here is an example that is used in a connection string setting:
<add name="DefaultConnection" connectionString="Data Source=.;Initial Catalog=Clinic;User
ID={Application Name};Password={Some Strong Password};Column Encryption Setting=Enabled" providerName="System.Data.SqlClient" />
For more details, see this link: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017
