System.Data.SqlClient.SqlCommand.ExecuteReader() error when using IIS

Question

i'm trying to use my asmx soap service on IIS. When i use it with Visual Studio it works fine, but when i make a post using it on IIS i get this as answer:

<faultcode>soap:Server</faultcode>
    <faultstring>System.Web.Services.Protocols.SoapException: Server was unable to process request. ---&gt; System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is closed.
       at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task&amp; task, Boolean&amp; usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader()
       at PLATAFORMAS.Servicio1.Login(Autenticacion value, String ServiceName) in C:\Users\jofre\Downloads\ServicioWeb - Roldan - Cajias\PLATAFORMAS\Servicio1.asmx.cs:line 67
       at PLATAFORMAS.Servicio1.CiudadBuscar(String ciudad) in C:\Users\jofre\Downloads\ServicioWeb - Roldan - Cajias\PLATAFORMAS\Servicio1.asmx.cs:line 260
       --- End of inner exception stack trace ---</faultstring>

To use it i have to send a header with UserName and Pass and the query for the service. In visual studio works fine, this fail on IIS. I've check lots of solutions on the web but none works.

This is the login method :

public static Boolean Login(Autenticacion value,string ServiceName)
{
    if (value == null)
    {
        return false;
    }

    ConexionBD conn = new ConexionBD();
    conn.abrir();

    string verificacion = string.Format("Select ID from Usuario where Usuario ='{0}' and Password = '{1}'", value.UserName, value.Password);
    SqlCommand commando = new SqlCommand(verificacion, conn.conectarbd);
    string cadena = "";
    SqlDataReader read = commando.ExecuteReader();
    Auditoria(ServiceName, value.UserName, GetLocalIPAddress());

    while (read.Read())
    {
        cadena = read["ID"].ToString();
    }

    if (cadena != "")
    {
        return true;
    }
    else
    {
    return false;
    }
}

This is my CONEXIONBD CLASS:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace PLATAFORMAS
{
    public class ConexionBD
    {
        string cadena = "Data Source =JOFRERLAP\\SQLEXPRESS;Database=Api;Trusted_Connection=True;MultipleActiveResultSets=True";
        public SqlConnection conectarbd = new SqlConnection();

        public ConexionBD()
        {
            conectarbd.ConnectionString = cadena;
        }

        public void abrir()
        {
            try
            {
                conectarbd.Open();
                Console.WriteLine("CONEXION CORRECTA");
            }
            catch (Exception ex)
            {
                Console.WriteLine("SE MURIO :( " + ex.Message);

            }
        }
        public void cerrar()
        {
            conectarbd.Close();
        }
    }

}

My database is on SQL SERVER.

Hope someone can help!

Answer 1

Issue is because you are opening the connection but not closing it everytime. call the cerrar() method once you are done with the query. I would suggest you do call the cerrar() method in finally, so it will be called always. Its not giving any issue while running from visual studio because you have very less connections open.

Refer this answer, it will be really helpful