Stephen W

My feedback

  1. 30 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  12 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Stephen W commented  · 

    A quick update to my previous comment:

    "I've found that I don't need the trace flag on permanently to be able to decrypt and encrypt, it was just necessary to create the correct interoperable symmetric key. But doesn't hurt to make it permanently on."

    Turns out this was not exactly true. As long as the trace flag is on before the symmetric key is created, SQL Server 2017 will always be able to decrypt data from earlier versions, but the opposite is not true. If the trace flag is not ON permanently, any data encrypted by 2017 will not be able to be decrypted by earlier versions. Hard lesson to learn.

    I used: `/opt/mssql/bin/mssql-conf traceflag 4631 on` (Linux, may vary with Windows) to permanently enable the trace flag to avoid any future issues with data being undecryptable by older versions.

    Stephen W commented  · 

    Faced this same problem and found an obscure solution from Microsoft.

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

    Turns out SQL Server 2017 is using SHA2 for passphrases when generating the keys instead of the older SHA1 which leads to keys that even though they share the same GUID and appear to be interoperable with previous version, are not actually. This seems like a pretty major issue and the solution took much longer than it should have to find and figure out. Hopefully this helps others that face the problem in the future.

    The support page says its fixed in CU2, but the import part is the Note in the resolution:

    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 it doesn't say and I've found by experimentation is that the trace flag needs to be ON globally before creating the master key, certificate, or symmetrical key in SQL Server 2017 in order for them to be interoperable with previous SQL Server versions.

    ```
    DBCC TRACEON(4631, -1);
    GO

    CREATE MASTER KEY ENCRYPTION BY PASSWORD ....
    GO
    CREATE CERTIFICATE ...
    GO
    CREATE SYMMETRIC KEY ...
    GO
    ```

    I've found that I don't need the trace flag on permanently to be able to decrypt and encrypt, it was just necessary to create the correct interoperable symmetric key. But doesn't hurt to make it permanently on.

Feedback and Knowledge Base