Do you find the Support WebCast transcripts helpful?
Let us know!

Note This document is based on the original spoken WebCast transcript. It has been edited for clarity.

Farooq Mahmud: Today in this WebCast we will be talking about troubleshooting Microsoft® SQL Server™ 2000 connectivity. Connectivity is a vast topic, so instead of diving into troubleshooting connectivity issues right away, it helps to have some background on the topic (slide 2). So we will begin the WebCast by talking about the basics of client-server communications. Then we will get into Net-Libraries. We'll then talk about client-server communications using the TCP/IP Net-Library in a TCP/IP network. These topics will provide the necessary foundation so that we can then talk about troubleshooting common connectivity issues. We'll conclude the WebCast by presenting connectivity-troubleshooting references.

Let's start with the basics of client-server communications (slide 3). In this section we will discuss the high-level components involved and give you an overview of the communications process.

Here are the high-level components involved in client-SQL Server communications (slide 4). First, we have provider and driver. Provider and driver are .dll files on the client that communicate with SQL Server over a particular interprocess communication (IPC) mechanism. An example of a provider is the Microsoft OLE DB Provider for SQL Server. An example of a driver that you may have used is the Microsoft ODBC Driver for SQL Server. An IPC is the mechanism used to communicate with SQL Server from a client. A local IPC is used when the client and SQL Server are on the same machine. A network IPC is used when the client and SQL Server are on separate machines.

An IPC consists of two components: an API and a protocol. The API is the set of functions the software uses to send requests and receive results from the IPC. Named pipes and Winsock are examples of APIs. The protocol defines the format of the information sent between the client and server communicating through a particular IPC. TCP/IP and NWLINK IPX/SPX are examples of protocols.

The Net-Library extracts the computer running SQL Server and the client from the details of the protocol it communicates to the computer running SQL Server with. SQL Server can be configured to use multiple Net-Libraries. These Net-Libraries can be configured on the SQL Server side using the SQL Server Network Utility. We will talk more about this utility in later slides. The client-side Net-Libraries are configured using the SQL Server Client Network utility. We will talk more about this utility in later slides as well.

This diagram (slide 5) gives us a high-level picture of a client communicating with the computer running SQL Server. As we mentioned, an IPC consists of an API and a protocol. Named pipes is an example of an API. Another API would be Winsock. APIs can work with multiple network protocols. For example, named pipes can work over a TCP/IP network, as well as an NWLINK IPX/SPX network, which is common in Novell environments. However, in the case of the TCP/IP network library, it only functions over TCP/IP networks.

Now let's talk about Net-Libraries (slide 6). We'll go into the definition of a Net-Library and get a little more in-depth about what Net-Libraries are. Then we'll talk about configuring Net-Libraries on the client and server side. Then we'll conclude our Net-Library discussion by talking about aliases.

So what is a Net-Library (slide 7)? Net-Libraries shield data providers from the details of communicating with different IPC components. Simply put, an application does not have to know the details about the network when communicating with SQL Server. The Net-Libraries are responsible for handling that. TCP/IP and named pipes Net-Libraries are the most prevalent Net-Libraries in use, but there are other Net-Libraries available as well, and I'll mention them. First is shared memory, which is used by default when the client and the computer running SQL Server are on the same machine. Then there's NWLINK IPX/SPX, which is used with Novell networks. Then you have AppleTalk, Banyan Vines, and finally, Virtual Interface Architecture, or VIA, which is used for high-speed communications between servers in the same data center.

The Net-Libraries are present on the server side and client side. For a client to communicate with SQL Server, they must have a common Net-Library enabled. For example, if a client has enabled only the TCP/IP Net-Library, SQL Server will need to have this enabled as well, or this particular client will not be able to communicate with SQL Server. Of course, a common network protocol also needs to be present. For example, with the TCP/IP Net-Library, both client and server need to have a TCP/IP protocol stack installed.

Starting with MDAC 2.6 clients, the client-side Net-Library is implemented in Dbnetlib.dll (slide 8). The server-side Net-Library is implemented in Ssnetlib.dll. The TCP/IP and IPX/SPX Net-Libraries work directly with the underlying network. This is in contrast to other Net-Libraries, which need to use a Net-Library router, which is an intermediate .dll to get to the network. For example, the named pipes Net-Library router on the client side is implemented in Dbnmpntw.dll. On the server side, it is implemented in Ssnmpn70.dll. The routers for VIA and multiprotocol are shown in the slide as well.

This diagram (slide 9) gives us a high-level picture of where Net-Libraries fit in client SQL Server communications. We first start with the application, using the OLE DB Provider for SQL Server or the SQL Server ODBC driver to initiate a connection with the computer running SQL Server. If you've ever programmed an application that used ADO to connect to SQL Server, for example, you're using the OLE DB Provider for SQL Server under the covers. The provider/driver then talks to the client side Net-Library Dbnetlib.dll. At this point we are still on the client machine. If the client is using the TCP/IP or the NWLINK IPX/SPX Net-Library, Dbnetlib.dll communicates directly with the network, as shown in the diagram. For all other protocols, Dbnetlib.dll talks to that Net-Libraries' router, which happens to be a .dll, and this routing .dll talks directly with the network.

Now we come to the server side and the sequence of events is the reverse of the client side. In the case of TCP/IP or NWLINK IPX/SPX, the communication goes from the network to the server-side Net-Library Ssnetlib.dll, and then to the SQL Server database engine. For other Net-Libraries, information goes through the server-side Net-Library router before getting to Ssnetlib.dll and the SQL Server database engine. More in-depth information about Net-Libraries can be found in the Microsoft SQL Server 2000 Books Online topic on client and server Net-Libraries.

Now let's talk about configuring the server-side Net-Libraries using the SQL Server Network Utility and configuring the client-side Net-Libraries using the SQL Server Client Network Utility (slide 10). You use the SQL Server Network Utility (slide 11) to configure server-side Net-Libraries. SQL Server can listen on multiple Net-Libraries, so one client can communicate with SQL Server using the TCP/IP library for example, and at the same time another client on another machine can use the named pipes Net-Library to communicate with SQL Server, and yet another client, which happens to be on the same machine as SQL Server, can talk with SQL Server using the shared-memory Net-Library.

By default, the SQL Server Network Utility is located in the Binn folder where the SQL Server instance is installed. For a default instance, the Server Network Utility is located in C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe. You can open the Server Network Utility by clicking the shortcut for it in the SQL Server program group on the Start menu, and please refer to Microsoft SQL Server 2000 Books Online topic "SQL Server Network Utility" for more information about this tool.

Here we have a screen shot (slide 12) of the General tab of the SQL Server Network Utility. You can manage the server-side Net-Libraries for all instances installed on the machine through the drop-down list. To enable a Net-Library, select a protocol in the Disabled protocols list, and for the purposes of this utility, protocols is analogous to Net-Library. So to enable a protocol, you select a protocol in the Disabled protocols list, click the Enable button, and that's it; it's enabled. Conversely, to disable a protocol select an enabled protocol and then click the Disable button. To further configure the Net-Libraries you can select an enabled Net-Library and click the Properties button. In the case of TCP/IP, the port that SQL Server listens on can be configured. In the case of named pipes, you can configure the default pipe that SQL Server listens on.

