Advertisements
Home > Troubleshooting > SQL Server Browser Service and Default Instances

SQL Server Browser Service and Default Instances

Microsoft SQL Server Browser service is critical component of SQL Server, that lets allows users to connect to an instance if the default port has been changed or SQL Server is using a dynamic port. Right!?!

No!  Or so I found today, I cannot get SQL Server Browser service to work with default instances.  It only reports port information for named instances.  The Books Online article says “On startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all SQL Server instances on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser will return all ports enabled for SQL Server. SQL Server 2005 and SQL Server Browser support ipv6 and ipv4.” [ref].  However it doesn’t work.

After digging into Microsoft Connect came across this article.  Looks like a known issue with no solution.

My on testing to prove it to myself.

  1. Start SQL Server Browser service from Command Prompt with -c option.  This will cause the Browser Service to report all incoming/outgoing requests.
  2. Change the default port on default instance from 1433 to different port, lets say 51000.
  3. Disable all protocols expect TCP/IP.
  4. Restart SQL Server Instance.
  5. Open up SQL Server management studio and try to connect to SQL Server now.

We get the following error:

TITLE: Connect to Server
——————————

Cannot connect to SQLServer-Test.

——————————
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

So interesting thing here is why is it using Named Pipes? I am unsure why SSMS is defaulting to Named Pipes.  My protocol priority lists it as #3.  Alright I am not troubleshooting this so tried to force TCP/IP.

TITLE: Connect to Server
——————————

Cannot connect to SQLServer-Test.

——————————
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

No matter what I do I cannot get connection.  I have to use “Server,Port” for it to work.  I have also used Network Monitor to try to catch what packets are being sent, nothing, zilch, zero, nadda.  Urggg!

Anyhow lesson learned .. I have submitted MS Connect article to add that info the Books Online? Hopefully? (Link).

Interesting things learned while testing this…

  • There is no SQLBrowser.exe in the Microsoft SQL Server\100 or Microsoft SQL Server\110 folder.  Even with my SQL 2012 install it is in Microsoft SQL Server\90 folder.
  • It is a 32-bit only application (makes sense I guess).
  • Learned about the -c option to do troubleshooting and testing.

PS the browser service was running, tested against SQL 2008, SQL 2008 R2 and SQL 2012.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: