Detent PointDomain: SQL Database Engine · Risk: Changes state (an ACL grant) · Runs on: Windows, PowerShell 5.1 or 7
This is the least visible and most important step. A certificate is installed, it looks bound, everything appears fine — but if the account SQL Server runs as can’t read the certificate’s private key, SQL quietly ignores it and connections stay unencrypted. This command grants exactly the access needed, and nothing more, and refuses to grant it to a dangerously broad group.
Finds the target certificate (by exact thumbprint, or an anchored CN= match as a fallback), locates its private-key file — probing both the CNG key store and the legacy CAPI machine-key store — and adds a single Read rule for the named account. Existing permissions are left intact. Never throws.
The dual-store probe matters: the “Microsoft RSA SChannel Cryptographic Provider” that SQL Server’s TLS certs commonly use is surfaced through the CNG API yet writes its key file to
RSA\MachineKeys. Both locations are checked, so the grant doesn’t fail on a valid cert.
Recipe 01 — Grant the default instance’s service SID
Add-SqlCertPrivateKeyAccess -ServiceAccount 'NT SERVICE\MSSQLSERVER' -Thumbprint $tpRecipe 02 — A domain service account
Add-SqlCertPrivateKeyAccess -ServiceAccount 'CORP\svc_sql' -Thumbprint $tpRecipe 03 — Select the cert by common name instead of thumbprint
Add-SqlCertPrivateKeyAccess -ServiceAccount 'NT SERVICE\MSSQLSERVER' -CommonName 'sql01.corp.com'Recipe 04 — A remote node
Add-SqlCertPrivateKeyAccess -ServiceAccount 'CORP\svc_sql' -Thumbprint $tp -Node SQL02 -Credential $credGuardrail — broad principals are refused. Passing a group like Everyone or Authenticated Users returns Failed with a hint; add -AllowBroadPrincipal only if that exposure is genuinely intended.
Every certificate has a private key — a secret file that proves the certificate really belongs to this server. Windows protects that file with permissions. SQL Server runs under a service account, and that account must be allowed to read the key, or SQL can’t use the certificate.
This command adds exactly one permission: the service account gets Read on the key. Not full control, not “everyone” — just the one account, just read. That’s the least-privilege way to do it.
| Question | Answer |
|---|---|
| Outbound calls? | None. It edits a file permission on the local (or named) machine. |
| What it changes | Adds one Read ACE on the private-key file for the SQL service account. |
| Privileges | Local admin (to edit the key file’s ACL). |
| Safety rail | Refuses broad principals (Everyone, Authenticated Users, BUILTIN/Guests, Anonymous) unless explicitly overridden. |
| Dependency | Why | Required? |
|---|---|---|
| Windows + PowerShell 5.1 or 7 | Reads the key store and edits the ACL. | Yes |
| The certificate present with its private key | There must be a key to grant access to. | Yes |
| The SQL service account name | The principal being granted Read. | Yes |
- Least privilege by construction: exactly one Read rule, for one resolved account SID, added to the existing ACL — nothing is widened.
- Broad-principal refusal is the default, so a careless grant to Everyone or Authenticated Users can’t happen without an explicit, auditable override.
- The result records the resolved SID the grant was applied for — an auditor can confirm it matches the SQL service account and nothing broader.