实现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了。