重庆思庄Oracle、Redhat认证学习论坛

标题: 实现SQLSERVER透明数据加密 [打印本页]

作者: 郑全 时间: 2023-2-17 20:17
标题: 实现SQLSERVER透明数据加密
有时数据库不想让别人直接拖库，我们就可以使用透明加密来解决。下面直接介绍SQLSERVER透明加密的步骤，以及异机恢复步骤：

1.创建SQLSERVER MASTER的MASTER KEY和CERTIFICATE
  USE master;
  go

  --看一下master是否被加密
  select name,is_master_key_encrypted_by_server
from sys.database;

  --创建master下的主数据库密钥
create master key encryption by password=N'sztech@4U';

--查看master的密钥信息
select * from sys.symmetric_keys；

--创建证书保护数据库加密密钥（dek)
create certificate master_server_certficate with
   subject=N'Master protect DEK Certificate';

2.创建一个测试库 sztechdb ,该数据库将会开启tde 加密：
  create database sztechdb;
  go

3.启用sztechdb 数据库加密密钥(对称密钥)
  use sztechdb;
  go

  create database encryption key with algorithm=aes_128
encryption by server certificate master_server_cetficate;

4.备份master 数据库的master key 和证书
  use master;
  go

  open master key decryption by password='sztech@4U';

  backup certificate master_server_certficate to file ='d:\mm\master_server_certficate.cer'
with private key (
   file='d:\mm\master_server_certficate.pvk',
   encryption by password='sztech@4U');

  go

  use master;
  go

  --备份数据库主密钥(master)
  open master key decryption by password='sztech@4U';
  backup master key to file='d:\mm\master.cer'
  encryption by password='sztech@4U');

可以去确认在 d:\mm\ 下应该出现三个文件，保存好这三个文件：
master.cer
master_server_certficate.cer
master_server_certficate.pvk

5.开启测试数据库sztechdb 的TDE加密
  use sztechdb
  go

  --生产环境，设置成单用户再运行加密
  alter database sztechdb set single_user with rollback immediate;
  go

  --开启tde 加密
  alter database sztechdb set encryption on;
  go

  --设置多用户访问
alter database sztechdb set multi_user with rollback immediate;
go

6.查看sztechdb是否加密
select DB_NAME(database_id),encryption_state
   from sys.dm_database_encryption_keys;

7.备份
  use master;
  go

  open master key decryption by password=N'sztech@4U';

  backup database sztechdb to disk='d:\bakdb\sztechdb.bak'

8.异机恢复
  8.1 把前面备份的三个文件拷贝到新机器，如果说这三个文件没有了，就只有呵呵了。
  8.2 先还原master key
      use master
      go

   restore master key
      from file ='d:\mm\master.cer'
   decryption by password='sztech@4U'
   encryption by password='sztech@4U';

   go

8.3 还原证书
use master;
go

--打开数据库连接master key
open master key decryption by password=N'sztech@4U';

  --创建证书
create certificate master_server_cetficate
   from file='d:\mm\master_server_certficate.cer'
   with private key (file='d:\mm\master_server_certficate.pvk',
                           decryption by password='sztech@4U');

go

8.4 还原数据库 sztechdb
  use master;
  go

  open master key decryption by password=N'sztech@4U';
  restore database sztechdb from disk='d:\bakdb\sztechdb.bak'
with move 'sztechdb' to 'd:\data\sztechdb.mdf',
         move 'sztechdb_log' to 'd:\log\sztechdb_log.ldf'

  go

8.5 附加启用了tde 的数据库
  use master;
  go

open master key decryption by password=N'sztech@4U';
--附加数据库
create database sztechdb
   on primary
   (filename=N'd:\data\sztechdb.mdf')
   log on
   (filename=N'd:\data\sztechdb_log.ldf')
for attach;

go

8.6 验证数据库文件是否有错误
use master;
go

open master key decryption by password=N'sztech@4U';
dbcc checkdb(sztechdb) with no_infomsgs

如果上面直接灭有问题，就OK了。

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)