Problem using named instances

[ddlink]

Hello!

I've been struggling with this problem for some time. I cannot use named instances directly. I can use either the port number for the named instance or create an Alias but not a dynamically assigned named instance port number.

I think this is an error within SQL-server because:
1. netstat -ano shows that sqlserver is listening on udp 1434 (which resolves named instances)
2. There is no problem with firewalls because I've tried to connect on both the same network and locally.
3. I can see proof with Microsoft utility PortQRY v2.0

PortQRY looks like this on a working server:

Querying target system called:

192.168.1.20

querying...

UDP port 1434 (ms-sql-m service): LISTENING or FILTERED

Sending SQL Server query to UDP port 1434...

Server's response:

ServerName SQL-Server1
InstanceName MSSQLSERVER
IsClustered No
Version 8.00.194
tcp 1433
np \\SQL-Server1\pipe\sql\query

==== End of SQL Server query response ====

UDP port 1434 is LISTENING

but on my server it looks like this:

Querying target system called:

192.168.1.20

querying...

UDP port 1434 (ms-sql-m service): LISTENING or FILTERED

Sending SQL Server query to UDP port 1434...

UDP port 1434 is FILTERED


Any ideas?

 

[gmmastros]

From the server, you can run the svrnetcn utility. This will tell you the port number that your named instance is using.

Click Start -> Run
Type: svrnetcn

When the utility opens, you will see a combo box that shows the instances on the server. Change it to your named instance. tcp/ip should be an 'enabled protocol'. Click tcp/ip and the properties. The port that your named instance uses will be displayed on the screen.

This port needs to be opened on your firewall.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ddlink]

Thank you for the fast reply!

This is not the problem. I can see all the instances on the server the way you point out. I can even connect to them using either static port, alias or a connection string with the port number. But I cannot connect to any instance using names. Neither does the instances show up automatically when browsing the server using any SQL-browser.

Normally an SQL-server listens on TCP 1433 (default instance) and on UDP 1434 (tells you which instances there are and which port number to find them on.)

Using portqry like this:
c:\portqry\portqry -n server -e 1434 -p UDP

querys the server about its instances and should tell you all the instances there is. But my server does not do that. Netstat shows that its listening on 1434 UDP but nothing is replyed from there.

Server is 2003 and SQL 2000 std.

More info on portqry:

http://support.microsoft.com/default.aspx?kbid=832919

 

[gmmastros]

Are you 'hiding' your servers.

In the svrnetcn utility, as described in my previous post, click tcp/ip, then properties. The properties screen shows you the port number, but there is also a check box for 'Hide Server'. If you hide the server, then it will not be enumerated in a list of servers.

Unless I am mistaken, making changes in the properties dialog of the SQL Server Network Library will NOT take affect until you stop and restart the SQL Server service.

Good Luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

First you will need to patch all the SQL Servers to at least sp3a. This is required to connect to a named instance on SQL 2003.

Is SQL isn't patched Windows 2003 will automatically block access to UDP port 1434 because it's not secure if it's not patched.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[ddlink]

George, none of the instances are hidden.

Denny, all instances are SP3a. Default is now even SP4. I have been suspecting that the order of installation might be the problem. That I have installed several instances without upgrading to SP3.

Anyway, all instances are SP3 (8.00.760) except default which is SP4 (8.00.2039).

Could there be something with 2003 SP1 and SQL server?

 

[mrdenny]

Something is blocking UDP port 1434. You'll want to check the builtin firewall software and open up that port.

Windows 2003 will block that port if the firewall is enabled.

There are some issues with 2003 SP1 and SQL 2000, but it's almost all related to the firewall.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ddlink]

Something is wrong with UDP port 1434.

[tt]
C:\>netstat -ban | find "1434"
UDP 0.0.0.0:1434 *:* 1544

C:\>netsh firewall show state
The service has not been started.

C:\PortQryV2>PortQry.exe -n localhost -e 1434 -p udp

Querying target system called:

localhost

Attempting to resolve name to IP address...


Name resolved to 127.0.0.1

querying...

UDP port 1434 (ms-sql-m service): LISTENING or FILTERED

Sending SQL Server query to UDP port 1434...

UDP port 1434 (ms-sql-m service): FILTERED
[/tt]

As you can see the firewall is not turned on, netstat shows that sqlserver is listening globally on UDP 1434 and a local portqry doesnt show any difference than a portqry from another machine.

 

[mrdenny]

If netstat shows that it's open but you can't connect to it, then something is blocking the connection. Are you sure that the default instance is patched to SP3 or above?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ddlink]

Since yesterday all instances are SP4. No difference.