SQL Conectivity Error

[01310564]

Hello.

I have been struggling with this problem for about a week so if anyone can help it will be more than aprechated.

On 20 computers (out of 150) when someone tries to connect to the sql server they get these 2 errors:


Connection Failed:
SQLState:'01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect())

Connection Failed:
SQLState:'08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied on they


If they log onto anouther computer it works fine.

The only way we seem to be able to get these people connected on these computers is to make them full administrators on the network.

The current setup on these computers has been working fine without any problems for about 6 months.

Also if we format the computer it works fine.

No one is admitting they made any changes ( appart from a virus def update accross the board).

Not supprisingly the security guy isn't too happy about the situation. If anyone has any ideas how ever trivial then please let me have them.

 

[lovalles]

There are many things to check around before you get to the real problem, so please start checking the protocol you are using maybe is a protocol different that tcp/ip


please follow

http://www.kbalertz.com/kb_Q818047.aspx

hope this help

 

[Corran007]

Some questions.

Can they still ping the sql box from those machines? you are using nt auth i assume? can anyone log onto those macheines and connect? or is it restricting everyone.

whether somone is admiting something or not, somethign has changed. it may not be anything intentional. Is there any new software or patches that have put on those machines recently? when you format and do a fresh intall, do you do anything special to them to get them ready?

 

[mrdenny]

It sounds like account security settings to me, based on what you have described. But here are some things to check.

1. Has XP SP2 been installed on any systems?
2. Has the firewall been turned on by someone/something (see 1)?
3. Can you telnet from the nonworking systems to the SQL Server on port 1433?
4. Are there security settings on the machines preventing the users from accessing the network on the non-working machines?
5. Are there security settings on the server preventing users from accessing it? (I'm guessing probably not)
6. When trying to connect to the SQL Server do a netstat -n and see if you can see the connection to the SQL Server, and what it say.
7. Can you ping the SQL Server from the offending workstations?
8. Are you having a subnetting problem (maybe a DHCP change)?

Let me know what the answers to these are, and we'll work from there.

Denny

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

 

[01310564]

Thanks for replying.

Questions and answers

I'm using nt auth. Only people who have full administrator powers over the network can connect. When we format the computers we set them up exactly the same as before.

1. Firstly it is only effecting 2000 machines but not all of them so SP2 has not been installed.
2. We only have an external Firewall
3. We can't Telnet to the SQL Server on port 1433
4. The rest of the network is visible as well as the C & D drives of the sql server (So i can also ping the sql server)
5. No because they can logon to an uniffected computer without any problems.
6.netstat results show that 0n port 1433 TCP is established BUT ON PORT 139 IT SAYS TIME_WAIT (could this be the problem??)
7. As mentioned above yes.
8. Not that we are aware of.

I have also been told that the night before the problem started there was a virus update applied which forced the SQL server to reboot, so far no one else is admiting anything. I don't think this is the problem because otherwise we would see it accross the board or at least on all the 2000 machines.

Cheers,

Hugh

 

[mrdenny]

It looks like either the SQL Server isn't listening on port 1433, or for some reason the machines aren't able to talk to it on 1433, and they don't seam to like talking to it on port 139.

Check your SQL Log to see if it started listening on port 1433.

Mabey you having a names users licensing problems on the OS of the SQL Server? If everyone is connecting to the SQL Server via NetBios (which they are if it's not listening on port 1433) a licening problem on the OS of that box would kick out weird errors. But that shouldn't be because of #4 where you say that you can connect to the C and D drives of the SQL Server.

#s 6 and 3 are telling me that there is something funky going on, on your SQL Server. Deffinettly check to make sure that it's listening on port 1433. If it is, and you still can't telnet from the workstations to port 1433 then the problems is relating to that.

Can you telnet to port 1433 on the SQL server from other working machines on the network?

Denny

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

 

[MDXer]

Have you done any packet sniffing with a toll like Network monitor to see what is happening on the network side of thing. I had this issue once and it turned out to be a routing issue caused by a route change that was made by the network admins.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[01310564]

Mrdenny,

I can telnet from the computers that are working. Also if I start to create a ODBC to the sql server on a computer that isn't working, the server appers in the server dropdown but when it comes to authenticate it doesn't work unless i'm logged in as an administrator which is very strange because i'm using the sa login.

MDxer,

I will try to do some packet sniffing on moday and let you know the results.

Cheers,

Hugh

 

[mrdenny]

At this point, I'm with MDxer. It's packet sniffing time.

Denny

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

 

[01310564]

Ok

I have tried packet sniffing. The PC's are sending packets but the server isn't sending Anything back. Which seems very strange. Is there anything in partiqular i should be looking for in the packets.

Cheers,

 

[mrdenny]

Try repeating the test with the packet sniffer running on the server. That will tell you if the packets are getting to the server at all.

If the packets aren't getting to the SQL Server at all (which I'm guessing that they aren't) you've got a firewall somewhere blocking the connections. Mabey something that came with an anti-virus product that's installed. Mabey an ISA firewall that's deployed somewhere.

At the moment it doesn't matter what's in the packets since they aren't getting to the Server and back yet.

Denny

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