# Changing Algorithms

• Comments posted to this topic are about the item Changing Algorithms

• Now that is interesting.

Learned something, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

• I have to say I think this is a bit naughty.  The usual assumption is that all servers involved in a question are up to date. In this case though, the correct answer only appears correct if the server is not up to date.  The question 'why wouldn't it work?' is a valid one but 'what is returned?' isn't as clear cut.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• What do you mean, Neil? The question states the data is encrypted in 2016, moved to 2017. There's no "up to date" here. The algorithm changes. You can't xfer a symmetric key. You create it, because they are supposed to be deterministic. However, the algorithm changed for the hashing of the salt in 2017 as the default, so the key isn't symmetric.

• Good question, I learned something new.

• Good question, I did some Googling before answering and I got the question correct.

• Steve Jones - SSC Editor wrote:

What do you mean, Neil? The question states the data is encrypted in 2016, moved to 2017. There's no "up to date" here. The algorithm changes. You can't xfer a symmetric key. You create it, because they are supposed to be deterministic. However, the algorithm changed for the hashing of the salt in 2017 as the default, so the key isn't symmetric.

Neil could be referring to this issue:

FIX: SQL Server 2017 cannot decrypt data encrypted by earlier versions of SQL Server by using the same symmetric key

Sue

• Steve Jones - SSC Editor wrote:

What do you mean, Neil? The question states the data is encrypted in 2016, moved to 2017. There's no "up to date" here. The algorithm changes. You can't xfer a symmetric key. You create it, because they are supposed to be deterministic. However, the algorithm changed for the hashing of the salt in 2017 as the default, so the key isn't symmetric.

I meant the issue to which Sue referred.  I'd found that the algorithm had changed between the years but then saw the references to the CU which fixed it.   The link below suggested it could be done, if the CU was applied.  As I said, the usual assumption is that the servers in question are up to date with CU's, so I went with the answer I did.  I was then a bit surprised to see that the answer was that it couldn't be done so I was wondering what I'd missed.  I do accept my pre-caffeine tone was a little snippy, and for that, I apologise.

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-identical-symmetric-keys-on-two-servers?view=sql-server-ver15

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• No need to apologize, but the "up to date" doesn't fix this. You need a trace flag, which is part of what you would need to know. This isn't fixed, but there is a workaround that allows you to enable the old behavior.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply