Author: way0utwest

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.