SQL Server: Always Encrypted

SQL Server 2016+ includes a database security feature called Always Encrypted. As we've added Always Encrypted support to the SQL Server ODBC driver, our customers will be able to take advantage of this feature.

Always Encrypted protects SQL Server data at the point at which it is most susceptible to attack: when that data is being used. For example, during transactions and computations. This differs from existing SQL Server encryption features as they require data to be decrypted before operations can be performed on it.

The encryption key that protects Always Encrypted columns is stored on the application machine. This means that SQL Server cannot decrypt the Always Encrypted data. If the SQL Server machine is compromised, the attacker will only be able to access Always Encrypted data in cipher form.

For most users, the Always Encrypted feature will be transparent: they are insulated from the workings of Always Encrypted and do not have to change what they are doing to benefit from the feature.

At the application end, the encryption is done by the software driver that provides the client interface for SQL Server. On Linux and UNIX, this is an ODBC driver, which transparently encrypts or decrypts data depending on the direction of travel. In the case of the Easysoft driver, Always Encrypted is turned on by setting a connection string parameter.

As people are increasingly concerned that their data is safe in the cloud, Always Encrypted will be available in Azure SQL, the cloud-based pay-as-you-go version of SQL Server. Easysoft's ODBC driver for Azure SQL will also support Always Encrypted therefore.

Walkthrough: Working with Always Encrypted column data on Linux

Easysoft's SQL Server ODBC driver enables you to update and query data held in Always Encrypted columns.

Create the table and generate the encryption keys

These steps are done on the SQL Server machine.

  1. In SQL Server Management Studio 2016 CTP3 or later, create a new database.
  2. In the new database, create a table that contains one or more columns whose contents you want to encrypt. For example: 
    CREATE TABLE dbo.EncryptedTable
(
  ID INT IDENTITY(1,1) PRIMARY KEY, LastName NVARCHAR(32), Salary INT NOT NULL
);
  3. Right-click the database. From the pop-up menu, choose Tasks > Encrypt Columns.

    The Always Encrypted Wizard starts.

  4. On the Column Selection page, expand the tables, and select the columns that you want to encrypt.
  5. Choose an encryption type for each column.

    Deterministic always encrypts to the same cipher text, allowing equality lookups, joins, and group by to be performed.

    Randomized generates a different cipher text value for the same plain text, which is more secure, but does not support any operations.

  6. Choose CEK_Auto1 (New) as the encryption key for each column, which is a new auto-generated key. Choose Next. Always Encrypted Column Selection Wizard
  7. In the Master Key Configuration page, accept the default settings:
    Field Value
    Select column master key Auto generate column master key
    Select the key store provider Windows certificate store
    Select column master key Current User
  8. Use the Next button to proceed to the Summary page. Choose Finish.
  9. Wait for the wizard to complete and then choose Close.

Exporting the certificates

To transfer the certificates to the Linux machine, you first need to export them on Windows.

  1. In a command prompt window, enter certmgr, to launch the Certificates snap-in.
  2. The new Always Encrypted certificate will be available under Certificates - Current User > Personal > Certificates.
  3. Right-click the certificate (which will be called something like Always Encrypted Auto Certificate1). From the pop-up menu, choose All Tasks > Export.

    The Certificate Export Wizard starts. Choose Next.

  4. Choose Yes, export the private key.
  5. Accept the defaults in the Export File Format page. Choose Next.
  6. Supply a password when prompted. Choose Next.
  7. Name and save the certificate when prompted. For example, CMK_Auto1.pfx.
  8. Use the Next and Finish buttons to complete the wizard.

Installing the certificates on Linux

Transfer the exported certificates to the Linux machine from which you want to access the Always Encrypted columns:

  1. Copy the certificate you have just exported to ~/ssl/private on the Linux or UNIX machine where you have installed the SQL Server ODBC driver.

    ~ is the home directory of the user who will run the application that connects to SQL Server through the Easysoft ODBC driver. ~/ssl/private is the location where the OpenSSL layer built into the driver will attempt to load a personal certificate from. Create the directory if it does not exist. For example:

    $ mkdir -p ~/ssl/private
