Execution Flow
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.
-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
| Parameter | Type | Default | Description |
|---|---|---|---|
| -SqlInstance | String | $env:COMPUTERNAME | SQL Server instance to connect to. |
| -SqlCredential | PSCredential | , | Credential for the SQL connection. |
| -CertificateName | String | Required | Exact name of the certificate to renew (from sys.certificates). |
| -Database | String | master | Database containing the certificate. |
| -NewCertificateName | String | OldName_YEAR | Name for the new certificate. Auto-generated as <OldName>_<ExpiryYear> if omitted. |
| -ValidityYears | Int | 5 | Validity period for the new certificate in years (1–20). |
| -BackupPath | String | Config OutputPath | Directory on the SQL Server host for the .cer and .pvk backups of the old and the new certificate. |
| -BackupEncryptionPassword | SecureString | , | Password to encrypt the exported private keys (.pvk) of the old and the new certificate. Required when the old certificate has a private key. |
| -RenameOldCertificate | Bool | $true | Obsolete, no effect. SQL Server cannot rename certificates; the old one always stays until you drop it. Kept for compatibility of existing scripts. |
| -BindEndpoint | Switch | false | Switch the AlwaysOn (FOR DATABASE_MIRRORING) or Service Broker (FOR SERVICE_BROKER) endpoint immediately. For AlwaysOn only after all partners have the new public key. |
| -BindTde | Switch | false | Re-encrypt the database encryption keys with the new certificate (online, no downtime). In an AG only after the new certificate exists on all secondaries. |
| -EnableException | Switch | false | Throw terminating exceptions instead of writing errors. |
Return Value
| Property | Type | Description |
|---|---|---|
| Success | Bool | True on success, False on error. |
| OldCertificateName | String | Name of the original certificate. |
| NewCertificateName | String | Name of the newly created certificate. |
| NewExpiryDate | DateTime | Calculated expiry date of the new certificate. |
| NewThumbprint | String | Hex thumbprint of the new certificate. |
| EndpointBound | Bool | Whether the endpoint was automatically rebound. |
| TdeBound | Bool | Whether TDE was automatically rotated. |
| BackupDirectory | String | Path to the backup directory containing .cer, .pvk, and protocol. |
| CerBackupFile / PvkBackupFile | String | Backup of the old certificate. |
| NewCerBackupFile / NewPvkBackupFile | String | Backup of the new certificate (.pvk only with -BackupEncryptionPassword). |
| ProtocolFile | String | Full 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
FOR SERVICE_BROKER, and the function works under Windows PowerShell 5.1 (it failed there before with "parameter Database specified more than once")..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.