Using Always Encrypted with the Linux ODBC Driver

Download ODBC Driver

Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to SQL Server. An Always Encrypted enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the SQL Server client application. The driver encrypts the data in sensitive columns before passing the data to SQL Server, and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data stored in encrypted database columns that are contained in query results. For more information, see Always Encrypted (Database Engine).

Limitations of the Preview release

Developing client applications for accessing Always Encrypted data

Querying a database using Always Encrypted from a client application requires minimal development effort. The following summarizes the prerequisites and steps you must take for your application to read and write Always Encrypted data.

Prerequisites

• Configure the server by following the directions in the Getting Started with Always Encrypted section of the Always Encrypted (Database Engine) topic.

• Install Microsoft ODBC Driver 13 (Preview) for SQL Server on Linux on the client computer

Set up the client application

The client application must be configured to have access to a key store containing a Column Master Key (CMK) protecting the data the application is going to access. For information about master keys, see Always Encrypted (Database Engine). The ODBC driver supports storing CMKs in the local machine key stores: local user and local machine.

Using the local machine key store providers

The ODBC driver comes with a built in key store provider implementation for the local machine and local user Key Stores. Client applications should ensure that OpenSSL is installed and configured as recommended by their distribution. The creator/administrator of certificates (usually the DBA) will need to distribute the CMK to each client machine that will work with encrypted columns. Specifically you will need to install the Column Master Key in the location specified when you created it on SQL Server. On Linux Systems, the LOCALMACHINE location corresponds to the "/etc/ssl/private" directory, and the CURRENTUSER location corresponds to the path “~/ssl/private". You can read more about how to do this on the Always Encrypted (Database Engine) page.

Enabling Always Encrypted for a client connection

Modify the connection string in your client application by adding the ColumnEncryption=Enabled parameter to the connection string.

The following is an example of a connection string for the Microsoft ODBC Driver 13 for SQL Server that enables Always Encrypted:

SQLWCHAR *connString = L"DRIVER=ODBC Driver 13 for SQL Server;SERVER=.;ColumnEncryption=Enabled";   

Inserting/Retrieving data example

Insert data into encrypted columns by using SQLPrepare/SQLExecute or SQLExecDirect functions in conjunction with the SQLBindParam function. The application passes plaintext SQL with parameter markers in the function arguments, and binds buffers with plaintext values for all marked parameters. The driver then transparently encrypts these parameters and passes the encrypted values to the server. Similarly when encrypted columns are retrieved via BindCol or GetData functions, the driver transparently decrypts the values before sending the values to the cound buffers in the application. When a column is encrypted no plaintext data is available to the SQL Server; it only sends/receives encrypted data.

There is no difference in regular operation when using the ODBC driver with encrypted columns from a developer’s perspective. See the ODBC sample code for an example of how to interact with the driver.

Always Encrypted API reference

There is a modification to the ODBC driver API for use in client applications that use Always Encrypted.

SQLServerConnection Class

See Also

Microsoft ODBC Driver for SQL Server on Linux
Always Encrypted (Database Engine)