Copy the encrypted column to another database SQL Server 2012

Question

I have created a database and it has a table with an encrypted column.

If you insert encrypted information into this column, then it can be decrypted very easily. But, if you create the exact same table in another database, with the same key and certificate, then when you insert the data there is a problem.

UPDATE 1:

There is a DB_TEST database with a TNT table, in which two columns, one of which is encrypted. If you extract several values from this column, you can decode them and get the desired result.

But if you create the exact same table with the same encryption key, with the same certificate, but in another database, then when you insert values from the first database, it can not be deciphered at the NULL output either.

UPDATE 2:

The goal I'm pursuing, I need to encode a column in one table, then copy the encoded values using Ctrl+C to another table in another database, insert these values with the INSERT VALUES command, and decode them.

If my understanding of the problem is incorrect, tell me how I can solve it in another way within the framework of T-SQL.

--First DB, work properly:
use db_test;

--drop table db_test.dbo.tnt
create table db_test.dbo.tnt
(
    id bigint not null
)


insert into db_test.dbo.tnt
values (8001111111), (8003333333), (8002222222)


CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = 'Bazalt92!'; 


create certificate xxx
    with subject = 'xxx'


create symmetric key xxx
    with algorithm = aes_256
    encryption by certificate xxx


alter table tnt
    add id_encrypted varbinary(128)
go


open symmetric key xxx
    decryption by certificate xxx


UPDATE tnt
SET id_encrypted = EncryptByKey(Key_GUID('xxx'), convert(varbinary, id))
GO  

id          id_encrypted
8001111111  0x0046DEDF99E34448ABE06B52739EECFE01000000ED3EF450F91A9B71F9E9363B1EFB7DC9E17933BA9B321762664926BCA4E0C821EFC24E528DAB051FFBF1AA5F4C6AE8D9
8003333333  0x0046DEDF99E34448ABE06B52739EECFE0100000041BD3CDA540CC85ACF81D16D2807486FA3B97534620C5B9B0800D5A764E39AABDFE567143B48431EB375871261282365
8002222222  0x0046DEDF99E34448ABE06B52739EECFE01000000653FAA82454CDA429108F45F10A86A72D5D52F7BC10A5AA6DB8AE74B39BF5280AC2883C937A0D9AD33E701748D19D524
--------------------------------------------------
insert into tnt(id, id_encrypted)
values 
 (8001111111, 0x0046DEDF99E34448ABE06B52739EECFE01000000FED846877DA0183619888D1C2C57B07EB4AA013A8B1D8A992B7D71610BA43834F2FBE5E2243B7B7DE0C60ED49FFF6A85)
,(8003333333, 0x0046DEDF99E34448ABE06B52739EECFE01000000C60266AED3C3D25D882282B9121719A7D8AFAF51D5D03719F6146609BF915D4FBE8E38202EF68689E5C98C8C76BCA6BC)
,(8002222222, 0x0046DEDF99E34448ABE06B52739EECFE01000000D0F0C432E0998487AF358CEC405651C0DE7BCE31AB2E746EC52BA5E2D560FF5BE1CA088D88E74D7DB9D355A85CAD8954)
--------------------------------------------------
select * from tnt
--------------------------------------------------
select id, convert(bigint, decryptbykey(id_encrypted))
from tnt

After that I'm trying to do the same, but in another DB:

--CODE FOR SECOND DB
use TMP_BASE;
--drop table dbo.tnt
create table dbo.tnt
(
    id bigint not null
)

--------------------------------------------------
CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = 'Bazalt92!'; 
--------------------------------------------------
create certificate xxx
    with subject = 'xxx'
--------------------------------------------------
create symmetric key xxx
    with algorithm = aes_256
    encryption by certificate xxx
--------------------------------------------------
alter table tnt
    add id_encrypted varbinary(128)
go
--------------------------------------------------
open symmetric key xxx
    decryption by certificate xxx
--------------------------------------------------
insert into tnt(id, id_encrypted)
values 
 (8001111111, 0x0046DEDF99E34448ABE06B52739EECFE01000000FED846877DA0183619888D1C2C57B07EB4AA013A8B1D8A992B7D71610BA43834F2FBE5E2243B7B7DE0C60ED49FFF6A85)
,(8003333333, 0x0046DEDF99E34448ABE06B52739EECFE01000000C60266AED3C3D25D882282B9121719A7D8AFAF51D5D03719F6146609BF915D4FBE8E38202EF68689E5C98C8C76BCA6BC)
,(8002222222, 0x0046DEDF99E34448ABE06B52739EECFE01000000D0F0C432E0998487AF358CEC405651C0DE7BCE31AB2E746EC52BA5E2D560FF5BE1CA088D88E74D7DB9D355A85CAD8954)
--------------------------------------------------
select * from tnt
--------------------------------------------------
/*RETURN NULLS*/
select id, convert(bigint, decryptbykey(id_encrypted))
from tnt

Answer 1

Few things:

• 1st: please use semicolons (it is good practice and in the future it will be necessary)
• 2nd: read SQL Server and Database Encryption Keys and make sure that you have backups of your SMK,DMK, ...
• 3rd: Create Identical Symmetric Keys on Two Servers

You could add KEY_SOURCE and IDENTITY_VALUE:

CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = 'Bazalt92!'; 

CREATE CERTIFICATE xxx
    WITH SUBJECT = 'xxx';

CREATE SYMMETRIC KEY xxx
WITH ALGORITHM = aes_256,
KEY_SOURCE = 'My key generation bits. This is a shared secret!',  
IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'
ENCRYPTION BY CERTIFICATE xxx;