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

Question

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 messges 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 integreted 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 atricle link for wcf deployment in iis and authenticating wcf communication would be really appreciated. thanks in advance.

Solution

Integrated security means that the connection occurs under the credentials of the thread doing the Open operation. Normally the thread has the process credentials, in the case of IIS and WCF meaning the AppPool configured credentials to run as. If the thread is impersonating (as is often the case with WCF) then the thread has the credentials of the caller and constrained delegation occurs in order to authenticate with a remote DB server. Whatever credentials are used, they have to be trusted and allowed to connect by the DB server.

So the solution to your problems is dependent on what you're doing, and you provided a lot of code but not the actual relevant information.

• do you impersonate?
• if your WCF service does not impersonate the caller then app pool configured to run the WCF service under IIS must be granted the required permission to connect on the DB.
  • if your WCF app pool uses a domain account, grant permission on the DB to the domain account
  • if your WCF app pool uses a local account and the DB is hosted on the same host as IIS then the local account need to be granted permission to connect
  • if your WCF app pool uses a local account and the DB is remote from IIS host then you cannot connect (mirrored accounts are not a a supported option)
  • if your WCF app pool uses LocalSystem or NETWORK SERVICE and the DB is remote from IIS then the machine account of the IIS host has to be granted permission
  • if your WCF app pool uses LocalSystem or NETWORK SERVICE and the DB is local then the localsystem account needs to be granted permission
  • if your WCF app pool uses LOCAL SERVICE and DB is rmeote from IIS you cannot connect
• if WCF impersonates and DB is local then you need to grant connect to the caller
• if WCF impersonates and DB is rmeote then you need to grant connect to caller and configure constrained delegation.

All these are described in great detail in the product documentation and you shouldn't have any problem following the MSDN: - WCF Security Fundamentals - Delegation and Impersonation with WCF - WCF Security Guidelines

Comments

[yasar]

Here's the same question with the Solution on Stack Overflow: stackoverflow.com/q/3754721/391389

[yasar]

public static ICustomer GetCustomerByUsername(string username) { try { //ConnectionString= "Server=MyPC\SQLEXPRESS;Database=MySampleDb Integrated Security=true"; OpenConnection(); I have this OpenConnection() which i am calling before cmd.ExecuteReader(...) As I said it is working fine with debug mode, but when I copy the application in wwwroot folder and try to call some function it don't authenticate my call, and what i see this exception in server side.