In the land of Oracle, if you want to prevent user access to your database, you stop the listener. Without that agent running, you can still connect to the database locally, via shared memory, but all TCP/IP connectivity is disabled. As a DBA, this is pretty handy if you need time to perform maintenance tasks without users accessing the system. But that’s Oracle.

SQL Server doesn’t have a listener agent. Sure, there’s the SQL Browser, but stopping that service won’t actually prevent remote access to your instance. In fact, if you’re running on the default port, 1433, stopping SQL Browser won’t do anything at all. So, if you need time to perform some system maintenance in SQL Server while users are off the system, what can you do to prevent connections?

One answer might be to alter all the logins and DENY CONNECT permissions. You can find scripts online to automate the process. Personally, I’m hesitant to start altering all my logins like that. Sure, it should work ok, but it just seems a bit…invasive.

You could disable TCP/IP protocol in Configuration Manager. If a client tries to connect using TCP/IP the connection will fail. After a failed connection the client will automatically attempt to connect using other protocols, like Named Pipes. So if you’re going to go this route, make sure that your other remote protocols are disabled, too.

There’s another way, though. But before we get into that, let’s back up a bit and talk about how clients talk to the SQL Server instance. Don’t worry, we’ll stick to a 100 foot level.

SQL Server Network Interface (SNI) and Tabular Data Stream (TDS)

Both the SQL Native Client and the SQL Server instance use a protocol layer called the SQL Server Network Interface, or SNI, to communicate with each other over a generic network protocol, like TCP/IP. When the client wants to send a message to the server, T-SQL query for example, the SNI packages the message into a Tabular Data Stream (TDS) packet and ships it off over the network.

On the server, there are a set of endpoints listening for TDS packets. Each network protocol has one of these TDS endpoints assigned to it, whether the protocol has been enabled or not. There’s also one specifically for DAC connections. You can view these system endpoints in the sys.endpoints system view. You can also view information on TCP/IP-specific endpoints using sys.tcp_endpoints

  select * from sys.endpoints
 select * from sys.tcp_endpoints 

Stopping the “listener”

You can’t drop any of the system endpoints, but you *can* stop them. When you stop an endpoint, it still listens, but any attempted connections are immediately closed. This is different from disabling the network protocol. Remember that, when you disable the TCP/IP protocol for SQL Server, the client will automatically try to connect using another protocol. However, when you stop the “TSQL Default TCP” endpoint, the client doesn’t try other protocols. It just gives up.

 alter endpoint [TSQL Default TCP] state = stopped 

To re-enable connectivity, simply start the endpoint.

 alter endpoint [TSQL Default TCP] state = started 

You should note that, while stopping the endpoint will prevent any new connections to the instance, it will not close any existing connections. So if your purpose is to get all users off the system, you’ll want to stop the endpoint and then start closing existing sessions (gracefully or otherwise).