1

Topic: EncryptByPassPhrase and DecryptByPassphrase

Good afternoon.
There is data which shifrujutsja/will be decoded in a DB.
And this data from one DBMS is poured in other DBMS.
It is a question about MSSQL2012 and MSSQL2017
Faced a problem that the data which transited enciphering in MSSQL2012, cannot be deciphered in MSSQL2017 and on the contrary.
I so understand that algorithms in EncryptByPassPhrase and DecryptByPassphrase in MSSQL2017 changed.
Enciphering and decoding example:

DECLARE @PassphraseEnteredByUser NVARCHAR (512) = ' MN89HFG5HBRJOmKB ';
DECLARE @Text NVARCHAR (512) = ' 12345-6788990'
DECLARE @R NVARCHAR (512)
SET @R = (SELECT CONVERT (NVARCHAR (4000), EncryptByPassPhrase (@PassphraseEnteredByUser, @Text, 1), 1))
DECLARE @InquiryBinary VARBINARY (900)
SET @InquiryBinary = CONVERT (VARBINARY (900), @R, 1)
SELECT CONVERT (NVARCHAR (1024), DecryptByPassphrase (@PassphraseEnteredByUser, @InquiryBinary, 1))

The most interesting, in MSSQL2005 2008 2012 is not present problems, I can cipher for example on 2012 and decrypt the data on 2008. And here with 2017 there was a problem...
Whether there are methods to adjust compatibility of enciphering with MSSQL2017?

2

Re: EncryptByPassPhrase and DecryptByPassphrase

_UUS;
https://support.microsoft.com/en-sg/hel … r-versions

3

Re: EncryptByPassPhrase and DecryptByPassphrase

Thanks, similar are my subject...
But I did not understand as to solve a problem, here is written:
"Note This fix requires trace flag (TF) 4631 to be enabled after you install the cumulative update. This trace flag can be enabled by using the SQL Server Startup option or by using DBCC TRACEON."
What means to include a trace flag "(TF) 4631" after setting? I so understood, it is necessary to update MSSQL, washing the current version: 14.0.1000

4

Re: EncryptByPassPhrase and DecryptByPassphrase

felix_ff;
Or it can is possible, that MSSQL2017 used by default  "SHA1" instead of new "SHA2".
I.e. there is a possibility to switch an option that enciphering went on "SHA1"?

5

Re: EncryptByPassPhrase and DecryptByPassphrase

_UUS;
Is not present it is impossible, for 2017 sequels algorithm SHA-2 is specially used because SHA-1 any more it is not considered .

6

Re: EncryptByPassPhrase and DecryptByPassphrase

felix_ff wrote:

Igor_UUS;
Is not present it is impossible, for 2017 sequels algorithm SHA-2 is specially used because SHA-1 any more it is not considered .

And that there is here it:
Resolution
This issue is fixed in the following cumulative update for A SQL Server:
Cumulative Update 2 for A SQL Server 2017
I then did not understand, what means?

7

Re: EncryptByPassPhrase and DecryptByPassphrase

felix_ff;
Not to be trusted at all, was to mean MSSQL2016, then decided it to update on 2017, and then the data ciphered in tables already you can not decrypt... Even as that not to be believed that in  this moment missed

8

Re: EncryptByPassPhrase and DecryptByPassphrase

_UUS wrote:

it is passed...
And that there is here it:
Resolution
This issue is fixed in the following cumulative update for A SQL Server:
Cumulative Update 2 for A SQL Server 2017
I then did not understand, what means?

You need to be delivered Cumulative Update 2 for a SQL Server 2017 then to include the specified flag (through DBCC TRACEON), and  a key.

9

Re: EncryptByPassPhrase and DecryptByPassphrase

Minamoto wrote:

it is passed...
You need to be delivered Cumulative Update 2 for a SQL Server 2017 then to include the specified flag (through DBCC TRACEON), and  a key.

The matter is that I do not create any keys, all happens in :

DECLARE @PassphraseEnteredByUser NVARCHAR (512) = ' MN89HFG5HBRJOmKB ';
DECLARE @Text NVARCHAR (512) = ' 12345-6788990'
DECLARE @R NVARCHAR (512)
SET @R = (SELECT CONVERT (NVARCHAR (4000), EncryptByPassPhrase (@PassphraseEnteredByUser, @Text, 1), 1))

Also I decrypt as in  using the same key:

DECLARE @PassphraseEnteredByUser NVARCHAR (512) = ' MN89HFG5HBRJOmKB ';

And where to take "Cumulative Update 2 for a SQL Server 2017"?

10

Re: EncryptByPassPhrase and DecryptByPassphrase

_UUS wrote:

it is passed...
The matter is that I do not create any keys, all happens in :

DECLARE @PassphraseEnteredByUser NVARCHAR (512) = ' MN89HFG5HBRJOmKB ';
DECLARE @Text NVARCHAR (512) = ' 12345-6788990'
DECLARE @R NVARCHAR (512)
SET @R = (SELECT CONVERT (NVARCHAR (4000), EncryptByPassPhrase (@PassphraseEnteredByUser, @Text, 1), 1))

Also I decrypt as in  using the same key:

DECLARE @PassphraseEnteredByUser NVARCHAR (512) = ' MN89HFG5HBRJOmKB ';

And where to take "Cumulative Update 2 for a SQL Server 2017"?

Then, most likely, flag activation at start is required to you, read about parameters of start with flags.
The link on CU is in article which to you resulted.

11

Re: EncryptByPassPhrase and DecryptByPassphrase

Minamoto;
That's just the point, I cannot understand that for a flag at start...
What exactly it is possible to try?

12

Re: EncryptByPassPhrase and DecryptByPassphrase

_UUS wrote:

Minamoto;
That's just the point, I cannot understand that for a flag at start...
What exactly it is possible to try?

Read...
https://docs.microsoft.com/en-us/sql/t- … ansact-sql
How to include - it is specified right at the end.

13

Re: EncryptByPassPhrase and DecryptByPassphrase

Minamoto wrote:

it is passed...
Read...
https://docs.microsoft.com/en-us/sql/t- … ansact-sql
How to include - it is specified right at the end.

I understood... Earlier trace did not use... Esteemed about this business. But, similar update still needs to be delivered, since does not work. I swing Update2. I will write, helped or not...

14

Re: EncryptByPassPhrase and DecryptByPassphrase

After update and trace application, all earned as it is necessary!
Many thanks to all who helped to understand!