master Endpoints

Overview

An endpoint is a door.

Every instance has the four or five the engine creates for itself, and most have nothing else. The ones that matter are the mirroring, availability group and Service Broker endpoints somebody created, because each of those accepts connections from other servers and each carries its own encryption setting that nobody has looked at since the day it was made.

An availability group endpoint still running RC4 is a real finding, and RC4 is what the older defaults left behind.

The master Endpoints page: every door into this instance and what is coming through it
The whole page. Five endpoints, all of them the engine’s own, ranked by the connections on them right now, and the grid carrying the state, the encryption and who may connect.

Where to find it

Expand a server in the tree, expand the master database, then Master → Endpoints.

The catalog half and the live half

Half What it says
Catalog What exists.
Live, from sys.dm_exec_connections Which of those doors anybody is actually coming through.

That is what turns a listing into a picture: an endpoint in the catalog with no connections on it for weeks is a door nobody uses and everybody forgot to close.

Two joins that are left joins for a reason

Both are easy to get wrong, and both fail silently.

The default TSQL endpoints have no row in sys.tcp_endpoints at all

An inner join there silently drops the endpoints every instance has.

The permissions are class 105

In a view whose other rows are about logins and availability groups, so the class filter is not optional.

Reading the chart

The bars: one per endpoint, ranked by the connections on it right now
Ranked by connections at this moment. The two carrying traffic are the local machine and the default TCP endpoint, and the dedicated admin connection sits at zero, as it should.

One bar per endpoint, ranked by the sessions on it right now, with that count on the right.

That ranking is what turns a listing into a picture. An endpoint somebody created, sitting at zero week after week, is a door nobody uses and nobody has closed.

Reading the grid

The grid: state, port, encryption, owner and who may connect
The five endpoints the engine creates for itself. Port and encryption are blank on those, and the Connected now column is where the bars above come from.
Column What it is
Endpoint The endpoint name.
Carries TSQL, database mirroring, Service Broker, or SOAP.
State Started, stopped or disabled.
Port The TCP port, where there is one.
Encryption The algorithm and whether it is required. RC4 is called out.
Owner The principal that owns it.
Connected now How many sessions are on it at this moment. Zero on a created endpoint is the finding.
Who may connect The principals with CONNECT on it, from the class 105 permissions.

The toolbar

Control What it does
Server permissions Opens master Server Permissions.
Server triggers Opens master Server Triggers, since a logon trigger sits on the same path.
Connections now Opens the Connections report.

Where the data comes from

Source What it gives
sys.endpoints The endpoints, their type, state and payload.
sys.tcp_endpoints The port and listener address. Left joined, because the default TSQL endpoints have no row here.
sys.database_mirroring_endpoints The encryption algorithm and authentication for the mirroring and availability group endpoints.
sys.service_broker_endpoints The same for Service Broker.
sys.server_permissions at class 105 Who may connect.
sys.dm_exec_connections Who is connected right now.
Report Why you would go there
master Server Permissions The grants behind Who may connect.
master Server Triggers A logon trigger fires on every one of these connections.
Connections The live sessions in full.
Security Posture Where a weak endpoint encryption belongs in the wider picture.
master Service Facts The port the instance is really listening on.

Frequently asked questions

An availability group endpoint shows RC4. That is the old default. It is worth changing to AES, and it is a change both replicas have to agree on, so it belongs in a window rather than in an afternoon.

An endpoint has zero connections. Either the feature behind it is not in use, or its partner has moved. Both are worth knowing about, and neither is visible from anywhere else in the product.

Why do I see endpoints I never created? The engine creates four or five for itself: the TSQL default TCP, named pipes, shared memory and via. Those are normal and are listed so the created ones stand out against them.

The permissions column is empty on the default endpoints. CONNECT on those is granted to public by default, so there are no explicit rows to show.