CREATE MASTER KEY (Transact-SQL)CREATE MASTER KEY (Transact-SQL)

适用于: 是SQL Server(从 2008 开始)是Azure SQL 数据库是Azure SQL 数据仓库是并行数据仓库APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

创建数据库主密钥。Creates a database master key.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Parallel Data Warehouse  

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'  
[ ; ]  
-- Syntax for Azure SQL Database and Azure SQL Data Warehouse  

CREATE MASTER KEY [ ENCRYPTION BY PASSWORD ='password' ]
[ ; ]  

参数Arguments

PASSWORD ='password'PASSWORD ='password'
用于加密数据库主密钥的密码。Is the password that is used to encrypt the master key in the database. password 必须符合运行 SQL ServerSQL Server 实例的计算机的 Windows 密码策略要求。password must meet the Windows password policy requirements of the computer that is running the instance of SQL ServerSQL Server. password 在 SQL DatabaseSQL DatabaseSQL 数据仓库SQL Data Warehouse 中是可选的。password is optional in SQL DatabaseSQL Database and SQL 数据仓库SQL Data Warehouse.

RemarksRemarks

数据库主密钥是指用于保护证书私钥的对称密钥以及数据库中存在的非对称密钥。The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. 当创建主密钥时,会使用 AES_256 算法以及用户提供的密码对其进行加密。When it is created, the master key is encrypted by using the AES_256 algorithm and a user-supplied password. SQL Server 2008SQL Server 2008SQL Server 2008 R2SQL Server 2008 R2 中,不使用 Triple DES 算法。In SQL Server 2008SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 R2, the Triple DES algorithm is used. 若要启用主密钥的自动解密功能,请使用服务主密钥对该主密钥的副本进行加密,并将副本存储在数据库和 master 中。To enable the automatic decryption of the master key, a copy of the key is encrypted by using the service master key and stored in both the database and in master. 通常,每当主密钥更改时,便会在不进行提示的情况下更新存储在 master 中的副本。Typically, the copy stored in master is silently updated whenever the master key is changed. 可以使用 ALTER MASTER KEY 的 DROP ENCRYPTION BY SERVICE MASTER KEY 选项对该默认行为进行更改。This default can be changed by using the DROP ENCRYPTION BY SERVICE MASTER KEY option of ALTER MASTER KEY. 必须使用 OPEN MASTER KEY 语句和密码打开未使用服务主密钥进行加密的主密钥。A master key that is not encrypted by the service master key must be opened by using the OPEN MASTER KEY statement and a password.

master 中 sys.databases 目录视图的 is_master_key_encrypted_by_server 列指示是否使用服务主密钥对数据库主密钥进行加密。The is_master_key_encrypted_by_server column of the sys.databases catalog view in master indicates whether the database master key is encrypted by the service master key.

可以在 sys.symmetric_keys 目录视图中查看有关数据库主密钥的信息。Information about the database master key is visible in the sys.symmetric_keys catalog view.

对于 SQL Server 和并行数据仓库,通常用服务主密钥和至少一个密码保护主密钥。For SQL Server and Parallel Data Warehouse, the Master Key is typically protected by the Service Master Key and at least one password. 如果以物理方式(日志传送、还原备份等)将数据库移动到另一个服务器,数据库将包含主密钥的两份副本,一份由原始服务器服务主密钥进行加密(除非已使用 ALTER MASTER KEY DDL 显式删除此加密),一份由在 CREATE MASTER KEY 或后续 ALTER MASTER KEY DDL 操作过程中指定的每个密码进行加密。In case of the database being physically moved to a different server (log shipping, restoring backup, etc.), the database will contain a copy of the master Key encrypted by the original server Service Master Key (unless this encryption was explicitly removed using ALTER MASTER KEY DDL), and a copy of it encrypted by each password specified during either CREATE MASTER KEY or subsequent ALTER MASTER KEY DDL operations. 移动数据库后,若要恢复主密钥以及所有数据(使用主密钥加密为密钥层次结构中的根),用户需要通过用于保护主密钥的其中一个密码,使用 OPEN MASTER KEY 语句在新服务器上还原主密钥的备份,或在新服务器上还原原始服务主密钥的备份。In order to recover the Master Key, and all the data encrypted using the Master Key as the root in the key hierarchy after the database has been moved, the user will have either use OPEN MASTER KEY statement using one of the password used to protect the Master Key, restore a backup of the Master Key, or restore a backup of the original Service Master Key on the new server.

对于 SQL DatabaseSQL DatabaseSQL 数据仓库SQL Data Warehouse,当数据库从一个服务器移动到另一个服务器时,密码保护不能视为防止数据丢失的安全机制,因为主密钥的服务主密钥保护由 Microsoft Azure 平台进行管理。For SQL DatabaseSQL Database and SQL 数据仓库SQL Data Warehouse, the password protection is not considered to be a safety mechanism to prevent a data loss scenario in situations where the database may be moved from one server to another, as the Service Master Key protection on the Master Key is managed by Microsoft Azure platform. 因此,主密钥密码在 SQL DatabaseSQL DatabaseSQL 数据仓库SQL Data Warehouse 中是可选的。Therefore, the Master Key password is optional in SQL DatabaseSQL Database and SQL 数据仓库SQL Data Warehouse.

重要

你应该使用 BACKUP MASTER KEY 备份主密钥,并将备份存储于另外一个安全的位置中。You should back up the master key by using BACKUP MASTER KEY and store the backup in a secure, off-site location.

服务主密钥和数据库主密钥是通过使用 AES-256 算法进行保护的。The service master key and database master keys are protected by using the AES-256 algorithm.

PermissionsPermissions

要求对数据库具有 CONTROL 权限。Requires CONTROL permission on the database.

示例Examples

以下示例为当前数据库创建数据库主密钥。The following example creates a database master key for the current database. 该密钥使用密码 23987hxJ#KL95234nl0zBe 进行加密。The key is encrypted using the password 23987hxJ#KL95234nl0zBe.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';  
GO  

另请参阅See Also

sys.symmetric_keys (Transact-SQL) sys.symmetric_keys (Transact-SQL)
sys.databases (Transact-SQL) sys.databases (Transact-SQL)
OPEN MASTER KEY (Transact-SQL) OPEN MASTER KEY (Transact-SQL)
ALTER MASTER KEY (Transact-SQL) ALTER MASTER KEY (Transact-SQL)
DROP MASTER KEY (Transact-SQL) DROP MASTER KEY (Transact-SQL)
CLOSE MASTER KEY (Transact-SQL) CLOSE MASTER KEY (Transact-SQL)
加密层次结构Encryption Hierarchy