次の方法で共有

如何把一个有加密数据的数据库迁移到其他服务器上

  • [アーティクル]

 

从SQL Server 2005开始，SQL Server进入了加密数据的功能来数据。下面是一段示例T-SQL代码来展示SQL Server如何保护数据的。

USE [master]

GO

Create database testencryption

go

USE testencryption;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'First p@55w0Rd'

GO

CREATE CERTIFICATE HumanResources037

WITH SUBJECT = 'Employee Social Security Numbers',

START_DATE = '01/01/2012';;

GO

CREATE SYMMETRIC KEY SSN_Key_01 WITH

KEY_SOURCE = 'shared_secret!',

ALGORITHM = AES_256,

IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'

ENCRYPTION BY CERTIFICATE HumanResources037;

GO

Create TABLE TestEncryptionColumn

(NationalIDNumber nvarchar (15),

EncryptedNationalIDNumber varbinary(128));

GO

Insert TestEncryptionColumn values ('12345678',NULL)

Go

-- 加密数据.

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;

UPDATE TestEncryptionColumn

SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),

NationalIDNumber);

GO

 

-- 验证数据可以被正确解密

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;

go

SELECT NationalIDNumber, EncryptedNationalIDNumber

AS 'Encrypted ID Number',

CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))

AS 'Decrypted ID Number'

FROM TestEncryptionColumn;

GO

在这个示例中，我们使用密码来保护database master key，然后用database master key加密证书（其实是加密证书中的私钥），再使用证书来加密对称密钥，最后使用对称密钥来加密数据。这是一个很常见的的加密链条。

你可能会注意到一个有意思的现象：要解密数据数据，你只需要打开对称密钥就可以了，你不需要使用之前设置的密码来打开database master key。那么我们为什么还要设置密码呢？没有密码的保护，数据还是安全的吗？

要理解这个问题，我们先要了解下SQL Server加密系统的层次架构。

从这个架构来看，其实最简单的加密方法是使用密码直接保护对称密钥，然后用对称密钥来加密数据。但是加密提供的保护比较简单，数据容易被攻破。在不考虑性能影响的前提下，我们可以使得加密链条尽可能的长来获得更强的保护。

而service key是由DP API (Data Protection API)来加密。DP API也可以使用两种方法来加密service key: （1）SQL Server启动账户（2）安装SQL Server这台服务器的Machine account。如果使用SQL Server configuration manager来修改启动账号的话，configuration manager会先用老的密码解密service master key，再使用新密码来加密service master key。如果使用service control manager来修改账号的话，就会导致无法使用启动账号密码来解密service master key了。幸运的是，此时还有machine account可以用来解密service master key，所以你加密的数据还是可以被访问。如果一旦machine account也由于某些原因发生了变化，那么整个加密链条就头上就断了，其结构就是所有的加密数据都不能访问。所以我们一直推荐使用configuration manager来修改启动账号。

你可以发现，database master key也有两种方法来保护：（1）密码（2）service master key。上面的示例代码其实就是sysadmin用户直接用service master key打开database master key进而访问数据，所以就不需要密码。有的客户基于安全的考虑会把service master key的保护去除掉，这样sysadmin也无法通过打开service master key的方式来访问数据。要访问数据就一定要知道密码。

要去除service master key的保护，可以运行语句

ALTERMASTERKEYDROPENCRYPTIONBYSERVICEMASTERKEY

我们建议你备份service master key， database master key和证书。备份的重要性你很快就会看到。

-- 备份SMK, DMK and certificates

BACKUP SERVICE MASTER KEY TO FILE = 'c:\backup\exportedServiceMasterkey' ENCRYPTION BY PASSWORD = 'Backup p@55w0Rd'

Go

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'First p@55w0Rd';

BACKUP MASTER KEY TO FILE = 'c:\backup\exportedmasterkey' ENCRYPTION BY PASSWORD = 'Backup p@55w0Rd';

GO

BACKUP CERTIFICATE HumanResources037 TO FILE = 'c:\backup\HumanResources037Certificate' WITH PRIVATE KEY ( FILE = 'c:\backup\CertificatePrivateKey' ,ENCRYPTION BY PASSWORD = 'Backup p@55w0Rd' );

