The .NET Framework 4.6.2 client driver for Always Encrypted intermittently fails during row decryption

This article provides resolution for the problem that causes the .NET Framework 4.6.2 client application to fail when accessing data from an Always Encrypted-enabled database.

Original product version:   SQL Server 2016, SQL Server 2017
Original KB number:   3204545

Symptoms

You have a client application that uses the .NET Framework Data Provider for Microsoft SQL Server (Sqlclient) that's included in the Microsoft .NET Framework 4.6.2. You use this application to connect to an Always Encrypted-enabled database that's running on Microsoft SQL Server 2016, SQL Server 2017 on Windows or Microsoft Azure SQL Database.

When you try to decrypt the records from the Always Encrypted-enabled database, you receive intermittent error messages that resemble the following error:

Decryption failed. The last 10 bytes of the encrypted column encryption key are: 7E-0B-E6-D3-39-CE-35-86-2F-AA.The first 10 bytes of ciphertext are: 01-C3-D7-39-33-2F-E6-44-C3-B1.Specified ciphertext has an invalid authentication tag.

When this problem occurs, queries against columns that are protected by the Always Encrypted setting may display incorrect results, for example, too few records are returned. This, in turn, may trigger incorrect behavior. For example, the client application tries to insert missing values or run other update processes that either create additional errors or cause inconsistent data in the database.

Resolution

To fix this issue, install the security update from Microsoft Security Bulletin MS16-155.

Workaround

To work around this problem, follow these guidelines, as appropriate:

• The .NET client isn't yet upgraded to version 4.6.2

  In the environments that are mentioned in the Symptoms section, we recommend that you don't upgrade to the .NET Framework 4.6.2 until this problem is fixed. This problem doesn't affect earlier versions of the driver.

• The .NET client is already upgraded to 4.6.2

  Use one of the following methods:

  • Method 1: Roll back the .NET Framework version

    Uninstall the .NET Framework 4.6.2 from the system's hosting client applications. This forces the applications to use an older version of the NET Framework Data Provider for SQL Server that doesn't experience this problem. To do this, follow these steps:

    1. In Control Panel, open the Programs item, and then select Programs and Features.
    2. Select View installed updates, and then uninstall Update for Microsoft Windows KB3120737.

    Note

    We recommend that after you uninstall any version of the .NET Framework, you test .NET Framework-dependent applications. These applications may not function correctly if you uninstall the version of the .NET Framework that they are dependent on.

  • Method 2: Turn off column encryption key caching

    If you can't roll back the .NET Framework installation, turn off column encryption key (CEK) caching. To do this, set the SqlConnection.ColumnEncryptionKeyCacheTtl Property in the client application to 0. For example, run the following code to disable column encryption in C# applications:

    System.Data.SqlClient.SqlConnection.ColumnEncryptionKeyCacheTtl = TimeSpan.Zero
    

After you use either method, verify that the error doesn't reappear during a table scan (such as SELECT * FROM < table with Always Encrypted>) that's run from a query window in SSMS over a database connection that has Column Encryption Setting=Enabled in the connection string. Running this kind of scan validates the correct encryption.

References

.NET Framework 4.6.2 client driver for Always Encrypted resulting in intermittent failures to decrypt individual rows