Troubleshooting Connectivity Problems
When a user reports that he or she can't connect, the first thing to do is to find out what message the user is getting. If the message says, Login Failed for User, the user most likely forgot his or her password. You can reset the password with sp_password. The command looks like this:
sp_password NULL, newpassword, loginid
Only the system administrator can issue this command.
IMO
The sp_password command should be available to members of the security admin role (Chapter 9 discusses that role in detail), but it is not.
If the user is getting messages, such as Specified SQL Server Not Found (named pipes) or General Network Error (Sockets), you most likely have a connectivity problem. These messages can also be issued if the server is down, so you want to make sure the server is running before doing anything else.
Unfortunately, most client connectivity problems have to be solved at the client workstation; so unless you have a utility such as PC Anywhere or Remotely Possible, you will have to visit the workstation of the user with the problem. You can try several things when you are at the user's workstation.
First, use the Client Network utility to check out the network library the client is using. This was installed along with Client Connectivity.
When you start the utility, the General tab shows you what the default network library is. A client can be set up to connect to more than one server, using different network libraries. You will see these aliases on the General tab as well. If the default network library or the one associated with a particular alias is not the one that SQL Server is listening on, just change the library. You can either select a different default, modify an existing alias entry, or add a new network library and alias. The Network Libraries tab shows you the network libraries available on the client workstation.
You might also need to check that the client and server are using the same underlying network protocol. If the server is using TCP/IP and the client is using NetBEUI, for example, there will be no communication even if the SQL Server netlibs are the same. You can find the installed network protocols by right-clicking on Network Neighborhood, and then choosing Properties. On a Windows 9x machine, highlight the network adapter and choose Properties. On an NT machine, look at the information shown on the Protocols tab.
If you can't resolve the problem with the Client Network utility, try the methods described in the following sections.
Troubleshooting Named Pipes Connectivity
At the operating system prompt, on the client workstation, type the following:
NET VIEW\\SERVERNAME
This tells you whether the workstation can see the server. If you get back information about the server, the workstation can see the server. If you get an Error 5 Access Denied, there is a problem with the NT Authentication login. If the user can't see the server at all, you receive an Error 53. In this case, or if you get any other message, a network problem exists and you should contact the network administrator.
If this succeeds, but you still can't connect to the SQL Server, you should test the pipe with makepipe and readpipe. Unfortunately, you need to do part of this on the server and part of this on the client. makepipe runs on NT only; readpipe runs on NT, Windows 9x, and Windows 3.11. These tools install automatically when you install SQL Server. They are in the \MSSQL7\BINN directory. There is no icon for them, and they do not appear in the Start menu.
makepipe
You run makepipe on the server with the following command:
> makepipe
Upon running this command, you should see output that looks something like the following:
Making PIPE:\pipe\abc read to write delay (seconds):0 Waiting for Client to Connect...
At this point, you can return to the client workstation and issue the readpipe command.
readpipe
You run readpipe on the client with the following command:
> readpipe /Sservername /Dstring
Assuming, for example, that you have run makepipe on a server named MyServer, run the following:
> readpipe /SmyServer /Dhello
If this command is successful, you should see output that looks like this:
SvrName:\\myserver PIPE :\\myserver\pipe\abc DATA :hello Data Sent: 1 : hello Data Read: 1 : hello
If you do not see the Data Read message, you have a network problem and should contact your network administrator. Press CTRL+C on the server to terminate the makepipe program.
Troubleshooting Sockets Connectivity
To test TCP/IP connections, use ping at the client workstation. It takes either a server name or an IP address, as shown here:
> ping MyServer
or
> ping 11.11.11.11
You should see results that look like this:
Pinging myserver [1.1.1.40] with 32 bytes of data: Reply from 1.1.1.40: bytes=32 time<10ms TTL=128 Reply from 1.1.1.40: bytes=32 time<10ms TTL=128 Reply from 1.1.1.40: bytes=32 time<10ms TTL=128 Reply from 1.1.1.40: bytes=32 time<10ms TTL=128 Ping statistics for 1.1.1.40: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milliseconds: Minimum = 0ms, Maximum = 0ms, Average = 0ms
If you get a message that the request timed out, you have a network problem and should contact your network administrator.
Troubleshooting ODBC Connectivity
If you have network connectivity but still can't connect to SQL Server, you may have a problem with ODBC connectivity. You can use ODBCPING to diagnose these problems. ODBCPING is in the \MSSQL7\BINN directory. There is no icon for it in your Start menu. You can use it to test a direct ODBC connection to a server and to test an ODBC data source name (DSN).
To test a direct ODBC connection, use the following command:
ODBCPING Sservername Uusername Ppassword
If you don't get a response, you probably need to install a newer version of the ODBC driver.
If this works, you need to identify which ODBC DSN the client is using (you can probably determine this by looking at the ODBC applet in Control Panel), and then issue the following command:
ODBCPING Ddatasourcename Uusername Ppassword
If the direct ODBC connection worked, but this one did not, you need to check the DSN for correctness. For example, is does the DSN specify a database the user does not have access to or provide an incorrect server name? Do this with the ODBC applet in Control Panel.
General Tip
You can also test connectivity to an ODBC data source with the ODBC applet. Highlight the data source and choose Configure. Click Next, and then Finish. On the final screen, click the Test Data Source button.
If none of these tricks help, you can try using SQL Profiler (described in Chapter 10) to see what login message is actually getting to SQL Server. Tools such as the NT Network Monitor and the Network General Sniffer can also help you diagnose and correct connectivity problems. Ask your network administrator for help with these last two tools.