locked
Error : cannot create table since it references a column encryption key from a different database RRS feed

  • Question

  • Hi,

    I have 2 different database lets consider Database A and Database B  in the same SQL server 2017.In both database some columns are encrypted.

    I want to access a encrypted column of database A lets say mobile_no and insert it into table test_table in Database B .

    Below is the query which is used

    Use B

    GO

    SELECT mobile_no INTO test_table FROM a.table


    I am getting below error on execution

    Cannot create table since it references a column encryption key from a different database


    And we also tried creating the same column master key and column encryption key on both the databases A AND B.

    Regards,

    Saurabh


    Wednesday, January 9, 2019 9:57 AM

Answers

  • The message speaks for itself: that's a road block. You need to read this to a client and insert from the client back to the new database.

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

    • Marked as answer by Saurabh Mhatre Tuesday, January 15, 2019 9:23 AM
    Wednesday, January 9, 2019 11:37 AM

All replies

  • The message speaks for itself: that's a road block. You need to read this to a client and insert from the client back to the new database.

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

    • Marked as answer by Saurabh Mhatre Tuesday, January 15, 2019 9:23 AM
    Wednesday, January 9, 2019 11:37 AM
  • Look at the marked answer in this post

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4b56da88-1166-4a96-bfb5-d2694e03d7b5/recreate-symmetric-key-on-different-server-database?forum=sqlsecurity

    When we create a symmetric keys we use the same key_source, identity_value, and algorithm for each of the databases that would need to encrypt/decrypt the column values of other databases. 

    IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'SymmetricKey1')
        BEGIN
    	DECLARE @SQLCommand nvarchar(max)
    	DECLARE @String varchar(256)
    	DECLARE @String2 varchar(256)
    	SET @String = 'The red fox digs holes'
    	SET @String2 = 'Alligators run fast'
    
         use DB1
    	SET @SQLCommand = '
    		CREATE SYMMETRIC KEY SymmetricKey1 with
    		KEY_SOURCE = ''' + @String + ''', 
    		IDENTITY_VALUE = ''' + @String2 + ''', 
    		ALGORITHM = AES_256
    		ENCRYPTION BY CERTIFICATE Certificate1;'
        
    	EXEC sp_executesql @SQLCommand 
    	
    	use DB2
    	SET @SQLCommand = '
    		CREATE SYMMETRIC KEY SymmetricKey1 with
    		KEY_SOURCE = ''' + @String + ''', 
    		IDENTITY_VALUE = ''' + @String2 + ''', 
    		ALGORITHM = AES_256
    		ENCRYPTION BY CERTIFICATE Certificate1;'
        
    	EXEC sp_executesql @SQLCommand 
        END
    
    GO

    Wednesday, January 9, 2019 3:08 PM
  • Hi Saurabh Mhatre,

     

    >>Cannot create table since it references a column encryption key from a different database.

     

    when encryption and decryption occur in separate databases or on separate servers, the key stored in one database is not available for use on the second database. So you can try to  create Identical Symmetric Keys on two databases.

     

    You can refer to the following statement on both databases to create the same key for the encrypted and decrypted columns: 

    CREATEMASTER KEY ENCRYPTION BY PASSWORD = 'My p@55w0Rd'; GO

    CREATE CERTIFICATE [cert_keyProtection] WITH SUBJECT = 'Key Protection'; GO

    CREATE SYMMETRIC KEY [key_DataShare] 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 BYCERTIFICATE [cert_keyProtection];

    GO.

    For more details, please see the blog: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-identical-symmetric-keys-on-two-servers?view=sql-server-2017

     

    Best regards,

    Dedmon Dai


    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

    Thursday, January 10, 2019 9:08 AM