DB Connection Problem

DB Connection Problem

Post by Tim For » Wed, 09 Oct 2002 23:46:36



People,

I keep on getting this error if I connect from VB or Enterprise Manager to a
SQL Server 2k Box

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied.

The strange thing is that i have been working on the server for the last 6
hours, I get this problem every day and it is resolved by doing a simple
restart of my computer. The time has come for me to try and resolve this
problem. No one has changed the security setting under SQL Server 2k and I
know for a fact that if I restart my machine it will be fixed, but only for
another couple of hours.

The only possible thing that I think it could be is that I am opening
connections in VB and not shutting them, but exiting the application but I
would have thought that it would not cause this error. Perhaps something is
running out of memory i'm not sure.

Any help would be most appreciated.

Thanks Tim.

 
 
 

DB Connection Problem

Post by Bill Hollinshead [MS » Thu, 10 Oct 2002 01:47:20


Hi Tim,

This can be a tough one <g>. Connecting to SQL Server from a client
requires the following (ordered upwards from the lowest layer of the Open
Systems Interconnect model - see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wcec...
1. Physical and Data-Link layers: Functional hardware (for example: NIC,
Ethernet cable, hub, switch, and router)
2. Network and Transport layers Functional operating system (for example:
name resolution, packet delivery, matching network protocols, matching
network libraries)
3. Application, Presentation, and Session layers: Functional SQL Server,
Client application, Database API, MDAC drivers (for example, SQL Server
accepting a client connection, the client specifying a connection to a
server that exists).

An intermittent loss of network connectivity (or denied network access)
will result in the error message that SQL Server does not exist or access
is denied during an attempt to do a  Winsock connect (the [ConnectionOpen
(Connect()).]). The problem is that message can be due to a failure within
any of the above layers <g>. I generally prefer to work from the lowest
layer (1) upwards. Here are some things to try:

Layer 1. Check the client's system event log for errors about networking
hardware. The Source (in the event log) for such problems will usually be
the manufacturer's driver. Problems with routers, hubs are not logged
there, but the need to reboot the client almost rules that type of hardware
as the problem.

Layer 2. Client the client's system event log for problems connecting to
domain controllers, problems with WINS and/or DNS. Follow
http://support.microsoft.com/support/kb/articles/q314/0/67.asp (for Windows
XP) or http://support.microsoft.com/support/kb/articles/q300/9/86.asp (for
Windows 2000). Also (for Windows 2000) follow
http://support.microsoft.com/support/kb/articles/q321/7/08.asp. I
personally prefer pathping.exe over tracert.exe. A failure in this 'layer'
can happen when there is a failure in 'layer' 1 (this is why I like to
check 'layer' 1 first). May sure that both the names and the IP addresses
returned by pings (by IP address with /a, and by name respectively) match.
Ensuring the client and server has matching network protocols and network
libraries is not likely to be helpful because of the intermittent nature of
this client's problem.

