Tuesday, April 29, 2014

Enabling remote connections to a named instance of SQL Server

Recently, I installed SQL Server 2012 on a machine that I also had SQL Server 2008 R2 installed.  Because the default instance was the 2008 instance, I had to create a new instance that I named STEVE2012.  Even though STEVE2012 was configured to allow remote connections (by default), I still got the tell-tale "make sure remote connections are allowed" error messages when I tried to connect a web app to this instance.  I had to do the following:

  1. Open up SQL Server Configuration Manager (You can find this in the Start Menu under Microsoft SQL Server).
  2. Expand SQL Server Network Configuration and then click on Protocols for STEVE2012.  Double-click TCP/IP and set the Enabled property to "Yes".
  3. Then, click SQL Server Services in the tree view, and restart the service for STEVE2012.
So Named Instances are listening on a dynamic port rather than the standard 1433.  If your database server has a firewall (mine doesn't...development), you'll need to configure the instance to listen on a static port using the configuration manager.  Then open up this port in your firewall.

No comments:

Post a Comment