A few other options are available in this utility, and I'll mention them briefly. We have Force protocol encryption and Enable Winsock proxy. If you want to encrypt all SQL Server communications, you can select the Force protocol encryption option. If you want SQL Server to listen on a proxy server using Microsoft Internet Security and Acceleration (ISA) Server or Microsoft Proxy Server, you can enable the Winsock proxy option. Remember that for any change you make in this utility to take effect, the SQL Server instance must be restarted.

Now let's talk about the Client Network Utility (slide 13). The SQL Server Client Network Utility is used to configure the client-side Net-Libraries. The easiest way to open the Client Network Utility is to go to Start, Run, and type Cliconfg.exe in the Open box and click OK. If the client has the SQL Server Client Tools installed, you can get to Client Network Utility through the Microsoft SQL Server program group in the Start menu. Note that the SQL Server Client Tools do not have to be installed on the machine to have the Client Network Utility. The interface of the Client Network Utility also varies depending on the version of MDAC installed on the client. For the purposes of this WebCast we will be talking about the Client Network Utility that comes with MDAC 2.6 and above, unless mentioned otherwise.

Here is a screenshot (slide 14) of the General tab of the Client Network Utility. Enabling and disabling protocols is done the same way as in the Server Network Utility. You can also set client-side encryption, as well as enable the shared-memory Net-Library for clients that are on the same machine as SQL Server. To further configure the Net-Libraries, select an enabled Net-Library and click the Properties button, just like in the Server Network Utility. In the case of TCP/IP, the port on the computer running SQL Server that the client will attempt to connect to can be set. Note that you can also set a protocol order in this utility. The client will attempt to connect to SQL Server using the protocol order. In this screen shot the client will first try a TCP/IP connection. If a connection using the TCP/IP Network Library cannot be made, then a connection using named pipes Net-Library is attempted. Any changes made with this utility will take effect on the next client connection attempt.

The next two slides will cover the Alias tab in the Client Network Utility. The client-side alias (slide 15) forces a client to connect to a particular computer running SQL Server through a specific Net-Library or network name. Aliases set for a computer running SQL Server override the Net-Library configurations in the General tab of the Client Network Utility. Aliases can be useful when troubleshooting connectivity. For example, if a connection over named pipes fails, you will want to see if a connection over TCP/IP succeeds, because you want to know if this problem is specific to the Net-Library or just a general connectivity issue. To force the connection to use TCP/IP, you can configure a client-side alias.

To configure an alias, go the Alias tab of the Client Network Utility and click Add (slide 16). Select a Net-Library and then set the alias name, the name of the computer running SQL Server, and the connection parameters. In the case of a TCP/IP alias, you can set the port that the computer running SQL Server is listening on. Although aliases can help to troubleshoot connectivity, an incorrectly configured alias can cause connectivity problems as well. So when confronted with a connectivity issue, look for an incorrectly configured alias on the client, or unnecessary aliases present on the client.

Because TCP/IP networks are the most common networks out there, let's talk about the specifics of connecting to SQL Server using the TCP/IP Net-Library over TCP/IP networks (slide 17). We will then discuss the specifics of how a client connects to a default instance of SQL Server and to a named instance of SQL Server.

Applications exchange information over TCP/IP through the use of ports, and there are two kinds of ports: TCP and UDP ports (slide 18). The server listens on an IP address and port for connection requests. The client then connects to the IP address in port the server is listening on. After this is done, the exchange of information can begin. In the next slides we'll see how this relates to a client connecting to a default and named instance of SQL Server.

By default, SQL Server 2000 listens on TCP port 1433 for incoming connections on startup (slide 19). SQL Server is set to bind to port 1433 when it starts up. This port can be configured using the SQL Server Network Utility, as we mentioned earlier. The SQL Server error log can be used to verify the IP addresses and ports the computer running SQL Server is listening on. Take a look at the last error log and the output will be similar to the output shown here at the bottom of the slide. In this case, the computer running SQL Server is listening on the TCP/IP and named pipes and Shared Memory Network-Libraries.

SQL Server is also listening on port 1433 of IP addresses 10.10.10.10 and 127.0.0.1, which happens to be the loop-back address. From this you can see that the computer running SQL Server is combined to multiple IP addresses if multiple network cards are present in the server. SQL Server attempts to bind to all the network interfaces present on the server when it starts up, including a loop-back address. By default, the client will try to connect to port 1433 of the computer running SQL Server. If the computer running SQL Server is listening on a different port, the connection attempt will fail unless one of two things are done. Either create a TCP/IP alias specifying the port SQL Server is listening on, or go into the Client Network Utility and alter the default port and the properties of the TCP/IP Net-Library. With MDAC 2.6 and above, there is also an option to dynamically determine the port when you create an alias. So this is another way a client can still find a computer running SQL Server if it is not listening on the default port of 1433.

Connecting to a named instance of SQL Server is a bit more involved (slide 20). By default, a named instance will listen on a dynamically assigned port when the service starts up. However, a static port can be configured for a named instance as well, using the SQL Server Network Utility. Dynamic ports are useful because the ports listening for a named instance are configured automatically by Windows® when the SQL Server service starts up. So if the previous port is unavailable — for example, some other application has taken control of it — another free port will be chosen. And this way clients can continue to connect to the instance even if the port number has changed. This is borrowing any aliases configured on the client that would try to connect to the previous port.

However, dynamic ports can be problematic as well, especially when clients need to connect to a firewall, as we will see in later slides. Note that dynamic ports are supported only for named instances of SQL Server 2000. You can refer to Knowledge Base article 286303 for more information on dynamic port assignment in SQL Server 2000.

This slide (slide 21) diagrams the process of a client connecting to a named instance of SQL Server; let's go through this diagram. In step 1, the client wants to connect to the SQL Server instance SQLSRV1/INST1. INST1 is the instance name, and SQLSRV1 is the name of the SQL Server computer.

In step 2 the client-side Dbnetlib.dll connects to UDP port 1434 on the computer running SQL Server. This listener component is present on all computers running SQL Server and always listens on UDP port 1434. It is responsible for sending information about the instances of SQL Server installed on the machine. Information on all named instances and also the default instance are sent to the client. What kind of information is sent? The IP address the computer running SQL Server is listening on, the server-side Net-Libraries configured, and whether the instance is clustered or not. And the properties of the Net-Libraries, for example, the listen-on port for SQL Server, or the default named pipe for this particular SQL Server instance, that information is sent back to the client in step 3. In step 4 it goes through a Dbnetlib.dll, that same packet, and on to step 5, where the client finally receives it. So in step 6 the client picks out the instance it wants to connect to, in this case it would be INST1, and determines the Net-Library to use and, for this example, let's say it is TCP/IP, and then connects to the IP address end port of INST1; the port happens to be TCP port 4323. Remember, all this information is in the packet sent to the client from UDP port 1434 on the computer running SQL Server. In step 7, the connection to the computer running SQL Server is completed.

