Connect to a SQL Server Named Instance from a remote server

by Oliver 23. June 2012 19:26

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:
    • image
  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:
  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:
    • image
  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:
  5. Now you should be able to connect to the SQL server instance using <IP address>\<instance name>.

That’s it – happy SQL’ing!

enjoyed the post?

Tags:

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

About Oliver

shades-of-orange.com code blog logo I build web applications using ASP.NET and have a passion for javascript. Enjoy MVC and Orchard CMS, and I do TDD whenever I can. I like clean code. Love to spend time with my wife and our three children. My profile on Stack Exchange, a network of free, community-driven Q&A sites

About Anton

shades-of-orange.com code blog logo I'm a software developer at teamaton. I code in C# and work with MVC, Orchard, SpecFlow, Coypu and NHibernate. I enjoy beach volleyball, board games and Coke.