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

Applies to: SQL Server 2017 Developer on WindowsSQL Server 2017 Enterprise on WindowsSQL Server 2017 Enterprise Core on Windows

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.

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 terminology that Microsoft uses to describe software updates.