Clients that have MDAC 2.6 and above installed can connect to a named instance of SQL Server listening on the dynamic port without any special configuration on the client side (slide 22). All the client needs to know is the server name and instance name it wants to connect to. So what about pre-MDAC 2.6 clients? How can they connect to a named instance? You may not be able to upgrade the clients to MDAC 2.6 for whatever reason, so there is a way to accommodate these legacy clients. You will need to do the following: configure the named instance to listen on a static port using the SQL Server Network Utility; create a TCP/IP alias on the client; specify the server name only, not the instance name, and the port the named instance is listening on in the alias properties. If the named instance was using a dynamic port and listened on port change when the computer running SQL Server restarts, the legacy clients will not be able to connect, because the alias still has the old port number, so all clients will need to be reconfigured. Having a fixed port for the named instance removes this administrative step.

Now that we have a solid foundation of how client and SQL Server connectivity works, let's begin our discussion of troubleshooting connectivity issues (slide 23). Because connectivity issues can become complex, it helps to know how to narrow down a connectivity issue to make the troubleshooting easier. We'll present a connectivity checklist with common questions you should ask yourself and information you should gather when confronted with a connectivity problem. After the issue is narrowed down somewhat, several tools are available to help you effectively troubleshoot the connectivity problem. One of the more advanced tools we'll mention is Network Monitor. We will then go through some of the more common connectivity issues you may encounter, and talk about steps to troubleshoot them. We will then conclude the WebCast by discussing SQL Server connectivity when a firewall is involved.

Narrowing down the scope of the connectivity issue (slide 24) makes troubleshooting more manageable. One question to ask yourself is, what is the exact error message? When a client application raises a connectivity error, it is important to note the whole message, not only a part of it. For example, the message "General Network Error" usually appears with another message that provides details about the connection error. You can then search for this more specific part of the general network error in the Knowledge Base.

Find out if the connectivity problems are happening only for remote clients, or if local connections are failing as well. By local connections I mean connections from clients on the same machine as SQL Server. If local connections also fail, it may be easier to troubleshoot the local connectivity issue first, and sometimes solving the local connectivity issue may help solve the remote connectivity issue as well.

Find out if all clients are unable to connect with the computer running SQL Server, or only some of the clients. If only some clients are unable to connect, this could indicate a client-side issue, such as the MDAC version drivers are not correctly installed, for example.

Then find out, is the issue specific to a particular Net-Library? Using the Client Network Utility, which you learned about earlier, you can see if the connection works over named pipes and not TCP/IP, for example. If the connectivity issue fails over named pipes and TCP/IP, it's probably a more general connectivity error; whereas if it fails on one protocol and not the other, your troubleshooting method would be a little bit different.

Then find out, is the issue specific to a particular authentication method? For example, does the connection fail when Windows Authentication is used but it is successful with SQL Server Authentication? After the issue has been narrowed down, you can use the available tools to resolve the issue.

On this slide (slide 25) we have a troubleshooting connectivity checklist, some more items that are pieces of information that you can find and that I find useful when troubleshooting connectivity issues. Again, gathering this information will make troubleshooting more manageable, especially when faced with a very complicated connectivity problem. Let's go through each item.

First find out, is the SQL Server service running? This may sound simple, but always check if the computer running SQL Server that the clients are trying to connect to is in fact running. Is the error reproducible at will, or is it an intermittent problem? Intermittent connectivity problems are more difficult to troubleshoot, so try to find patterns. For example, does a problem occur during a particular time of day? Is it a function of the amount of activity occurring on the network? What Net-Libraries are configured on the client and server? Remember, a common Net-Library needs to be configured on the client and the computer running SQL Server for a connection to take place. Are you able to connect using Query Analyzer osql, isql, an ODBC data source name (DSN), or a .udl file?

It helps to use several different tools to see if it is a general connectivity problem or it is specific to OLE DB or ODBC. In this list Query Analyzer and osql happen to be ODBC applications, and we'll talk about those two tools later. A .udl file uses the OLE DB Provider for SQL Server under the covers, and we'll talk about this utility later as well. Then check to see if the problem coincides with an upgrade of MDAC components on the client and the computer running SQL Server. Try to get some historical background. Were there any major upgrades, such as an MDAC upgrade or a Windows service pack, or a SQL Server service pack upgrade, prior to the connectivity issues?

All of these items can possibly install newer versions of connectivity-related files such as Dbnetlib.dll. Of course, get the SQL Server version and service pack information. Also get the MDAC version from the clients that are enabled to connect to SQL Server. You can use the component checker tool, which can be downloaded from http://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28001860, to check the MDAC version on your clients. What type of network is between the client and SQL Server? Are there firewalls between the client and SQL Servers? Is a network reliable, or has their been a history of network outages and slow transmission speeds? An unstable network will almost always result in connectivity issues, so you want to get some background information on the type of network separating the client and server.

Then finally, are clients able to connect to SQL Server by specifying its IP address but not the name? If so, this could indicate name resolution issues: DNS or WINS problems, for example. These items are by no means a comprehensive list, but these will definitely help you narrow down the issue and hopefully make your troubleshooting a bit more manageable.

Now let's get into the tools available to help you troubleshoot connectivity issues (slide 26). We'll first talk about what I call basic troubleshooting tools that you will use at the outset when you encounter a connectivity problem. We'll then get into more advanced tools, like SQL Server Profiler and Network Monitor. Of course, SQL Server 2000 Books Online and the Microsoft Knowledge Base are also excellent troubleshooting tools.

Let's begin by talking about the basic troubleshooting tools (slide 27). We first have Ping. Ping, a command-line tool, can determine if basic communication between the client and SQL Server can be made. From the client that is experiencing the connectivity issue, Ping the computer running SQL Server by its IP address and by its name. If the Ping by IP address does not return results, this tells you that there's an issue on the network level somewhere. If a Ping by IP address works but a Ping by name fails, this can indicate name resolution issues. Another way to test for name resolution issues is to issue a ping -a followed by the IP address of the computer running SQL Server, and this should give you the fully qualified name of the computer running SQL Server in the output. If it does not, or if it gives you an incorrect fully qualified domain name, this indicates name resolution issues as well.

Then we have Netstat. Netstat, when executed on the computer running SQL Server with the -an switch, lists the ports that are being used on the computer running SQL Server, both TCP and UNP ports. However, this tool cannot tell you the process that is using the port. If you look in the SQL Server error log and you see messages that SQL Server failed to bind to a port, you can run netstat -an on the computer running SQL Server to see if that port is actually in use. Portqry is a tool similar to Netstat except that can sometimes tell you what process is using the port. For more information on this utility, refer to Knowledge Base article 310099.

Then we have Tracert. Tracert determines the network path a network packet takes to reach SQL Server. When you have a connectivity problem you can run Tracert from an affected client and see what kind of path it takes. Then you could go to a client that can connect to the computer running SQL Server, run the same command, and see are the clients taking different routes to get to the computer running SQL Server? Another useful way Tracert can be used is when you want to know if there are any routers between the client and SQL Server. And this information is important if a Network Monitor trace needs to be made.

When tracing in a routed environment, it's important to run traces on both sides of the router. If name resolution issues are suspected, then you can add an entry in the clients' HOSTS or LMHOSTS files. If after adding an entry in one of these files a connection can't be made to SQL Server, there are likely problems with DNS or WINS name resolution.

Of course, you should always look at the SQL Server error logs and Windows event logs for any errors (slide 28). For example, the SQL Server error log will tell you what IP addresses and Net-Libraries the computer running SQL Server is listening on, as we saw on a previous slide, but it can also tell you if there were any port bind failures. For example, if SQL Server is configured to listen on TCP port 1433, and if it was unable to bind to that port, you will see a message to that affect in the SQL Server error log.

If the client has SQL Server Client Tools installed, try making a connection to the computer running SQL Server using Query Analyzer, Osql.exe, and Isql.exe (slide 29), because the error message can be different depending on the tool you used. You can then look for these error messages in the Knowledge Base.

Another tool that comes with SQL Server is Odbcping.exe. If you suspect that the problem is specific to an ODBC application, this utility can be used to verify if ODBC connectivity is functioning properly.

Because it is helpful to see if connectivity fails through ODBC, OLE DB, or both — if it fails with one or the other driver but not both, there is probably an issue with either the ODBC driver or the OLE DB provider on the client. If Odbcping is not available or any of the SQL Server Client Tools are not available on that client machine, then you can test ODBC by configuring a DSN to the computer running SQL Server (slide 30). DSNs are configured in the ODBC Data Sources tool in Control Panel. You can configure the DSN to use different Net-Libraries, and you can also specify if you want to connect using Windows Authentication or SQL Server Authentication. And, like I mentioned before, Query Analyzer and Osql.exe, two of the client tools that come with SQL Server, are ODCB applications.

However, if your application is using OLE DB, and if you want to verify connectivity using OLE DB outside of your application, you can create data link files. The easiest way to do this is to create a file with a .udl extension on the client, double-click the file, and it will open it in the data link editor. The .udl can be configured to use different Net-Libraries as well, and different forms of authentication, such as SQL Server Authentication or Windows Authentication. Both tools are useful if the SQL Server Client Tools are not handy, or if you're unable to install the tools on the client for whatever reason.

Network Monitor and SQL Server Profiler (slide 31) are tools that can be used to do more in-depth troubleshooting. Network Monitor allows you to trace all network activity between two machines. From the trace it can be determined, for example, if packets are getting lost somewhere between the client and SQL Server. Because reading a Network Monitor trace can be challenging for a DBA, it would be best to let a network administrator look over the trace to determine if there is a network issue. SQL Server Profiler can be used to see if the client's connection is even getting to the computer running SQL Server. Because SQL Server Profiler captures server-side activity, you can use the session's event to see what clients connect and disconnect from your SQL Server.

Let's talk a little bit more about Network Monitor (slide 32). This WebCast is not intended to be a complete tutorial for Network Monitor. Instead, it is hoped that you will be somewhat familiar with it, so as a DBA you can capture a trace and do some basic analysis. At the end of the WebCast I'll present some Network Monitor references, which you can go to for even more detailed information about using this tool. In this section we'll see what a successful connection to SQL Server looks like, what a graceful disconnect looks like, and then we'll present some Network Monitor tips.

When a client attempts a connection to SQL Server, a three-way handshake is performed. A three-way handshake consists of the client sending a SYN packet — the first packet in the Network Monitor output under the first bullet in this slide (slide 33) denoted by an S, in the output here. This server then acknowledges this packet by sending an ACK/SYN packet. The Netmon trace also includes the source and destination IP addresses for the client in SQL Server, but these were omitted from the slide for clarity. The client then responds to the ACK/SYN packet by sending an ACK packet of its own, and this completes the three-way handshake. If you see this in the Network Monitor trace, you can be sure that the client can at least find the computer running SQL Server, and that basic connectivity on the network level is okay.

You can get a lot of other important information from a Network Monitor trace. For example, note that each packet has a source and destination port listed. In the case of the first packet sent by the client, we can see the destination port is port 3679. This happens to be the port the SQL Server instance is listening on. Of course, the client also has a port, with which it communicates to the computer running SQL Server, and it happens to be port 2236. The exchange of information between these two machines occurs through these ports.

The client source port is dynamically chosen by Windows. This fact will become important when we discuss client SQL Server connectivity through a firewall, toward the end of this WebCast.

Here is Network Monitor trace output (slide 34) of a client gracefully disconnecting from the SQL Server instance. Again, the Network Monitor trace normally shows the source destination and IP addresses, but it was omitted from this slide for clarity. A similar handshake occurs when a client wants to disconnect. First, the client sends an ACK/SYN packet to SQL Server, then the server responds, and then the client responds again with an ACK/SYN packet, and then finally the server sends an acknowledgement completing the disconnect. For a more detailed explanation of the three-way handshake process, you can refer to Knowledge Base article 172983.

Again, as a DBA it would be helpful to know at least the basics of capturing a Network Monitor trace. Here are some tips (slide 35) to make sure that you can get a good trace for a network administrator to analyze in detail, if necessary.

First, capture end-to-end traces. By end-to-end, we mean capture a trace in both the client and SQL Server at the same time. This is especially true if the client and SQL Server are separated by routers. It is also important that the times be synchronized between the two machines. Again, because Network Monitor traces can be difficult to analyze, it is best to let a network administrator do this. One last thing, it is important to set the capture buffer size to a higher value, especially if you're going to run the Network Monitor trace for extended periods of time. If the buffer fills, it will overwrite the previously captured frames, and you may miss information in your trace.

Now let's talk about some of the common connectivity issues you may encounter (slide 36). With all of these, you will find that you have a common starting point or initial troubleshooting steps. The issues we will talk about in this WebCast are "SQL Server does not exist, or access denied" messages, "Cannot generate SSPI context" messages, and "General network error" messages.

Let's go over some initial troubleshooting steps (slide 37) that you can perform to narrow down the issue. First, see if the problem is specific to a particular Net-Library or authentication method. If it happens that connectivity fails over both forms of authentication, SQL Server and Windows, and it fails over multiple Net-Libraries, named pipes, or TCP/IP, this indicates a more general connectivity issue. Determine if the issue occurs locally on the computer running SQL Server only for remote clients or both from local and remote clients, again, to get the scope of the connectivity problem. Then try connecting with SQL Server Client Tools from the affected client. If the client tools are not handy, use DSNs or .udl files. As always, you should search for the connection error in the Microsoft Knowledge Base and in Microsoft SQL Server 2000 Books Online.