Layer 3. Try odbcping per
http://msdn.microsoft.com/library/en-us/odbcsql/od_6_100_3hdc.asp and
http://support.microsoft.com/support/kb/articles/q138/5/41.asp. I am afraid
there is no directly equivalent utility for ADO/OLE DB (although the OLE DB
SDK's rowsetviewer offers somewhat of an independent OLE DB check). Ensure
another client (box) can connect to SQL Server while the client is failing
(this test defines whether the server is not listening or whether it is
just the client that cannot communicate). While this latest test may not
seem relevant (because a reboot of the client resolves the problem), it is
in fact very important (because a client can improperly (even 'buggedly')
cause SQL Server to stop listening (to all client connection requests) and
thus we would need to concentrate upon SQL Server (errorlogs, traces, etc)
as opposed to concentrating upon the client/network. Ensuring the client is
running the latest MDAC service pack may also avoid the error.

Following this paragraph is a shopping list (Examples of Potential Causes)
that is presented in a slightly more of a shotgun approach than the ordered
steps I had mentioned above (please excuse some duplication of content
<g>). You may find it useful to fit the following causes into the above 3
layers (so that the troubleshooting is more methodical) and test them at
the same time that you follow what I mentioned above.

Examples of Potential Causes
----------------------------

Server:

If no clients can connect to the SQL Server, then there may be a server-side
problem.
 - SQL Server is not installed on the machine specified in the connection
   string. Verify that SQL Server is actually installed and started on the
   machine you specify in your connection string.
 - SQL Server is not started. SQL Server must be started to be able to
accept
   connections.
 - SQL Server is not listening on the protocol and/or port you are using to
   connect. The Server Network Utility on the server determines what types
of
   protocols and what TCP/IP ports SQL Server will listen on. The Client
Network
   Utility, an MDAC DSN, or your connection string determines which
protocol and
   port your client uses when it connects. See:
   Q289573 PRB: Configuring DSNs with SQL Server Net-Libraries
   and
   Q328383 INF: SQL Srv. Clients May Change Protocols When They Try to Conn
 - When SQL Server started, it was not able to listen on the specified
TCP/IP
   port and is not accepting TCP/IP connections. See:
   Q293107 PRB: Unable to Release TCP\IP Port 1433 for Client Communication
 - The SQL Server name is not the same as the computer name. For SQL Server
2000
   see:
   Q303774 BUG: Renaming A Server Topic in Books Online is Incomplete
   For SQL Server 7.0 see item 5 in
   Q195759 INF: FAQs - SQL Server 7.0 - SQL Setup
   For clusters, see:
   Q307336 INF: How to Change a Clustered SQL Server Network Name
   Q298822 FIX: MSDE Connection May Fail on a Multihomed Computer
   Q306199 FIX: Dynamic IP Addresses Cannot Connect to SQL Server 2000

Client or Application:

If some client machines or applications can connect but others cannot, it is
likely a client side problem.
 - The machine name as typed does not exist. Double-check the spelling of
the
   machine you are trying to connect to.
 - You are trying to access a named instance but are not specifying the
proper
   instance name. See:
   Q265808 INF: Connect to an SQL 2000 Named Inst. w/Prev. Ver. Client Tool
 - The Windows-level security you are connecting with is different that
what you
   expect. For example, IIS uses IUSR_machinename, not the account you used
to
   log in to Windows with, or perhaps another account, depending on how IIS
is
   configured. Also, linked servers use the windows-level security context
of
   the sqlagent in some circumstances. The security context of the account
you
   are actually using may not have access to the machine where SQL Server
   resides.
 - You aren't using the protocol you think you are. Check your Client
Network
   Utility (CNU) to see what protocols are enabled (for older versions of
MDAC
   you set a default rather than enabling protocols). Also see if there are
any
   aliases defined in the CNU that specify a different protocol and/or port
than
   you expect. See
   Q328383 INF: SQL Srv. Clients May Change Protocols When They Try to Conn
   and
   Q289573 PRB: Configuring DSNs with SQL Server Net-Libraries
 - You removed the Client for Microsoft Networks networking component from
the
   network properties on the client. See:
   Q253959 Client for Microsoft Networks Functions When Unbound from Network
   Adapter
 - If SQL Server is clustered, the NICs may be improperly named or
configured.
   If any NICs are named with special characters or with mixed case, rename
   them. Verify that the NICs on the machine are configured properly.
 - You may be experiencing the side-effects of an MDAC mismatch. See:
   Q307255 INFO: Component Checker: Diagnose Problems and Reconfigure MDAC
 - Check the protocol specified in

"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo\DSQUERY"

 
 
 

1. Hierarchical(SHAPE) report db connection problem

Hi,

I have an ActiveX DLL project which basically runs inside
a host application. My project contains several MS Data
Reports, connecting through an MS Data Environment. The
host application establishes the database connection to an
MS SQL Server through OLE DB. Normally, my program should
use the connection already built by the host application.
The problem is that I should create hierarchical reports -
so called SHAPE reports - that use the special SHAPE
syntax. As far as I am concerned, the standard OLEDB
provider cannot parse the SHAPE syntax, one should use the
MSDataShape provider for this reason. Since the connection
built by the host application is not of this kind, I
should use a separate (SHAPE)connection, which of course
raises security concerns(new user and password etc.). Is
there any way or trick to resolve this problem, without
using a separate connection?

Thanx in advance,
Victor.

2. VB error msg

3. oracle db connection problem

4. Data Replication

5. Strange DB connection problem

6. Cant' Start Repot Server2.1 Service(ENV: NT4.0, Oracle8.04, Dev2K 2.1)

7. SQL Server 7/OLE DB Connection Problem

8. ESQL/C EXECUTE IMMEDIATE BUG?

9. DB connection problem from Crystal Report.

10. Actuate DB Connection Problems

11. OLE DB Connection problem

12. OLE DB connection problem on Windows 200 Professional

13. Oracle DB connection problem