Using Data Protected with a Custom Key Store from Linux

The process for working with custom key store secured Always Encrypted columns from Linux is:

  1. Install the SQL Server ODBC driver 1.10.5+ on your Linux Machine.
  2. Configure an ODBC data source in /etc/odbc.ini that connects to a SQL Server 2016+ instance: 
    [SQLSERVER_2016]
Driver=Easysoft ODBC-SQL Server SSL
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
  3. On this Linux machine, create two new files: 
    $ cd ~/Documents
$ touch MyKSP.c KspApp.c
  4. Copy and paste the code for the example Keystore Provider into MyKSP.c.
  5. Copy and paste the code for the example ODBC application into KspApp.c.
  6. Open MyKSP.c and KspApp.c in a text editor. In both files, replace this line: 
    #include "msodbcsql.h"

    with:

    #include <sqlncli.h>
    32-bit Note To use a 32-bit version of sample ODBC application, we had to change the code in KspApp.c:
    1. We added this function: 
      {
    wchar_t c1, c2;

    do {
        c1 = *s1++;
        c2 = *s2++;
        if (c1 == '\0')
            return c1 - c2;
        }
    while (c1 == c2);
    return c1 - c2;
}

      immediately after this line:

      static int safe_wcscmp( wchar_t *s1, wchar_t *s2 )
    2. We replaced calls to wcscmp with safe_wcscmp.
  7. Compile the code and set the execute permission on the resultant library and application. For example: 
    $ gcc -I/usr/local/easysoft/unixODBC/include -I/usr/local/easysoft/sqlserver/include \
	                             -fshort-wchar -fPIC -o MyKSP.so -shared MyKSP.c
$ gcc -I/usr/local/easysoft/unixODBC/include -I/usr/local/easysoft/sqlserver/include \
                                     -fshort-wchar -fPIC -o KspApp -fshort-wchar \
	                             KspApp.c -lodbc -L/usr/local/easysoft/unixODBC/lib/ \
            	                     -L/usr/lib/x86_64-linux-gnu/libdl.so -ldl
$ chmod +x MyKSP.so KspApp
  8. Run the application (KspApp), which uses the custom key store (MyKSP.so) to create and populate a table with AlwaysEncrypted columns, retrieves the unencrypted data and drops the table: 
    $ ./KspApp DSN=SQLSERVER_2016
Press Enter to continue...

KSP Decrypt() function called (keypath=Retrieved data: c1=1 c2=Sample data 0 for column 2
Retrieved data: c1=12 c2=Sample data 1 for column 2
  9. Prior to cleaning up the sample data, we used isql to confirm that, for an application that does not have access to the custom key vault, the data is encrypted. We turned off ColumnEncryption for this application, because unless is done the SQL Server ODBC driver will attempt to decrypt the data with a local key store, which will not work: 
    /usr/local/easysoft/unixODBC/bin/isql -v -k "DRIVER={Easysoft ODBC-SQL Server SSL};Server=machine\sqlserver_instance;
                              UID=user;PWD=password;Database=database_with_always_encrypted_data;ColumnEncryption=No"
SQL> select top 1 c2 from CustomKSPTestTable
+----+
| c2 |
+----+
| 0104590D628739293CD8D455BD43EC59...