Detent PointDomain: Endpoint authentication · Risk: Changes state · Runs on: Windows, PowerShell 5.1 or 7
This sets up an instance’s data endpoint to trust the other replica by certificate instead of by Windows account. It’s how two SQL Servers that don’t share a domain — or that you’d rather not join by Windows authentication — can still mirror or run an availability group between them.
If the endpoint doesn’t exist yet, the command creates it with standard settings. If it exists but authenticates a different way, the command switches it. If it already uses the certificate you named, it makes no change and says so.
Ensures the endpoint authenticates with the named local certificate. It reads the current state first (via Test-SqlEndpointCertAuth); when there is no endpoint it runs CREATE ENDPOINT with certificate authentication and defaults (TCP LISTENER_PORT = 5022, ENCRYPTION = REQUIRED ALGORITHM AES, and ROLE = ALL for mirroring), and when an endpoint exists but isn’t certificate-authenticated it runs ALTER ENDPOINT. Idempotent — already authenticating with this certificate returns Skipped. The local certificate must already exist; if it doesn’t, the result is Pending with guidance to create it (New-SqlTdeCertificate). Never throws.
Recipe 01 — Convert the mirroring / AG endpoint
Set-SqlEndpointCertAuthentication -SqlInstance node1 -CertificateName 'Node1_Endpoint_Cert'Creates or converts node1’s database-mirroring (also Always On) endpoint to authenticate with the certificate.
Recipe 02 — The Service Broker endpoint
Set-SqlEndpointCertAuthentication -SqlInstance node1 -CertificateName 'Node1_Broker_Cert' -EndpointType ServiceBroker-EndpointType ServiceBroker targets the Service Broker endpoint; the default is DatabaseMirroring.
Watchpoint — configuring the endpoint is only half the trust. Setting an endpoint to certificate authentication doesn’t let the partner connect on its own. Each replica must also import the other’s public certificate and grant it CONNECT (Export-SqlEndpointCertificate then Grant-SqlEndpointCertAccess), on both sides, before the endpoints authenticate.
Two SQL Servers that mirror data or run an availability group talk over a special connection called an endpoint, and each end has to prove who it is. One way to prove it is with a certificate: each server holds its own certificate and trusts the other’s. This command sets one server’s endpoint to use certificate authentication with the certificate you name. If that server has no endpoint yet, it builds one with common defaults; if it has one that authenticates a different way, it changes it.
The certificate has to be created on the server first — this command won’t make it for you. If the certificate is missing, you get a Pending result telling you to create it, not an error.
| Question | Answer |
|---|---|
| Outbound calls? | It connects to the SQL instance you name (via the dbatools/SqlServer provider) and runs the endpoint statements there. No other outbound calls. |
| What it changes | Creates the endpoint (certificate authentication, TCP 5022, AES encryption, ROLE ALL for mirroring) when absent, or alters an existing endpoint to certificate authentication. |
| Privileges | A SQL login permitted to create and alter server endpoints (typically sysadmin). |
| Reversibility | An ALTER can be undone by altering the endpoint back to its previous authentication; an endpoint this command created can be dropped. Both are manual. |
| Dependency | Why | Required? |
|---|---|---|
The named certificate present in master |
The endpoint is set to authenticate with it; absent → Pending. | Yes |
| A reachable SQL instance (dbatools/SqlServer) | The endpoint statements run there. | Yes |
Grant-SqlEndpointCertAccess on each replica |
Completes the mutual trust after the endpoints are configured. | To go live |
- The certificate must already exist — the command won’t create key material silently, returning Pending with guidance instead.
- Idempotent: an endpoint already authenticating with the named certificate is left unchanged and reported as Skipped, so re-running is safe in a script.
-WhatIfstates the CREATE or ALTER it would run before any change is made.- The result records the endpoint name, type, certificate, and whether it created or altered the endpoint — reviewable evidence of what changed.