This post demonstrates the use of endpoints to secure application connectivity to an instance of SQL Server.
The first step in the demonstration is to create two logins to test the functionality of the endpoints. In the code below, I’m using local user accounts on a machine named MyServer. You’ll need to create these accounts on your test system and update the code appropriately if you want to recreate this demonstration:
CREATE LOGIN [MyServer\TestUserX] FROM WINDOWS;
CREATE LOGIN [MyServer\TestUserY] FROM WINDOWS;
With the logins created, the next step is to create the endpoint. This endpoint listens on TCP port 5000 for all IP addresses associated with the server and is intended to accept traditional client-server interactions:
CREATE ENDPOINT [MyAppEndPoint]
STATE = STARTED
AS TCP (
Execution of this statement returns the following message:
Creation of a TSQL endpoint will result in the revocation of any ‘Public’ connect permissions on the ‘TSQL Default TCP’ endpoint. If ‘Public’ access is desired on this endpoint, reapply this permission using ‘GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]’.
This message indicates the default TSQL endpoint which is not accessible to those outside the system administrator role or who otherwise haven’t been granted explicit access. To return this endpoint, the statement provided at the end of the message should be executed.
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO [public];
At this point, any login can connect to the default TSQL endpoint. If we wished to restrict TestUserX to connect by just the newly created endpoint, we need to grant TestUserX connect permissions to it and revoke TestUserX connect permissions on any active default endpoints:
GRANT CONNECT ON ENDPOINT::[MyAppEndPoint] TO [MyServer\TestUserX];
DENY CONNECT ON ENDPOINT::[TSQL Default TCP] TO [MyServer\TestUserX];
DENY CONNECT ON ENDPOINT::[TSQL Named Pipes] TO [MyServer\TestUserX];
DENY CONNECT ON ENDPOINT::[TSQL Local Machine] TO [MyServer\TestUserX];
Now the server is configured to allow connection attempts by most users to the default TSQL endpoint but TestUserX and only TextUserX can connect to our newly created endpoint. To test this, open a command-line window and execute the following to launch SQL Server Management Studio (SSMS) as TestUserX:
runas /user:MyServer\TestUserX ssms
Once SSMS launches, attempt to connect to MyServer without specifying a port number. A login failed error will be raised. Then attempt to connect to MyServer using TCP port 5000, i.e. MyServer, 5000. The connection attempt should succeed.
Close SSMS, return to the command-line window and execute the following to launch another copy of SSMS but this time as TestUserY:
runas /user:MyServer\TestUserY ssms
Again, attempt to connect to MyServer without specifying a port number. The connection attempt should succeed. Repeat this against TCP port 5000 and the connection attempt should fail.
Once you are done with the demonstration, you can drop the logins and endpoint through the following statements:
IF EXISTS(SELECT * FROM syslogins WHERE name=’MyServer\TestUserX’)
DROP LOGIN [MyServer\TestUserX];
IF EXISTS(SELECT * FROM syslogins WHERE name=’MyServer\TestUserY’)
DROP LOGIN [MyServer\TestUserY];
IF EXISTS(SELECT * FROM sys.endpoints WHERE name=’MyAppEndPoint’)
DROP ENDPOINT MyAppEndPoint;