How does a client talk to SQL Server? What is a net-lib? What network protocols are used? What net-libs support NT authentication/encryption?

A. There are good descriptions in the SQL Server portion of the Back-Office resource kits about how this all hangs together. This FAQ article will attempt to bring all the salient points together. It covers SQL 7.0 and earlier.

Client to Server communication works in a layered fashion. Each layer 'talks' to it's neighboring layers in a standard fashion. I have given these layers arbitrary numbers - these numbers in no way correspond to the layer numbers in the OSI model. The only time that layers can be bypassed are :-

(a) If a client application directly accesses the layer 3 low-level api interface it can bypass layer 2.

(b) If shared-memory or a local named-pipe (".pipesqlquery") is used then layers 5 and 6 are bypassed.

(c) BCP and DTC do their own TDS formatting so bypass layer 3.

At the transport protocol layer this conversation goes between the client machine and the server machine (and back again). For all other layers the conversation is done locally, "in-memory" between the various dll's involved. 

The layers are listed below :-

1. Client App - written in VB, C, Delphi etc.

2. Client "High-Level" Data Access API. This level is optional - it is possible for the application to directly call the layer 3 interfaces. However these interfaces generally need a lot of lines of client code/API calls to achieve the business requirement. Hence there are higher-level api's that call these lower level ones - higher-level api's need less client code/API calls to achieve the same result. Examples of these API's are ADO, RDO, DAO, Embedded SQL and VBSQL.

3. Client DB Interface (OLE-DB, ODBC, DB-Library). Note that not all client languages can call this layer directly - e.g. VB cannot call OLE-DB directly as VB lacks the necessary memory address pointer support.

3. Client TDS formatter. All communication to SQL Server has to be in TDS (Tabular Data Stream) format. See tds.txt in the faq for more information.

4. Client Net-Lib. Shared Memory(local 95/98 only), Multi Protocol, Named Pipes, TCP/IP Sockets, Novell IPX/SPX, AppleTalk(NT only), Banyan VINES

5. Client Transport Protocol. NW Link IPX/SPX, NetBEUI, TCP/IP, AppleTalk, Banyan VINES

6. Server Transport Protocol. NW Link IPX/SPX, NetBEUI, TCP/IP, AppleTalk, Banyan VINES

7. Server Net-Lib. Shared Memory(local 95/98 only), Multi Protocol, Named Pipes(NT only), TCP/IP Sockets, Novell IPX/SPX, AppleTalk(NT only), Banyan VINES(NT only)

8. Server DB Interface (Open Data Services - ODS).

9. SQL Server

Other points, issues and clarifications :-

If you want to see some of the details described after this actually happening then you can do a network trace with Microsoft Network Monitor (or another network data-capture tool) and see the packets SQL sends/receives for yourself. If you use the version of NM that comes with SMicrosoft 2.0 this contains a built-in TDS parser that will show detailed information.

If you want to see what net-lib a client has connected with, do a select from master..sysprocesses which shows the net-lib used.

The client and server MUST have at least one matching Transport Protocol. So if a client only runs tcp-ip then the server must have tcp-ip as one of it's network protocols. To check what protocols are configured look at control panel/networks/protocols or run the command "NET CONFIG WORKSTATION" at a command prompt. The order in which these protocols are tried is determined by their binding order which you can see in the network applet in control panel under the bindings tab. The way NT works is that it will try all the protocols relevant to a net-lib "simultaneously" - it doesn't wait for one to fail to connect before trying the others. However, as only one network packet can be sent at a time, the various connection attempt packets must be sent in an order - which the bindings dictate. The first protocol used is usually the one to succeed (assuming the server is listening on that protocol).

The client and server MUST have at least one matching network library. i.e. If the client connection is configured to use the Multi-Protocol net-lib then the server must be listening on that net-lib. A client is configured to have a default network library. If a connection needs to be set-up to a server with a different net-lib then this can be configured with the advanced option of the SQL Client config utility. If you need to connect to the same server with different net-libs then this can be achieved by setting up multiple aliases via the advanced option.

To see what client net-libs are configured run SQL Client configuration utility, which is in the SQL program group. 

To see what server net-libs are configured for SQL 6.5 and below run SQL Setup and choose configure server and then the network option. For SQL 7.0 you can run SQL Server Network Utility in the SQL progam group.

Which net-libs support which network transport protocols? :-

Multi ProtocolNW Link IPX/SPX, NetBEUI, and TCP/IP
Named-PipesNW Link IPX/SPX, NetBEUI, and TCP/IP
TCP/IP SocketsTCP/IP
IPX/SPXNW Link IPX/SPX
AppleTalkAppletalk
Banyan Vines Vines IP
Shared-memoryn/a (it uses internal rpc calls)
DECNetDECNet (This net-lib only existed in 6.5 and earlier)

Which net-libs use which DLL's? :-

Net-lib32-bit DLL16-bit DLL
Multi Protocoldbmsrpcn.dlldbmsrpc3.dll
Named-Pipesdbnmpntw.dlldbnmp3.dll
TCP/IP Socketsdbmssocn.dlldbmssoc3.dll
IPX/SPXdbmsspxn.dlldbmsspx3.dll
AppleTalkdbmsadsn.dlln/a
Banyan Vines dbmsvinn.dlldbmsvin3.dll
Shared-memorydbmsshrn.dlln/a
DECNetdbmsdecn.dlln/a

NT Authentication (SQL 6.5 and below). Multi-Protocol and Named-Pipes both support and enforce NT authentication. i.e. your client must be connected with a set of NT credentials that the server can validate. The easiest way to check this is to do a "NET USE \IPC$" command from the client. If this works then you can NT authenticate with the server.

NT Authentication (SQL 7.0). All net-libs support NT authentication as SQL Server calls the NT SSPI interface to check credentials rather than the old NTLANMAN interface. However only named-pipes and multiprotocol "enforce" this authentication - this is because the way these net-libs connect to the server they go through NT networking layers that demand authentication implicitly. If you use one of the other net-libs and do not make a trusted connection - i.e. you pass in SQL Server standard userid/password - then you don't have to be NT authenticated. If you want to use these other net-libs with integrated security/NT authentication then you need to put "Trusted_Connection=yes" in your connect string.

Multi-Protocol is the only net-lib that supports encryption. All packets sent using multi-protocol are encrypted - that includes all data, userids/passwordd etc. Encryption (on/off) can be configured at the client and server end. At the server end you can enforce encryption - if you do this then any clients not configured for encryption won't be able to connect with the multi-protocol net-lib. The encryption used is the built-in NT encryption libraries - therefore the strength of encryption depends on NT. By default this is 40-bit - if you are able to apply the NT "high-encryption option" version then you will get 128-bit strength encryption.

Netware - if the clients are true NetWare clients, they probably run IPX/SPX as their Transport Protocol (In Microsoft systems NWLink which is Microsoft's compatible protocol for IPX/SPX). In newer systems, you will find TCP/IP as the transport protocol because Netware is in a transition to TCP/IP as well. Keep in mind that even if we are talking about 'TRUE' Netware clients, they are still running a Microsoft operating system; there are no Netware clients that run a NW operating system.

The Client DB Interface api's (OLE-DB, ODBC, DB-Library, Embedded SQL) do not call each other. ODBC does not run "over" db-lib or call it in any way. This is true for all current versions of these api's - in the past there was an early version of OLE-DB that used to work with an interface called "Kagera" that used to call ODBC, but this is an obsolete driver/version now.

Named-pipes requires "netbios" to be there to work. Therefore the relevant NT service has to be installed and working - e.g. netbios over ip (NBT), netbios over IPX.

It is possible to trace net-lib packets - at least with 6.5 and earlier anyway - without a network trace. This uses a tracing dll/utility called NLSPY. This can be found with the BackOffice Resource Kit Part II.

SQL Server uses standard NT name-resolution methods to turn a server name into a network level address. The methods and order this is done in are described in the nameresolution.txt faq entry.