Troubleshoot Sql Server 2005 Connection issue

Question

I have a .Net 3.5 App using a SQL Server Express 2005 database that allows connections over TCP/IP. The computer that has the SQL Server installed is part of a domain. The program is installed on 3 computers and share the same database. The connection string is in a file that is shared among all the computers. SQL Server is installed on Windows Server 2003 machine. The other two computers are Windows XP. One of the XP machines is part of the domain and resides at the same location as the server. The other XP machine is not part of the domain and connects to the server using hardware VPN (always on ). The program on Windows Server and the XP machine that are part of the domain are able to open connection to the database, but the XP machine that connects via VPN is timing out when trying to open a connection. Server has Mixed-mode authentication. The authentication I am using is Sql server user-name and password.

Here is the check list I used trying to figure out the issue:
1. The SQL Browser is running on the Windows Server machine. [Port: 1434]
2. The SQL Server is accepting connections over TCP (but not Named Pipes)
3. Server responds to Ping from the client.
4. Checked the ErrorLog of sql server and noted the port. I am able to telnet to the server ip and sql port from the command line on the client.

How can I find where is connection is being timed-out? Is there is portable-program (no installation) that I can use on the server to check the network traffic and see if the request is being received or dropped in the middle?

Thanks,
Kishore. A

Answer 1

There is no portable network sniffer that I am aware of as you need WinCAP to put the card into promiscuous mode. What you can do is install Wireshark on the XP machine to dump the network traffic and use management studio on the server to watch the connections.

You could also use netstat on the server to view open ports and with knowledge of the VPN IP address this will also tell if a socket has been opened on the server.

eg: netstat -p tcp -b

This will generate output showing src/dest IP address and the application that is using that socket

TCP 192.168.1.1:1146 192.168.1.1:80 ESTABLISHED
[FIREFOX.EXE]

Answer 2

You can use SysInternals TCPView to monitor traffic.

Answer 3

are you sure that you're not talking on 1433? i'd get on the client machine and try to telnet through port 1433 and 1434 and see if you can connect to the servers (obviously you won't be able to login, but you should be able to see if you can connect). at least that tells you whether you have a firewall issue of some sort or if you're connecting on the right port.

-don