· Uwe Janke
Commands / Install-sqmCertificate

Install-sqmCertificate Security

Imports a certificate (PFX on SQL Server 2022+, CER+PVK, or the public key of a partner replica) into SQL Server via CREATE CERTIFICATE … FROM FILE and binds it to its purpose: the local AlwaysOn or Service Broker endpoint, TDE, or, for -Purpose SSL, the TLS certificate of the instance. Writes a detailed installation protocol as TXT.

Execution Flow

START dbatools available? throw ERROR ① Get-sqmCertFileInfo -CertFile -CertPassword -IsPfx → Subject, Issuer, NotBefore, NotAfter, Thumbprint X509Certificate2 (EphemeralKeySet); warn if expired or expiring within 30 days ② Pre-checks (not for SSL): PFX needs SQL 2022+, master key for private keys, -AuthorizationUser, duplicates ShouldProcess confirmed? return $null ③ (not for SSL) CREATE CERTIFICATE [$name] [AUTHORIZATION [$user]] FROM FILE = N'$path' PFX: WITH FORMAT = 'PFX', PRIVATE KEY (DECRYPTION BY PASSWORD) | CER+PVK: WITH PRIVATE KEY (FILE = N'pvk') | CER: public key ④ (if -ImportToWindowsStore or Purpose=SSL) import into LocalMachine\My ⑤ (if -SetSqlServerSslCert) Set-sqmSqlTlsCertificate: thumbprint + key permission → restart ⑥ AlwaysOn/ServiceBroker: private key → switch local endpoint; public key → owner only ALTER ENDPOINT [$ep] FOR DATABASE_MIRRORING | SERVICE_BROKER (AUTHENTICATION = CERTIFICATE [$name]) ⑦ (if Purpose=TDE) auto-detect TDE DB or use -TdeDatabaseName (must be single-encrypted DB) ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE [$name] ⑧ Write TXT protocol → CertInstall_<instance>_<name>_<ts>.txt; Copy-sqmToCentralPath Includes purpose-specific next-steps: AlwaysOn replica distribution, TDE backup reminder, SSL restart hint Return [PSCustomObject] { SqlImported, WindowsStoreImport, SslCertSet, EndpointBound, PartnerKeyOwner, TdeBound, Success, … } DONE

Synopsis

Supports three input formats: PFX (certificate + private key, import into SQL Server from SQL Server 2022 on, with WITH FORMAT = 'PFX'), CER+PVK (separate files, all versions) and CER-only (public key). Certificate metadata is validated before import; expired certificates trigger a warning but are not blocked. Before anything is created the function checks that the instance can take a PFX, that a database master key exists when a private key is imported, and that the -AuthorizationUser exists. Each purpose triggers a specific binding step after import. The TXT protocol includes purpose-specific next steps (partner distribution for AlwaysOn, mandatory backup command for TDE, service-restart reminder for SSL).

The certificate files are read twice: by PowerShell for validation and by the SQL Server service account for CREATE CERTIFICATE … FROM FILE. Use a path both can reach, typically a UNC path.

AlwaysOn renewal: import the new public key on every partner (-Purpose AlwaysOn -AuthorizationUser <endpoint user>) before switching the endpoint on the replica that owns the certificate, otherwise the replicas disconnect. TLS binding via -SetSqlServerSslCert runs on the SQL Server host and needs a service restart. TDE certificates must be backed up immediately after installation.
Changed in 1.9.179.0: PFX import fixed (FORMAT = 'PFX'), TLS binding now via Set-sqmSqlTlsCertificate, a public key without private key no longer switches the endpoint (new -AuthorizationUser), Service Broker endpoints use FOR SERVICE_BROKER, endpoint and TDE binding work under Windows PowerShell 5.1. Requires dbatools. SupportsShouldProcess with ConfirmImpact=High. The backward-compatibility alias Build-sqmCertImportSql maps to the private helper New-sqmCertImportSql.

Supported Purposes

PurposeBinding ActionNotes
AlwaysOnWith private key: ALTER ENDPOINT … FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE). Public key only: no endpoint change, owner = -AuthorizationUserEndpoint auto-detected from sys.database_mirroring_endpoints. The partner's public key must be owned by the database user of the login that has CONNECT on the endpoint.
TDEALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATERequires private key. TDE DB auto-detected when exactly one is encrypted.
SSLWindows store import (no import into SQL Server); with -SetSqlServerSslCert binding via Set-sqmSqlTlsCertificateForces -ImportToWindowsStore. Registry thumbprint plus Read on the private key for the service account. Run on the SQL Server host, restart required.
ServiceBrokerALTER ENDPOINT … FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE)Endpoint auto-detected from sys.service_broker_endpoints. Public key only: same as AlwaysOn.
UserDefinedNoneSQL import only, no automatic binding.

