Backwards Compatible Symmetric Keys in SQL Server 2017+

I discovered recently that there was a change made in SQL Server 2017 to the way that symmetric key passphrases are hashed. There’s a KB article that notes the fix, but basically the passphrases used to be encrypted with SHA1. That’s cryptographically insecure, so the algorithm was updated to SHA2.

This is a problem, and can cause some issues. I’ll show the issue and then how to get around it.

No More Decryptions

Let’s say I have a SQL Server 2016 instance and database. I run 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
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 can easily decrypt this data:

2019-12-05 12_12_54-SQLQuery2.sql - Plato_SQL2016.sandbox (PLATO_Steve (66))_ - Microsoft SQL Server

Let’s now say I move this data to SQL Server 2017. It could be a restore, some ETL, replication, etc. In any case, I have the data there.

Now, if I drop the symmetric key, or it doesn’t exist, I need to recreate it. These are supposed to be deterministic, which means I can run the code above and get the same key. I’ve done this on SQL 2014 and SQL 2016 databases, and I can decrypt data encrypted in another database if I use the same code to create the key. Let’s try this. I’ll run 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
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

I get this:

2019-12-05 12_15_24-SQLQuery1.sql - Plato_SQL2017.sandbox (PLATO_Steve (54))_ - Microsoft SQL Server

Why do I get NULL? SQL Server can’t decrypt this data, so it returns a NULL This isn’t supposed to happen, but the hash change caused this.

Let’s fix this.

A Trace Flag

The KB article linked above mentions that trace flag 4631 will fix this. Let’s try it. I’ll run this code:

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

Now, let’s open the key and requery:

2019-12-05 12_19_32-SQLQuery1.sql - Plato_SQL2017.sandbox (PLATO_Steve (54))_ - Microsoft SQL Server

Hmm, this doesn’t seem right. With a little experimentation, I discovered the trace flag needs to be global, or it can be enabled instance wide. Let’s do that.

DROP SYMMETRIC KEY SalaryKey
DBCC TRACEOFF( 4631)
DBCC TRACEON( 4631, -1)
GO
CREATE SYMMETRIC KEY SalaryKey
WITH ALGORITHM = AES_256
, IDENTITY_VALUE = 'Salary Protection'
, KEY_SOURCE = 'Protect this from hackers'
ENCRYPTION BY PASSWORD = 'SomeReallyStr0ngP@ssword';
GO

Now we query, and this works.

2019-12-05 12_21_38-SQLQuery1.sql - Plato_SQL2017.sandbox (PLATO_Steve (54))_ - Microsoft SQL Server

Most people don’t deal with column encryption, but if you do, be aware of this.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

2 Responses to Backwards Compatible Symmetric Keys in SQL Server 2017+

  1. Pingback: SQL Server 2017 and Column-Level Encryption – Curated SQL

  2. CeeCee says:

    Awesome! I hit this exact problem. Thank you so much for describing the solution.

Comments are closed.