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:
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:
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:
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.
Most people don’t deal with column encryption, but if you do, be aware of this.