August 25, 2005 at 11:43 am
Hello all!
Need some help
We got 3 machince say Pc1, Pc2, Pc3 - There are two instance of sql server on pc1. OS on all the systems is WIN XP PRO SP2
Instances on PC1 are "Server" and "Server\Goody" all the machince are able to access "Server" but "Server\Goody" is not accessable it give an error saying "[DBNETLIB] Sql Server does not exist or Access denied"
leoaugust
August 25, 2005 at 12:07 pm
What does the window's error log say?
August 25, 2005 at 12:33 pm
What entries are in the server network utility?
Tom
August 25, 2005 at 2:51 pm
I have to deal with these issues and usually there are 3 reasons:
1. Firewall: default instance is on port 1433. You have Firewall open for 1433 but the named instance is running on a different port. Check Server Network Utility
2. SQL Browser (have a couple of other names too) Uses port 1434, usually blocked. The client can not browse instances then
3. Enabled Protocol: If the named instance is MSDE then it is possible that none of the TCP/IP ot Named Pipes are enabled by default. Only Shared Memory. You have to enable at least one. Additionally, port may be different of what you expect. Check Server Network utility
Yelena
Regards,Yelena Varsha
August 26, 2005 at 5:41 am
Thanks for reply,
We have disabled our firewall which on by default in WIN XP SP2.
Still the problem persist.
Point 2 is not understood properly.
In our server network utility we have enabled TCP/IP and MultiProtocol.
August 26, 2005 at 10:20 am
Have you checked the errorlog for that instance, per Remi's suggestion? Make sure that the system is actually listening on both TCP/IP and Named Pipes. I have seen many instances where the protocols are enabled but the instance was not listening for some reason. You should see entries like the following somewhere in the log:
SQL server listening on 10.143.62.60: 1433.
SQL server listening on 127.0.0.1: 1433.
SQL server listening on TCP, Shared Memory, Named Pipes.
Also, when you check the Server Network Utility make certain you have selected the right instance from the dropdown box. (Forgive me for stating the obvious but I have tripped over that one myself.)
Are you using SQL Mixed Mode or NT Authentication to connect to the named instance? Are the logins still present? The "Access denied" portion of the message may be the critical piece.
Don
August 27, 2005 at 12:07 am
Thanks Donaldw,
I have not checked the ErrorLog, i will certainly. But i have checked few other things like, I am not able to access the Sql Server by IP Address of the machine, whereas i am able to access through Server Name. In case of the NT Server (which forgot to mention in a hurry sorry for that) which the main server on the network i am able to access by both IP Address and Server Name.
What does the above means? No other machine is able to access through IP Address. I have tried pinging with other machine it is working fine. But Sql server with IP Address is not accessable between nodes.
Suggest
Leoaugust74
August 27, 2005 at 12:15 am
Donaldw / Remi,
I am using Sql mixed mode to access the server.
Leoaugust
August 29, 2005 at 3:04 am
Remi / Donaldw
My errorlog reads something like this
2005-08-26 10:11:43.94 server SQL Server is ready for client connections
2005-08-26 10:11:43.94 server SQL server listening on Shared Memory.
2005-08-26 10:11:13.21 spid3 Recovery complete.
2005-08-26 10:11:12.98 spid5 Starting up database 'tempdb'.
2005-08-26 10:11:12.15 spid5 Clearing tempdb database.
2005-08-26 10:11:11.91 spid3 Skipping startup of clean database id 5
2005-08-26 10:11:11.91 spid3 Skipping startup of clean database id 4
2005-08-26 10:11:11.91 spid3 Server name is 'BHARAT\SAUNAY'.
2005-08-26 10:11:11.88 spid5 Starting up database 'model'.
2005-08-26 10:11:11.84 server Using 'SSNETLIB.DLL' version '8.0.194'.
2005-08-26 10:11:11.34 spid3 Starting up database 'master'.
2005-08-26 10:11:11.29 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2005-08-26 10:11:11.26 server SQL Server configured for thread mode processing.
2005-08-26 10:11:11.17 server SQL Server is starting at priority class 'normal'(1 CPU detected).
leoaugust
August 29, 2005 at 6:43 am
That's the server's log. We asked for the WINDOW'S log.
August 29, 2005 at 9:32 am
Actually that log information is useful because it shows that the SQL Server is not listening on TCP/IP. I see this sometimes in troubleshooting problems for clients. Try this:
1. stop the SQL Server service
2. open the SQL Server Server Network Utility and disable TCP/IP
3. restart the SQL Server service then stop it again
4. in the Server Network Utility enable TCP/IP
5. restart the SQL Server service
6. check the SQL Server error log again, does it show SQL Server listening on TCP/IP?
Sometimes going thru these steps will make SQL Server wake up and smell the TCP/IP.
Also, open the SQL Server client network utility and click the Alias tab. There should be an entry where the server alias name is a period or dot. The network library should be TCP/IP and the connection parameters should be (local),1433. If such an entry does not exist create it. If the network library or connection parameters are wrong, change them. Restart the SQL Server service.
If you are not able to ping the server where SQL Server is running using the the server name then you probably have a DNS issue. Is your DNS server running properly? Can you reboot it?
Don
August 29, 2005 at 11:48 am
I don't see BHARAT\SAUNAY listening on Named Pipes too in addition to TCP/IP. It is only Shared memory that is mentioned.
In this case the instance should be accessible only from a local machine. You do have to use server network utility to add protocols. This is a default path to it, you have to use your path.
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe"
As for #2 in my previous post, I say that if Firewall is blocking UDP port 1434 SQL can not browse between instances. The service is known by 3 names: "SQL Browser" ,"SQL Monitor" or "SQL Resolution Service" Here is an explanation from
http://www.microsoft.com/technet/security/bulletin/MS02-039.mspx
SQL Server 2000 and MSDE 2000 introduce the ability to host multiple instances of SQL Server on a single physical machine. Each instance operates for all intents and purposes as though it was a separate server. However, the multiple instances cannot all use the standard SQL Server session port (TCP 1433). While the default instance listens on TCP port 1433, named instances listen on any port assigned to them. The SQL Server Resolution Service, which operates on UDP port 1434, provides a way for clients to query for the appropriate network endpoints to use for a particular SQL Server instance.
Regards,Yelena Varsha
August 30, 2005 at 1:15 am
Remi / Donaldw
Here is the windows log i have copied only the required portion, i thinking it is listening only on Shared memory and also it has given one warning message regarding supersocket...?? no idea about this
SQL Server is ready for client connections
SQL server listening on Shared Memory.
Event Type: Warning
Event Source: MSSQL$SAUNAY
Event Category: (8)
Event ID: 19011
Date: 30/08/2005
Time: 12:26:12 PM
User: N/A
Computer: BHARAT
Description:
SuperSocket info: (SpnRegister) : Error 1355.
leoaugust
August 31, 2005 at 2:12 pm
Here is a link to a KB article regarding the event log warning: http://www.support.microsoft.com/?id=303411. This newsgroup entry indicates that it may not be a problem: http://groups.google.com/group/microsoft.public.sqlserver.setup/browse_thread/thread/f9eaec9890177911/735c864af5c16782?lnk=st&q=SuperSocket+SpnRegister+Error+1355&rnum=9&hl=en#735c864af5c16782
Per the SQL errorlog the SQL Server instance named Goody is definitely not listening on the TCP/IP and named pipes protocols, even though they are enabled in your network utility and the errorlog shows that Goody is using SSNETLIB. I'm not sure that the supersocket message has anything to do with it, though, since the default instance of SQL Server IS listening and one would think that such an issue would affect all instances of SQL Server equally.
Have you tried my earlier suggestion of stopping the Goody instance, disabling the TCP/IP and named pipes protocols for that instance, restarting then stopping the instance, enabling the protocols, and restarting the instance? I did find that resolved the problem in a similar instance. I overlooked named pipes earlier and Yelena pointed out that the service is not listening on that protocol, either, which is highly suspicious.
I don't recall if I asked earlier, but what is the service pack of the Goody instance? Have you applied at least SP3? If I remember correctly, XP SP2 behaves like 2003 Server and blocks outside access to any instance of SQL Server that does not have at least SP3 applied.
Don
September 1, 2005 at 8:11 am
Thanks Donaldw and Remi
Thanks guys for all your valuable support, I have tried install MS SQL Server Pack 3 and all the Default as well as Instances Server started listening on TCP/IP, and hence all the machine on the network were able to access the both servers through TCP/IP.
LeoAugust
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply