MS SQL Server Connectivity - SQL Server does not exist or access denied.

Author

SQL Server does not exist or access denied.

stevehunter_1@hotmail.com

2005-09-29, 8:23 pm

I get the following error when trying to connect to SQL Server

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

Scenario:

The application connects to SQL Server very frequently (million+ times
per day). It fails with this error message about 100 times.

The failure rate is not very high and unfortunately, I can not open one
or more connections. I must open connection and close and do this
again a million times.

So I need to solve this problem. Where would you start? I set
timeout to 2 minutes and this helped a lot, but i still get errors.

Brad Sarsfield

2005-09-29, 8:23 pm

There are a number of things that could be going on here. Let's get some
more info on your environment

What transport are you using (TCP, LPC, NP)?
TCP has is less overhead and network reads/writes over NP.
Check out the following KB's for tweaking your TCP ack depending on your
environment
http://support.microsoft.com/kb/328890/
http://support.microsoft.com/defaul...b;en-us;q315669

There is also a limit on the number of open TCP ports that you can have
at any given time.
There are others under HKLM\SYSTEM\CurrentC
ontrolSet\Services\T
cpIp
check it out on MSDN.

Are you using SQL auth?
I have seen issues if the DC isn't available (network) and you're using
domain users.

What is the load of the client(s)/server connecting? Are there any specific
loads during failure time?
If either are under heavy memory / cpu pressure you could experience
dropped connecitons.

I would really like to help understand what's going on in your environment.

Have you considered using connection pooling? A million connections per day
is about 10-12 new connections per second. That could be a significant load
depending on how much work is going on in the engine.

--
Brad Sarsfield [MSFT] bradsa(at)microsoft.com

This posting is provided "AS IS", with no warranties, and confers no rights.

< stevehunter_1@hotmai
l.com> wrote in message
news:1128030310.743829.224010@g47g2000cwa.googlegroups.com...
>I get the following error when trying to connect to SQL Server
>
> SQL Server does not exist or access denied.
> ConnectionOpen (Connect()).
>
> Scenario:
>
> The application connects to SQL Server very frequently (million+ times
> per day). It fails with this error message about 100 times.
>
> The failure rate is not very high and unfortunately, I can not open one
> or more connections. I must open connection and close and do this
> again a million times.
>
> So I need to solve this problem. Where would you start? I set
> timeout to 2 minutes and this helped a lot, but i still get errors.
>

stevehunter_1@hotmail.com

2005-09-30, 8:23 pm

Brad, Thanks for your help. see my replies below:

Brad Sarsfield wrote:
> There are a number of things that could be going on here. Let's get some
> more info on your environment
>
> What transport are you using (TCP, LPC, NP)?
> TCP has is less overhead and network reads/writes over NP.
> Check out the following KB's for tweaking your TCP ack depending on your
> environment
> http://support.microsoft.com/kb/328890/
> http://support.microsoft.com/defaul...b;en-us;q315669
>

I am using TCP/IP. Do I apply these KB articles to the Client or the
SQL Server? The SQL Server is W2000, Client is 2003

> There is also a limit on the number of open TCP ports that you can have
> at any given time.
> There are others under HKLM\SYSTEM\CurrentC
ontrolSet\Services\T
cpIp
> check it out on MSDN.
>

At peak, we will open/close 100 connections a second.

> Are you using SQL auth?
> I have seen issues if the DC isn't available (network) and you're using
> domain users.
>

Using SQL Auth.

> What is the load of the client(s)/server connecting? Are there any specific
> loads during failure time?
> If either are under heavy memory / cpu pressure you could experience
> dropped connecitons.

During heavy use, we see the problem more, but I do not attribute it to
load, i just attribute it to more connection attempts. So more
connection attempts=more failures.

> I would really like to help understand what's going on in your environment.
>
> Have you considered using connection pooling? A million connections per day
> is about 10-12 new connections per second. That could be a significant load
> depending on how much work is going on in the engine.
>

I have considered connection pooling, but there is no performance issue
doing it this way. It works very well otherwise. I would prefer a
simple solution to the problem and would tackle connection pooling or
some other method in the future when it is time to upgrade the
application.

[color=darkred]
> --
> Brad Sarsfield [MSFT] bradsa(at)microsoft.com
>
> This posting is provided "AS IS", with no warranties, and confers no rights.
>
> < stevehunter_1@hotmai
l.com> wrote in message
> news:1128030310.743829.224010@g47g2000cwa.googlegroups.com...

Brad Sarsfield

2005-09-30, 8:23 pm

I'd try applying it first on server. It sounds like you may be getting into
the situation where you could be running out of available socket connections
for new connections or memory for new connections. For the first two you
can try to play with the following two reg keys. (Decrease the
TcpTimedWaitDelay). For the second you may need to add more memory (if sql
is infact running out of memory).

