PRB: Connection Problems from NT Clients via IPX/SPX

• Microsoft SQL Server, version 4.21a

SYMPTOMS

In a large LAN with many Novell NetWare file servers and routers, or a small LAN but attached to a Wide Area Network (WAN), a Windows NT client may have difficulties connecting to SQL Servers using IPX/SPX. SQL Server may be located on the local LAN, and other SQL Server clients, such as MS- DOS or Windows, may connect to the same SQL Server just fine using IPX/SPX.

When trying to enumerate available SQL Servers on the network by selecting the List Servers button in SQL Server tools such as ISQL/W, you may not see the SQL Server you are trying to connect to, although you might see other SQL Servers running in remote LANs.

CAUSE

Before a client can connect to a SQL Server, it first needs to resolve the server name to its network address. This is done through browsing the bindery on NetWare file servers. To browse through a bindery, the client needs to attach to the NetWare file server first. On a regular client such as DOS or Windows, the file server attachment is made right after the NetWare shell is loaded, and usually you can specify a preferred file server to attach to. On a Windows NT client, however, direct attachment to a specified NetWare file server is not possible unless the Microsoft Service for NetWare software is installed.

SQL Server client for Windows NT works around this limitation by dynamically attaching to a NetWare file server. It does so by broadcasting General Query SAPs to the local network segment, and attaches to the first NetWare file server that responds to the SAP query. This should not cause any problems as the file server attached this way will always be a local file server. However, when a router responds to the SAP query, it will return a list of file servers that it is aware of. If a router's response arrives first, then the file server on the top of the list will be used for the attachment, and the NT client may end up attaching to a file server that is on a remote LAN many routers away or even across a WAN.

When the NT client browses the bindery of the remote file server for SQL Server objects, it will see the SQL Servers running on the remote network, and may not see the local SQL Servers if the routers in-between are configured to filter out SQL Server SAP traffic, which is typically done to avoid unnecessary SAP broadcasting to remote LAN's.

RESOLUTION

You will not experience this connection problem under normal situations. However, if you do, you can workaround it by using the direct connections.

To configure for direct connections, you need to find out the following information about the SQL Server machine: NIC MAC address, IPX network segment number and IPX socket number for SQL Server. You can use the Windows NT command IPXROUTE CONFIG to retrieve MAC address and network segment number, and the IPX socket number should be 33854, by default.

You can use SQL Client Configuration Utility to configure this client for direct connections. Using Advanced feature, you add an entry with the following as the connection string:

   <MAC address>,33854,<network number>

NOTE: The network number returned from IPXROUTE is in hexadecimal, and you should convert it to decimal. For further information, see SQL Server "Configuration Guide."