SQL Server Performance Forum – Threads Archive

Connection to named instances

I have looked at most of the post on this site regarding connection to named instances and I am still confused so I am creating this new posting. I am run SQL2K. On server abc, I have created a default instance and a named instance called name1. I can see the two different services running on the server (MSSQLSERVER and MSSQL$NAME1). Before anyone reply saying that these are stupid names for server and instances, they have been changed to protect the innocent. From a remote client (also SQL2K), I can connect to the default instance on server abc by issuing the following command: osql -S abc -E However when I tried to connect to the named instance on server abc with the following command: osql -S abc
ame1 -E I get the following error
[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()). My understanding is that I should be able to connect to named instance on server abc using the naming standard server_nameinstance_name without having to supply the port number. Both the client and server network configuration is set to accept tcp/ip and named pipe (in this order). I have created the following aliases on the client and successfully used them to connect to both the default and named instance on server abc. default_abc tcp/ip connection parameters: abc
named_abc tcp/ip connection parameters: abc
ame1,1257 where 1257 is the port number of the named instance. osql -S default_abc and
osql -S named_abc both are successful. Is this normal behavior that I cannot connect to named instance by using abc
ame1? What is the best way to handle connection to named instances. Should standard aliases be created on all clients that will be accessing the named instances or is there a better way to solved the connection to named instance issue? I don’t want applications to have to code port number into their code. What happen if the port number is changed, will all aliases have to be changed to reflect the new port number? This could become a maintenace issue.

Did you try osql -S name1 -E.?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Luis, I have not tried that originally but I have just tried it now and it does not work. Without the server name in front of the instance name, it doesn’t know where to go. As long as I put the port number after the instance name, it works. osql -S abc/name1,1257 -E (this works). However, it is really bad to have to hardcode a port in the connection. Am I confused in that normally I should be able to connect using osql -S abc/name1 because of dynamic port. Thanks,
Jethro.

Hi ya, is there a firewall between the client and the server? or has the server been set up as a ‘hidden’ server? It looks like the client is not able to get to your server on port 1434 to find out what port to connect on… Cheers
Twan

To isolate connectivity problems, disable the Shared Memory Net-Library using the SQL Server 2000 Client Network Utility. If you change the port number on server to connect then ensure all the clients are updated to connect to the newly defined port number, and before that you need to restart SQL server services to affect the new port number. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Twan, To determine if port 1434 is opened, I manually set the port for the named instance to 1434 and restarted the service. I then connected by osql -S abc/name1,1434 -E and this work. I am assuming this indicates that at the very least port 1434 is open and not blocked for any reason. Is this a correct assumption? I reset the named instance to again use dynamic port by setting default port to 0 and verified the registry setting to determine that the appropriate port number is in the registry (TcpDynamicPorts and TcpPort). The registry setting are pointed to the correct port. I tried to connect using osql -S abc/name1 -E and this fails. Howerver osql -S abc/name1,2458 works. I understand that a listener service is started at the instance startup and populates port 1434 with all relevant instance information such as port number, etc. How can I verify if the listener service is doing its job and that information is available via port 1434? Is there a utility that I can use to extract what information is available via port 1434? Thank You,
Jethro

Hi ya, the port 1434 should have a listener on the server. it uses UDP, so is the test that you did where you set a sql instance to 1434 is not quite the same since that is TCP based. I don’t know of a utility to test this other than perhaps osql -L but I’m not sure if that always lists all of the servers… If there are firewalls in the way then chec (or ask) if port 1434 UDP is being allowed to the SQL server. There are security reasons why this is not normally the case… Cheers
Twan

jethrojaw, I am facing a similar situation right now, and I would be interested in knowing if your problem has been resolved. You can verify that the server is listening on UDP 1434 by issuing the <pre id="code">netstat -a</pre id="code"> command. You should see a line like this: <pre id="code">UDP servername:ms-sql-m *:*</pre id="code"> ms-sql-m stands for Microsoft-SQL-Monitor and ms-sql-s stands for Microsoft-SQL-Server (see <a href=’http://www.iana.org/assignments/port-numbers’ target=’_blank’ title=’http://www.iana.org/assignments/port-numbers'<a target="_blank" href=http://www.iana.org/assignments/port-numbers>http://www.iana.org/assignments/port-numbers</a></a>). I found that using the following source in the connect string works: <pre id="code">source=tcp<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />ervername,1130</pre id="code"> where 1130 is the port that the named instance is listening on. The following alternatives didn’t work: <pre id="code">Source=ServerNameInstanceName,1130 Source=ServerNameInstanceName Source=192.168.10.4InstanceName Source=192.168.10.4InstanceName,1130</pre id="code"> Obviously, I also happen to think that it’s plain wrong to hard code the port number in the connect string. What gives? I’m running SQL Server 2000 SP4 on Windows 2003. The client is a web server on Windows 2000 within the same domain (no firewall between the machines). I have no problem connecting to the default instance. The stupid thing is that I can connect from the web server using servername
amedinstance in Query Analyzer. It’s only from IIS when using a connection string that I can’t connect. I’ve applied SP4 on the web server too, so I’m using the latest version of MDAC. Thanks for any help.

]]>