Using Data Protected with an Azure Key Vault from Linux

Microsoft has enhanced its Always Encrypted security feature by enabling the keys that secure the data to be stored in an Azure Key Vault.

The SQL Server ODBC driver has supported Always Encrypted columns since Microsoft initially introduced the feature in SQL Server 2016. Starting with build 1.10.4 of the driver, we have added the necessary connection string settings to support the Always Encrypted data / Azure Key Vault scenario.

The process for working with Azure secured Always Encrypted columns from Linux and UNIX is:

  1. Create an Azure Key Vault in Windows Azure.
  2. Create a new App registration.

    During this process, you will generate an Application client ID and secret. You will need these values later in this tutorial when you configure your SQL Server ODBC driver data source.

  3. In SQL Server Management Studio, create a test table in a SQL Server 2016 (or later) instance: 
    CREATE TABLE dbo.EncryptedTable
(
  ID INT IDENTITY(1,1) PRIMARY KEY, LastName NVARCHAR(32), Salary INT NOT NULL
);
  4. Right-click the table. From the pop-up menu, choose Encrypt Columns.

    The Always Encrypted Wizard starts.

  5. On the Column Selection page, expand the tables, and select the columns that you want to encrypt.
  6. 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.

  7. Choose CEK_Auto1 (New) as the encryption key for each column, which is a new auto-generated key. Choose Next.
  8. Choose Azure Key Vault, and then sign into your Azure account when prompted.
  9. Choose your Azure Key Vault from the list. Choose Next.
  10. Choose Next.
  11. Choose Finish.
  12. Choose Close.
  13. On your Linux or UNIX machine, install version 1.10.4+ of the SQL Server ODBC driver.
  14. Configure an ODBC data source in /etc/odbc.ini that connects to your SQL Server 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
KeyStoreAuthentication=KeyVaultClientSecret
KeyStorePrincipalId=ac9e3f9b-ed5e-4f11-9746-f1d25cc5867c   # Your application client ID
KeyStoreSecret=wQOFhXY/1yIEUK75j8m/o4IUU7tf3AnVRc3ybO6b3VA # and application secret generated when registering
                                                           # an App in your Azure portal.
  15. Test that you can display the unencrypted data: 
    /usr/local/easysoft/unixODBC/bin/isql.sh -v SQLSERVER_2016
SQL>  select * from EncryptedTable
  16. If you want to insert data into an Always Encrypted column, you need to use a parameterised INSERT, and our other Always Encrypted blog provides some examples that show how to do this.