You can find a lot of resources describing how to enable remote access to MS SQL Server, but I couldn’t find anything describing how to restrict remote access.
You might need to disable ability to connect to SQL Server instance from network altogether if you place SQL Server in the DMZ (screened subnet) and the web/application server located on the same box is the only service that needs an access to the database.
There are several ways we can do that:
1. Using Windows Firewall. Just make sure that Firewall is enabled and firewall rules deny an access to all ports your SQL Server uses. We don’t need to do anything if all rules are defined by default and haven’t be changed.
2. Using IPSec. We can define rules for IPSec driver to drop any pockets that are related to SQL Server ports. This is the best option if you need to restrict access from specific networks or devices. There are a lot of documentation and blog posts on how to set up IPSec, so you should be able to find this information.
3. If we cannot enable Firewall or IPSec for whatever reason then we can disable remote communication in SQL Server itself by using Network Configuration Manager.
- Open TCP/IP Properties in SQL Server Network Configuration. One of the options would be disabling all protocols except for Shared Memory. However, many application require TCP/IP even if they run on the same box with SQL Server. That’s why I recommend to leave TCP/IP enabled and restrict access to local host only.
- To do that we need to disable listening on all network adapters
- Choose network adapters and port numbers that SQL Server will be listening on. In our case we want to leave only local loppback for IPv4 and IPv6. That means that local applications and services will be able to access SQL Server by using ‘localhost’ name and loopback IP address 127.0.0.1 (::1 for IPv6)
- Restart SQL Server service and check SQL Server logs to make sure that server is listening on locahost only (127.0.01 and ::1). Make sure that no other IP addreses are on the current log.
Now your SQL Server available only on the local computer and unavailable from the network.
The only downside of this configuration is that you need to run SQL Server Management Studio and SQLCMD as any other application from the server itself. Hopefully you have remote desktop access to the server.
By the way, be aware that unchecking ‘Allow remote connection to this server’ under Server Properties in SSMS doesn’t prevent client connections. This option is for server-to-server communication only and controls execution of stored procedures from local or remote servers.
The method described above should work in SQL Server 2000, 2005, 2008, 2012. If you experienced any issues with that approach please share your experience in the comments.