Strange "access denied" message - in the middle of set of queries

Question

Strange "access denied" message - in the middle of set of queries

obgc

Grasshopper

Points: 17
More actions
November 30, 2009 at 6:52 am

#137167

Hi,
I am new here and please forgive me if it's not the right place for such question.
I have an application which goes in a loop - something like this:
Open Connection1
Retrieve Data (recordset) for the LOOP
LOOP BEGIN (for each record from the statement above)
Open Connection2 (to the same server, the same database, the same user)
Execute SELECT Statement
Execute INSERT Statement
Close Connection2
LOOP END
Close Connection1
So I have maximum 2 simultaneous connections. What happens is that after some time (somewhere there in the middle of the loop - which of course shows that the loop and connections work properly) I get a message "Server does not exist or access denied" and my application "breaks". What is interesting that the "deny message" shows usually almost in the same place - there may be several loops more or less processed. I have also executed my app locally on the SQL server so network problems can be removed for the list of suspects.
Could You tell me if there is some kind of a configuration which tells SQL Server to block next connection as the client used too many of them in a small period of time? Is it something like "anti-DOS attack" protection? Any other ideas why the SQL denies the connection?
How can I tell SQL to allow my application to finish the loop and use as many connections as it wants?
I know that I can always redesign the app, but the problem is that it's not mine and I don't have the source codes [and it does something more than just the loop above 🙂 ]
I need some help, please respond.
Greg

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

i've seen this before, but in a vb6 application; are you using .NET?

opening and closing a connection takes resources, and time, since the server has to answer back with a new or reused pooled connection for your app.

There is no reason to open and close the connection inside your loop; that's your core issue here. while logically you have only "two" connections, the server sees it as a LOT more...the old connection has not finished releasing by the time you are reconnecting again....

either reuse the first connection, or opent he connection, do the work inside the loop, and then close the connection; it'll fix the issue and be much faster as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

obgc Grasshopper Points: 17 More actions · Answer 2

Thanks for Your reply.

The problem is that the application works on several installations and such problem does not occur. This is the first time I came into such problem.

Another problem is (as I mentioned) that it's impossible for me to change the application as I do not have its source codes 🙁 and there is some more logic in it so it is not so easy to implement again.

The application is not written in .NET

Thanks again,

Any other ideas?

Greg

Lowell SSC Guru Points: 323547 More actions · Answer 3

well, I'm pretty sure you are running out of connections. it may have run without a problem previously, but breaking now because you are finally looping/creating more than 32767 connections.

since it's an app connection with ADO, you could try changing the timeout for connection pooling; see this article, which covers it fairly well:

http://www.15seconds.com/issue/970531.htm

you can check the limits for SQL, and the current # connected with this:

sp_configure 'user connections'

--results

name minimum maximum config_value run_value

user connections 0 32767 0 0

the only correct solution is to fix the code...i know you said you can't, but that is the way to fix it. you said you don't have the source code, but how do you know the new connection is inside the loop, then? you must have been able to view that piece of source code, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

obgc Grasshopper Points: 17 More actions · Answer 4

Lowell (11/30/2009)
you said you don't have the source code, but how do you know the new connection is inside the loop, then? you must have been able to view that piece of source code, right?

I turned on the SQL Profiler and noticed "Audit Login, Audit Logout" events on the list.

I really don't have the source code 😉

When I was refreshing the list of current processes on the server I could 1 or maximum 2 connections from my application. Is there a chance that internally my app used 32767 connections? I could only see one or two on the list.

Is there a way to check it by sql statement? I mean the current number of real connections to made by a specific user?

Thanks

Greg

Lowell SSC Guru Points: 323547 More actions · Answer 5

when you run the profiler, does the spid in the loop change, or is it incrementing?

if it was incrementing, that would imply to me that it is getting new connections.

if it's staying the same, maybe we should look at something else; i could be wrong;

the error "Server does not exist or access denied" could be something as simple as connection to the server by ip address insteead of by name, because the WINS/name resolution might take too long or fail...is the connection info for the server name configurable in an ini file or something, or is that trapped in the source as well?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

obgc Grasshopper Points: 17 More actions · Answer 6

Lowell (11/30/2009)
when you run the profiler, does the spid in the loop change, or is it incrementing?

For over 31000 records from profiler result there are only 13 different SPID values so I assume that's FINE.

Lowell (11/30/2009)
the error "Server does not exist or access denied" could be something as simple as connection to the server by ip address insteead of by name, because the WINS/name resolution might take too long or fail...is the connection info for the server name configurable in an ini file or something, or is that trapped in the source as well?

The connection is configurable 🙂 I was running my app locally on the server and I was calling the server by IP address.

So I think that - still - the problem is somewhere else.

Are there any logs which may contain information why the server rejected the connection? If it actually rejected it.

Any anti-DOS mechanisms?

Thanks

Greg

obgc Grasshopper Points: 17 More actions · Answer 7

Hi,

Today I received other message. Strange.

[DBNETLIB] [ConnectionOpen (PreLoginHandshake()).] Allgemeiner Netzwerkfehler. Weitere Informationen finden Sie in der Dokumentation ....

Which means something like "General network error".

Does that mean that my problems come from some network problems?

What is also strange that the process finishes (crashes) always in more or less the same place (this process generates a local file and it is always something around 700KB, sometimes 698, sometimes 702, ...) Looks as it comes to a point, number of cycles of the loop, ...? I just can;t tell what's wrong.

Any other ideas?

Thanks

Greg