Jester - if you use multiprotocol or named-pipes then you must authenticate to
> If you are using standard security, do you still have to have domain
> access? I am having the same problem from Win NT WS. I've tried
> MultiProtocol, Names Pipes and TCP/IP. The really odd thing is that
> there is one machine on our floor that can access the SQL Server with no
> problem and he is set up identical to me. Just FYI - we have access to
> the domain, but not to the machine that the SQL Server is running on.
Q. 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 (".\pipe\sql\query") 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,
6. Server Transport Protocol. NW Link IPX/SPX, NetBEUI, TCP/IP, AppleTalk,
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
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 Protocol NW Link IPX/SPX, NetBEUI, and TCP/IP
Named-Pipes NW Link IPX/SPX, NetBEUI, and TCP/IP
TCP/IP Sockets TCP/IP
IPX/SPX NW Link IPX/SPX
Banyan Vines Vines IP
Shared-memory n/a (it uses internal rpc calls)
DECNet DECNet (This net-lib only existed in 6.5 and earlier)
Which net-libs use which DLL's? :-
Net-lib 32-bit DLL 16-bit DLL
Multi Protocol dbmsrpcn.dll dbmsrpc3.dll
Named-Pipes dbnmpntw.dll dbnmp3.dll
TCP/IP Sockets dbmssocn.dll dbmssoc3.dll
IPX/SPX dbmsspxn.dll dbmsspx3.dll
AppleTalk dbmsadsn.dll n/a
Banyan Vines dbmsvinn.dll dbmsvin3.dll
Shared-memory dbmsshrn.dll n/a
DECNet dbmsdecn.dll n/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 \\<servername>\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
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.
Neil Pike MVP/MCSE. Protech Computing Ltd
(Please reply only to newsgroups)
SQL FAQ (412 entries) see
sqlfaq.zip in lib 7 (SQL Public) @
or www.ntfaq.com/sql.html (+ ntfaq download)
read more »