Securing your SQL Server using endpoints

As per MS docs, endpoint is a service that can listen natively for requests. What that means is endpoint is basically the “gateway” to your SQL Server.

Any connection coming to SQL Server always comes through an endpoint, if you are connecting to SQL Server using SSMS then that connection is built using endpoint, any .Net application connection, any sqlcmd connection, even the DAC connection will go through the endpoint. And yes as we all know about the mirroring endpoint for Database Mirroring and now Always-On.

From Instance Endpoints

The T-SQL System endpoints: TCP, Local Machine, Named Pipes, VIA and DAC. The first 3 are the ones which we configure using SQL Server Configuration Manager. VIA is now deprecated. DAC is for sysadmin members only.

From SQL Server Configuration manager

We generally don’t provide permission to TCP endpoints but that can be done, and for one client I encountered the not so common usage of the endpoints.

The issue started with a user reporting login failure, with the common error 18456. On checking the logs got to know that the error was very different than usual ones.

Login failure (duplicated in my environment)

Error read like: Login failed for user ‘test’. Reason: Login-based server access validation failed with an infrastructure error. Login lacks connect endpoint permission. [CLIENT: 169.254.165.179]

On checking the endpoints got to know that they have created many other endpoints for TSQL.

Created endpoint named Testendpoint for demo

The below command is used to create the above endpoint:

CREATE ENDPOINT [Testendpoint] 
STATE = STARTED 
AS TCP 
   (LISTENER_PORT = 2123, LISTENER_IP =ALL) 
FOR TSQL() ; 
GO

Client environment:

Client being a small organization didn’t have many SQL Server licenses and was using a single instance to hold databases for many different applications.
The AD groups created for each application had different naming conventions and permissions to their specific databases. Till here it is common and can be seen at many places, but they had went a step ahead and had configured endpoints for each application on a specific Port number dedicated to that application.
AD groups for each application had GRANT permission to the endpoint with specific TCP port only and the system defined default TCP endpoint was disabled for all.

If you want to secure the application based on port number you can follow the below steps:

Step 1

Add all the required port numbers for the SQL Server Configuration Manager.

Added ports 2122,2123.

This will require service restart for SQL Server.

Step 2

Once added into SSCM. Create endpoints for each TCP Port using the below command. (change the port number with your specific port number)

CREATE ENDPOINT [Testendpoint] 
STATE = STARTED 
AS TCP 
   (LISTENER_PORT = 2123, LISTENER_IP =ALL) 
FOR TSQL() ; 
GO

Step 3

Once all the endpoints are created, GRANT CONNECT to groups/users to their endpoint. And DENY CONNECT to the rest.

DENY CONNECT ON ENDPOINT::[TSQL Default TCP] to [test]
GO

GRANT CONNECT ON ENDPOINT::[Testendpoint] to [test]
GO

Step 4

Once that is done provide the specific Port numbers to the groups so that they can add that in their connections string.

Connection with port number

And similar to this for the user who was getting login failure we found out the endpoint created for the application, provided Connect permission to the user and shared the port number to the user for making the connection. It worked fine thereafter.

Advertisement