SQLSERVER SQL TDE数据库加密操作

SQLSERVER SQL TDE数据库加密操作

一、创建master数据库下的主数据库密钥

在Transact-SQL下执行
USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'master主数据密钥密码';

二、创建证书用来保护数据库加密密钥

在Transact-SQL下执行
USE master
CREATE CERTIFICATE master_server_cert WITH SUBJECT = N'证书的名称';

三、创建数据库

提示：如果加密现有的数据库可以跳过这一步

在Transact-SQL下执行
CREATE DATABASE  db_encryption_test;

四、创建要加密的数据库的密钥

（以下两种操作选择其中一种就可以完成操作）

1、在Transact-SQL下执行

USE db_encryption_test;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

2、在SQL Server Management Studio操作
选择要加密的数据库右键单击–>任务–>管理数据库加密

选择要加密的算法
选择服务器证书

五、加密数据库

（以下两种操作选择其中一种就可以完成操作）
1 脚本执行
在Transact-SQL下执行
USE db_encryption_test;
ALTER DATABASE db_encryption_test SET ENCRYPTION ON;

2、在SQL Server Management Studio操作
打开之前的管理数据库加密
对话框勾选【将数据库加密设置为ON(S)】

用SQL Server Management Studio可以和上一步一起操作。

以上操作数据库加密完成

打开数据库属性对话框时，选择【选项】可以看到【已启用加密】项为True则证明数据库已经加密
SQLSERVER SQL TDE数据库解密操作

逆向解密操作

一、解密数据库

USE db_encryption_test;
ALTER DATABASE db_encryption_test SET ENCRYPTION OFF;

执行完成后数据库会显示为未加密状态
可以执行

select name,is_master_key_encrypted_by_server from 	sys.databases

查看哪些数据库被加密

二、删除数据库密钥

USE db_encryption_test;
DROP DATABASE ENCRYPTION KEY;

可以执行

SELECT * FROM master.sys.dm_database_encryption_keys

查看哪些数据库还存在密钥

三、删除证书

USE master;
DROP CERTIFICATE MyServerCert(证书名);
可以执行SELECT * FROMmaster.sys.certificates查看都有哪些证书

四、删除主数据库密钥

USE master;
DROP MASTER KEY;
可以执行select * from master.sys.key_encryptions查看主密钥是否被删除

SQLSERVER SQL TDE数据库备份加密证书

USE master;
BACKUP CERTIFICATE master_server_cert TO FILE = 'D:\MSSQL\Certificate\master_server_cert.cer' 
WITH PRIVATE KEY ( 
FILE = 'D:\MSSQL\Certificate\master_server_cert.pvk' ,
ENCRYPTION BY PASSWORD = '123456789' );

当执行加密数据库的时候会提示你需要备份证书。

SQLSERVER SQL TDE数据库异地附加数据库

一、在TDE加密环境下分离数据库

execmaster.dbo.sp_detach_db @dbname = N'db_encryption_test ';

分离数据库完成后，将数据库文件和日志文件拷贝到新环境进行附加；

二、在没有恢复认证的新环境下附加数据库

CREATE DATABASE db_encryption_test ON
(FILENAME = N'C:\SQLData\ db_encryption_test.mdf'),		--数据文件
(FILENAME = N'C:\SQLData\ db_encryption_test _log.ldf')	--日志文件
FOR ATTACH;

执行完成以上附加操作时，会报错：

找不到指纹为 ‘0xA7ED737B887F1707B99F4E8D1C43B54AA25AC542’ 的服务器证书。（证明数据库加密成功）

三、在新环境下创建master数据库的主数据库密钥

USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'master主数据密钥密码';

四、在新环境下恢复证书

CREATE CERTIFICATE MyServerCert
FROM FILE = N'C:\SQLData\KEY\MyServerCert.cer'
WITH PRIVATE KEY
(
	FILE = N'C:\SQLData\KEY\MyServerCert_key.pvk',
	DECRYPTION BY PASSWORD = N'123456789'
);

五、在已经恢复认证的新环境下附加数据库

CREATE DATABASE db_encryption_test ON
(FILENAME = N'C:\SQLData\ db_encryption_test.mdf'),		--数据文件
(FILENAME = N'C:\SQLData\ db_encryption_test _log.ldf')	--日志文件
FOR ATTACH;

附加成功

SQLSERVER SQL TDE数据库异地还原数据库

一、在TDE加密环境下备份数据库

BACKUP DATABASE db_encryption_testTO DISK = N'D:\db_encryption_test.bak'

二、在没有恢复认证的新环境下还原数据库

RESTORE DATABASE db_encryption_test FROM DISK = N'C:\SQLData\db_encryption_test.bak'
WITH RECOVERY,
MOVE N'db_encryption_test' TO N'C:\SQLData\db_encryption_test.mdf',
MOVE N'db_encryption_test _log TO N'C:\SQLData\db_encryption_test _log.ldf';

执行完成以上附加操作时，会报错：

找不到指纹为 ‘0xA7ED737B887F1707B99F4E8D1C43B54AA25AC542’ 的服务器证书。（证明数据库加密成功）

三、在新环境下创建master数据库的主数据库密钥

USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'master主数据密钥密码';

四、在新环境下恢复证书

CREATE CERTIFICATE MyServerCert
FROM FILE = N'C:\SQLData\KEY\MyServerCert.cer'
WITH PRIVATE KEY
(
	FILE = N'C:\SQLData\KEY\MyServerCert_key.pvk',
	DECRYPTION BY PASSWORD = N'123456789'
);

五、在已经恢复认证的新环境下还原数据库

RESTORE DATABASE db_encryption_test FROM DISK = N'C:\SQLData\db_encryption_test.bak'
WITH RECOVERY,
MOVE N'aa' TO N'C:\SQLData\db_encryption_test.mdf',
MOVE N'aa_log' TO N'C:\SQLData\db_encryption_test _log.ldf';

还原成功
以上操作说明：SQL SERVER的TDE加密针对于数据库文件、日志文件和备份文件进行加密。
当没有备份证书或者忘记证书的密码或者证书丢失都会造成无法异地恢复的问题但是可以本地恢复。所以证书和密码要保存好并且。
当用户拥有master数据库的db_owner权限或者拥有服务器级别的sysadmin权限可以导出证书。
如果数据库在证书备份之后再进行加密，也可以做异地恢复。