Security

Set-sqmSqlTlsCertificate

Binds a certificate from Cert:\LocalMachine\My to SQL Server as its TLS certificate — replacing the auto-generated self-signed certificate. Grants the SQL Server service account read access to the private key, writes the thumbprint to the registry, and optionally enables Force Encryption and restarts the service.

Module: sqmSQLTool
Requires: none (registry + WMI)
ShouldProcess: Yes (Medium)
Output: PSCustomObject

Execution Flow

START STEP 1: Resolve instance registry key from Instance Names registry STEP 2: Find cert by thumbprint in LocalMachine\My, check expiry + private key Cert valid + has private key? throw — cannot bind STEP 3: Resolve SQL service name (MSSQLSERVER or MSSQL$INSTANCE) + service account via WMI STEP 4: Grant READ on private key to service account (supports CSP keys in MachineKeys and CNG keys in Crypto\Keys) STEP 5: Write thumbprint to SuperSocketNetLib registry key -ForceEncryption? Yes Set ForceEncryption=1 in registry No -Restart? Yes Restart SQL Server service No (manual restart required) Return PSCustomObject (Status=Success) DONE
A SQL Server service restart is always required for the new certificate to take effect — either via -Restart or manually. Without a restart, the old certificate (or the self-signed one) remains active.
Supports both CSP private keys (stored in MachineKeys) and CNG private keys (stored in Crypto\Keys). Spaces are stripped from the -Thumbprint value automatically. Does not require dbatools — uses only registry and WMI.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalSQL instance name. For default instance use the computer name. For named instances: COMPUTER\INSTANCE. Default: $env:COMPUTERNAME.
-ThumbprintStringRequiredCertificate thumbprint (hex string). Spaces stripped automatically. Must match a cert in Cert:\LocalMachine\My.
-ForceEncryptionSwitchOptionalSets ForceEncryption=1 in the SuperSocketNetLib registry key, requiring all connections to use TLS.
-RestartSwitchOptionalRestarts the SQL Server service automatically after the registry change.
-WhatIf / -ConfirmSwitchOptionalStandard ShouldProcess support.

Return Value

Returns a PSCustomObject with: SqlInstance, Thumbprint, CertSubject, CertExpiry, ServiceAccount, ForceEncryption, ServiceRestarted, Status, Message.

Examples

Example 1 — Bind certificate to default instance

Set-sqmSqlTlsCertificate -SqlInstance "SQL01" -Thumbprint "A1B2C3D4E5F6..."

Example 2 — Named instance, Force Encryption, auto-restart

Set-sqmSqlTlsCertificate -SqlInstance "SQL01\INST1" -Thumbprint "A1B2C3D4E5F6..." -ForceEncryption -Restart