ArcGIS Help 10.2 - Transparent Data Encryption for the BIS workspace in SQL Server (Bathymetry Solution)

可采取多种预防措施来协助保护数据库安全，例如设计安全系统、加密机密资产以及在数据库服务器周围建立防火墙。但如果物理介质（如驱动器或备份磁带）被盗，则恶意方可以恢复或附加数据库并浏览数据。一种解决方案是对数据库中的敏感数据进行加密，并通过一个证书来保护用于加密数据的密钥。这样便可防止任何没有密钥的用户使用该数据，但此种保护必须提前计划。

TDE 执行数据和日志文件的实时 I/O 加密与解密。此加密使用数据库加密密钥 (DEK)，该密钥存储在数据库启动记录中以供恢复期间使用。DEK 是通过使用存储在服务器主数据库中的证书进行保护的对称密钥，或者是通过 EKM 模块进行保护的不对称密钥。TDE 可以保护静止的数据，即数据和日志文件。它可用于遵守各行业中建立的多种法律、法规和准则。从而使软件开发者能够使用 AES 和 3DES 加密算法对数据进行加密，而无需更改现有应用程序。

数据库文件的加密是在页面级别执行的。已加密数据库中的页面将在写入磁盘之前进行加密，并在读取到内存时进行解密。TDE 不会增加已加密数据库的大小。

The benefits of using TDE are the following:

As a security administrator, you can be sure that sensitive data is safe in case the storage media or data file gets stolen.
Implementing TDE helps you address security-related regulatory compliance issues.
You do not need to create triggers or views to decrypt data for the authorized user or application. Data from tables is transparently decrypted for the database user and application.
Database users and applications need not be aware that the data they are accessing is stored in encrypted form. Data is transparently decrypted for the database users and applications.
Applications need not be modified to handle encrypted data. Data encryption and decryption is managed by the database.
Key management operations are automated. The user or application does not need to manage encryption keys.

有关详细信息，请参阅 MSDN 库中的透明数据加密 (TDE)。

要使用 TDE，请在 SQL Server Management Studio 中执行以下步骤。

步骤:

创建主密钥。
创建或获取受主密钥保护的证书。
创建数据库加密密钥并通过证书对其进行保护。
将数据库设置为使用加密。

Example of TDE

You can use the SQL commands below to configure TDE. You can choose the password for the master key, and when backing up the master key, you can choose the folder and file name.

注:

对示例进行复制和粘贴可能会导致语法错误。

USE master
GO
/* Verify master key */
SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%'
GO

/* if there are no records found, then it means there was no predefined Master Key. 
 To create a Master Key, you can execute the below mentioned TSQL code. */
 
/* Create master key */
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'bis$$@admin';
GO
/* Backup master key */
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'bis$$@admin';
GO
BACKUP MASTER KEY TO FILE = 'D:\mssqlbackup\master\masterkey.mk' 
    ENCRYPTION BY PASSWORD = 'bis$$@admin';
GO

/* Create Certificate */
CREATE CERTIFICATE bis_cert WITH SUBJECT = 'BIS Server Certificate';
GO

/* Verify Certificate */
SELECT * FROM sys.certificates where [name] = 'bis_cert'
GO

/* Backup certificate */
BACKUP CERTIFICATE bis_cert TO FILE = 'D:\mssqlbackup\master\bis.cer'
   WITH PRIVATE KEY (
         FILE = 'D:\mssqlbackup\master\bis.pvk',
         ENCRYPTION BY PASSWORD = 'bis$$@admin');
GO

--use bis database
USE bisdb
GO
/* Create Encryption key */
CREATE DATABASE ENCRYPTION KEY
   WITH ALGORITHM = AES_256
   ENCRYPTION BY SERVER CERTIFICATE bis_cert;
GO

/* Encrypt database */
ALTER DATABASE bisdb SET ENCRYPTION ON;
GO

/* Verify Encryption */
SELECT 
DB_NAME(database_id) AS DatabaseName
,Encryption_State AS EncryptionState
,key_algorithm AS Algorithm
,key_length AS KeyLength
FROM sys.dm_database_encryption_keys
GO
SELECT 
NAME AS DatabaseName
,IS_ENCRYPTED AS IsEncrypted 
FROM sys.databases where name ='bisdb'
GO

4/27/2014