"Executereader requires an open and available connection" error

Discussion in 'ASP .Net' started by karim, Aug 13, 2003.

  1. karim

    karim Guest

    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
     
    karim, Aug 13, 2003
    #1
    1. Advertising

  2. 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
    }


    --
    Chris Jackson
    Software Engineer
    Microsoft MVP - Windows XP
    Windows XP Associate Expert
    --
    "karim" <> wrote in message
    news:1wx1ntk57iwqw.2o5i582wwgpr$...
    >
    > 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, Aug 13, 2003
    #2
    1. Advertising

  3. 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" <> wrote in message
    news:1wx1ntk57iwqw.2o5i582wwgpr$...
    >
    > 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
     
    S. Justin Gengo, Aug 13, 2003
    #3
  4. karim

    karim Guest

    On Wed, 13 Aug 2003 12:42:50 -0400, Chris Jackson wrote:

    > 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, Aug 13, 2003
    #4
  5. karim

    karim Guest

    On Wed, 13 Aug 2003 11:47:55 -0500, S. Justin Gengo wrote:

    > 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
     
    karim, Aug 13, 2003
    #5
  6. > 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
    --
     
    Chris Jackson, Aug 14, 2003
    #6
  7. karim

    karim Guest

    On Wed, 13 Aug 2003 09:26:15 -0700, karim wrote:

    >
    > 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
     
    karim, Aug 14, 2003
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. hfk0
    Replies:
    2
    Views:
    21,678
  2. JavaQueries
    Replies:
    1
    Views:
    3,675
    John C. Bollinger
    Mar 1, 2005
  3. Balaji
    Replies:
    3
    Views:
    10,115
  4. =?Utf-8?B?TWlrZQ==?=
    Replies:
    6
    Views:
    5,275
    =?Utf-8?B?TWlrZQ==?=
    Sep 12, 2006
  5. Bishop
    Replies:
    1
    Views:
    792
    Bishop
    Feb 24, 2007
Loading...

Share This Page