Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

← SQL Server

Identical symmetric keys do not work between SQL Server 2017 and other SQL Server version

hi everybody!

I believe, there is a general product issue in SQL Server 2017. The problem concerns encrypting & decrypting data using the same symmetric key on different servers.
I have an issue in SQL Server 2017 CU3 (version 14.0.3015.40). I need to create identical symmetric keys on two servers of different versions, as it is described in MS article:

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

These steps work well within and between different versions of SQL Servers (2012, 2014, 2016), but not between SQL Server 2017 and any other server.
I can create identical symmetric keys on SQL Server 2012, 2014 and 2016. So I can use the same key on SQL Server 2016 to decrypt the data encrypted on SQL Server 2014 and vice versa: using this method I can decrypt data on SQL Server 2012, which were encrypted on SQL Server 2016.

If I however create the same symmetric key on SQL Server 2017 it does not work for other servers (in both directions): I can not decrypt already encrypted data on any older versions of SQL Server / and data encrypted on SQL Server 2017 can also not be decrypted on SQL Server 2016 / 2014 / 2012.

Such an identical key is secured by MASTER KEY (ENCRYPTION BY PASSWORD) and CERTIFICATE with the same KEY_SOURCE, ALGORITHM, IDENTITY_VALUE (ENCRYPTION BY CERTIFICATE).

Are there any other security parameters one have to consider creating an identical key on SQL Server 2017 or any other solutions? Otherwise it is not possible to transfer already encrypted data by upgading the SQL Server to SQL Server 2017.

thanks in advance,
Denis

30 votes
Sign in
(thinking…)
Sign in with: Microsoft
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close

We’ll send you updates on this idea

Denis shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

12 comments

Attach a File
Sign in
(thinking…)
Sign in with: Microsoft
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close
Submitting...
  • Anonymous commented  ·   ·  Flag as inappropriate

    I am having same from problem that data encrypted on SQL 2016 can not be decrypted on SQL 2012, keep giving NULLs. I ETL data from SQL 2016 to SQL 2012 and I made sure DMK, Certs, Symmetrickeys are same between both the servers. Still decryption not working on SQL 2012 server. Not sure am I missing something here. Really appreciate your timely help. Thanks

  • Anonymous commented  ·   ·  Flag as inappropriate

    I am having same from problem that data encrypted on SQL 2016 can not be decrypted on SQL 2012, keep giving NULLs. Appreciate any work around ?

  • jason walton commented  ·   ·  Flag as inappropriate

    The security algorithm used to encrypt passphrases in SQL Server 2016 (or below) is SHA1, in SQL Server 2017 this has been updated to use SHA2.

    You can work-around this issue by enabling trace-flag 4631.

    But, the ultimate fix is going to be a total decryption > migration > re-encryption using a new certificate that is based on the newer algorithm

  • Stephen W commented  ·   ·  Flag as inappropriate

    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.

  • Denis commented  ·   ·  Flag as inappropriate

    Hi Stephen,

    thanks you for your response and sharing this strange workaround. It seems to work indeed! I have tested one way, now need to proove if it makes symmetric key fully interoperable with previous SQL Server versions. What about future versions? :)

    Interesting what does this undocumented trace flag actually do? Does it force the older SHA1? If so, I wonder why there is no such <algorithm> - option for SHA by creating a master key / symmetric key?

    I've not found trace flag (TF) 4631 in the Microsoft list:
    https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql

  • Stephen W commented  ·   ·  Flag as inappropriate

    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.

  • DK commented  ·   ·  Flag as inappropriate

    SQL Server 2017 CU5 (version 14.0.3023.8) still has the same error... curious... is it no big deal or too big deal?

    someone can already produce "wrong" encrypted data within SQL Server 2017... these data might become unusable when correction in DECRYPTION finally will be done

  • Denis commented  ·   ·  Flag as inappropriate

    Are there any solution? Server 2017 CU4 (version 2017.140.3022.28) still the same problem

    I wonder how many people / companies use unencrypted data on SQL server... and do not have such problems with encryption :)

  • Doug commented  ·   ·  Flag as inappropriate

    So has anyone offered a solution/workaround? I'm performing a major upgrade from SQL Server 2008 R2 to SQL Server 2017 next week and our QA team just pointed out that all of our encrypted values are being returned as NULLs.

  • Denis commented  ·   ·  Flag as inappropriate

    following is an example of encrypting simple string '123' and decrypting it back with the same symmetric key on different SQL server versions:

    1. Create a Master Key:
    CREATE MASTER KEY ENCRYPTION BY
    PASSWORD = 'My p@55w0Rd'
    CREATE CERTIFICATE TEST_Certificate
    WITH SUBJECT = 'Key Protection'
    CREATE SYMMETRIC KEY TEST_KEY WITH
    KEY_SOURCE = 'My key generation bits. This is a shared secret!',
    ALGORITHM = AES_256,
    IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'
    ENCRYPTION BY CERTIFICATE TEST_Certificate

    2. Ecnrypting a string '123' on SQL Server 2016 returns the output: '0x00D1CCE9771AE7554D479F7B93A456110100000013F8665AAE4A1AD1922171D19ED08B4422DB2C7B626045EAABF885378B4BDCC4'

    declare @script varchar(8000)
    declare @enc varbinary(8000)
    declare @enc_varchar varchar(8000)

    -- Open the symmetric key with which to encrypt the data.
    OPEN SYMMETRIC KEY TEST_KEY
    DECRYPTION BY CERTIFICATE TEST_Certificate

    declare @key_guid uniqueidentifier = KEY_GUID('TEST_KEY')
    -- Encrypt
    SET @script='123'
    set @enc = ENCRYPTBYKEY(@key_guid,@script)
    set @enc_varchar = CONVERT(varchar(8000),@enc,1)
    SELECT @enc
    SELECT @enc_varchar

    3. Decrypting the encrypted string back '0x00D1CCE9771AE7554D479F7B93A456110100000013F8665AAE4A1AD1922171D19ED08B4422DB2C7B626045EAABF885378B4BDCC4'
    • SQL Server 2016 / SQL Server 2014 / SQL Server 2012 returns original string '123' back
    • SQL Server 2017 returns NULL

    declare @dec varbinary(8000)
    declare @dec_varchar varchar(8000)
    declare @enc_varchar varchar(8000)

    -- Open the symmetric key with which to decrypt the data.
    OPEN SYMMETRIC KEY TEST_KEY
    DECRYPTION BY CERTIFICATE TEST_Certificate
    declare @key_guid uniqueidentifier = KEY_GUID('TEST_KEY')
    -- Decrypt
    set @dec_varchar='0x00D1CCE9771AE7554D479F7B93A456110100000013F8665AAE4A1AD1922171D19ED08B4422DB2C7B626045EAABF885378B4BDCC4'
    set @dec = CONVERT(varbinary(8000),@dec_varchar,1)
    set @enc_varchar = CONVERT(varchar(8000),DECRYPTBYKEY(@dec))
    SELECT @enc_varchar

    NB: encrypting '123' on SQL Server 2017 returns a similar output '0x00D1CCE9771AE7554D479F7B93A4561102000000917D28D28AC21BD875D117DBA83F0448C25751947845B19033F1AD29C219B076' BUT this is not decryptable on any other server except SQL Server 2017.

SQL Server: Bugs

Feedback and Knowledge Base

(thinking…)
Hosted by UserVoice