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.