1

I am trying to synchronize data between two databases(SQL Server 2014 and SQL Server 2017). The Windows application saves the data to SQL Server 2017 database using the encryptbypassphrase function. This encrypted binary data is transferred to SQL Server 2014 database. I am unable to decrypt the data using the decryptbypassphrase function in SQL Server 2014 database.

Below are scripts to replicate the issue.

In SQL Server 2017

declare @clearText nvarchar(max) = 'Hello world!'
select ENCRYPTBYPASSPHRASE('Password', @clearText)

prints 0x02000000DA28E89D43CC03C11BBDC34041B20BC3627D93CC84E73A83545F379316C0F016DB8A9E22CAABFACC9C70C956217E9604FB69B465C6FF251E397779F1EAEF2464 and

declare @encryptedValue varbinary(max)= 0x02000000DA28E89D43CC03C11BBDC34041B20BC3627D93CC84E73A83545F379316C0F016DB8A9E22CAABFACC9C70C956217E9604FB69B465C6FF251E397779F1EAEF2464
select Convert(nvarchar,DECRYPTBYPASSPHRASE('Password',@encryptedValue))

prints 'Hello world!'

In SQL Server 2014:

declare @clearText nvarchar(max) = 'Hello world!'
select ENCRYPTBYPASSPHRASE('Password', @clearText)

prints 0x010000008DE2368BEE3A899341956117C504F5DC1696A4847D6CFC0A276B2C2D1EFE0052045784B9DC1A9A1E552F4E927794AB0F 

declare @encryptedValue varbinary(max)= 0x010000008DE2368BEE3A899341956117C504F5DC1696A4847D6CFC0A276B2C2D1EFE0052045784B9DC1A9A1E552F4E927794AB0F
select Convert(nvarchar,DECRYPTBYPASSPHRASE('Password',@encryptedValue))

prints 'Hello world!'

What I am trying to do is transferring the binary data from SQL Server 2017 to SQL Server 2014 and trying to decrypt the binary data

In SQL Server 2014

declare @encryptedValue varbinary(max)= 0x02000000DA28E89D43CC03C11BBDC34041B20BC3627D93CC84E73A83545F379316C0F016DB8A9E22CAABFACC9C70C956217E9604FB69B465C6FF251E397779F1EAEF2464
select Convert(nvarchar,DECRYPTBYPASSPHRASE('Password',@encryptedValue))

prints NULL

I expect the output to be 'Hello World!'

char
  • 2,063
  • 3
  • 15
  • 26
lakshminarayana rao
  • 11
  • 1
  • 2
    It won't work and the reason why is hinted at in the first byte of the output: SQL Server 2017 uses new algorithms for the encrypted data (version 2) which SQL Server 2014 does not support (version 1). An enterprising soul has reversed and shared the [algorithms](https://stackoverflow.com/a/51794958/4137916); in theory, it's possible to (re-)implement them in T-SQL, in practice, this will be difficult without something like a CLR function. – Jeroen Mostert Aug 01 '19 at 12:03
  • 1
    Could you also install an SQL 2017 Express instance on the same machine as the 2014 instance and use this to decrypt the data locally? – bendataclear Aug 01 '19 at 12:43

0 Answers0