Have you ever heard the term “endpoint” being thrown around in technical discussions related to SQL Server? If so, you might have wondered what exactly an endpoint is and why it is important. In this blog post, we will explore the concept of endpoints in SQL Server and understand their significance.

What is an Endpoint?

In the simplest form, an endpoint is a connection or point of entry into SQL Server. Think of it as the ends of a line, where each end represents a stop point that can connect to something else. Similarly, an endpoint in SQL Server serves as a point of entry into another component or service.

By default, SQL Server comes with a set of default endpoints. These default endpoints are owned by the system administrator (sa) and do not require any specific actions from the user. To view the default endpoints in your SQL Server instance, you can execute the following query:

USE master;
GO
SELECT e.name, e.protocol_desc, e.type_desc
FROM sys.endpoints e
WHERE e.endpoint_id < 65536;

This query will provide you with a list of default endpoints, including their names, protocols, and types.

It’s worth noting that any endpoint with an ID lower than 65536 is considered a default endpoint. All other endpoints are user-defined endpoints, which we will discuss further in the next section.

User-Defined Endpoints

In addition to the default endpoints, you can also create your own endpoints in SQL Server. When you create a user-defined endpoint, you become the owner of that endpoint by default. This ownership is similar to when you restore or create a database, where the default owner is the person who performed the restore or creation.

If you have implemented features like mirroring, Availability Groups, or Service Broker, you may have unknowingly created additional endpoints in your SQL Server instance. To identify the endpoints and their owners, you can modify the previous query as follows:

USE master;
GO
SELECT ISNULL(e.name, 'Unused PayloadType') AS EndpointName,
       e.endpoint_id,
       sp.name AS EndpointOwner,
       CASE WHEN e.protocol IS NULL AND oa.typeid = 1 THEN 1
            WHEN e.protocol IS NULL AND oa.typeid = 3 THEN 2
            WHEN e.protocol IS NULL AND oa.typeid = 4 THEN 2
            ELSE e.protocol
       END AS protocolid,
       CASE WHEN e.protocol IS NULL AND oa.typeid = 1 THEN 'HTTP'
            WHEN e.protocol IS NULL AND oa.typeid = 3 THEN 'TCP'
            WHEN e.protocol IS NULL AND oa.typeid = 4 THEN 'TCP'
            ELSE e.protocol_desc
       END AS protocol_desc,
       oa.typeid,
       oa.PayloadType,
       e.state,
       e.state_desc,
       e.is_admin_endpoint
FROM sys.endpoints e
INNER JOIN sys.server_principals sp ON e.principal_id = sp.principal_id
RIGHT OUTER JOIN (VALUES (1, 'SOAP'), (2, 'TSQL'),
                    (3, 'SERVICE_BROKER'), (4, 'DATABASE_MIRRORING')) AS oa (typeid, PayloadType)
ON oa.typeid = e.type;

This modified query will provide you with a comprehensive list of both in-use and unused endpoint types, along with their owners. It’s essential to know the owners of your endpoints, as it can help you manage and maintain your SQL Server environment effectively.

If you find that the owner of a particular endpoint is no longer present in your organization or you want to change the ownership for any other reason, you can use the following script:

USE master;
ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO sa;

In this example, we are changing the ownership of the “Hadr_endpoint” to the system administrator (sa). Make sure to replace “Hadr_endpoint” with the name of your endpoint in the script.

Conclusion

Endpoints are a fundamental component of SQL Server, serving as points of entry into various components or services. Understanding your endpoints and their owners is crucial for managing your SQL Server environment effectively. By knowing the owners, you can ensure that the right individuals have the necessary permissions and avoid any potential issues in the future.

So, take some time to explore your SQL Server endpoints and familiarize yourself with their owners. It may save you a few grey hairs down the line!