Sdílet prostřednictvím

  • Článek

sp_control_dbmasterkey_password (Transact-SQL)

Adds or drops a credential containing the password needed to open a database master key.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_control_dbmasterkey_password @db_name='database_name',@password='master_key_password',@action= { 'add' | 'drop' }

Arguments

  • @db\_name=N'database_name'
    Specifies the name of the database associated with this credential. Cannot be a system database. database_name is nvarchar.

  • @password=N'password'
    Specifies the password of the master key. password is nvarchar.

  • @action=N'add'
    Specifies that a credential for the specified database will be added to the credential store. The credential will contain the password of the database master key. The value passed to @action is nvarchar.

  • @action=N'drop'
    Specifies that a credential for the specified database will be dropped from the credential store. The value passed to @action is nvarchar.

Remarks

When SQL Server needs a database master key to decrypt or encrypt a key, SQL Server tries to decrypt the database master key with the service master key of the instance. If the decryption fails, SQL Server searches the credential store for master key credentials that have the same family GUID as the database for which it needs the master key. SQL Server then tries to decrypt the database master key with each matching credential until the decryption succeeds or there are no more credentials.

Warning

Do not create a master key credential for a database that must be inaccessible to sa and other highly-privileged server principals. You can configure a database so that its key hierarchy cannot be decrypted by the service master key. This option is supported as a defense-in-depth for databases that contain encrypted information that should not be accessible to sa or other highly privileged server principals. Creating a master key credential for such a database removes this defense-in-depth, enabling sa and other highly privileged server principals to decrypt the database.

Credentials that are created by using sp_control_dbmasterkey_password are visible in the sys.master_key_passwords catalog view. The names of credentials that are created for database master keys have the following format: ##DBMKEY_<database_family_guid>_<random_password_guid>##. The password is stored as the credential secret. For each password added to the credential store there is a row in sys.credentials.

You cannot use sp_control_dbmasterkey_password to create a credential for the following system databases: master, model, msdb, or tempdb.

sp_control_dbmasterkey_password does not verify that the password can open the master key of the specified database.

If you specify a password that is already stored in a credential for the specified database, sp_control_dbmasterkey_password will fail.

Note

Two databases from different server instances can share the same family GUID. If this occurs, the databases will share the same master key records in the credential store.

Parameters passed to sp_control_dbmasterkey_password do not appear in traces.

Note

When you are using the credential that was added by using sp_control_dbmasterkey_password to open the database master key, the database master key is re-encrypted by the service master key. If the database is in read-only mode, the re-encryption operation will fail, and the database master key will remain unencrypted. For subsequent access to the database master key, you must use the OPEN MASTER KEY statement and a password. To avoid using a password, create the credential before you move the database to read-only mode.

Permissions

Requires CONTROL permission on the database.

Examples

A. Creating a credential for the AdventureWorks master key

The following example creates a credential for the AdventureWorks database master key, and saves the master key password as the secret in the credential. Because all parameters that are passed to sp_control_dbmasterkey_password must be of data type nvarchar, the text strings are converted with the casting operator N.

EXEC sp_control_dbmasterkey_password @db_name = N'AdventureWorks', 
    @password = N'sdfjlkj#mM00sdfdsf98093258jJlfdk4', @action = N'add';
GO

B. Dropping a credential for a database master key

The following example removes the credential created in example A. Note that all parameters are required, including the password.

EXEC sp_control_dbmasterkey_password @db_name = N'AdventureWorks', 
    @password = N'sdfjlkj#mM00sdfdsf98093258jJlfdk4', @action = N'drop';
GO

Change History

Updated content

Added the Note about using sp_control_dbmasterkey_password to open the database master key.