Let's talk about "SQL Server does not exist, or access denied" messages (slide 38). When you see "SQL Server does not exist, or access denied," this means that the computer running SQL Server cannot be contacted by the client. Reasons for this are the SQL Server instance may not be started, or there may not be a common Net-Library between the client and server, or the client is trying to connect to the wrong port on the computer running SQL Server. Name resolution issues are also a possibility. Incorrectly configured aliases can also cause this error. However, this error does not indicate a login failure. A login failure is usually denoted by an error message, similar to "logon failed for user," and then the user name. So in the case of a logon failure for the SA account, you would get a message saying, "logon failure for user SA."

Another error you may see on a client in a Windows 2000 environment is "Cannot generate SSPI context" (slide 39). Let's get some background information (slide 40) on this error before talking about how to troubleshoot it.

SSPI stands for Security Support Provider Interface, and this interface allows user tokens to be transmitted securely between the client and server. The named pipes Net-Library does not use SSPI, but the TCP/IP Net-Library does. Another central concept is the Service Principle Name, or SPN. The SPN allows the SQL Server service to be uniquely identified on the network. "Cannot generate SSPI context" messages are usually the results of incorrectly configured Service Principle Names.

"Cannot generate SSPI context" messages occur when connecting using the TCP/IP network library and when using Windows Authentication at the same time (slide 41). So you will find that SQL Server Authentication succeeds, or that a connection over named pipes and Windows Authentication may succeed as well. If you absolutely have to use a TCP/IP network library along with Windows Authentication, then look for name resolution issues first, when you get "Cannot generate SSPI context" messages from your client. Go to the client that is receiving this error and perform a ping -an of the SQL Server IP address. You should get the proper fully qualified domain name back. If you do not, then correct this issue. If name resolution looks fine, then configure the SPN for SQL Server service startup, account using the Setspn tool.

This tool is available in the Windows 2000 Resource Kit. You can use the Setspn tool to list, add, and remove SPNs from a SQL Server. The SPN is simply an object in the Active Directory®. To add an SPN, you must be a domain administrator. After adding the SPN, allow some time for the change in the SPN to take effect before trying the connection from the client.

Now let's talk about "General network error" (slide 42). This message usually indicates a problem at the network layer somewhere; for example, a packet sent from a client is not getting to the computer running SQL Server, or vice versa. This could be caused by, say, a malfunctioning router, for example, or a problem with the interface cards on the client or SQL Server, or a problem with the network cabling or some other hardware type of issue. Usually an additional error is returned to the client, in addition to the "General network error," as shown under the second bullet. You can then search on this more specific error in the Knowledge Base. If you get a "General network error" you can try testing the network outside the computer running SQL Server or the client application. For example, see if you can copy a large file across a network.

If this causes issues, this further suggests network problems. In almost every "General network error" issue, though, a Network Monitor trace is a must. This will show if any packets are getting dropped or retransmitted. Again, reading these traces can be challenging for a DBA, so it is advisable to have your network administrator look at the trace to perform in-depth analysis, to see if there are network issues.

Before we conclude this WebCast, let's discuss SQL Server connectivity using TCP/IP Net-Library when a firewall is present (slide 43). Care must be taken to open the proper ports, for clients to connect to the computer running SQL Server. To connect to a default instance of SQL Server, and by default, a default instance listens on TCP port 1433, that TCP port 1433 should be open on the firewall and going from the client to the computer running SQL Server. SQL Server listens on port 1433 by default. Of course, if your SQL Server is listening on another port, then open this port from the client to the computer running SQL Server. Going in the other direction, back from SQL Server to the client, all ports above 1024 should be opened. This is just the way TCP/IP applications work, and it is not a limitation of SQL Server.

When connecting to a named instance, we recommend that the instance listen on a static TCP/IP port, when connectivity to a firewall is required. So that particular port should be open, going from the client to the computer running SQL Server. And in the reverse direction, all ports above 1024 should be opened. If dynamic ports were configured, then should the computer running SQL Server be restarted and the port changed, the firewall would have to be reconfigured to allow traffic through this new port. This is why it is advisable to configure your SQL Server instances on static ports.

This diagram (slide 44) illustrates what ports need to be open on the firewall. In this diagram the client wants to connect to the computer running SQL Server listening on the IP address and port 1433, so this port needs to be opened in the direction from client to the computer running SQL Server. Now the client also has a port that it uses to communicate with the computer running SQL Server. This source port is a randomly chosen port about 1024. This is why all ports above 1024 need to be opened, going from the server back to the client.

Here are some helpful references (slide 45) on SQL Server connectivity.

This concludes my WebCast. As I mentioned, connectivity is a vast subject, and this WebCast served as an introduction into troubleshooting connectivity issues. I hope you can use the information in this WebCast to more easily troubleshoot the connectivity issues you encounter. Thank you.

Otto Cate: Thank you very much for the presentation. Before we jump into the Q&A portion of this Support WebCast today, I have a couple of program notes I'd like to share with our listeners.

The Q&A portion of the Support WebCast is intended to encourage further discussion of the topic that we discussed today. If you need technical assistance, your best bet is to contact Product Support directly through an online incident or by phone, if you're looking for one-on-one product support, or if you have a situation that might take quite a bit of technical research. With that, let's jump into the first questions.

The first question is as follows: I'm using MDAC 2.5 and the connection string says that I use Dbmssocn.dll and not Dbnetlib.dll. In fact, my Web server does not have the Dbnetlib.dll at all. Is that something that you're able to touch on a little bit?

Farooq: Dbmssocn in MDAC 2.5, that is the client side of TCP/IP Net-Library. Starting with MDAC 2.6, the Net-Library implementation was a bit different, so a new .dll, Dbnetliv.dll, was included. So if that .dll is not on your Web server, it does mean that your Web server is probably not on MDAC 2.6, but it's on your previously installed version. So connectivity can still work going to a default instance of SQL Server 2000, even if you're client is not on MDAC 2.5, like in your case.

Otto: We may have touched on this a little earlier when we were talking about the general network error. The question is: When connecting to remote SQL Server 2000 servers using the Query Analyzer, we sporadically received the general network error, "Check your network documentation — connection broken." This occurs only with SQL Server 2000, not when we connect to a SQL Server 7.0 server. It also occurs for small queries and large queries; a simple SELECT COUNT (*) from a table of five rows can cause it. Do you have any ideas that may be helpful?

Farooq: First, it seems to me that you are able to make a connection from your client to the computer running SQL Server, but after running some queries, whether small or large, the connection is just terminated. So I would try to see if there's a pattern, or check to see if all your clients are getting this message when they run these queries — is the problem reproducible, or is it just an intermittent problem?

If you can reproduce it, I would check in the Knowledge Base to see if there are any similar situations, and check if there are articles that document the situation. If you can't seem to find any, then I would probably suggest you open a support incident with Product Support Services to help you resolve it. Because general network error, unfortunately, is a very difficult error to troubleshoot, probably the most difficult connectivity problem you can get. But as much as you can narrow it down and find patterns, that's the way to go, and then search the Knowledge Base for similar situations. Then, if you still need further help, please open an incident with Product Support Services.

Otto: Are there any pros or cons to supporting multiple Net-Libraries on the computer running SQL Server?

Farooq: Pros and cons — by default, I believe TCP/IP, named pipes, and the multiport Net-Libraries are enabled on the computer running SQL Server. And all this means is that clients can connect to the computer running SQL Server using any of these Net-Libraries, so it just adds some flexibility to the computer running SQL Server. It doesn't add any administrative overhead, really, because you can simply set them through the Server Network Utility. But if you wanted to remove them for whatever reason, you could do that. There is really no advantage or disadvantage.

Otto: If you have named pipes and TCP/IP protocols enabled, which one is used on the server? Previous versions of SQL used whichever libraries, basically on the very top of the menu.

Farooq: The network library used is actually going to be specified by the client. So SQL Server, in your case, is going to have named pipes and TCP/IP enabled. So basically it will accept connections from clients over named pipes or TCP/IP; it is a client that is going to specify what Net-Library it wants to use. And that's specified in the Client Network Utility on the client side.

Otto: In the client utility, if there are no enabled protocols, does that mean SQL connectivity is not possible?

Farooq: If you do not enable any protocols in the MDAC 2.6 Client Network Utility, you should get a message box saying that no protocols have been enabled and the default will be used. What the default is, I can follow-up with you on that, but connectivity should occur. Now whether it's going to be TCP/IP or named pipes, I will follow-up on that.

Otto: What's a good port to use other than 1433? Id there a general rule of thumb on how to choose the port? It looks like they're looking for some information on connecting to a SQL Server through a firewall that actually blocks 1433 and 1434.

Farooq: If you cannot use 1433 and 1434, you can definitely configure SQL Server to listen on another port. As for what port to choose, there's really no rule of thumb, except that you do not want to choose a well-known port. Say, for example, your Web server might be listening on TCP port 80. You don't want to chose a port like that. So aside from that, really that's up to you or your company to decide what port to use, as long as it does not conflict with another application that's going to be using the same port. That's the only guideline that I can give you there.

Otto: Using a Windows NT® user locally, through IIS and ASP, is not an issue with us here, but connecting through remote is not working. I've duplicated the IUSR account on the SQL box and made that user a member of the group that works locally. Is that something you're able to address?

Farooq: That's more of a security issue than connectivity, but first of all I would need to know the error that you're getting when you're trying to connect remotely. You could have various errors, like the logon failed for NT authority/anonymous logon, for example, or the logon failed for NT authority/system. Usually you'll get a logon failure message, but you may be getting some other message, like connectivity-related issue, a general network error, or something like that. So without knowing the specific error message, it would be kind of hard to give a specific answer; that can only be general. Maybe if they could tell me the actual error that they are getting, I could discuss that a little bit more.

Otto: Here's the error message. It looks like it's: "log in failed for user … (null). Reason: not associated."

Farooq: If this is happening when you're running a connection directly from the IIS Server to SQL Server, then it looks like the credentials are not getting passed to the computer running SQL Server for some reason. There are articles out there. You can go to the Knowledge Base and search on that "Log in failed for user (null)" message, and try the tips in there, but usually it comes down to a misconfigured SPN for the computer running SQL Server, like we talked about earlier. You may want to reconfigure the SPN for the computer running SQL Server account and see if that resolves the issue.

Otto: When you get the "SQL Server does not exist" message, and it's only happening on a VPN connection, what does that point to?

Farooq: When you're connecting that VPN check if you can do a Ping, first of all, and see if it returns the proper IP address. Also do a ping -a of the SQL Server IP address and see if you get the proper domain back. If not, then there could be some kind of name resolution issue with that VPN connection. You can also try to add an entry in the HOSTS or LMHOSTS file on the client to see if that alleviates the problem. But usually "SQL Server does not exist, or access denied" means that there are name resolution issues or that computer running SQL Server isn't running. But if at the same time other clients can connect, then obviously that's not the issue. I would look at name resolution first.

Otto: Is there any relation between the SQL Server Agent and connectivity problems, like, for instance, jobs don't run because of connectivity issues, and so on?

Farooq: SQL Server Agent is a client, as well as SQL Server. And because it's on the same machine — by default clients on the same machine as SQL Server are going to use shared memory. You can disable shared memory through the Client Network Utility. I would check that; is shared memory disabled? Perhaps it is and you're going to TCP/IP or named pipes, you may have connectivity issues that relate to those network libraries. So we recommend that you just enable shared memory and configure your SQL Server agent to use shared memory when it's connecting to SQL Server. So I would check that. If you're still having memory issues, even with shared memory, I would search in the Knowledge Base on that error that you're getting, when you're running your jobs.

Otto: Can you touch base on some suggestions for connection time-out errors?

Farooq: We didn't talk about that, but that's a good question. If you're getting messages like "Time-out expired from your application," if you have access to the source code, for example a Visual Basic® program, take this as an example. If you're connecting to SQL Server over ADO, there is a way you can configure the time-out value, so you may want to increase that value.

You can also try to test it outside of the application. Like if you have the SQL Server Client Tools, you can try to connect with Query Analyzer and see if it times out. If it does, increase the time-out value. It's the same if you want to use a Odbcdsn or a .udl file. You can configure the time-out values and play around with it, increase it until you can get a connection. In the long run, you also probably need to find out does this happen very often or all the time, or is it just a once in a blue-moon-type thing? Because network conditions can also play a part. Maybe the network is a bit busy or congested, and for that particular moment you couldn't get a connection in the time-out period. So those are some general ideas for you.

Otto: I believe we already touched on this. This is a clarification question: For the default instance of SQL Server 2000, is UPD port 1434 still needed for connectivity?

Farooq: If you want to connect to a default instance that's listening on port 1433, you do not have to have that enabled, provided that on the client side you've also specified that it's using TCP/IP and also that it's going to connect to 1433. By default, that client will connect to 1433 on the computer running SQL Server, or attempt a connection, anyway. So if UDP 1434 is shut off, you should still be able to connect to a default instance, at least.

Otto: Are there any special considerations we need to take into account for multiplatform environments? We have everything from Windows 95 to Windows XP Professional on our clients.

Farooq: For connectivity purposes, the most important thing is that the client and the server have a common network library and a common network protocol. So if you're on a TCP/IP network, named pipes, Net-Library, the TCP/IP Net-Library will work in a TCP/IP network, so make sure the computer running SQL Server is configured for those Net-Libraries as well as your clients. And on Windows 95, Windows XP Professional, and Windows 98, named pipes and TCP/IP client-side Net-Libraries are supported. So it should be pretty straightforward.

Otto: Another question concerning ports: Is there any way to limit dynamic port assignment on the client machine? For instance, between ports 1024 and 2000?

Farooq: No. The source port on the client is going to be chosen by Windows, and it's going to be a random port, above 1024. And there's no way to control it or set what the source port on the client will be.

Otto: Do you have any pointers to documentation that would explain how to configure a trusted connection between two separate Windows NT domains on Windows NT 4.0, non-Active Directory servers?

Farooq: You can look in SQL Server Books Online to see how to configure Windows NT accounts to work with SQL Server. The high-level overview is, if they were all in the same domain, you would go to the computer running SQL Server and add that account or group as a SQL Server login. Now if you want to do it between two different domains, say your SQL Server is in one domain and all your accounts are in another resource domain, you will need to make sure those two domains trust each other. And you can look at the Windows documentation to get into how to configure the trusts. But after the trusts are worked out, you can go to SQL Server and you should be able to add that logon from that other domain easily. So the key is to get the trust configured properly first.

Otto: We have a related, clarifying question on ports: For connection through a firewall, does port 1434 need to be opened from the server to client or a named instance?

Farooq: For a named instance, and I think you mean port 1434 from the client to SQL Server, that port should be open in that direction. If you've shut port 1434 off and your SQL Server is configured to use another port, aside from 1433, the client won't be able to connect unless the client has an alias configured that tells it to connect to port 1433 on the computer running SQL Server, to connect to that port that the instance is listening on, on that SQL Server. So yes, you can shut port 1434 off, but then on the client side you would have to configure an alias so that the client knows what port it needs to connect to, to get to that named instance. That's how, by default, a client can only specify a server name/instance name and not know any details about what port it's listening on. Because it will eventually go to port 1434 to get that information. But if that's shut off, then the client will have to have an alias that specifies what port on the computer running SQL Server it needs to connect to. I hope that answered your question.

Otto: Generally, how long should a connection take? It takes two to four seconds on my system, sometimes longer. Do you have any suggestions on performance improvements in that area, or does that look like it might be some kind of a connectivity issue?

Farooq: Two to four seconds, that's very general, and it's hard to say, because it depends on a lot of factors. Mostly it can depend on the network. I assume you're connecting over a network and it takes two to four seconds. If this is too long, are you getting any kind of errors, or does it just take two to four seconds? I wouldn't say that was a big issue. As far as improving that time, you can run the Network Monitor trace on the client end of SQL Server, and then look at the time the packet takes. Maybe when you're connecting that three-way handshake, maybe that's taking a while for some reason, or maybe there are other factors on the network that are contributing to it. That's a very general question, but if you really want to know, I would suggest running a Network Monitor trace on the client server and seeing where the time is going to connect.

Otto: Are there any troubleshooting tools available for MSDE that we can distribute?

Farooq: That's a good question. Yes, MSDE comes with Osql.exe, which is a command-line type of Query Analyzer tool. That comes with MSDE, and you can distribute that to the customers. So that's a great troubleshooting tool you can use.

Again, if they do not have any of the SQL Server tools on the client machine, you can always use an Odbcdsn — go into Control Panel and Data Sources. Or you can configure .udl files. .udl files and Odbcdsns can be made to test connectivity, and you don't have to have SQL Server or the SQL Server tools installed.

Otto: Is there a difference between using a Client Network Configuration Utility and the administrative tools data connectivity tool, the ODBC tool?

Farooq: Not really, because you can set both to the SQL Server computer you want to connect to. The only difference is that when you set the Client Network Utility, you can configure aliases so that you can specify a particular server and a particular port that you want to connect to. And with Odbcdsn, I believe, you have the concept of aliases. But they're both connectivity tools. Also, with Odbcdsn you can specify what network libraries you want to use; you can use TCP/IP or named pipes. So they're very similar tools.

Otto: I'm having some troubles connecting to SQL Server 6.5 on Windows NT using the SQL Server 2000 Upgrade Wizard on a Windows XP machine. All the requirements have been met for the upgrade, and the Query Analyzer can connect to SQL Server 6.5. The wizard can connect to other computers running SQL Server 6.5 on the network, and I suspect that the SAM on the Windows NT machine might be corrupted. Can you discuss this issue?

Farooq: What error are you getting from the upgrade wizard? Maybe you can supply us with that.

Otto: If that user can provide the error message in regard to that question, we'll certainly follow-up.

Moving on to the next question: What does the connection check for data mean in the general network error?

Farooq: Connection check for data is like a function in your client-side Net-Library. Basically, when you get general network error, you'll get an additional error message that tells you, pretty much, what function was called, or what function returned the error message. That's what that is, the connection check; it's a function.

Otto: When should you select named pipes versus TCP/IP Net-Libraries?

Farooq: Named pipes and TCP/IP, that's a common question. It depends, again. If you're thinking in performance terms, you might have heard TCP/IP is faster than named pipes. In a local area network, you may not notice the difference. TCP/IP named pipes in a local area network, performance should probably be about the same, even though the TCP/IP Net-Library, when you get down to it, is probably technically faster than named pipes. But if you go into a wide area network scenario, for example, you may notice that named pipes is a bit slower than TCP/IP. So again, it depends on your scenario. And I really encourage you to test both protocols out, test both Net-Libraries, to see which one performs better and suits your requirements better.

Otto: We have a follow-up to that question concerning the SQL Server 6.5 connection: The error message that we receive is "unable to connect to the export server. Please verify that you are an NT administrator on that machine." And I'm using a user in the domain that actually belongs to the local and domain admin group.

Farooq: That sounds more like a security authentication type of issue than a connectivity issue, so it may be out of scope, but I can follow-up with this at a later time. I would suggest that you search the Knowledge Base for this particular error message. But I'll take that as a follow-up question.

Otto: We have a follow-up to a previous question. The original question was: Is there any relation between the SQL Server agent and connectivity problems? The user is asking: Where can you configure the default connection that Isql.exe uses? I'm getting the "DB library unable to connect. SQL Server is unavailable or does not exist. Access denied" Net-Library error. And I'm running Isql.exe on the local SQL Server, which is actually clustered.

Farooq: If you're connecting to a named instance, note that Isql.exe cannot connect to named instances, so I would check that. If it's on a cluster, I assume that it may be a named instance, and that is probably the problem. I would try Osql.exe to see if you get the same error. But all in all, the way you configure what Net-Library Isql or Osql is going to use is in the Client Network Utility. And by default, shared memory should be enabled if the client is on the same machine as SQL Server.

Otto: We want to use the server name alias CNAME entries for servers with multiples instances, and we're wondering what the best way is to configure the clients so the client alias won't ever have to change.

Farooq: What you can do is set up a DNS alias and specify the DNS alias as the server name and the IP address of the computer running SQL Server. So on the client side, they can connect using that CNAME alias, and then it should find SQL Server. So if the name or IP address of the computer running SQL Server should change, all you have to do is update that alias on DNS side, and your clients will still be able to connect. So for how to add DNS aliases, I would look in the Windows documentation.

Otto: What BCP port should be used? Because we have two versions of MDAC in the client and server, the BCP is not working between the client and server.

Farooq: Again, without an error message, it would be hard to give you an answer. Maybe they can follow-up with what kind of errors they're seeing when they're doing the BCP.

Otto: If that user can give us a little more detail on what kind of error messages you're receiving regarding BCP, that would certainly be helpful. We will certainly attempt to address it during the WebCast today.

Farooq: Do they mean port 1433?

Otto: Actually it was a follow-up. I believe they may have meant 1433. I wanted to know if we had to worry about anything other than SQL Server trying to use 1433.

Farooq: There may be. What I would suggest is to go to http://www.iana.org, and they have a list of well-known ports that have been registered by various applications. And port 1433 is one of the SQL Server ports, and you'll find others. So if you don't see it in that list, then you should be able to use it. But I would first check that list, on iana.org, to see if port 1483 is a well-known port.

Otto: Do the Force protocol encryption check boxes have to match between client and server? Will the server demote it if the clients' check box is checked, but not the servers?

Farooq: When configuring encryption, it is important that you do not have it enabled on the client and the computer running SQL Server. So one or the other should be checked, but not both, or you will have connectivity errors. We do have Knowledge Base articles on setting up encryption, as well as a good WebCast on setting up encryption server side and client side; you can do one or the other. So I would either look at that WebCast or do a Knowledge Base search. But to answer your question, it should only be enabled on the client or the server, but not both.

Otto: Does that confuse matters using the listening tools to find encrypted messages?

Farooq: I'm not sure what that question means. Can they clarify that?

Otto: Yes. If we could get some clarification on the last part of that question, that would certainly be helpful.

Moving on to the next question: I'm running Microsoft Project Web Server with SQL Server 2000, and currently I'm getting Internet server errors when I attempt to perform any view functions, such as user groups or the actual project itself. Do you know if those types of errors necessarily come from SQL, or do you know if those might be Project application related? It might be kind of hard to tell, just based on the fact that we don't have the exact error message.

Farooq: Right, and I'm not sure of the way the application is designed, like where does that Project server sit; is it on the computer running SQL Server or a separate machine? Without knowing the environment and the error message, it would be kind of hard to give an answer to that, unfortunately.

Otto: Perhaps we can get a little bit more clarification from the user on that one as well.

Farooq: That's a very good question. Yes, when you're troubleshooting connectivity issues, you want to reproduce what the application is doing as closely as possible, under the same conditions. So the answer to that is yes, if you do know what queries are being executed by the application. And if you don't, you can always use SQL Server Profiler. You should execute those same commands in SQL Server to see if you get similar behavior. By doing this you will see if it's just an application problem or a general SQL Server issue, perhaps.

Otto: We touched on VPN a little earlier, and a couple of users are asking: Other than having the proper ports open, are there any special considerations we need to take into account for VPN users?

Farooq: You should be sure that the name resolution works through the VPN line. That's probably the next most important thing. If you are hitting walls trying to figure out what's happening, you always can run Network Monitor. And that's probably the best tool you can use. Have an network administrator to look at it, to see what's going on. Perhaps there are some router issues, and maybe some packets are just getting lost or not getting to the computer running SQL Server, or getting misrouted. So yes, if you're sure SQL Server is running the right ports, I would check name resolution. But again, if you hit a wall, there's always Network Monitor.

Otto: We have a follow-up to one of the questions that we received earlier, where we were asking if using the listening tools on the encrypted messages confused matters. My concern is that I have to turn off the encryption to pinpoint the server and client conversation while using a network trace.

Farooq: When you're running a network trace, if tracing activity between a client and SQL Server, and you have encryption enabled, all SQL Server traffic is going to be encrypted. So you won't be able to see any kind of queries going back and forth; they will just be pretty much garbled, because it's encrypted. So yes, your network trace will pretty much be unreadable in terms of the SQL Server traffic. I hope that answers the question.

Otto: Can I install MDAC 2.6 and specify Network Library as Dbnetlib in a connection string even if my Visual Basic .dll files are compiled with MDAC 2.5? Will that end up using Dbnetlib instead of Dbmssocn?

Farooq: I believe you can still use the Dbmssocn, and that will force it to use TCP/IP Net-Library. If you search in the Knowledge Base, I believe there is an article that gives us the different connection strings for the different Net-Libraries. A Dbmssocn can still be used with MDAC 2.6. You wouldn't actually use Dbnetlib.

Otto: Can you expound a bit on the differences behind the use of MDAC versus ODBC and versus the SQL Agent?

Farooq: Again, if you could clarify that question a little more, it might be helpful. What I can say is the ODBC driver and the OLE DB providers for SQL Server are part of MDAC and they work hand-in-hand with their Net-Libraries to help the client communicate with SQL Server. Maybe you can clarify the SQL Agent part of that question.

Otto: We have a follow-up. It simply states: Connectivity between the client and the server.

Farooq: Was this related to the previous question?

Otto: Yes, the MDAC versus ODBC versus a SQL agent.

Farooq: The SQL Agent is also going to use MDAC, because it's just another client of SQL Server; it just happens to be running on the same machine. So SQL Server agent is going to connect to SQL Server using ODBC, but all in all, the Net-Library it's going to be choosing is dictated by the network utilities settings. The ODBC is on a higher level than your Net-Libraries. But they all work hand-in-hand to get connectivity between client and SQL Server working. Does that answer your question?

Otto: It looks like they just wanted to know if one was preferred over the other.

Farooq: SQL Server Agent is an ODBC application, so it's going to use ODBC at the high level. But internally we're going to go through a particular Net-Library. And by default, if the client is on the same machine as SQL Server, it will use shared memory. But those settings are dictated in the Client Network Utility.

Otto: Can you create SQL parsers in the Network Monitor for SQL Server? And is there any document for it?

Farooq: There is no documentation on creating a parser for SQL Server packets. The protocol that SQL Server uses is TDS, and that is not available; it's not documented. So no, you cannot do that. But you should still be able to see the packets in the Network Monitor trace, going from a client to SQL Server.

Otto: The final question that we have in the queue: Running the Osql.exe command finishes with the error, "Shared memory. SQL Server does not exist, or access denied," and we're wondering how we specify the default server name. I can only successfully execute the command when I specify the Osql.exe -s with a full instance name.

Farooq: When connecting to a named instance, you have to specify the server name, then a backslash (\), then the name of the instance. So even if you didn't do it in Osql, say you used Query Analyzer or Enterprise Manager, or another client application, if connecting to a named instance, they would have to specify the server name\ name of the instance, if they were trying to connect to name. So in your Osql.exe connection string, make sure you have the actual instance name specified as well.

Otto: What's the best practice for connecting to SQL from IIS and ASP? Currently we're using a connection string in Global.asa, using OLE DB.

Farooq: That is certainly a good way to do it. As far as preferred, Windows Authentication is probably preferred to SQL Server Authentication, but I'm assuming you may already be using that. But as far as OLE DB or ODBC, OLE DB is perfectly fine. So the way you have it right now, there shouldn't be any issues there.

Otto: With that, we've addressed all the questions that were submitted to the queue today. I'm going to wrap up the session. I want to thank everyone for joining us. I hope that the information was useful to you. I certainly want to thank Farooq for coming out and giving us a great presentation.

If you have any suggestions for future topics, general comments about today's session, or even the WebCast program as a whole, e-mail us at supweb@microsoft.com. Our overall goal is to ensure that we're providing you with the right content in the best way possible, so that feedback is absolutely important to us. I hope that everyone has the opportunity to tune in again soon. Have a great day.