Deploying WCF application in IIS, but SQL Server Database Connection is not working.

Question

Hello,

I am new with WCF, I am trying to deploy my WCF sample application on IIS, this application works fine in debug mode with VS2008, this application authenticate the WCF messages with following code. I am doing it like this, I have added the resulted .dlls web.config and the Service.svc in the wwwroot directory, and I have also added connection string in IIS Manager, which is

Server=MyPC\SQLEXPRESS;Database=MySampleDb;Integrated Security=true

I am using Windows Integrated Security. I am using same connection string for connection in the database class but I get following exception,

Please guide me to deploy this application

In Validater

public override void Validate(string userName, string password)  
{  
       ValidateUser(userName, password);   
}  

public static bool ValidateUser(string userName, string password)  
{  
    if (!string.IsNullOrEmpty(userName))  
    {         
       ICustomer customer = GetCustomerByUsername(userName);  
       if (customer ==null)  
       {  
           throw new exception("User Not found.");  
       }  
       else  
       {  
           return true;  
       }  
    }   
    else   
    {  
        throw new FaultException("User name is required!");  
    }  
}  

public static ICustomer GetCustomerByUsername(string username)  
{  
   try  
   {  
       //ConnectionString= "Server=MyPC\SQLEXPRESS;Database=MySampleDb;Integrated Security=true";  
       OpenConnection();  

       var cmd = new SqlCommand("GetUserByUsername", _connection) { CommandType = CommandType.StoredProcedure };  

       cmd.Parameters.Add("Username", username);  

       connState = _connection.State.ToString();  

       if (_connection.State == ConnectionState.Closed)  
       {
           OpenConnection();  
       }    

       SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);  

       ICustomer customer = null;  

       customer = ExtractCustomerFromDataReader(dr)[0];  
       dr.Close();  
       return customer;  
    }  
    catch (Exception e)  
    {  
       throw new Exception(e.Message + Environment.NewLine + e.StackTrace);  
    }  
    finally  
    {  
       CloseConnection();  
    }  
}  

Exception:

ExecuteReader requires an open and available Connection. The connection's current state is closed. at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method) at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) 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(CommandBehavior behavior) at Aschrafi.MobileZollServer.Services.DatabaseHelper.GetCustomerByUsername(String username) in

I think I am missing some point in database settings or in IIS Manager the website settings. Some tutorial or article link for WCF deployment in IIS and authenticating WCF communication would be really appreciated.

Thanks in advance.

Answer 1

The error message quite clearly states that your connection is not open at the time you're trying to execute your data reader.

Basically, I'd recommend completely rewriting your GetCustomerByUsername method - use the using(...) { ... } blocks as best practice around your SqlConnection and SqlCommand - something like this:

public static ICustomer GetCustomerByUsername(string username)  
{  
   ICustomer customer = null;

   try  
   {  
       using(SqlConnection _con = new SqlConnection(connectionString))
       using(SqlCommand _cmd = new SqlCommand("GetUserByUsername", _con))
       { 
           _cmd.CommandType = CommandType.StoredProcedure;  
           _cmd.Parameters.Add("Username", username);  

           _con.Open();

           using(SqlDataReader dr = cmd.ExecuteReader())
           {
              customer = ExtractCustomerFromDataReader(dr)[0];  
              dr.Close();  
           }

           _con.Close();
        }

        return customer;  
    }  
    catch (Exception e)  
    {  
       throw new Exception(e.Message + Environment.NewLine + e.StackTrace);  
    }  
}  

By employing the using() blocks, you make sure the class instance in question will be released at the end of the { ... } block - no more need for keeping track of state and no more need for a finally clause.

Also: integrated security works fine from your desktop - but it won't work well in a hosted environment. After all: it's the IIS process that is now connection to your database - I would rather use a specific user with a password here.

Update: you can name your user anything you like - e.g. use the application's name as your user name or whatever makes sense for you. You need to create that using e.g. SQL Server Express Management Studio, and you need to create a login first using CREATE LOGIN or the GUI equivalent (under "Security" - "Logins" - right-click and pick "New Login") - this step gives that "name" the right to log into the SQL Server in the first place. This is the point where you pick a password, too.

Once you have that login created, then you need to create a user based on that login in your database (USE <database name> GO - CREATE USER ...... - or use the GUI - Your Database -> Security -> Users -> right-click and choose "New User").

Once you have all of this, your connection string will look something like:

Server=MyPC\SQLEXPRESS;Database=MySampleDb;User Id=(your user name);Pwd=(your password)