Alter a certificate in the database.
Syntax:
ALTER CERTIFICATE certificate REMOVE PRIVATE KEY
ALTER CERTIFICATE certificate WITH PRIVATE KEY ( private_key_spec [ ,... ] )
ALTER CERTIFICATE certificate WITH ACTIVE FOR BEGIN_DIALOG = [ON | OFF]
private_key_spec:
[ FILE = 'path_to_private_key' | BINARY = private_key_bits ]
[, DECRYPTION BY PASSWORD = 'key_password' ]
[, ENCRYPTION BY PASSWORD = 'password' ]
Key:
key_password Password to decrypt the private key.
password Password to encrypt the private key of the certificate in the database.
REMOVE PRIVATE KEY The key should no longer be maintained inside the database.
ACTIVE FOR BEGIN_DIALOG
Make the cert available to the initiator of a Service Broker dialog conversation
certificate_name Is the unique name by which the certificate is known in the database.
REMOVE PRIVATE KEY Specifies that the private key should no longer be maintained inside the database.
WITH PRIVATE KEY Specifies that the private key of the certificate is loaded into SQL Server.
FILE ='path_to_private_key' Specifies the complete path, including file name, to the private key. This parameter can be a local path or a UNC path to a network location. This file will be accessed within the security context of the SQL Server service account. When you use this option, make sure the service account has access to the specified file.
If only a file name is specified, the file is saved in the default user data folder for the instance. This folder might (or might not) be the SQL Server DATA folder. For SQL Server Express LocalDB, the default user data folder for the instance is the path specified by the %USERPROFILE% environment variable for the account that created the instance.
BINARY ='private_key_bits' Private key bits specified as binary constant. These bits can be in encrypted form. If encrypted, the user must provide a decryption password. Password policy checks are not performed on this password. The private key bits should be in a PVK file format.
DECRYPTION BY PASSWORD ='current_password' Specifies the password that is required to decrypt the private key.
ENCRYPTION BY PASSWORD ='new_password' Specifies the password used to encrypt the private key of the certificate in the database. new_password must meet the Windows password policy requirements of the computer that is running the instance of SQL Server. For more information, see Password Policy.
ACTIVE FOR BEGIN_DIALOG = { ON | OFF } Makes the certificate available to the initiator of a Service Broker dialog conversation.
The private key must correspond to the public key specified by certificate
Requires ALTER permission on the certificate.
ALTER CERTIFICATE MyCert1
WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'oldCertPassword3456gsf',
ENCRYPTION BY PASSWORD = 'newCertPassword$214kH');
GO
ALTER CERTIFICATE MyCert2
WITH PRIVATE KEY (ENCRYPTION BY PASSWORD = 'newKeyPassword64Jjh7',
DECRYPTION BY PASSWORD = 'oldKeyPassword706tt');
GO
“If life was fair, Elvis would still be alive and all the impersonators would be dead” ~ Johnny Carson
CREATE CERTIFICATE
DROP CERTIFICATE
BACKUP CERTIFICATE