Enable Always Encrypted for Your MSSQL Databases

By Feng Yi.

Nowadays, with the ubiquity of cloud computing, having a wide application across several major industries. It’s important that we are able to find a way to ensure that the data stored in the cloud is continually and constantly encrypted. Moreover, it’s also important that data is kept private, and even the cloud service providers cannot see the plaintext data stored in the database, thus guarantying the absolute security and privacy of customer data in the cloud database. Well, one way to go about achieving both of these things is through the Always Encrypted feature introduced in Microsoft’s SQL Server 2016.

Always Encrypted in SQL Server functions to keep data constantly and continually encrypted. Only applications that call SQL Server can read/write and/or operate encrypted data. Always Encrypted can prevent your database or OS administrators from accessing the sensitive data of customer applications. Always Encrypted in SQL Server 2016 controls client applications by verifying encryption keys, which are not transmitted over the network to a remote SQL Server. Therefore, Always Encrypted provides the maximum level of customer data security for cloud databases. With Always Encrypted on and running, even your cloud service providers cannot obtain the plaintext of customer data.

Procedure

You can follow the steps below to enable the Always Encrypted feature.

Create a Test Database

For this tutorial, to keep things simple, let’s create a test database called AlwaysEncrypted first for demonstration purposes. You can do so with the following code script:

Create a Column Master Key

Next, you’ll want to create a column master key (CMK) in the AlwaysEncrypted database.

Create a Column Encryption Key

Next, create a column encryption key (CEK).

Check the CMK and CEK

Check the column master key (CMK) and column encryption key (CEK) that we have just created:

As shown in the following screenshot, everything is normal.

Of course, you can also use the SSMS IDE to check the CMK and CEK.

To do that, expand the database to be checked > Security > Always Encrypted Keys > Column Master Keys and Column Encryption Keys. See the following figure for reference.

Create an Always Encrypted Test Table

Follow the provided code to create an Always Encrypted test table:

When creating the Always Encrypted test table, specify the following parameters for the encryption field:

  • Encryption type: DETERMINISTIC and RANDOMIZED
  • Algorithm: AEAD_AES_256_CBC_HMAC_SHA_256 (a special algorithm for Always Encrypted)
  • Encryption key: The name of the created encryption key

Export the Server Certificate

Export the server-side certificate as a file:

Control Panel > Internet Options > Content > Certificates > Export¡­. See the following figure for reference.

Enter a password to protect your private key in the Certificate Export Wizard.

Select a storage path.

The certificate is finally exported.

Application-side Testing

After configuring the SQL Server on the server side, import the certificate for the application to be tested and start to test the application.

Import the Certificate on Client

  • The steps to import the certificate on client are similar to exporting the server-side certificate are: Control Panel > Internet Options > Content > Certificates -> Import¡­. See the following figure.

Then, enter the encryption password for the private key file. The file should be successfully exported now.

Test the Application

We can use Visual Studio to create a C# Console Application as the test Application and use the NuGet Package feature to install Dapper as the tool for performing SQL Server database operations.

Note: The SQL Server driver for Always Encrypted is supported only in .NET Framework 4.6 or later, so you’ll need to make sure that the Target framework of your project is at least .NET Framework 4.6 by clicking your project > Properties > Application. If the Target framework shows a .NET Framework version older than for 4.6, change it to .NET Framework 4.6 or later.

Next, in this tutorial, for the sake of simplicity and convenience, we will test the application directly on the server side of the SQL Server. Therefore, the connection string that you will see will connect to the local SQL Server service. If you want to test a remote SQL Server, modify the connection string. The complete test application code is as follows:

Next, to support the Always Encrypted feature in SQL Server 2016, simply add the property configuration Column Encryption Setting = Enabled; to the application code. To make this clear, I put this property configuration at the beginning of the connection string:

Then, after doing this, run the test application, and the following result will be returned:

The application test result shows that the Always Encrypted test table can be read and written normally and that the test application works well. But the question remains: What if we use another method to read/write data from/to the test table instead of using the application?

Test SSMS

Suppose that we use Microsoft’s SQL Server Management Studio (SSMS) as the test tool, for example. Then, first, look over the data in the Always Encrypted test table:

The result would be as follows:

Then, you can use SSMS to insert data directly into the test table:

The following error is reported:

Now consider the following screenshot.

This indicates that we cannot use any method other than a test application to read and operate the plaintext data in an Always Encrypted table.

Analyze the Test Results

After comparing the test results of using the application and using SSMS for direct access to the Always Encrypted test table, we know that the former method allows normal table writes/reads. The latter cannot read the plaintext data in the test table and can only be used to view the ciphertext data after the test table is encrypted. When the latter is used, a write operation fails with an error.

Source Code of the Test Application

To obtain the source code of the test application used in this article, click here to download.

Summary

In this tutorial, you have seen the principle behind and implementation of the new feature in SQL Server 2016 — Always Encrypted. This feature ensures that the data stored in databases on the cloud is always encrypted and that even cloud service providers cannot see the plaintext data in databases. Therefore, Always Encrypted ensures absolute security of customer data in cloud databases and alleviates suspicion towards the credibility of cloud service providers, which is a critical issue in cloud database scenarios.

Original Source

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store