How can I know what hashing algorithm SQL Server used to decrypt the encrypted data when using the function DECRYPTBYPASSPHRASE?

Question

My question is related to the following experiment with two instances:

SQL Server 2017 Express instance (Microsoft SQL Server 2017 (RTM-CU16))
SQL Server 2014 Express instance (Microsoft SQL Server 2014 (SP2-CU18))

I used the function ENCRYPTBYPASSPHRASE to encrypt a text and used the result as the @ciphertext for DECRYPTBYPASSPHRASE. The result of my tests were these:

According to this Microsoft fix,

[...] SQL Server 2017 uses the SHA2 hashing algorithm to hash the passphrase. SQL Server 2016 and earlier versions of SQL Server use the SHA1 algorithm that's no longer considered secure.

But how does it know what was the algorithm used to encrypt data if there's no argument related to that on the function DECRYPTBYPASSPHRASE? Is it part of the encrypted data?

By the results of my tests I'd guess SQL Server always uses the newer version of algorithm available on the instance to encrypt data, but tries all the algorithms to decrypt data until it finds one that fits or returns NULL when no corresponding algorithm is found. It's just a guess though since I couldn't find any way to check what hashing algorithm SQL Server used to decrypt the encrypted data.

Answer 1

But how does it know what was the algorithm used to encrypt data if there's no argument related to that on the function DECRYPTBYPASSPHRASE? Is it part of the encrypted data?

Yes, right on point.

I'm going to use the following for the output:

DECLARE @Data VARBINARY(MAX)
DECLARE @Text NVARCHAR(MAX) = N'I''ll get you, and your little dog too!'
DECLARE @Phrase NVARCHAR(100) = N'Fly My Pretties!'

SELECT @Data = ENCRYPTBYPASSPHRASE(@Phrase, @Text)

SELECT @Data AS [Encrypted_Data]

SELECT CAST(DECRYPTBYPASSPHRASE(@Phrase, @Data) AS NVARCHAR(MAX))

If I run this on my 2014 instance, I'll get the following for Encrypted_Data: 0x01000000E565142762F62...

If I run this on my 2017 instance, I'll get the following for Encrypted_Data: 0x020000004D261C666204F...

What should pop out is the preamble, where you can see the 2014 instance starts with 0x01 and the 2017 instance starts with 0x02. This is the versioning of the encryption type that is used. Note that there is more than just this but there is no need to get into that detail for the purposes of this answer, nor does it need to be public knowledge.

SQL Server 2017 understands 0x01 and 0x02 because it's new and knows the new things. SQL Server 2014 understands only 0x01 because it's older and does not know any of the new things as the new things were not backported.

[...] SQL Server 2017 uses the SHA2 hashing algorithm to hash the passphrase. SQL Server 2016 and earlier versions of SQL Server use the SHA1 algorithm that's no longer considered secure.

This is not the same thing, but generally has to do with Symmetric keys being created with the same initialization vector across both versions. I blogged about this when 2017 came out and it was fixed a little bit later with the trace flag that must be used whereas in your question there is no trace flag needed for 2017 to read 2014 data as shown.