Connect to a SQL Server Named Instance from a remote server

UPDATE: The SQL Server Browser is actually needed. Don’t know how it worked before without it. See below for instructions.

Recently, we tried to setup a failover SQL server instance but couldn’t get out remote web servers to connect to it. The setup of our current live DB server had been done a few years ago and unfortunately we didn’t write any documentation on the tricky steps back then. This time, we’re smarter ;-) The important parts are:

1. Enable TCP/IP in the networking settings:
   • 
2. Set a static TCP port to connect to – otherwise the port will be dynamic and for every connection you’d need to find out which port is currently used. Enter the properties for TCP/IP connections and set a TCP port in the IPAll section. I use the default SQL Server port 1433:
   • 
   • Thanks go to http://stackoverflow.com/questions/9478957/unable-to-access-an-instance-of-sql-server-2008-r2-remotely :-)
   • I assume that it should be possible to specify a given port for only the publicly visible IP address (instead of for all) but unfortunately that didn’t work and I didn’t have the time to investigate the issue any further.
3. In the Sql Server Configuration Manager, make sure both SQL Server and SQL Server Browser services are running and have their start mode set to Automatic:
   • 
4. Open the firewall to allow connections through the port 1433 for the SQL Server service and through 1434 for the SQL Server Browser, potentially restricting access to only the hand-full IP addresses you need to connect:
   • 
   • For more SQL Server specific firewall port opening instructions see this Microsoft support article: http://support.microsoft.com/kb/968872/en-us.
5. Now you should be able to connect to the SQL server instance using <IP address>\<instance name>.

That’s it – happy SQL’ing!