Neil - Please answer a connection problem

Neil - Please answer a connection problem

Post by Jeste » Thu, 03 Feb 2000 04:00:00



Neil,

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.

Any ideas?

Wayne - can you NET VIEW \\servername ?  I suspect you aren't logged on
to the
NT domain.

Quote:> I'm having a problem where a W95 computer can't see sql server.
> It can see the network, including the server and can copy files from
> that server.

> I've installed the sql client utilities on the W95 computer. but
ISQL/w
> and SQL-EM can't connect to the sql server. The error message that I'm
> getting is:

> Msg. No. 10004
> "Unable to connect. SQL Server is unavailable or does not exist.
General
> network error. Check your documentation."

> I'm trying to log in with standard security via the sa access.

> The other computers on the network can access the sql server.

> Any and all help would be gratefully appreciated.
> Thanks,
> Wayne

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (412 entries) see

http://forumsb.compuserve.com/vlforums/UK/default.asp?SRV=MSDevApps
 or www.ntfaq.com/sql.html (+ ntfaq download)
 or http://www.sql-server.co.uk
 
 
 

Neil - Please answer a connection problem

Post by Richard Waymir » Thu, 03 Feb 2000 04:00:00


If you use sockets you can get around the security requirements (assuming
you're requesting a standard security connection), but named pipes and
multi-protocol are authenticated by Windows before they ever get to SQL
Server.

--
Richard Waymire, MCT, MCSE+I, MCSD, MCDBA

> Neil,

> 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.

> Any ideas?

> Wayne - can you NET VIEW \\servername ?  I suspect you aren't logged on
> to the
> NT domain.

> > I'm having a problem where a W95 computer can't see sql server.
> > It can see the network, including the server and can copy files from
> > that server.

> > I've installed the sql client utilities on the W95 computer. but
> ISQL/w
> > and SQL-EM can't connect to the sql server. The error message that I'm

> > getting is:

> > Msg. No. 10004
> > "Unable to connect. SQL Server is unavailable or does not exist.
> General
> > network error. Check your documentation."

> > I'm trying to log in with standard security via the sa access.

> > The other computers on the network can access the sql server.

> > Any and all help would be gratefully appreciated.
> > Thanks,
> > Wayne

>  Neil Pike MVP/MCSE.  Protech Computing Ltd
>  (Please reply only to newsgroups)
>  SQL FAQ (412 entries) see

> http://forumsb.compuserve.com/vlforums/UK/default.asp?SRV=MSDevApps
>  or www.ntfaq.com/sql.html (+ ntfaq download)
>  or http://www.sql-server.co.uk


 
 
 

Neil - Please answer a connection problem

Post by Neil Pik » Thu, 03 Feb 2000 04:00:00


Jester - if you use multiprotocol or named-pipes then you must authenticate to
NT first.

> 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?
(v1.5 1999.11.18)

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,
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 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
   AppleTalk       Appletalk
   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
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.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (412 entries) see
 sqlfaq.zip in lib 7 (SQL Public) @
http://forumsb.compuserve.com/vlforums/UK/default.asp?SRV=MSDevApps
 or www.ntfaq.com/sql.html (+ ntfaq download)
...

read more »