OPEN MASTER KEY (Transact-SQL)

Opens the Database Master Key of the current database.

Topic link iconTransact-SQL Syntax Conventions

Syntax

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password' 

Arguments

  • 'password'
    The password with which the Database Master Key was encrypted.

Remarks

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 to a new instance of SQL Server, 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. Once the Database Master Key has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY statement to provision the server with a copy of the Database Master Key encrypted with the Service Master Key. This option is discussed in ALTER MASTER KEY (Transact-SQL).

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.

Permissions

Requires CONTROL permission on the database.

Examples

The following example opens the Database Master Key of the AdventureWorks database, which has been encrypted with a password.

USE AdventureWorks;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '43987hkhj4325tsku7';
GO

See Also

Reference

CREATE MASTER KEY (Transact-SQL)
CLOSE MASTER KEY (Transact-SQL)
BACKUP MASTER KEY (Transact-SQL)
RESTORE MASTER KEY (Transact-SQL)
ALTER MASTER KEY (Transact-SQL)
DROP MASTER KEY (Transact-SQL)

Other Resources

Encryption Hierarchy

Help and Information

Getting SQL Server 2005 Assistance