Tue, 30 Jan 2018
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:
- Create an Azure Key Vault in Windows Azure.
- 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 );
- Right-click the table. From the pop-up menu, choose Encrypt Columns.
The Always Encrypted Wizard starts.
- On the Column Selection page, expand the tables, and select the columns that you want to encrypt.
- 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.
CEK_Auto1 (New)as the encryption key for each column, which is a new auto-generated key. Choose Next.
- Choose Azure Key Vault, and then sign into your Azure account when prompted.
- Choose your Azure Key Vault from the list. Choose Next.
- Choose Next.
- Choose Finish.
- Choose Close.
- On your Linux or UNIX machine, install version 1.10.4+ of the SQL Server ODBC driver.
- Configure an ODBC data source in
/etc/odbc.inithat 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 # The SQL Server ODBC driver's application client ID KeyStoreSecret=wQOFhXY/1yIEUK75j8m/o4IUU7tf3AnVRc3ybO6b3VA # and application secret. When using an Azure Key Vault, # you must include these settings and use these values.
- Test that you can display the unencrypted data:
/usr/local/easysoft/unixODBC/bin/isql.sh -v SQLSERVER_2016 SQL> select * from EncryptedTable
- 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.