How to manage Always Encrypted columns from a Delphi application!

FireDac and ADO are your friends!

The previous article of this series of posts dedicated to the SQL Server Encryption features, describes how Always Encrypted works. This article describes how to manage encrypted columns from a Delphi application.

Always Encrypted is a client-side encryption technology that ensures sensitive data (and related encryption keys) are never revealed to the SQL Server instance or Azure SQL. With Always Encrypted, a client driver transparently encrypts sensitive data before passing the data to the Database Engine, and it transparently decrypts data retrieved from encrypted database columns.

The driver encrypts the data in sensitive columns before passing the data to the database Engine and automatically rewrites queries so that the semantics of the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.

When we execute the following query, SQL Server returns to the application encrypted values stored in the encrypted column. The client application must decrypt the values you want to see.

SELECT EncryptedColumn FROM dbo.TableAlwaysEncrypted;

When we execute this query, we are asking SQL Server to compare the non-encrypted value to the encrypted values found in the encrypted column.

SELECT EncryptedColumn FROM dbo.TableAlwaysEncrypted WHERE EncryptedColumn = 'non-encrypted value';

This query will fail with an error like this:

Msg 206, Level 16, State 2, Line 68
Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'AlwaysEncryptedDB') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

The client application must encrypt the value you want to use in the WHERE clause before sending it off to SQL Server.

A Delphi application that manages columns encrypted with Always Encrypted can use a FireDAC connection configured with an ODBC driver to encrypt/decrypt data before letting SQL Server or Azure SQL manage it. The easiest way to enable both parameter encryption and result set encrypted column decryption is by setting the value of the ColumnEncryption connection string keyword to Enabled. The connection string must contain the following settings:

Trusted_Connection=yes;ColumnEncryption=Enabled;

Always Encrypted may also be enabled in the DSN configuration, using the same key and value (which will be overridden by the connection string setting, if present) or programmatically with the SQL_COPT_SS_COLUMN_ENCRYPTION pre-connection attribute. Setting it this way overrides the value setted in the connection string or DSN.

The following picture shows an example configuration of an ODBC driver with Column Encryption enabled.

The following picture shows next configuration page in which we have enabled the “Trust server certificate” option and the “Connection encryption” option.

Let’s see what the application needs to INSERT, UPDATE, or DELETE encrypted data. If the application tries to execute a DML statement using local variables in comparison to encrypted columns, the DML command will fail with an error like this:

Encryption scheme mismatch for columns/variables '@value1'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '1' expects it to be DETERMINISTIC, or PLAIN TEXT.

The application cannot use local variables for Always Encrypted columns, they must come from client-side parameters. In SQL Server Management Studio, it works because SQL Server Management Studio parses your script and pulls out the variables into parameters but in Delphi or other clients you must parameterize it by yourself.

The following picture shows the hint of using local variables in a query on Always Encrypted columns from SSMS.

In a Delphi client application, you must parametrize queries by yourself as shown in the following piece of code.

Procedure.Update;
var
  LSQL: string;
begin

  LSQL :=
    'UPDATE ' +
    EncryptedTableName + ' ' +
    'SET ' +
    EncryptedColumnName + ' = :NonEncryptedValue ';

  if (ColumnNameWhere <> '') then
  begin
    LSQL := LSQL +
      'WHERE (' + ColumnNameWhere + ' = ''' + ColumnValueWhere + ''')';
  end;

  QryUpdateEncryptedData.Close;
  QryUpdateEncryptedData.SQL.Text := LSQL;
  QryUpdateEncryptedData.ParamByName('NonEncryptedValue').DataType := ftFixedChar;
  QryUpdateEncryptedData.ParamByName('NonEncryptedValue').Size := 11;
  QryUpdateEncryptedData.Prepare;

  QryUpdateEncryptedData.ParamByName('NonEncryptedValue').AsString := NonEncryptedValue;
  QryUpdateEncryptedData.ExecSQL;
end;

As you can see, the “Prepare” method of the FireDAC query named QryUpdateEncryptedData is used to parametrize the query. You cannot use either literals or SQL local variables to INSERT, UPDATE, or compare with Always Encrypted columns, as the server has no access to the decrypted data. The client driver needs to have access to the relevant certificate. ODBC driver does it for us. More details about using Always Encrypted with the ODBC Driver for SQL Server are available here.

Pay attention to the data type and the size of the parameters for Always Encrypted columns, they must reflect the same data type and the same size of the encrypted columns; If not, the DML statement will fail with an error like this:

Msg 206, Level 16, State 2, Line 107

Operand type clash: char(32) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'AlwaysEncryptedDB') collation_name = 'SQL_Latin1_General_CP1_CI_AS' is incompatible with char(11) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'AlwaysEncryptedDB') collation_name = 'Latin1_General_BIN2'
Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 104]

A Delphi sample application for managing Always Encrypted columns with FireDAC and ODBC is available in my Delphi-demos GitHub repository.

Enabling Always Encrypted isn’t sufficient for encryption or decryption to succeed; you also need to make sure that:

• The application has the VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION database permissions, required to access the metadata about Always Encrypted keys in the database. For details, see Database Permissions
• The application can access the CMK that protects the CEKs for the queried encrypted columns. This behavior is dependent on the keystore provider that stores the CMK. For more information, see Working with Column Master Key Stores

Summary

A Delphi application can manage columns encrypted with SQL Server Always Encrypted; you can use a FireDAC connection with ODBC or ADO.

Enjoy!