SQL Server2012中的安全.bak文件

Question

请给我一个完整的方法来保护我的.bak文件使用SQL Server2012使用密码或加密。我试过证书的方式，但它不起作用！

BACKUP DATABASE t2 
TO DISK = 'A:\test3.bak' 

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pwd1';
GO

USE MASTER
GO
CREATE CERTIFICATE testEncCer
    WITH SUBJECT = 'test Backup Encrytion Certificate3';
GO

ALTER DATABASE t2
    SET ENCRYPTION ON;
    GO

USE t2
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE testEncCer

BACKUP CERTIFICATE testEncCer
  TO FILE = '/var/opt/mssql/data/testEncCer.cer'  
  WITH PRIVATE KEY   
  (  
      FILE = '/var/opt/mssql/data/testEncCer.pvk',  
      ENCRYPTION BY PASSWORD = 'pwd1'  
  );  
  GO

Answer 1

您可以备份数据库，临时/aux恢复它(在另一台服务器上或在同一服务器上，使用不同的名称)，TDE (透明加密)临时/辅助数据库，并备份临时/辅助数据库，还添加了媒体密码。最终备份将受媒体密码保护，并且需要证书才能恢复。原始数据库仍可运行，且未加密。

create database mytestdb
go

select *
into mytestdb.dbo.supersensitivedata
from master.dbo.spt_values;
go

select *
from mytestdb.dbo.supersensitivedata
go

--(aux) backup the database (for recreating a temp/aux db)
backup database mytestdb to disk = 'c:\temp\mytestdb_aux_full.bak'
go

--restore from the backup with a diff dbname
restore database mytestdbtde from disk = 'c:\temp\mytestdb_aux_full.bak'
with 
move 'mytestdb' to 'c:\temp\mytestdb.mdf',
move 'mytestdb_log' to 'c:\temp\mytestdb_log.ldf';
go

--create master cert&transparently encrypt the new db
use master
go

CREATE CERTIFICATE tdeEncCer
    WITH SUBJECT = 'testdb database Encryption Certificate';
GO
--backup the cert
BACKUP CERTIFICATE tdeEncCer
  TO FILE = 'c:\temp\tdeEncCer.cer'  
  WITH PRIVATE KEY   
  (  
      FILE = 'c:\temp\tdeEncCer.pvk',  
      ENCRYPTION BY PASSWORD = 'pwd1'  
  );  
  GO

--tde the secondary/aux db
use mytestdbtde
go

CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_128  
ENCRYPTION BY SERVER CERTIFICATE tdeEncCer;  
GO 

ALTER DATABASE mytestdbtde  
SET ENCRYPTION ON;  
GO 

--backup again and also protect the backup with a mediapassword
backup database mytestdbtde to disk='c:\temp\mytestdb_tde_full.bak' with mediapassword='123456'
go

--drop the aux db
use master
go
drop database mytestdbtde
go

--lets read the backup file
restore filelistonly from disk='c:\temp\mytestdb_tde_full.bak';--access denied
--super secure mediapassword, we can read the bak
restore filelistonly from disk='c:\temp\mytestdb_tde_full.bak' with mediapassword='123456';

--simulate another server, which does not have the tdeEncCer
--drop tdeEncCer 
drop certificate tdeEncCer;
--super secure mediapassword....but the other server does not have the cert
restore filelistonly from disk='c:\temp\mytestdb_tde_full.bak' with mediapassword='123456';

--cleanup
drop database mytestdb;
--delete the files in c:\temp