SQL Server Endpoint

Configuring endpoint security

A SQL Server endpoint is a door opened from or to SQL Server. Everything that can go from your SQL Server to the network, or from the network to SQL Server, goes through an endpoint. Endpoints can be system or user-defined. The system endpoints allow the usual T-SQL connections to SQL Server to send queries.
The endpoints are defined for a specific protocol, which were either HTTP or TCP in the
previous versions. In SQL Server 2012, the HTTP endpoints previously used for the native
web services feature are removed. Now you only have TCP endpoints.
You can create a user-defined endpoint for three purposes: TCP requests, service broker, or
database mirroring. Here we will talk about the first one, which is useful to set up a dedicated
and secured connection to SQL Server for administrative purposes or distant access.

How to do it...

To configure endpoint security, follow these steps:
1. In a SQL query window, type the following T-SQL command:
CREATE ENDPOINT myTSQLEndpoint
STATE = started
AS TCP (
LISTENER_PORT = 8080,
LISTENER_IP = (127.0.0.1)
)
FOR TSQL ();
2. You will receive a message saying that it will cause the revocation of any Public
connect permissions on the TSQL default TCP endpoint. This effectively means that
all logins will lose their permission to connect to SQL Server through the default
T-SQL endpoint. If you still want to allow connection permissions to the default TCP
endpoint, issue the following command:
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];
3. You can view the state of the endpoints with this command:
SELECT * FROM sys.tcp_endpoints;
Chapter 1
45
4. You can start or stop the endpoints with the ALTER ENDPOINT command.
For example, you can stop the default TCP endpoint as follows:
ALTER ENDPOINT [TSQL Default TCP]
STATE = STOPPED;
The state we just creat@ed will remain even after a service restart.

How it works...

When SQL Server is installed, a system endpoint is created for each network protocol used
in SQL Server. The permission to access these endpoints is given to the Public server role.
Every login declared in SQL Server is a member of this role, and permissions on the Public
server role can be changed, unlike other fixed server roles. You can grant, revoke, or deny
permissions to connect to an endpoint to all the logins through the Public role, or to specific
logins by revoking CONNECT permissions to the Public role, and by granting specific privileges
as follows:
REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [a_specific_login];
If you want to allow connections to SQL Server from only a specific client IP address, you can
stop the default endpoint, or deny access to it, and create a user-defined T-SQL endpoint, with
a client IP address and a TCP port.
Stopping default endpoints has the same effect as disabling them in SQL Server
Configuration Manager.

There's more...

In SQL Server 2012, you can create user-defined server roles. We will detail this functionality
later. This interests us for now, because a server role could be used to grant CONNECT
permissions on an endpoint to a group of logins.
The following code creates a user-defined server role, adds a login as a member, and grants
the CONNECT privilege on the default TCP endpoint to the role:
USE [master];
CREATE SERVER ROLE [TCPRole];
ALTER SERVER ROLE [TCPRole] ADD MEMBER [my_login];
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO [TCPRole];

Reference: Microsoft SQL Server 2012 Security

Cookbook