| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
OPEN MASTER KEY (Transact-SQL) |
OPEN MASTER KEY (Transact-SQL) |
VanMSFT |
vanto |
03/14/2017 |
sql |
t-sql |
reference |
|
|
|
>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE sql-asdb-asdbmi-asa-pdw]
Opens the Database Master Key of the current database.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
Note
[!INCLUDEsynapse-analytics-od-unsupported-syntax]
[!INCLUDEsql-server-tsql-previous-offline-documentation]
'password'
The password with which the Database Master Key was encrypted.
If the database master key was encrypted with the service master key, it will be automatically opened when it is needed for decryption or encryption. In this case, it is not necessary to use the OPEN MASTER KEY statement.
When a database is first attached or restored to a new instance of [!INCLUDEssNoVersion], a copy of the database master key (encrypted by the service master key) is not yet stored in the server. You must use the OPEN MASTER KEY statement to decrypt the database master key (DMK). Once the DMK has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY REGENERATE statement to provision the server with a copy of the DMK, encrypted with the service master key (SMK). When a database has been upgraded from an earlier version, the DMK should be regenerated to use the newer AES algorithm. For more information about regenerating the DMK, see ALTER MASTER KEY (Transact-SQL). The time required to regenerate the DMK key to upgrade to AES depends upon the number of objects protected by the DMK. Regenerating the DMK key to upgrade to AES is only necessary once, and has no impact on future regenerations as part of a key rotation strategy.
You can exclude the Database Master Key of a specific database from automatic key management by using the ALTER MASTER KEY statement with the DROP ENCRYPTION BY SERVICE MASTER KEY option. Afterward, you must explicitly open the Database Master Key with a password.
If a transaction in which the Database Master Key was explicitly opened is rolled back, the key will remain open.
Requires CONTROL permission on the database.
The following example opens the Database Master Key of the [!INCLUDE sssampledbobject-md] database, which has been encrypted with a password.
USE AdventureWorks2022;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '43987hkhj4325tsku7';
GO Examples: [!INCLUDEssazuresynapse-md] and [!INCLUDEssPDW]
The following example opens the database master, which has been encrypted with a password.
USE master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '43987hkhj4325tsku7';
GO
CLOSE MASTER KEY;
GO