sqmSQLTool › Commands › Install-sqmCertificate

Install-sqmCertificate Security

Imports a certificate (PFX, CER+PVK, or CER-only) into SQL Server via CREATE CERTIFICATE … FROM FILE and automatically binds it to its purpose: AlwaysOn endpoint, TDE database, SSL/TLS network configuration, or Service Broker endpoint. 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 ② SELECT name, thumbprint FROM sys.certificates WHERE name = '$CertificateName' → check if already present ShouldProcess confirmed? return $null ③ SQL Server import: New-sqmCertImportSql → CREATE CERTIFICATE [$name] FROM FILE = N'$path' PFX: WITH PRIVATE KEY (FILE=N'pfx', DECRYPTION BY PASSWORD=...) | CER+PVK: WITH PRIVATE KEY (FILE=N'pvk') | CER-only: no private key ④ (if -ImportToWindowsStore or Purpose=SSL) X509Store LocalMachine\My .Add($x509) ⑤ (if -SetSqlServerSslCert) HKLM:\…\SuperSocketNetLib Certificate = thumbprint.ToLower() → restart required ⑥ (if Purpose=AlwaysOn/ServiceBroker) auto-detect or use -EndpointName ALTER ENDPOINT [$ep] FOR DATABASE_MIRRORING (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 $installResult [PSCustomObject] { SqlImported, WindowsStoreImport, SslCertSet, EndpointBound, TdeBound, Success, ProtocolFile, … } DONE

Synopsis

Supports three input formats — PFX (certificate + private key), CER+PVK (separate files), and CER-only (public key). Certificate metadata is validated before import; expired certificates trigger a warning but are not blocked. SQL import uses Invoke-DbaQuery with a dynamically generated CREATE CERTIFICATE statement built by the private helper New-sqmCertImportSql. Each purpose triggers a specific binding step after import. The TXT protocol includes purpose-specific next-step instructions (replica distribution for AlwaysOn, mandatory backup command for TDE, service-restart reminder for SSL).

SSL binding via -SetSqlServerSslCert requires a SQL Server service restart. AlwaysOn: the public key certificate must be installed on every replica using Install-sqmCertificate -Purpose AlwaysOn. TDE certificates must be backed up immediately after installation.
Requires dbatools. SupportsShouldProcess with ConfirmImpact=High. The backward-compatibility alias Build-sqmCertImportSql maps to the private helper New-sqmCertImportSql.

Supported Purposes

PurposeBinding ActionNotes
AlwaysOnALTER ENDPOINT … AUTHENTICATION = CERTIFICATEEndpoint auto-detected from sys.database_mirroring_endpoints. Public key (.cer) must be distributed to all replicas.
TDEALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATERequires private key. TDE DB auto-detected when exactly one is encrypted.
SSLWindows store import + Registry thumbprintForces -ImportToWindowsStore. Requires SQL Server service restart.
ServiceBrokerALTER ENDPOINT … AUTHENTICATION = CERTIFICATESame endpoint binding 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>]
    [-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; CER/CRT may be public-key only.
-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. Auto-detected when not specified.
-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.
-SetSqlServerSslCertSwitchfalseWrite thumbprint to SuperSocketNetLib registry key. Requires service restart.
-OutputPathStringGet-sqmDefaultOutputPathDirectory for the TXT installation protocol.
-EnableExceptionSwitchfalseThrow terminating exceptions on error.

Examples

# Import PFX from CA and bind to AlwaysOn endpoint
Install-sqmCertificate -SqlInstance "SQL01" -CertFile "C:\Certs\sql01.pfx" `
    -CertPassword (Read-Host -AsSecureString) -Purpose AlwaysOn

# Install public-key cert on AlwaysOn replica (no private key)
Install-sqmCertificate -SqlInstance "SQL02" -CertFile "\\share\SQL01_AG_CERT.cer" `
    -CertificateName "SQL01_AG_CERT" -Purpose AlwaysOn

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

# SSL certificate with Windows Store and registry
Install-sqmCertificate -SqlInstance "SQL01" -CertFile "C:\Certs\ssl.pfx" `
    -CertPassword (Read-Host -AsSecureString) -Purpose SSL -SetSqlServerSslCert