sql server 通明加密
在 master 数据库中,添加 数据库主密钥:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$$test$$';
在 master 数据库中,添加 加密数据库用的证书:
USE master;
CREATE CERTIFICATE TestCert WITH SUBJECT = '测试证书';
首先要从 master 数据库中,备份加密证书:
USE master;
BACKUP CERTIFICATE TestCert TO FILE = 'D:\TestCert.cer'
WITH PRIVATE KEY ( FILE = 'D:\TestCert.pkey', ENCRYPTION BY PASSWORD = '$$certpwd$$' );
在 要加密的数据库 中,设置 证书以及加密算法:
USE 数据库名称
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TestCert;
对 要加密的数据库 启用加密
ALTER DATABASE 数据库名称 SET ENCRYPTION ON;
想要查看当前数据库服务器中有哪些数据库已被加密,可执行以下语句:
SELECT DB_NAME(database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys;
删除
DROP DATABASE ENCRYPTION KEY
drop CERTIFICATE TestCert
drop master key
异地还原加密数据库
--1.还原设置数据库主密匙
USE master CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$$test$$';
--2、还原备份的证书(把证书拷贝过来)
USE master;
CREATE CERTIFICATE TestCert FROM FILE = 'D:\TestCert.cer'
WITH PRIVATE KEY ( FILE = 'D:\TestCert.pkey', DECRYPTION BY PASSWORD = '$$certpwd$$');
资料
https://blog.csdn.net/chelen_jak/article/details/78936240
https://www.jb51.net/article/43799.htm