将加密的列复制到另一个数据库SQL Server 2012 [英] Copy the encrypted column to another database SQL Server 2012
问题描述
我已经创建了一个数据库，并且它具有一个带有加密列的表.
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.
更新1:
有一个带有 TNT 表的 DB_TEST 数据库，其中有两列，其中一列是加密的.如果您从此列中提取了多个值，则可以对它们进行解码并获得所需的结果.
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.
但是，如果在另一个数据库中使用相同的加密密钥，相同的证书创建完全相同的表，则在从第一个数据库插入值时，也不能在NULL输出中对它进行解密.
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.
更新2:
我要追求的目标，我需要在一个表中对一列进行编码，然后使用 Ctrl + C 将编码后的值复制到另一个数据库中的另一个表中，并用
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.
如果我对问题的理解不正确，请告诉我如何在T-SQL框架内以另一种方式解决它.
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
推荐答案
几件事:
- 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
您可以添加 KEY_SOURCE 和 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;
这篇关于将加密的列复制到另一个数据库SQL Server 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
