"Executereader requires an open and available connection" error

[karim]

I have an asp.net page that stopped running properly giving the error
below. The app uses a SQL Server 2000 on another server. Enterprise Manager
and Query analyzer on the web server can connect to that sql server just
fine and run queries. I rebooted the web server and made aspnet member of
the admin group. I don't know why the error mentions open connections if I
can open several query anaylzer windows using the same sql server
credentials as the asp.net is using.
I suspect it's an issue with ado.net or .net in general.

Any ideas?

error message:
"Executereader requires an open and available connection

Karim

 

[Chris Jackson]

Whoa - don't go adding ASPNET to the admins group - that's a huge security
vulnerability!

First of all, you'll want to check your authentication. You can't use a
local account to use windows authentication to another machine - either use
a domain account or else use SQL Authentication (a domain account being the
more secure option, but if you have to use SQL authentication you can at
least encrypt your key using aspnet_setreg). Once you have that going, then
make sure you wrap the opening of your database connection in a try catch
block, and examine the error message that you receive for more information:

try {
conn.Open();
} catch (SqlException ex) {
#if (DEBUG)
Response.Write(ex.Message);
#else
// do something to handle the error in release mode that doesn't give
potential hackers too much info
#endif
} finally {
// do any cleanup
}

 

[S. Justin Gengo]

It's saying that the datareader was closed in your code.

To use a datareader you:

1) declare a connection to the sql server
2) open that connection
3) execute the datareader
4) close the datareader

If your looping your code and trying to read a second time after the
connection is closed you would see this error.

Another possibility is if you put the connection object into an application
level variable, then while one user trys to open a connection another might
be closing it at the same time. In both cases you would see this error.

Or maybe you just forgot to open your connection.

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche

 

[karim]

Whoa - don't go adding ASPNET to the admins group - that's a huge security
vulnerability!

I understand the implications. I am trying to troubleshoot. Plus the server
is an internal server.

First of all, you'll want to check your authentication. You can't use a
local account to use windows authentication to another machine - either use
a domain account or else use SQL Authentication (a domain account being the
more secure option, but if you have to use SQL authentication you can at
least encrypt your key using aspnet_setreg). Once you have that going, then
make sure you wrap the opening of your database connection in a try catch
block, and examine the error message that you receive for more information:

try {
conn.Open();
} catch (SqlException ex) {
#if (DEBUG)
Response.Write(ex.Message);
#else
// do something to handle the error in release mode that doesn't give
potential hackers too much info
#endif
} finally {
// do any cleanup
}

I am using sql authentication and the app used to work. The sql
authentication works as verified by using query analyzer.

karim

 

[karim]

It's saying that the datareader was closed in your code.

To use a datareader you:

1) declare a connection to the sql server
2) open that connection
3) execute the datareader
4) close the datareader

If your looping your code and trying to read a second time after the
connection is closed you would see this error.

Another possibility is if you put the connection object into an application
level variable, then while one user trys to open a connection another might
be closing it at the same time. In both cases you would see this error.

Or maybe you just forgot to open your connection.

Sincerely,

It's not the code. It used to work fine plus it works fine on my
development box connecting to the same sql server using the same sql
credentials. I recompiled and replaced the dll on the production box. Same
problem.

It has to be something in Windows or .net, a setting, permission or
something.

karim

 

[Chris Jackson]

I am using sql authentication and the app used to work. The sql

authentication works as verified by using query analyzer.

The only thing I can think of is to step through your code. Make sure that
all of our code is being called as you expect. After you open the
connection, check it's state before stepping through the next line, etc.

--
Chris Jackson
Software Engineer
Microsoft MVP - Windows XP
Windows XP Associate Expert
--

 

[karim]

I have an asp.net page that stopped running properly giving the error
below. The app uses a SQL Server 2000 on another server. Enterprise Manager
and Query analyzer on the web server can connect to that sql server just
fine and run queries. I rebooted the web server and made aspnet member of
the admin group. I don't know why the error mentions open connections if I
can open several query anaylzer windows using the same sql server
credentials as the asp.net is using.
I suspect it's an issue with ado.net or .net in general.

Any ideas?

error message:
"Executereader requires an open and available connection

I solved the problem but the I still need to know why my steps solved it.

The firewall log on the web server indicated SMB authentication errors to
the database server. The app used to work fine and nothing has changed
(afaik) and the firewall log hinted on permission or access problems. So I
changed the smpt, iis and iis admin services to run under the domain
administrator. The app worked! But I didn't want to keep this so I changed
them back to LocalSystem, restarted the services and it still worked. I
don't understand why all this made it happen. I appreicate any feedback.

karim