ENCRYPTBYPASSPHRASE Output Length Difference

Question

Post reply

ENCRYPTBYPASSPHRASE Output Length Difference

DB_Newbie2007

SSCarpal Tunnel

Points: 4448
More actions
December 7, 2017 at 8:35 am

#326746

We noticed there is a difference in the length of the output from SQL Server 2008 and 2014 versus 2017. When running the following:
select LEN(ENCRYPTBYPASSPHRASE('JohnSmith', '1' ) )
SQL Server 2008/2014 return a 28 character result.
SQL Server 2017 returns a 36 character result.
Anyone know why?
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

Viewing 6 posts - 1 through 5 (of 5 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 736212

December 7, 2017 at 12:53 pm

#1971268

No idea, but I'll try to track this down.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

One other note, I get two different encryption values:

2017 - 0x02000000A584538F00817EFA13FB8E176BC435F7FD2A3AEDC9E79AFD2DD203B1356DB2CE
2016 - 0x01000000CBBF1DA4C95CABB66EDE087B7EA983C599D663DB2DA80D49
2017 seems to decrypt 2016, but not vice versa. Need to test more, but this could be an issue.

Nice catch.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

Here you go: https://support.microsoft.com/en-sg/help/4053407/sql-server-2017-cannot-decrypt-data-encrypted-by-earlier-versions

DB_Newbie2007 SSCarpal Tunnel Points: 4448 More actions · Answer 4

Thanks immensely, Steve! I sent the information to our development team so they can make the appropriate modifications to our table(s) and code.

I love surprises but *not* "gotchas". LOL

Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

You are welcome and hope things work out. If you're interested, this would make a great short article for you to publish.