Syntax

Install-sqmCertificate
    [-SqlInstance <String>]
    [-SqlCredential <PSCredential>]
    -CertFile <String>                 # required; .pfx/.p12/.cer/.crt
    [-PrivateKeyFile <String>]         # .pvk for CER+PVK format
    [-CertPassword <SecureString>]
    [-CertificateName <String>]        # default: filename without extension
    [-Database <String>]              # default master
    [-Purpose <AlwaysOn|TDE|SSL|ServiceBroker|UserDefined>]
    [-EndpointName <String>]
    [-AuthorizationUser <String>]         # owner of a partner public key
    [-TdeDatabaseName <String>]
    [-ReplaceCertificateName <String>]
    [-ImportToWindowsStore]
    [-SetSqlServerSslCert]
    [-OutputPath <String>]
    [-EnableException]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString$env:COMPUTERNAMETarget SQL Server instance.
-CertFileString, Required. Path to certificate file. PFX/P12 includes private key (SQL Server import from 2022 on); CER/CRT may be public-key only. Must be readable by PowerShell and by the SQL Server service account.
-PrivateKeyFileString, Path to .pvk private key file when using CER+PVK format.
-CertPasswordSecureString, Password for PFX or PVK decryption.
-CertificateNameStringfilename (no ext)Name used in CREATE CERTIFICATE [$name]. Special characters replaced with _.
-DatabaseStringmasterDatabase context for the CREATE CERTIFICATE statement.
-PurposeStringUserDefinedDetermines automatic post-import binding. See purpose table above.
-EndpointNameStringautoEndpoint for AlwaysOn/ServiceBroker binding (certificate with private key only). Auto-detected from the mirroring or Service Broker endpoints.
-AuthorizationUserString, Owner of the imported certificate (CREATE CERTIFICATE … AUTHORIZATION). For a partner's public key: the database user of the endpoint login. Must exist; an existing certificate of the same name and thumbprint gets ALTER AUTHORIZATION.
-TdeDatabaseNameStringautoDatabase for TDE binding. Auto-detected when exactly one TDE-encrypted DB exists.
-ReplaceCertificateNameString, Name of the certificate being replaced. Old cert is logged but NOT deleted.
-ImportToWindowsStoreSwitchfalseImport into LocalMachine\My. Automatically set when Purpose=SSL.
-SetSqlServerSslCertSwitchfalseBind as TLS certificate via Set-sqmSqlTlsCertificate (registry thumbprint, Read on the private key for the service account). Run on the host; requires service restart.
-OutputPathStringGet-sqmDefaultOutputPathDirectory for the TXT installation protocol.
-EnableExceptionSwitchfalseThrow terminating exceptions on error.
-SqlCredentialPSCredential, PSCredential for the SQL Server connection.

Examples

Example 1, Import PFX (SQL Server 2022+) and switch the local AlwaysOn endpoint

Install-sqmCertificate -SqlInstance "SQL01" -CertFile "\\fs01\certs\sql01_ag.pfx" `
    -CertPassword (Read-Host -AsSecureString) -Purpose AlwaysOn

Example 2, Public key of partner SQL01 on replica SQL02 (endpoint unchanged)

Install-sqmCertificate -SqlInstance "SQL02" -CertFile "\\fs01\certs\SQL01_AG_CERT.cer" `
    -CertificateName "SQL01_AG_CERT" -Purpose AlwaysOn -AuthorizationUser "ag_user_sql01"

Example 3, CER + PVK with TDE binding

Install-sqmCertificate -SqlInstance "SQL01" `
    -CertFile "C:\Certs\tde_new.cer" -PrivateKeyFile "C:\Certs\tde_new.pvk" `
    -CertPassword (Read-Host -AsSecureString "PVK password") `
    -Purpose TDE -TdeDatabaseName "ProdDB"

Example 4, TLS certificate: Windows store + binding (on the host, restart afterwards)

Install-sqmCertificate -SqlInstance "SQL01" -CertFile "C:\Certs\ssl.pfx" `
    -CertPassword (Read-Host -AsSecureString) -Purpose SSL -SetSqlServerSslCert