KB4053407 - FIX: SQL Server 2017 cannot decrypt data encrypted by earlier versions of SQL Server by using the same symmetric key

Symptoms

Assume that you have a Microsoft SQL Server 2016 or an earlier version of SQL Server database that has data or objects encrypted by using symmetric key encryption. In this situation, you may be unable to decrypt the data or objects by using the same symmetric key in SQL Server 2017 on Windows, if the following conditions are true:

• The database is restored to SQL Server 2017.

• The existing symmetric key is dropped, and the same symmetric key is created.

Note This issue will not occur if the symmetric key from an earlier version of SQL Server isn't dropped or recreated in SQL Server 2017.

Cause

This issue occurs because SQL Server 2017 uses the SHA2 hashing algorithm to hash the passphrase. SQL Server 2016 and earlier versions of SQL Server use the SHA1 algorithm that's no longer considered secure.

Resolution

This issue is fixed in the following cumulative update for SQL Server:

       Cumulative Update 2 for SQL Server 2017

Note This fix requires Trace Flag (TF) 4631 to be enabled after you install the cumulative update. This Trace Flag can be enabled by using the SQL Server Startup option or by using DBCC TRACEON. The Symmetric Key must be created once the TF 4631 is already enabled.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References

Learn about the terminologythat Microsoft uses to describe software updates.