This topic has not yet been rated - Rate this topic

ALTER ASYMMETRIC KEY (Transact-SQL)

SQL Server 2012

Changes the properties of an asymmetric key.

Topic link icon Transact-SQL Syntax Conventions


          

ALTER ASYMMETRIC KEY Asym_Key_Name <alter_option>

<alter_option> ::=
    <password_change_option> 
    | 
    REMOVE PRIVATE KEY
<password_change_option> ::=
    WITH PRIVATE KEY ( <password_option> [ , <password_option> ] )
<password_option> ::=
    ENCRYPTION BY PASSWORD = 'strongPassword'
    |
    DECRYPTION BY PASSWORD = 'oldPassword'

Arguments

Asym_Key_Name: Is the name by which the asymmetric key is known in the database.
REMOVE PRIVATE KEY: Removes the private key from the asymmetric key The public key is not removed.
WITH PRIVATE KEY: Changes the protection of the private key.
ENCRYPTION BY PASSWORD ='stongPassword': Specifies a new password for protecting the private key. password must meet the Windows password policy requirements of the computer that is running the instance of SQL Server. If this option is omitted, the private key will be encrypted by the database master key.
DECRYPTION BY PASSWORD ='oldPassword': Specifies the old password, with which the private key is currently protected. Is not required if the private key is encrypted with the database master key.

Remarks

If there is no database master key the ENCRYPTION BY PASSWORD option is required, and the operation will fail if no password is supplied. For information about how to create a database master key, see CREATE MASTER KEY (Transact-SQL).

You can use ALTER ASYMMETRIC KEY to change the protection of the private key by specifying PRIVATE KEY options as shown in the following table.

Change protection from	ENCRYPTION BY PASSWORD	DECRYPTION BY PASSWORD
Old password to new password	Required	Required
Password to master key	Omit	Required
Master key to password	Required	Omit

The database master key must be opened before it can be used to protect a private key. For more information, see OPEN MASTER KEY (Transact-SQL).

To change the ownership of an asymmetric key, use ALTER AUTHORIZATION.

Permissions

Requires CONTROL permission on the asymmetric key if the private key is being removed.

Examples

A. Changing the password of the private key

The following example changes the password used to protect the private key of asymmetric key PacificSales09. The new password will be <enterStrongPasswordHere>.

ALTER ASYMMETRIC KEY PacificSales09 
    WITH PRIVATE KEY (
    DECRYPTION BY PASSWORD = '<oldPassword>',
    ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>');
GO

B. Removing the private key from an asymmetric key

The following example removes the private key from PacificSales19, leaving only the public key.

ALTER ASYMMETRIC KEY PacificSales19 REMOVE PRIVATE KEY;
GO

C. Removing password protection from a private key

The following example removes the password protection from a private key and protects it with the database master key.

OPEN MASTER KEY;
ALTER ASYMMETRIC KEY PacificSales09 WITH PRIVATE KEY (
    DECRYPTION BY PASSWORD = '<enterStrongPasswordHere>' );
GO

Reference

CREATE ASYMMETRIC KEY (Transact-SQL)

DROP ASYMMETRIC KEY (Transact-SQL)

CREATE MASTER KEY (Transact-SQL)

OPEN MASTER KEY (Transact-SQL)

Concepts

SQL Server and Database Encryption Keys (Database Engine)

Encryption Hierarchy

Extensible Key Management (EKM)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD