How to disable remote access to Microsoft SQL Server

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.

SQL Server Configuration Manager

  • To do that we need to disable listening on all network adapters

TCP IP properties

  • 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 (::1 for IPv6)

TCP/IP Properties disabled

  • 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.

Log File Viewer

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>