Execution Flow
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.
-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.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
| Purpose | Binding Action | Notes |
|---|---|---|
| AlwaysOn | With private key: ALTER ENDPOINT … FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE). Public key only: no endpoint change, owner = -AuthorizationUser | Endpoint 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. |
| TDE | ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE | Requires private key. TDE DB auto-detected when exactly one is encrypted. |
| SSL | Windows store import (no import into SQL Server); with -SetSqlServerSslCert binding via Set-sqmSqlTlsCertificate | Forces -ImportToWindowsStore. Registry thumbprint plus Read on the private key for the service account. Run on the SQL Server host, restart required. |
| ServiceBroker | ALTER ENDPOINT … FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE) | Endpoint auto-detected from sys.service_broker_endpoints. Public key only: same as AlwaysOn. |
| UserDefined | None | SQL 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
| Parameter | Type | Default | Description |
|---|---|---|---|
| -SqlInstance | String | $env:COMPUTERNAME | Target SQL Server instance. |
| -CertFile | String | , | 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. |
| -PrivateKeyFile | String | , | Path to .pvk private key file when using CER+PVK format. |
| -CertPassword | SecureString | , | Password for PFX or PVK decryption. |
| -CertificateName | String | filename (no ext) | Name used in CREATE CERTIFICATE [$name]. Special characters replaced with _. |
| -Database | String | master | Database context for the CREATE CERTIFICATE statement. |
| -Purpose | String | UserDefined | Determines automatic post-import binding. See purpose table above. |
| -EndpointName | String | auto | Endpoint for AlwaysOn/ServiceBroker binding (certificate with private key only). Auto-detected from the mirroring or Service Broker endpoints. |
| -AuthorizationUser | String | , | 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. |
| -TdeDatabaseName | String | auto | Database for TDE binding. Auto-detected when exactly one TDE-encrypted DB exists. |
| -ReplaceCertificateName | String | , | Name of the certificate being replaced. Old cert is logged but NOT deleted. |
| -ImportToWindowsStore | Switch | false | Import into LocalMachine\My. Automatically set when Purpose=SSL. |
| -SetSqlServerSslCert | Switch | false | Bind as TLS certificate via Set-sqmSqlTlsCertificate (registry thumbprint, Read on the private key for the service account). Run on the host; requires service restart. |
| -OutputPath | String | Get-sqmDefaultOutputPath | Directory for the TXT installation protocol. |
| -EnableException | Switch | false | Throw terminating exceptions on error. |
| -SqlCredential | PSCredential | , | 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 AlwaysOnExample 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