1

We're upgrading our software from using SQL 2000 to 2008 R2. Our customers will be installing an update which uninstalls 2000 and installs 2008 R2 under the same instance. So if no instance existed, then no instance name will be set (default). However, the problem starts with the customers which have a named SQL instance.

Starting in 2008 R2 (not sure of ones before), for some reason, a client connecting to the server by its instance name is unsuccessful. I'm testing from the Management Studio - if I can't connect this, then nothing can connect. I browse network servers, and find the specific server\instance in the list. But, upon trying to connect to an instance name like MyServer\INST, I get:

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: SQL Network Interfaces, error: 26 -
Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

I do in fact have TCP/IP and Named Pipes protocols enabled, this is the first thing I did. When I connect to the server using a comma (,) and port number like MyServer, 49195, it works just fine. So it appears that client computers are just unable to identify the instance names. This has happened on all our installations of SQL 2008 R2 and from all client computers, including Win 7, XP, Vista, Server 2008, and Server 2003. We never experienced such issues on earlier versions of SQL. The problem even persists if the firewalls and antiviruses are all disabled.

Now, this is a large update which we will be distributing soon to all our customers, and we want to minimize the interaction they need with us to get this installed. We absolutely hate the idea of using a port number, because it will always be different, and we would have to modify each client to point to this server/port. Some of our customers may have hundreds of client computers. How do I make client connections to a named SQL instance work again? After all, this is the whole purpose of named instances, and if a client can't connect to this instance by its name, then what is it even named for?

EDIT

It was mentioned to make sure SQL Browser is running, so I checked, and it is running. The server is also able to connect to its self (locally) - just external connections are refused.

UPDATE

After more careful checking, I learned the firewall wasn't completely disabled when testing, and upon disabling it completely, this works. So it appears that SQL Browser is being blocked by the firewall from external clients from accessing.

Improve this question
1
  • Make sure you mark on of the answers that helped you as the answer if they helped, if not write up your own answer so others know what fixed this for you.
    – Brent Pabst
    Jul 3, 2012 at 20:23

3 Answers 3

Reset to default
2

Try disabling your firewall, as it might block port 1434 for SQL Browser

Improve this answer
1
  • 1
    This did work, I thought I already had it disabled but only part of the firewall was disabled. Actually, adding port 1434 didn't work, but adding the sqlbrowser.exe service app to the firewall, it started working.
    – Jerry Dodge
    Jul 3, 2012 at 20:51
2

Is the SQL Browser Service Enabled and Started?

http://www.mssqltips.com/sqlservertip/1946/overview-of-the-sql-server-browser-service/

Improve this answer
7
  • Yes, SQL Browser is running.
    – Jerry Dodge
    Jul 3, 2012 at 15:07
  • Ooh, that's interesting. But the IP/Port combos work properly right?
    – Brent Pabst
    Jul 3, 2012 at 15:09
  • Do DNS queries properly resolve the server?
    – Brent Pabst
    Jul 3, 2012 at 15:09
  • I am using even IP Address, for example, 192.168.1.5\INST does not work while 192.168.1.5, 49591 does work.
    – Jerry Dodge
    Jul 3, 2012 at 15:11
  • Hmm, this really seems like a SQL Browser issue. There isn't really any reason why the other format shouldn't work as long as the instance is properly mapped to a port number. You don't have overlapping ports right?
    – Brent Pabst
    Jul 3, 2012 at 15:12
1

SQL Server 2005+ works differently than 2000 in identifying server instances.

The SQL Browser Service is probably not running.

See here.

In SQL Server 2000, the identification of the server connection endpoints was performed by the SQL Server service. SQL Server 2005 replaces that function with the SQL Server Browser service.

Improve this answer
1
  • Yes, SQL Browser is running.
    – Jerry Dodge
    Jul 3, 2012 at 15:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .