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

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是平行處理資料倉儲Parallel Data Warehouseyes平行處理資料倉儲Parallel Data Warehouse適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是平行處理資料倉儲Parallel Data Warehouseyes平行處理資料倉儲Parallel Data Warehouse

在 master 資料庫中建立資料庫主要金鑰。Creates a database master key in the master database.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

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

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法,請參閱舊版文件To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

引數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. SQL DatabaseSQL DatabaseAzure Synapse Analytics (SQL 資料倉儲)Azure Synapse Analytics (SQL Data Warehouse) 中,password 為選擇性。password is optional in SQL DatabaseSQL Database and Azure Synapse Analytics (SQL 資料倉儲)Azure Synapse Analytics (SQL Data Warehouse).

備註Remarks

資料庫主要金鑰是一個用來保護憑證私密金鑰和資料庫中非對稱金鑰的對稱金鑰。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 passwords 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 DatabaseAzure Synapse Analytics (SQL 資料倉儲)Azure Synapse Analytics (SQL Data Warehouse),在資料庫可以從某部伺服器移到另一部伺服器的情況下,密碼保護並非避免資料遺失的安全機制,因為主要金鑰上的服務主要金鑰保護是由 Microsoft Azure 平台來管理。For SQL DatabaseSQL Database and Azure Synapse Analytics (SQL 資料倉儲)Azure Synapse Analytics (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 DatabaseAzure Synapse Analytics (SQL 資料倉儲)Azure Synapse Analytics (SQL Data Warehouse) 中為選擇性。Therefore, the Master Key password is optional in SQL DatabaseSQL Database and Azure Synapse Analytics (SQL 資料倉儲)Azure Synapse Analytics (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.

權限Permissions

需要資料庫的 CONTROL 權限。Requires CONTROL permission on the database.

範例Examples

使用下列範例來在 master 資料庫中建立資料庫主要金鑰。Use the following example to create a database master key in the masterdatabase. 這個金鑰是利用密碼 23987hxJ#KL95234nl0zBe 來加密的。The key is encrypted using the password 23987hxJ#KL95234nl0zBe.

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

另請參閱See Also