$ cd ~/ssl/private
$ mv /tmp/CMK_Auto1.pfx .
  2. To use the certificate with the SQL Server ODBC driver, you need to remove the passphrase it contains. To do this, OpenSSL must be installed on the machine. (This is only necessary to remove the passphrase, for other operations, the SQL Server ODBC driver uses a built-in OpenSSL layer.) Remove the passphrase with the following commands. When prompted for the passphrase after the second command, press RETURN without entering anything. This will set the passphrase to nothing. 
    $ openssl pkcs12 -in CMK_Auto1.pfx -nodes -out temp.pem
Enter Import Password: *******
MAC verified OK
$ openssl pkcs12 -export -in temp.pem  -out nopassphrase.p12
Enter Export Password:
Verifying - Enter Export Password:
$
  3. To load the certificate, the SQL Server ODBC driver uses meta information it receives from SQL Server about the encrypted column. The certificate name the driver receives from SQL Server is in the form my/thumbprint. You need to use this naming convention for the certificate. Use OpenSSL to display the certificate's thumbprint and then rename the certificate in a subdirectory named my: 
    $ openssl x509 -in temp.pem -fingerprint -noout | tr -d ":"
SHA1 Fingerprint=EFC1940E545941D6C05C763361403F55A5DEF0E8
$ mkdir my
$ cp nopassphrase.p12 my/EFC1940E545941D6C05C763361403F55A5DEF0E8
$ ln -s my My

    Note During testing, we noticed that SQL Server sometimes named the certificate My/thumbprint. The symbolic link in the above example works around this inconsistency.

Installing the SQL Server ODBC driver

The SQL Server ODBC driver not only provides the connectivity layer between the application and SQL Server, it also handles the encryption and decryption of data stored in Always Encrypted columns.

Install and license the SQL Server ODBC driver. For instructions on how to do this, refer to the SQL Server ODBC driver documentation. If your application is 64-bit, download the 64-bit version of the ODBC driver. Otherwise, use the 32-bit version of the driver, regardless of the architecture of the operating system.

An ODBC data source contains the connection string information that enables the SQL Server ODBC driver to connect to the target SQL Server instance. On our machine, ODBC data sources are stored in /etc/odbc.ini. This data source extract shows the relevant settings for Always Encrypted columns:

[SQLSERVER_2016]
Driver=Easysoft ODBC-SQL Server SSL # Must use SSL version of driver
Server=machine\sqlserver_instance
Database=database_with_always_encrypted_data
User=user # This can be a Windows or SQL Server login.
Password=password
Trusted_Connection=Yes # Set this to No for a SQL Server login
ColumnEncryption=Enabled # To view Always Encrypted data or to
                         # insert into an Always Encrypted column set to Enabled

Note If your connection fails with the error "SSL connection failed in syscall," your system lacks a "randomness device." Refer to the Entropy attribute in the SQL Server ODBC driver manual for information about what to do about this.

Inserting data into an Always Encrypted column

We have now created an empty table with Always Encrypted columns and set up our Linux client machine so that the SQL Server ODBC driver can work with Always Encrypted data. Next, we need to populate the table with data.

To insert data into an Always Encrypted column, an application must:

  1. Use a parameterised insert (INSERT INTO EncryptedTable VALUES (?, ?)).

    This enables the SQL Server ODBC driver to differentiate between the column values (which it needs to encrypt) and the SQL statement text (which must remain in plain text; with Always Encrypted, remember, SQL Server does not do any decryption).

  2. Explicitly describe the data type of the parameters.

    SQL Server does not supply the necessary information about an Always Encrypted column for SQL Server ODBC driver to discover the data type by using SQLDescribeParam.

Here is a Perl sample that shows how to do this:

# Use Perl DBI / DBD:ODBC to insert data into Always Encrypted columns.
use strict;
use warnings;
use DBI;

my $data_source = q/dbi:ODBC:SQLSERVER_2016/;

my $h = DBI->connect($data_source)
    or die "Can't connect to $data_source: $DBI::errstr";
$h->{RaiseError} = 1;

