Quick one today:

Sometimes, there are times where there is a need to quickly prevent/disable any client connections to Sql Server. The occasions that warrant such drastic need are rare, but they do occur once in a while.

During a scheduled and planned deployment, we go through the steps to disable web and app servers to stop any connections to database servers. Once the traffic is dead, we’ll start with our database deployment steps. This is standard approach and it works.

Today, we’ll talk about a couple of different approaches that are more drastic. These are useful only in some rare occasions where the above step is not possible.

Options

1. Disable TCP/IP
2. Disable “Remote Connections”

Details

Option 1: Disable TCP/IP

Generally all connections to Sql Server from outside the server machine itself come through TCP/IP. Other network protocols are still used, but not with the same frequency. As a side note, only enable those protocols that you actually need; And disable all others. In our production environment, we only enable “Shared Memory” & “TCP/IP”.

• TCP/IP is used by all client connections that connect to Sql Server over network. Like my laptop SSMS connecting to Sql Server on a lab server. It uses TCP/IP protocol.
• Shared Memory is used when I’m logged into the lab server (RDP) and open SSMS to connect to Sql Server on the same machine. Or connecting to Sql Server running on my laptop using SSMS on my laptop.

So, we only enable Shared Memory and TCP/IP.

Coming back to the point, to disable TCP/IP, open Sql Server Configuration Manager, and go to “Sql Server Network Configuration”, then click on the relevant instance. Seethe image below.

Disable TCP IP

Option 2: Disable Remote Connections

Go to Sql Server Instance level “Server Properties” by right clicking on the Server and choosing Properties. Go to “Connections” and un-check the option to “Allow remote connections to this server”. See the image below.

Disable Remote Connections

Please note that this is not a standard approach, but just a technique for outlier situations.

_Sqltimes