From:
http://www.microsoft.com/technet/pr...pip03.mspx#EGAA

TcpTimedWaitDelay
Key: Tcpip\Parameters

Value Type: REG_DWORD-time in seconds

ValidRange: 30-300 (decimal)

Default: 0xF0 (240 decimal)

Description: This parameter determines the length of time that a connection
stays in the TIME_WAIT state when being closed. While a connection is in the
TIME_WAIT state, the socket pair cannot be reused. This is also known as the
2MSL state because the value should be twice the maximum segment lifetime on
the network. See RFC 793 for further details.

--
Brad Sarsfield [MSFT] bradsa(at)microsoft.com

This posting is provided "AS IS", with no warranties, and confers no rights.

< stevehunter_1@hotmai
l.com> wrote in message
news:1128110959.712139.326900@g43g2000cwa.googlegroups.com...
> Brad, Thanks for your help. see my replies below:
>
>
> Brad Sarsfield wrote:
>
>
> I am using TCP/IP. Do I apply these KB articles to the Client or the
> SQL Server? The SQL Server is W2000, Client is 2003
>
>
>
> At peak, we will open/close 100 connections a second.
>
>
> Using SQL Auth.
>
>
> During heavy use, we see the problem more, but I do not attribute it to
> load, i just attribute it to more connection attempts. So more
> connection attempts=more failures.
>
>
>
> I have considered connection pooling, but there is no performance issue
> doing it this way. It works very well otherwise. I would prefer a
> simple solution to the problem and would tackle connection pooling or
> some other method in the future when it is time to upgrade the
> application.
>
>
>

stevehunter_1@hotmail.com

2005-10-12, 11:23 am

Brad

I tried this with no success.

Also, I checked my ODBC Admin screen and note that the "SQL Server"
driver has enabled connection pooling. So I assume in my MFC
application (using CDatabase with ODBC) it is automatically connection
pooling for me?

There are no network issues connecting to the server (i am able to
"ping" millions of times all day long with success).

Any ideas?

Brad Sarsfield wrote:
> I'd try applying it first on server. It sounds like you may be getting into
> the situation where you could be running out of available socket connections
> for new connections or memory for new connections. For the first two you
> can try to play with the following two reg keys. (Decrease the
> TcpTimedWaitDelay). For the second you may need to add more memory (if sql
> is infact running out of memory).
>
> From:
> http://www.microsoft.com/technet/pr...pip03.mspx#EGAA
>
> TcpTimedWaitDelay
> Key: Tcpip\Parameters
>
> Value Type: REG_DWORD-time in seconds
>
> ValidRange: 30-300 (decimal)
>
> Default: 0xF0 (240 decimal)
>
> Description: This parameter determines the length of time that a connection
> stays in the TIME_WAIT state when being closed. While a connection is in the
> TIME_WAIT state, the socket pair cannot be reused. This is also known as the
> 2MSL state because the value should be twice the maximum segment lifetime on
> the network. See RFC 793 for further details.
>

Brad Sarsfield

2005-10-17, 1:24 pm

Steve,

Lets make sure that this is the problem that you're running into: If on your
server you run netstat -aon and you have thousands of ports in the TIME_WAIT
state your clients will run into this problem.

A while back we wrote a KB article on this problem. We should also try to
adjust your MaxUserPort in addition to the TcpTimedWaitDelay setting on the
server. Check this out and let me know how this goes.
http://support.microsoft.com/kb/328476

With regards to connection polling the ODBC admin connection pooling tab
'enable' 'disable' refers to performance monitoring of the pooled
connections. To pool the conneciton your appliction must follow the five
steps outlined here.
http://msdn.microsoft.com/library/d... /> pooling.asp
here's an examle as well
http://msdn.microsoft.com/library/d...ml/pooling2.asp

Let me know if the steps in the KB work for you.

--
Brad Sarsfield [MSFT] bradsa(at)microsoft.com

This posting is provided "AS IS", with no warranties, and confers no rights.

< stevehunter_1@hotmai
l.com> wrote in message
news:1129087454.925724.10410@f14g2000cwb.googlegroups.com...
> Brad
>
> I tried this with no success.
>
> Also, I checked my ODBC Admin screen and note that the "SQL Server"
> driver has enabled connection pooling. So I assume in my MFC
> application (using CDatabase with ODBC) it is automatically connection
> pooling for me?
>
> There are no network issues connecting to the server (i am able to
> "ping" millions of times all day long with success).
>
> Any ideas?
>
>
> Brad Sarsfield wrote:
>

Sponsored Links