Today's question (by Steve Jones - SSC Editor):

Cleaning Up NA Values

> fantasy.playoffs
                Team  Score           Opp
1 Way0utwest Cowboys 135.40          
2         SSC Ravens  66.26 Green Machine
3   Orange Engineers  85.20          
4          Mexicanos 111.52     Mexicanos

Think you know the answer? Click here, and find out if you are right.

Changing Algorithms

I create a symmetric key in a SQL Server 2016 database. I use this code:

CREATE SYMMETRIC KEY SalaryKey
WITH ALGORITHM = AES_256
, IDENTITY_VALUE = 'Salary Protection'
, KEY_SOURCE = 'Protect this from hackers'
ENCRYPTION BY PASSWORD = 'SomeReallyStr0ngP@ssword';
GO

I then use this to encrypt some data in a table:

OPEN SYMMETRIC KEY SalaryKey DECRYPTION BY PASSWORD  = 'SomeReallyStr0ngP@ssword'

UPDATE dbo.Employees
 SET EncryptedSalary = ENCRYPTBYKEY(KEY_GUID('SalaryKey'), CAST(Salary AS VARCHAR(50)))
GO

I now transfer this data to a SQL Server 2017 database, into the same structure table. Now I run this:

CREATE SYMMETRIC KEY SalaryKey
WITH ALGORITHM = AES_256
, IDENTITY_VALUE = 'Salary Protection'
, KEY_SOURCE = 'Protect this from hackers'
ENCRYPTION BY PASSWORD = 'SomeReallyStr0ngP@ssword';
GO
OPEN SYMMETRIC KEY SalaryKey DECRYPTION BY PASSWORD  = 'SomeReallyStr0ngP@ssword'
SELECT top 10
 e.EmpID
, e.EmpSSN
, e.Salary
, CAST(DECRYPTBYKEY(e.EncryptedSalary) AS VARCHAR(50)) AS DecryptedSalary
, e.EncryptedSalary
 FROM dbo.Employees AS e
GO

What is returned in the DecryptedSalary column?

Answer: I get back NULL because the encryption process changed in SQL Server 2017

Explanation: The encryption process actually changed in SQL Server 2017. The encryption algorithm for the data is the same, but the way that the key values are hashed changed from SHA1 to SHA2. This means the data encrypted with symmetric keys need to be decrypted and re-encrypted with the new key when you change from a version prior to SQL Server 2017 to one later. Ref:  

• FIX: SQL Server 2017 cannot decrypt data encrypted by earlier versions of SQL Server by using the same symmetric key - https://support.microsoft.com/en-us/help/4053407/sql-server-2017-cannot-decrypt-data-encrypted-by-earlier-versions

• Migrating Column Level Encryption from 2008R2 to Azure SQL Database - https://www.sqlservercentral.com/articles/migrating-column-level-encryption-from-2008r2-to-azure-sql-database

Discuss this question and answer on the forums

Here's a few of the new posts today on the forums. To see more, visit the forums.