sqmSQLTool › Commands › New-sqmSqlCertificate

New-sqmSqlCertificate Security

Renews an existing SQL Server self-signed certificate: backs up the old cert, creates a new one with the same subject and purpose, optionally auto-binds to AlwaysOn endpoints or TDE databases, 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 BACKUP CERTIFICATE → .cer + .pvk (HasPrivateKey) CREATE CERTIFICATE [NewName] if $BindEndpoint && $boundEndpoint ALTER ENDPOINT … CERTIFICATE [NewName] if $BindTde && $tdeDatabases ALTER DATABASE ENCRYPTION KEY … CERTIFICATE [New] RenameOld: WARNING — drop manually after verification 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 optionally auto-binds it to AlwaysOn/Broker endpoints or TDE-encrypted databases. Writes a detailed renewal protocol TXT file.

ConfirmImpact = High. Use -WhatIf to preview without making changes. For AlwaysOn, the new certificate must be distributed to all replica instances separately.

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 for .cer and .pvk backup files.
-BackupEncryptionPasswordSecureString—Password to encrypt the exported private key (.pvk). Required when the certificate has a private key.
-RenameOldCertificateBool$trueLog a reminder to rename/drop the old certificate after verification (SQL Server has no RENAME CERTIFICATE).
-BindEndpointSwitchfalseAutomatically rebind AlwaysOn / Service Broker endpoints to the new certificate.
-BindTdeSwitchfalseAutomatically rotate the TDE encryption key to the new certificate (online, no downtime).
-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.
ProtocolFileStringFull path to the renewal protocol TXT file.

Examples

# Simple renewal — no automatic binding
New-sqmSqlCertificate -SqlInstance "SQL01" -CertificateName "AG_CERT" `
    -BackupEncryptionPassword (Read-Host -AsSecureString)

# 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")

# 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.
AlwaysOn: After renewal, the new certificate must be distributed to every replica instance and installed there with Install-sqmCertificate -ForAlwaysOn. SQL Server has no RENAME CERTIFICATE statement — the old cert must be dropped manually after verification.
TDE: The new TDE certificate must be backed up including its private key immediately after creation. Without a backup, a database recovery is impossible if the master database is lost.