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.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

Share

Share

Rate