SQL Server – Configure to Associate User Login with Specific Port

configurationlistenersql server

We have SQL Server instances that sit in our DMZ, and associated firewall rules to allow access to them from our corporate network.

The server is correctly listening on the configured ports, but now I need to configure the RDBMS to grant access to specific users on a specific port.

Apparently there is a T-SQL script that will do what I need, but I can't find it anywhere. Can anyone point me in the right direction or alternatively, show me how to do this using Management Studio's GUI?

Best Answer

See the instructions at:

Configure the Database Engine to Listen on Multiple TCP Ports

To set up a new TDS endpoint a code sample is:

USE master;
GO
CREATE ENDPOINT [CustomConnection]
STATE = STARTED
AS TCP
   (LISTENER_PORT = 1500, LISTENER_IP =ALL)
FOR TSQL() ;
GO

The side-effect of creating a Custom Connection is that you will need to reestablish the Default Connection:

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];

Then to grant access to the CustomConnection endpoint to the SQLSupport group (as an example), you could use something like this: 

GRANT CONNECT ON ENDPOINT::[CustomConnection] to [corp\SQLSupport] ;
GO

Read the other details in that post that may need configuring.

Related Question