locked
Issue in SQL Server 2017 - Identical Symmetric Key on Two Servers to transfer encrypted data RRS feed

  • Question

  • 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:

    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

    Friday, January 26, 2018 2:26 PM

All replies

  • Hi Denis,

    >> 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.

    I tested on my machine and found that identical symmetric keys not work between SQL Server 2017 and other SQL Server version either. In this case, regarding this issue, I would recommend you submit it at this site. This site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on this website.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 29, 2018 8:24 AM
  • Hi Hannah,

    thank you for testing and the link to azure.com. I will certainly try to find the answer to this bug there.

    best regards,

    Denis

    Monday, January 29, 2018 12:18 PM
  • if anybody wants to test it too:

    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.


    • Edited by DeniSloth Monday, January 29, 2018 12:33 PM
    Monday, January 29, 2018 12:32 PM
  • Hi Denis,

    Thanks for your response and sharing the detailed code. You may give us an update if you get response from that site, so that other community members who have similar issue can benefit from it when they read this thread.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    • Edited by Hannah Yu Tuesday, January 30, 2018 8:49 AM
    Tuesday, January 30, 2018 7:35 AM
  • So is there a workaround? I have a large production db (SQL Server 2008 R2) that I'm migrating/upgrading to SQL Server 2017 next week. When I OPEN SYMMETRIC KEY xxx DECRYPTTION BY CERTIFICATE yyy and then SELECT CONVERT(NVARCHAR(255), DECRYPTBYKEY(SSN))AS SSN FROM zzz, I get NULL values in SQL Server 2017. This a a big deal. Thanks, Doug

    Friday, February 23, 2018 7:45 PM
  • hi

    I recently come to the same issue as i need to add encryption to 20 same schema databases

    We have SQL EXPRESS 2014 and after doing some test adding encryption with symmetric key ,i do the followings

    Install another SQL EXPRESS 2017 to a different machine.

    BAckup 2014 with ecrypted columns

    Restore to 2017 with encrypted columns

    Symmetric Key are included in the restore database

    Certificate included in the restore database

    Master key Not included so i need to create one. 

    If i just restore the master key from 2014 to 2017 without touching symmetric key and certificate then encryption decryption works fine BUT

    If i create the master key as i create it from 2014 instance FAILS return NULLS (Create Master Key encryption By Password='Password123')

    OR

    if i drop symmetric key, Certificate and then try to recreate them identically as with 2014

    Master Key

    Symmetric Key

    Certificate

    the decryption not working and return NULL records

    any clear workaround? 



    stelios ----------







    • Edited by stelios84 Thursday, June 28, 2018 7:30 AM
    Monday, June 25, 2018 8:39 AM
  • So is there a workaround? I have a large production db (SQL Server 2008 R2) that I'm migrating/upgrading to SQL Server 2017 next week. When I OPEN SYMMETRIC KEY xxx DECRYPTTION BY CERTIFICATE yyy and then SELECT CONVERT(NVARCHAR(255), DECRYPTBYKEY(SSN))AS SSN FROM zzz, I get NULL values in SQL Server 2017. This a a big deal. Thanks, Doug

    did you find any solution?

    stelios ----------

    Monday, June 25, 2018 10:15 AM
  • *If i just restore the master key from 2016 to 2017 without touching symmetric key and certificate then encryption decryption works fine

    Yes, that is how you need to do it.

    If i create the master key as i create it from 2016 instance FAILS return NULLS (Create Master Key encryption By Password='Password123')

    Of course, you are creating a different key.

    if i drop symmetric key, Certificate and then try to recreate them identically as with 2016

    Master Key

    Symmetric Key

    Certificate
    the decryption not working and return NULL records

    Of course. You are creating new keys. The fact that they have the same name and properties is irrelevant. Look at this:

    CREATE CERTIFICATE nissecert
    ENCRYPTION BY PASSWORD = 'P?)((L:GS*'''
    WITH SUBJECT = 'Some things never repeat'
    go
    SELECT thumbprint FROM sys.certificates WHERE name = 'nissecert'
    go
    DROP CERTIFICATE nissecert
    go
    CREATE CERTIFICATE nissecert
    ENCRYPTION BY PASSWORD = 'P?)((L:GS*'''
    WITH SUBJECT = 'Some things never repeat'
    go
    SELECT thumbprint FROM sys.certificates WHERE name = 'nissecert'
    go
    DROP CERTIFICATE nissecert
    go

    Note that you get different thumpprints.

    Monday, June 25, 2018 9:38 PM
  • Hi Erland,

    Maybe i misunderstand but help me out: 

    As i read from other sources if we create the symmetric key with identity_value and and KeySource we can replicate the symmetric key on another server ( and this is what i do for the test but returning nulls when i replicate symmetric Key providing identity_value and KeySource )

    The link i found that :https://benjii.me/2010/05/how-to-use-sql-server-encryption-with-symmetric-keys/

    And i create an image from the part saying for the symmetric key ( i highlighted the important )

    And this Picture highlighted is from microsoft

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


    stelios ----------


    • Edited by stelios84 Tuesday, June 26, 2018 8:09 AM
    Tuesday, June 26, 2018 6:31 AM
  • As i read from other sources if we create the symmetric key with identity_value and and KeySource we can replicate the symmetric key on another server ( and this is what i do for the test but returning nulls when i replicate symmetric Key providing identity_value and KeySource )

    Indeed, that gives the same key. I have mainly worked with certificates, and in this case, you do get a new certificate every time. But I made some tests now, and I was able to encrypt a value with a key, create a new one with the same passphrase and identity value, and I was able to decrypt. However, I did this on the same server, and I did not use a full key hierarchy. Then again, that only serves to decrypt the symmetric key in different ways.

    Can you show the exact code you use to decrypt the values?

    Have you checked that you get the same key_guid as you had on the source server?

    Tuesday, June 26, 2018 8:12 PM
  • Can you show the exact code you use to decrypt the values?

    --STEPS FOLLOW --Using SQL EXPRESS 2014 Creating Key, Certificate, Symmetric Key Create Master Key encryption By Password='Password123' ; CREATE CERTIFICATE Certificate1 WITH SUBJECT = 'Protect Data'; ; CREATE SYMMETRIC KEY SymmetricKey1 WITH ALGORITHM = AES_128, IDENTITY_VALUE='MyIdentityValue', KEY_SOURCE='myKeySourceExample' ENCRYPTION BY CERTIFICATE Certificate1; ; --BackUp the Master Key BACKUP MASTER KEY TO FILE = 'C:\cert\masterkey' ENCRYPTION BY PASSWORD = 'Password123' ; --Encrypting My Data in the same instance, SQL EXPRESS 2014 OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1; Update Customers SET Name = ENCRYPTBYKEY(Key_Guid('SymmetricKey1'), Name) CLOSE SYMMETRIC KEY SymmetricKey1;

    --Now data are encrypted ( i check that, i also check that i can decrypt them in the same instance ) --I backup the database, copy the .bak file and paste it into a new machine with SQL EXPRESS 2017 --NOW IN SQL EXPRESS 2017 --Restore the database to SQL EXPRESS 2017 --Certificate And Symmetric Key are already there.. --But Master Key No --Now i have 2 options in order to continew and decrypt the data.. --1) Restore the MASTER KEY (Working as explained previously) OR... --2) Drop Symmetric Key SymmetricKey1 -- Drop Certificate Certificate1 -- Drop master key -- Recreate them ( Decryption Not Working, Return NULLS) Create Master Key encryption By Password='Password123' ; CREATE CERTIFICATE Certificate1 WITH SUBJECT = 'Protect Data'; ; CREATE SYMMETRIC KEY SymmetricKey1 WITH ALGORITHM = AES_128, IDENTITY_VALUE='MyIdentityValue', KEY_SOURCE='myKeySourceExample' ENCRYPTION BY CERTIFICATE Certificate1; ; --And this is how i decrypt them OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1; select Name, CAST(DecryptByKey(Name) as NvarChar(500)) as DecryptedName From Customers CLOSE SYMMETRIC KEY SymmetricKey1;

    Have you checked that you get the same key_guid as you had on the source server?


    Key_Guid from SQL EXPRESS 2014 = 77514000-C00C-7585-4605-58AC0271611D

    Key_Guid from SQL EXPRESS 2017=  77514000-C00C-7585-4605-58AC0271611D

    After Restore databse to SQL 2017 or After restore the master key to SQL 2017

    ##MS_DatabaseMasterKey## from SQLEXPRESS 2014 is the same as SQL EXPRESS 2017 

    But if i drop them and recreate them identically ##MS_DatabaseMasterKey## SQL EXPRESS 2014 is different from 2017 


    stelios ----------









    • Edited by stelios84 Wednesday, June 27, 2018 7:24 AM
    Wednesday, June 27, 2018 6:53 AM
  • I played further with this. I was a little lazy, so I did not back up databases between version, but I simply ran a query over a linked server like this:

    CREATE SYMMETRIC KEY anton
    WITH KEY_SOURCE = 'Hej vad det brinner i holken idag!',
         IDENTITY_VALUE = 'Det är många som vill komma med tåget',
         ALGORITHM = AES_256
    ENCRYPTION BY PASSWORD = 'Second password'

    DECLARE @key_guid uniqueidentifier
    SELECT @key_guid = key_guid FROM sys.symmetric_keys WHERE name = 'anton'
    OPEN SYMMETRIC KEY anton DECRYPTION BY PASSWORD = 'Second password'
    SELECT @key_guid, convert(varchar(100), decryptbykey(alpha)) FROM LINKEDSERVER.slasketti.dbo.hemlig
    CLOSE SYMMETRIC KEY anton

    And then I had a table on the other end where I had encrypted data with a symmetric key created in the same way.

    What I found was this:
    Running query on SQL 2014 to other instance of SQL 2014: works.
    From SQL 2016 to SQL 2014: works
    From SQL 2017 to SQL 2014: does not work.
    From SQL 2017 to SQL 2016: does not work
    From SQL 2017 to SQL 2017: works

    I appears that they have made some changes in how the key is created on SQL 2017. That is not affected by the compat level - I tried that too.

    This blog post appears has some details on the matter:
    https://blogs.msdn.microsoft.com/sql_pfe_blog/2018/06/08/migrating-symmetric-keys-between-sql-2016-and-sql-2017azure/

    When I enabled the trace flag, as suggested in the article with
    DBCC TRACEON(4631, -1)
    I was able to decrypt the data from earlier versions.

    Wednesday, June 27, 2018 10:04 PM

  • I appears that they have made some changes in how the key is created on SQL 2017. That is not affected by the compat level - I tried that too.

    This blog post appears has some details on the matter:
    https://blogs.msdn.microsoft.com/sql_pfe_blog/2018/06/08/migrating-symmetric-keys-between-sql-2016-and-sql-2017azure/

    When I enabled the trace flag, as suggested in the article with
    DBCC TRACEON(4631, -1)
    I was able to decrypt the data from earlier versions.

    How exactly you enable the trace flag : 

    I try the following but again the decryption return NULL

    drop symmetric Key symmetrickey1
    Drop Certificate Certificate1
    drop master key
    
    DBCC TRACEON(4631, -1)
    Create Master Key encryption By Password='Password123'
    
    CREATE CERTIFICATE Certificate1
    WITH SUBJECT = 'Protect Data'
    ;
    CREATE SYMMETRIC KEY SymmetricKey1 
    WITH ALGORITHM = AES_128, 
    IDENTITY_VALUE='MyIdentityValue',
    KEY_SOURCE='MyKeySourceExample'
    ENCRYPTION BY CERTIFICATE Certificate1;

     

    stelios ----------

    Thursday, June 28, 2018 7:16 AM
  • My test script looks like this:

    DBCC TRACEON(4631, -1)
    go
    DROP SYMMETRIC KEY anton
    go
    CREATE SYMMETRIC KEY anton
    WITH KEY_SOURCE = 'Hej vad det brinner i holken idag!',
         IDENTITY_VALUE = 'Det är många som vill komma med tåget',
         ALGORITHM = AES_256
    ENCRYPTION BY PASSWORD = 'Second password'
    DECLARE @key_guid uniqueidentifier
    
    SELECT @key_guid = key_guid FROM sys.symmetric_keys WHERE name = 'anton'
    OPEN SYMMETRIC KEY anton DECRYPTION BY PASSWORD = 'Second password'
    SELECT @key_guid, convert(varchar(100), decryptbykey(alpha)) FROM [SOMMERWALD].slasketti.dbo.hemlig
    CLOSE SYMMETRIC KEY anton
    
    

    I would encourage you try with linked servers as well, and also with a symmetric key that is protected with a simple password. The purpose would be to see if you can get any of these to work. As I see it, it should not really matter how the symmetric key is protected.

    For reference, here is the script that I ran on SOMMERWALD, the source server:

    CREATE CERTIFICATE certo55
    ENCRYPTION BY PASSWORD = 'Julgransfot'
    WITH SUBJECT = 'Testar symmetriska nycklar'
    
    CREATE SYMMETRIC KEY anton
    WITH KEY_SOURCE = 'Hej vad det brinner i holken idag!',
         IDENTITY_VALUE = 'Det är många som vill komma med tåget',
         ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE certo55
    go
    CREATE TABLE hemlig(alpha varbinary(800) NOT NULL)
    go
    DECLARE @key_guid uniqueidentifier
    SELECT @key_guid = key_guid FROM sys.symmetric_keys WHERE name = 'anton'
    OPEN SYMMETRIC KEY anton DECRYPTION BY CERTIFICATE certo55 WITH PASSWORD = 'Julgransfot'
    INSERT hemlig (alpha)
        SELECT encryptbykey(@key_guid, 'This is our secret text')
    SELECT @key_guid, convert(varchar(100), decryptbykey(alpha)) FROM hemlig
    CLOSE SYMMETRIC KEY anton
    go
    DROP SYMMETRIC KEY anton
    go
    CREATE SYMMETRIC KEY anton
    WITH KEY_SOURCE = 'Hej vad det brinner i holken idag!',
         IDENTITY_VALUE = 'Det är många som vill komma med tåget',
         ALGORITHM = AES_256
    ENCRYPTION BY PASSWORD = 'Second password'
    DECLARE @key_guid uniqueidentifier
    SELECT @key_guid = key_guid FROM sys.symmetric_keys WHERE name = 'anton'
    OPEN SYMMETRIC KEY anton DECRYPTION BY PASSWORD = 'Second password'
    INSERT hemlig (alpha)
        SELECT encryptbykey(@key_guid, 'This is our second text')
    SELECT @key_guid, convert(varchar(100), decryptbykey(alpha)) FROM hemlig
    CLOSE SYMMETRIC KEY anton
    

    You may note that I create the symmetric key twice here.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, June 28, 2018 4:11 PM
  • By the way, maybe you should not spend too much time to get that trace flag to work, because in the end you need to decrypt and re-reencrypt if you don't want to keep the keep the master key from the restored database. The purpose of the trace flag is only to make it a littler simpler for you to make it easier to migrate to the new keys. But the trace flag is not a long-term solution, which is also spelt out in the blog post.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, June 28, 2018 5:49 PM