Install-SqlConnectionCertificate

Putting a valid TLS certificate on a SQL Server is a six-step chore, and every step has a way to fail quietly. This command does all six in the correct order and reports, at each step, whether it worked.

The payoff: encrypting a SQL Server stops being a half-day ticket a senior DBA has to babysit and becomes one reviewable command that runs the same way on one server or fifty. It removes the two mistakes that cause outages — binding a certificate the SQL service account can’t actually read, and turning on “force encryption” before confirming clients can still connect.

Three things to know before you approve its use:

1. It can use your existing certificate authority, or none at all. If you run AD Certificate Services, it requests and installs the cert for you. If you buy certs elsewhere, hand it the .pfx and it does the rest. No new certificate vendor to sign off.
2. Nothing about your certificates or servers leaves your network. The whole process runs inside your perimeter. There is no call home.
3. “Force encryption” is off unless you ask for it. That switch makes the server reject unencrypted connections — powerful, and occasionally an outage. The command never flips it silently.

What success looks like — one server, end to end, using your own CA:

Install-SqlConnectionCertificate -CommonName sql01.corp.com `
    -CaConfig 'CA01\Corp Issuing CA' -CertificateTemplate WebServer `
    -SqlInstance MSSQLSERVER -ServiceAccount 'NT SERVICE\MSSQLSERVER'

Outcome — a certificate requested, issued, installed, permissioned, bound, and verified serving over TLS. One command, one audit line per step.

The bottom line for a budget owner: read-only checks in this toolkit are free and unlicensed. This command is one of the paid actions. What you buy is the removal of a recurring, error-prone manual task and the outages it causes.

The end-to-end orchestrator. It sequences the six atomic functions — New-SqlCertRequest → Submit-SqlCertRequest → Import-SqlCert → Add-SqlCertPrivateKeyAccess → Set-SqlCertBinding → Test-SqlCertBinding — threading the issued thumbprint forward so Grant and Bind operate on the exact cert. Every stage returns a SqlCert.Result; a Failed or Pending stage stops the chain. It never throws.

What you actually need to remember:

1. -Stage runs any subset. Default is all six. Skip Request/Submit when you already have a .pfx.
2. Pending is not failure. A CA that queues the request returns Pending — resume later with the same command and it picks up where it stopped.
3. Remote nodes need -Credential. Import/Grant/Bind/Verify run over WinRM; Kerberos pass-through covers domain-joined, a credential covers workgroup/cross-domain.
4. -ForceEncryption is a separate, deliberate switch. It rejects every unencrypted connection. Verify first.

Recipe 01 — Bring your own PFX (no CA in the path)

Install-SqlConnectionCertificate -Stage Import,Grant,Bind,Verify `
    -PfxPath C:\certs\sql01.pfx -PfxPassword $pw `
    -SqlInstance MSSQLSERVER -ServiceAccount 'NT SERVICE\MSSQLSERVER'

Imports the pfx, grants the service account read on the private key, binds, and confirms it serves. Four results, four green stages.

Recipe 02 — A remote server in a workgroup

$cred = Get-Credential
Install-SqlConnectionCertificate -Node sql02 -Credential $cred `
    -PfxPath \\share\sql02.pfx -PfxPassword $pw -Stage Import,Grant,Bind,Verify `
    -SqlInstance MSSQLSERVER -ServiceAccount 'CORP\svc_sql'

Every stage runs on sql02 over WinRM under the supplied credential.

Recipe 03 — Confirm only (did last month’s bind survive?)

Install-SqlConnectionCertificate -Stage Verify -SqlInstance MSSQLSERVER

Reads the binding from the registry and reports the bound thumbprint and expiry. No change made.

Recipe 04 — Turn on Force Encryption, deliberately

Install-SqlConnectionCertificate -Stage Bind,Verify -SqlInstance MSSQLSERVER -ForceEncryption

Sets ForceEncryption=1 and confirms the bind. Restart the SQL service to apply; the instance then rejects unencrypted clients.

Recipe 05 — Resume a CA that queues requests for approval

# First call returns Pending on Submit; after the CA operator approves, re-run:
Install-SqlConnectionCertificate -CommonName sql01.corp.com `
    -CaConfig 'CA01\Corp Issuing CA' -CertificateTemplate WebServer `
    -SqlInstance MSSQLSERVER -ServiceAccount 'NT SERVICE\MSSQLSERVER'

Pending stops the chain without error; the same command resumes from where it stopped.

Watchpoint — the bind needs a restart. Writing the thumbprint to SuperSocketNetLib is instant; SQL loads it on the next service start. Verify confirms the registry value; the encrypted connection is live after the restart.

Start here if certificates are new to you. A TLS certificate is how a SQL Server proves it is really itself and encrypts the traffic to it. Getting one onto a server takes six small steps, and this command walks all six.

The whole thing, one line, with your own CA:

Install-SqlConnectionCertificate -CommonName sql01.corp.com `
    -CaConfig 'CA01\Corp Issuing CA' -CertificateTemplate WebServer `
    -SqlInstance MSSQLSERVER -ServiceAccount 'NT SERVICE\MSSQLSERVER'

You get six results back, one per step. Green all the way down means the server is encrypted.

The one that bites everyone is the Grant step. If the SQL service account can’t read the private key, SQL starts, ignores the cert, and connections stay unencrypted with no obvious error. This command never skips Grant.

The one line that matters for a security review: nothing leaves your network. The licence check is an offline signature verification, not a call home.

No hidden dependencies: no agent, no service, no database of its own, no internet. If the six things above are in place, it runs.

Controls built in:

• Least privilege on the private key. Grant gives the SQL service account Read only — not full control — on the key file.
• Broad principals are refused. Granting key access to Everyone, Authenticated Users, BUILTIN, BUILTIN, or Anonymous is blocked unless -AllowBroadPrincipal is explicitly passed. The refusal is the default.
• Force Encryption is opt-in. The switch that rejects unencrypted connections is never set implicitly.
• Every stage is evidence. Each step returns a structured SqlCert.Result — stage, status, detail, thumbprint, resolved SID — suitable for an audit log.

A real result an auditor can keep (from a live run):

Grant  Success  Granted Read to SQLSPNLAB\svc_sql_ao on: CLVLAB-NODE1
Bind   Success  Bound BD7B1179…9582C to: AOAG on CLVLAB-NODE1
       The certificate [Cert Hash(sha1) "BD7B1179…9582C"] was
       successfully loaded for encryption.

The thumbprint, the account, and the target are on the record. The SID the account resolved to is captured for the grant.

What to check in an audit:

1. The private-key ACL grants Read to the SQL service account SID and nothing broader.
2. The bound thumbprint matches an in-policy, unexpired certificate.
3. Where required, Force Encryption is on and clients still connect (no silent downgrade).