GO

 

如果我们要把一个有加密数据的数据库迁移到一台新的服务器上，有什么特别的步骤需要做吗？

如果你知道database master key的密码，你什么都不需要做。你只需要在新的服务器上还原数据库，然后使用密码打开database master key：

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'First p@55w0Rd'

然后就可以正常打开迁移后数据库中的加密数据了。

如果你不想每次都用密码打开database master key，你可以使用以下语句，在新服务器上使用service master key来保护database master key：

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'First p@55w0Rd'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

这样sysadmin用户就可以直接使用service master key来打开database master key进而打开对称密钥来访问加密数据，而不需要使用database master key的密码。注意，运行Add encryption语句前是一定要用密码打开database master key的，也就是说你一定要记得住你的密码。

如果Database Master Key密码忘记了怎么办？如何迁移数据库？

如果你从来没有去除过service master key的保护的话，你是很安全的。你只需要用sysadmin登录原服务器的SQL Server再运行一下命令来生成一个新的密码：

Alter Master Key Regenerate With Encryption By Password = 'NewPassword'

然后就可以按照上面的办法来迁移数据库了。

如果在原数据库上你没有去除过service master key的保护，但你又不想在原服务器上生成新密码（可能原服务器是生产环境不能随便动），你可以立刻备份一个service master key或者使用之前创建的service master key的备份，然后在新服务器上依次还原service master key和数据库，同样可以实现迁移的目的。

-- 在新服务器还原service master key

RESTORE SERVICE MASTER KEY FROM FILE = 'c:\exportedServiceMasterkey' DECRYPTION BY PASSWORD = 'Backup <p@55w0Rd'>;

-- 在新服务器还原数据库

然后在新服务器上就可以打开数据并访问了。

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;

go

SELECT NationalIDNumber, EncryptedNationalIDNumber AS 'Encrypted ID Number',CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS 'Decrypted ID Number'FROM TestEncryptionColumn;

GO

如果你忘记了Database Master Key密码并且已经去除了service master key的保护，还有可能打开加密数据吗？

有可能，只要你曾经备份过证书，database master key和service master key。你只要还原它们就万事ok了。

-- 假设对称密钥，证书和database master key都没了，service master key也被改掉了。此时加密数据完全打不开。

drop SYMMETRIC KEY SSN_Key_01

drop CERTIFICATE HumanResources037

drop MASTER KEY

ALTER SERVICE MASTER KEY REGENERATE;

GO

 

-- 依次还原service master key, database master key

RESTORE SERVICE MASTER KEY

FROM FILE = 'c:\backup\exportedServiceMasterkey'

DECRYPTION BY PASSWORD = 'Backup p@55w0Rd';

GO

 

RESTORE MASTER KEY FROM FILE = 'c:\backup\exportedmasterkey'

DECRYPTION BY PASSWORD = 'Backup p@55w0Rd'

ENCRYPTION BY PASSWORD = 'New p@55w0Rd'

 

-- 重建证书和对称密钥

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'New p@55w0Rd';

CREATE CERTIFICATE HumanResources037

FROM FILE = 'c:\backup\HumanResources037Certificate'

WITH PRIVATE KEY (FILE = 'c:\backup\CertificatePrivateKey',

DECRYPTION BY PASSWORD = 'Backup p@55w0Rd');

GO

CREATE SYMMETRIC KEY SSN_Key_01 WITH

KEY_SOURCE = 'shared_secret!',

ALGORITHM = AES_256,

IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'

ENCRYPTION BY CERTIFICATE HumanResources037;

GO

 

-- 现在数据又可以打开了

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;

go

SELECT NationalIDNumber, EncryptedNationalIDNumber

AS 'Encrypted ID Number',

CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))

AS 'Decrypted ID Number'

FROM TestEncryptionColumn;

GO

CLOSE SYMMETRIC KEY SSN_Key_01

GO

这个例子也适用于证书，database master key和对称密钥被误删的情况。

 

如果忘记了密码，又去除了service master key的保护，又没有备份service master key ，怎么办？

什么办法也没有。