Detent Point

SqlCertForge TLS certificate binding for SQL Server and Reporting Services

Grant-SqlEndpointCertAccess

Grants a partner replica CONNECT on the endpoint by importing its public certificate, creating a login from that certificate, and granting the login CONNEC...

Domain: Endpoint authentication · Risk: Changes state · Runs on: Windows, PowerShell 5.1 or 7

This is the step that tells one server to trust the other’s certificate. It takes the partner’s public certificate file, brings it into SQL Server, creates a login tied to it, and gives that login permission to connect to the endpoint. Once each server has done this for the other’s certificate, the two endpoints can authenticate and the mirroring or availability group traffic flows.

In one batch it runs CREATE CERTIFICATE ... FROM FILE (public certificate, no private key), CREATE LOGIN ... FROM CERTIFICATE, and GRANT CONNECT ON ENDPOINT. It reads first whether the certificate and login already exist; both present returns Skipped. Each CREATE is guarded by that existence check, and the GRANT is re-issued every time (idempotent). One ShouldProcess gate covers the batch. Only a public certificate is ever imported — there is no private-key parameter. Never throws.

Recipe 01 — Trust the partner’s certificate on this replica

Grant-SqlEndpointCertAccess -SqlInstance node2 -PartnerCertificatePath 'E:\exchange\node1.cer' -PartnerCertificateName 'Node1_Public_Cert'

On node2: imports node1’s public certificate, creates a login from it, and grants that login CONNECT on node2’s endpoint — so node1 can authenticate to node2. The login defaults to Node1_Public_Cert_login.

Recipe 02 — Preview the grant

Grant-SqlEndpointCertAccess -SqlInstance node2 -PartnerCertificatePath 'E:\exchange\node1.cer' -PartnerCertificateName 'Node1_Public_Cert' -WhatIf

Shows the certificate import, login creation, and endpoint grant without changing anything. Run it for real on each replica against the other’s public certificate to complete the mutual trust.

Watchpoint — the file is read by SQL Server, and the trust is mutual. CREATE CERTIFICATE ... FROM FILE reads -PartnerCertificatePath on the target instance’s file system, not the workstation’s. And run the command on each replica against the other replica’s public certificate — one side alone does not complete the trust.

For two servers to authenticate by certificate, each has to hold and trust the other’s public certificate. This command does the trusting side on one server: it imports the partner’s public certificate file, makes a login that stands for that certificate, and grants that login permission to connect to the endpoint.

Because it only ever imports a public certificate, nothing secret is involved. Run it on both servers — each pointing at the other’s certificate — and the two endpoints can then authenticate.

Question Answer
Outbound calls? It connects to the SQL instance you name (via the dbatools/SqlServer provider); SQL Server reads the partner .cer from the -PartnerCertificatePath you give on that server. No other outbound calls.
What it changes Imports the partner’s public certificate, creates a login mapped to it (when absent), and grants that login CONNECT on the endpoint.
Privileges A SQL login permitted to create certificates and logins and to grant on the endpoint (typically sysadmin).
Reversibility Reverse manually — REVOKE the CONNECT, DROP the login, DROP the certificate.
Dependency Why Required?
The partner’s public certificate file, reachable by the SQL Server service account CREATE CERTIFICATE FROM FILE reads it. Yes
The endpoint named by -EndpointName (default Hadr_endpoint) CONNECT is granted on it. Yes
Export-SqlEndpointCertificate on the partner Produces the public certificate this imports. Yes
  • Imports a public certificate only — there is no private-key path, so this command can’t bring a private key onto the server.
  • Idempotent: when the certificate and login already exist it makes no change and reports Skipped; each CREATE is guarded and the GRANT is safe to re-issue.
  • A single ShouldProcess gate covers the whole batch, and -WhatIf previews the import, login, and grant before any change.
  • The result lists the actions taken (imported certificate, created login, granted connect) — a reviewable record of what the grant did.