my $s = $h->prepare(q/insert into EncryptedTable values(?,?)/);
my $lastname='Smith';
my $salary=25000;

# Set the data type of the target columns.
# Cannot use SQLDescribeParam with Always Encrypted columns.
$s->bind_param(1, $lastname, DBI::SQL_WVARCHAR);
$s->bind_param(2, $salary, DBI::SQL_INTEGER);
$s->execute($lastname,$salary);
$h->disconnect;

Here is a C sample that shows how to do this:

#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlucode.h>
#include <sqlext.h>
#include <string.h>
#include <wchar.h>

#define LASTNAME_LEN 6

SQLHENV   henv  = SQL_NULL_HENV;   // Environment
SQLHDBC   hdbc  = SQL_NULL_HDBC;   // Connection handle
SQLHSTMT  hstmt = SQL_NULL_HSTMT;  // Statement handle
SQLRETURN retcode;

SQLCHAR strLastName[]="Jones";
SQLINTEGER pSalary=25000;

SQLLEN lenLastName=0;

int main () {
    // Allocate environment
    retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

    // Set ODBC Version
    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                            (SQLPOINTER*)SQL_OV_ODBC3, 0);

    // Allocate Connection
    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

    // Connect to DSN
    retcode = SQLConnect(hdbc, (SQLCHAR*) "MyDSN", SQL_NTS,
                         (SQLCHAR*) NULL, 0, NULL, 0);

    // Allocate Statement Handle
    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

    // Bind Parameters to all fields
    retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
                               SQL_WVARCHAR, LASTNAME_LEN, 0, strLastName, LASTNAME_LEN,
                    &lenLastName);

    retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG,
                               SQL_INTEGER, 0, 0, &pSalary, 0, NULL);

    retcode = SQLPrepare(hstmt, (SQLCHAR*)"INSERT INTO [dbo].[EncryptedTable] ([LastName],[Salary]) VALUES (?,?)",
                               SQL_NTS);

    lenLastName=strlen((char*)strLastName);

    retcode = SQLExecute(hstmt);

exit:

    printf ("\nComplete.\n");

    // Free handles
    // Statement

    if (hstmt != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

    // Connection
    if (hdbc != SQL_NULL_HDBC) {
        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }

    // Environment
    if (henv != SQL_NULL_HENV)
        SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return 0;
}

Now the columns are populated, we can use isql to retrieve the data:

$ /usr/local/easysoft/unixODBC/bin/isql.sh -v SQLSERVER_2016
SQL> select * from EncryptedTable
+----+----------+------------+
| ID | LastName | Salary     |
+----+----------+------------+
| 1  | Smith    | 25000      |
+----+----------+------------+

We had driver logging turned on in our data source. The following extract from the driver log shows that:

  1. SQL Server supplies the certificate name as meta information about the column.
  2. The column data is encrypted at the SQL Server end and therefore remains encrypted in transit. The SQL Server ODBC driver on the client decrypts the column values and then returns them in plain text to the application.
1.
M.S.S.Q.L._.C.E.
R.T.I.F.I.C.A.T.
E._.S.T.O.R.E.7.
C.u.r.r.e.n.t.U.
s.e.r./.m.y./.7.
2.8.8.1.8.C.5.F.
B.2.C.6.E.B.F.C.
2.5.3.D.B.C.1.2.
2.8.5.B.6.A.D.9.
4.8.9.0.8.3.E..R
.S.A._.O.A.E.P..
.....8.I.D......
...........@....
......L.a.s.t.N.
a.m.e........Q..
.....8....S.a.l.
a.r.y...........

2.
PKTDUMP:         Encrypted column
.);...'A..zs..I.
.N.]r..p.-..$...
.S;.].km6.....3c
r.OhR..j*.....fj
....ARN{V.F.....

DETAIL:  EVP_DecryptInit returns 1
DETAIL:  EVP_DecryptUpdate returns 1, 0
DETAIL:  EVP_DecryptUpdate returns 1, 16
DETAIL:  EVP_DecryptFinal returns 1, 0
PKTDUMP:         data

S.m.i.t.h.

Further information