· Uwe Janke
Commands / New-sqmSqlCertificate

New-sqmSqlCertificate Security

Renews an existing SQL Server certificate: backs up the old one, creates a new self-signed certificate with the same subject, backs up the new one as well, optionally switches the AlwaysOn / Service Broker endpoint or the TDE databases to it, and writes a renewal protocol.

Execution Flow

START dbatools available? throw + ERROR NO Invoke-DbaQuery: sys.certificates Cert found? throw "not found" NO Read endpoint binding (mirroring/broker endpoints) Read TDE binding (dm_database_encryption_keys) NewCertificateName = OldName_YEAR, ExpiryDate HasPrivateKey && no Password? throw "password YES ShouldProcess HIGH return $null NO xp_create_subdir + BACKUP old cert → _OLD.cer + _OLD.pvk CREATE CERTIFICATE [NewName] → BACKUP new .cer + .pvk if $BindEndpoint && $boundEndpoint ALTER ENDPOINT … FOR DATABASE_MIRRORING | SERVICE_BROKER if $BindTde && $tdeDatabases ALTER DATABASE ENCRYPTION KEY … CERTIFICATE [New] Old certificate stays (no rename in SQL Server), drop later Write renewal protocol TXT + Copy-sqmToCentralPath Return $result [PSCustomObject] DONE

Synopsis

Reads all relevant properties of the existing certificate (subject, endpoint binding, TDE binding), backs it up as .cer + .pvk, creates a new self-signed certificate with CREATE CERTIFICATE and backs that one up too, into the same folder. The new .cer is the public key for AlwaysOn / Service Broker partners; .cer + .pvk are what TDE secondaries and any later restore need. Optionally switches the endpoint (FOR DATABASE_MIRRORING or FOR SERVICE_BROKER) or re-encrypts the TDE database encryption keys. Writes a detailed renewal protocol TXT file.

-BackupPath is a path on the SQL Server host: BACKUP CERTIFICATE is executed by the service, and the folder is created there with xp_create_subdir.

Order for AlwaysOn: every partner must know the new public key before this replica's endpoint is switched, otherwise the replicas disconnect. Run without -BindEndpoint, import the new .cer on every partner with Install-sqmCertificate -Purpose AlwaysOn -AuthorizationUser <endpoint user>, then switch the endpoint. TDE in an AG: the new certificate (.cer + .pvk) must exist on every secondary before -BindTde. ConfirmImpact = High; use -WhatIf to preview.

Syntax

New-sqmSqlCertificate
    [-SqlInstance <String>]
    [-SqlCredential <PSCredential>]
    -CertificateName <String>          # required
    [-Database <String>]               # default: master
    [-NewCertificateName <String>]
    [-ValidityYears <Int>]
    [-BackupPath <String>]
    [-BackupEncryptionPassword <SecureString>]
    [-RenameOldCertificate <Bool>]
    [-BindEndpoint]
    [-BindTde]
    [-EnableException]
    [-WhatIf] [-Confirm]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString$env:COMPUTERNAMESQL Server instance to connect to.
-SqlCredentialPSCredential, Credential for the SQL connection.
-CertificateNameStringRequiredExact name of the certificate to renew (from sys.certificates).
-DatabaseStringmasterDatabase containing the certificate.
-NewCertificateNameStringOldName_YEARName for the new certificate. Auto-generated as <OldName>_<ExpiryYear> if omitted.
-ValidityYearsInt5Validity period for the new certificate in years (1–20).
-BackupPathStringConfig OutputPathDirectory on the SQL Server host for the .cer and .pvk backups of the old and the new certificate.
-BackupEncryptionPasswordSecureString, Password to encrypt the exported private keys (.pvk) of the old and the new certificate. Required when the old certificate has a private key.
-RenameOldCertificateBool$trueObsolete, no effect. SQL Server cannot rename certificates; the old one always stays until you drop it. Kept for compatibility of existing scripts.
-BindEndpointSwitchfalseSwitch the AlwaysOn (FOR DATABASE_MIRRORING) or Service Broker (FOR SERVICE_BROKER) endpoint immediately. For AlwaysOn only after all partners have the new public key.
-BindTdeSwitchfalseRe-encrypt the database encryption keys with the new certificate (online, no downtime). In an AG only after the new certificate exists on all secondaries.
-EnableExceptionSwitchfalseThrow terminating exceptions instead of writing errors.

Return Value

PropertyTypeDescription
SuccessBoolTrue on success, False on error.
OldCertificateNameStringName of the original certificate.
NewCertificateNameStringName of the newly created certificate.
NewExpiryDateDateTimeCalculated expiry date of the new certificate.
NewThumbprintStringHex thumbprint of the new certificate.
EndpointBoundBoolWhether the endpoint was automatically rebound.
TdeBoundBoolWhether TDE was automatically rotated.
BackupDirectoryStringPath to the backup directory containing .cer, .pvk, and protocol.
CerBackupFile / PvkBackupFileStringBackup of the old certificate.
NewCerBackupFile / NewPvkBackupFileStringBackup of the new certificate (.pvk only with -BackupEncryptionPassword).
ProtocolFileStringFull path to the renewal protocol TXT file.

Examples

Example 1, Simple renewal, no automatic binding

New-sqmSqlCertificate -SqlInstance "SQL01" -CertificateName "AG_CERT" `
    -BackupEncryptionPassword (Read-Host -AsSecureString)

Example 2, Renewal with automatic endpoint binding and 10-year validity

New-sqmSqlCertificate -SqlInstance "SQL01" -CertificateName "AG_CERT" `
    -ValidityYears 10 -BindEndpoint `
    -BackupEncryptionPassword (Read-Host -AsSecureString "Backup password")

Example 3, Renew TDE certificate with auto rotation

New-sqmSqlCertificate -SqlInstance "SQL01" -CertificateName "TDE_PROD" `
    -BindTde -BackupEncryptionPassword (Read-Host -AsSecureString "Backup password")

Notes

Requires sysadmin on the instance. dbatools, Invoke-sqmLogging, Get-sqmDefaultOutputPath, and Copy-sqmToCentralPath must be available. Changed in 1.9.179.0: the new certificate is backed up as well, the backup folder is created on the server, Service Broker endpoints use FOR SERVICE_BROKER, and the function works under Windows PowerShell 5.1 (it failed there before with "parameter Database specified more than once").
AlwaysOn: After renewal, import the new .cer on every partner with Install-sqmCertificate -Purpose AlwaysOn -AuthorizationUser <endpoint user>, then switch the endpoint. SQL Server has no RENAME CERTIFICATE statement; the old certificate stays until you drop it after verification.
TDE: The function backs up the new certificate with its private key. Store the backup and the password away from the server, and keep the old certificate as long as backups encrypted under it may need to be restored.