>Ed:
>
>I'm a little confused. Are you saying I need to create an LMHOSTS file so that Win95\98 can connect the SQL Server. How do they get validated on the network where the SQL Server resides?
>
Presumably, the SQL Server is a domain-managed resource. When you attach to the server, if you are not logged into the domain that the SQL Server is a member of, or a trusted domain, you'll have to pass a userid and password that is either validated relative to the local machine, or a domain controller if specified. If you are logged into a domain already, and your domain is trusted, your domain's authentication provider validates you. Otherwise, if no userid and password are provided explicitly, the Windows login userid and password are used to attempt to login to the local system as a user. No valid local account results in a bounce at this point. The two items below from the NTFAQ site might help here:
Q. I'm getting an access denied or CreateFile message when connecting to SQL Server.
A. The message may be one of the following
---------
Msg No 10004 Severity 9 State 0
Unable to connect: SQL Server is unavailable or does not exist. Access denied
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][dbnmpntw]ConnectionOpen(CreateFile())
MSG 10004, Severity 9.( sqlserver not available). OS 1326
---------
This isn't a SQL issue, it's an NT one. If you are using a net-lib that requires NT authentication - e.g. named-pipes or multiprotocol with 6.5 or below and any net-lib with 7.0 - then you MUST be able to authenticate to the copy of NT running SQL Server.
You can test whether you can do this by doing a "NET VIEW \\servername" from a command prompt on the client. If you get an access denied message, or get prompted for a password, then you aren't being authenticated.
If this happens then you need to setup a trust between the domains. Or, you could use a net-lib that does not need authentication - e.g. tcp-ip sockets.
If you can't have a trust (and really this IS the best method) then you can override the NT details by doing a "net use \\< server >\ipc$ /user:< serverdomain >\< userid > < password >" with an account that is in the domain. But this is a manual process and prone to fail when the password changes.
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, Banyan VINES
6. Server Transport Protocol. NW Link IPX/SPX, NetBEUI, TCP/IP, AppleTalk, Banyan VINES
--------------------------------------------------------------------------------
This FAQ is copyright © 1999 John Savill (SavillTech Ltd). No part of this document should be reproduced, distributed or altered without my written permission.
