Connecting to SQL Server 2000
-
Hello,
We have a single instance of SQL Server 2000 on a network server. In its Server Network Utility its default port is 1355.
On the client side in the client network utility the default port is 1433.
My assumption was that it wouldnt connect but it does
When it did my next assumption would be that it used UDP port 1434 to get the ip and port information from the server and then connect on 1355. However I used Wireshark to monitor the ip address and separately tcp pot 1433, 1355 and udp port 1434 and each time connected to the database
For port 1433 and 1434 it said there were no packets. Only when I set it to monitor 1355 were there packets
This has got my boggled. How can it know to connect on 1355 when it doesnt seem to be using udp port 1434 and the tcp port on the client is defaulted to 1433.
It is not a named instance and I also read that default instances assume 1433 if they cant connect.
But if the server network utility has a default of 1355 how can it be connecting.
Every now and then we have connection problems and the server needs to be restarted to clear them but I wanted to understand this connection issue to pursue a more intelligent and informed solution than "switching it off and on again"!!!!!!
Thanks in advance for any help
P
-
I believe the client queries port 1434 and the "SQL Server Browser Service" on the server tells the client which actual port to connect to; it does't try 1433 and then fail...it asks the service on 1434 where is the server, and gets the correct reply for whihc port.
if you stop that service(Administrative Tools>>Services), then you'd see the behavior you expect: failure to connect, unless you change the connection string to explicitly use the correct port.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi,
Thanks for your reply.
I thought the 1434 might have something to do with it which is why I tried to monitor it using Wireshark but when I did it said there were no packets being exchanged to or from the client to that particular IP address on that udp port.
Thats what got me double stumped.
Does anyone know an easy way of monitoring the communication between client and server for a particular udp port to see what is actually happening.
Thanks again
P
-
Hi again,
Another thought.....as the client is using a thrid party software application to connect to the database would the connection string be governed by the default port in Client Network Utility or would it be buried in their code. (I imagine it would be in the network utility allowing customers to change ports for security reasons but want to confirm my understanding).
Also when we get the connection problem which is a
[DBNETLIB] SQL Server does not exist or access denied
error on a client which could connect yesterday but doesnt today when others can connect what would be my best bet for properly investigating why this happened rather than the quick fix approach of switching it off and on again
Thanks again
P
-
every application I've ever seen always had the components of the connection string configurable...whether it's part of the GUI, or an ini/config file, the connection strings going to be configurable...they simply cannot assume the server name/databasename/username/password is static accross multiple installations, unless they control everything. If you have it installed, there's the ability to change the connection info, I'm sure.
the error "SQL Server does not exist or access denied" would be the error i would expect if the SQL Server Browser Service was no longer answering requests....the client tries to connect on 1433 because the service didn't respond, and times out after 30 seconds. stopping and starting that specific service would be the first thing i'd test, rather than bouncing the server.
Like i said, it's something you can easily test....set your own local instance of SQL to an alternate port, connect via SSMS by servername., then stop the service on your machine, adn try to reconnect. confirm it fails. try to connect as servername,1355 and confirm you can connect when specifying the port name.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi Lowell,
Appreciate your help.
The odd thing is that when we get the error on one client which might be the service not accepting requests we can connect with another client. The clients that cant connect can only re-connect when the server is bounced.
I tried to stop the service to test that - I assume I have to stop it on the server and not the client - but I wasnt sure what the sql server browser service is called as we have SQL server 2000. I stopped the one called MSSQL which said it was also going to stop SQLAGENT but the whole database stopped then.
Then I stopped SQLAGENT by itself but could connect on port 1433
Sorry to be a bother but when I have issues like this I become obsessed with getting to the bottom of them!!!!
Also do you know any good books that I could use which cover things like "the database only uses SQL server browser but reverts to the default port if it isnt accepting requests" and other things like that. I have been looking at Professional SQL Server Administration from Wrox but if you have gone through the process and have any advice I would be grateful
Thanks again
P
-
There was no separate browser service in SQL 2000--the SQL Server service itself does that job.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply