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.

Enpoints
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.

SCCM network
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
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.

Test endpoint
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.

SCCM TCP port
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 demo
Connection with port number
Connection successful.

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s