WCF Service returns 400 Bad Request

Question

I've got this application that works locally and when deployed and using a .mdf SQL Express database file (which I usually use for testing purposes). However, when I change it to work with our SQL Server 2008 the app works but the service doesn't.

For example if in my code behind of a page I have a button that adds data to a table such as this it works fine:

public static string connString = @"Data Source=server1;Initial Catalog=Project;Integrated Security=True";

protected void btnAddProj_Click(object sender, EventArgs e)
{
    using (var sqlc = new SqlConnection(connString))
    {
        sqlc.Open();
        var cmd = sqlc.CreateCommand();
        int intProjectID;

        // Add the project info to the database
        cmd.CommandText = "INSERT INTO tblProject VALUES(@ProjName,@ProjTeam,@ProjStart,@ProjEnd)";
        cmd.Parameters.Add("ProjName", System.Data.SqlDbType.NVarChar).Value = txtProjName.Text;
        cmd.Parameters.Add("ProjTeam", System.Data.SqlDbType.Int).Value = ddlTeamSupported.SelectedValue;
        cmd.Parameters.Add("ProjStart", System.Data.SqlDbType.NVarChar).Value = txtStartDate.Text;
        cmd.Parameters.Add("ProjEnd", System.Data.SqlDbType.NVarChar).Value = txtEndDate.Text;
        cmd.ExecuteNonQuery();
    }       
}

My web.config is setup to use impersonation on that server and all works perfectly well. However, for my service the query doesn't seem to return anything and I get a 400 Bad Request error.

The code for the jquery is:

$.ajax({
    type: "POST",
    async: false,
    url: "Services/ProjectService.svc/test",
    contentType: "application/json; charset=utf-8",
    dataType: "json",
    success: function (data) {
        console.log(data);
        }
    });

And for the Service:

[ServiceContract]
public interface IProjectService
{
    [OperationContract]
    [WebInvoke(ResponseFormat = WebMessageFormat.Json)]
    ArrayList test();
}


    public static string connString = @"Data Source=server1;Initial Catalog=Project;Integrated Security=True";

    public ArrayList test()
    {
        var sqlc = new SqlConnection(connString);
        sqlc.Open();
        var cmd = sqlc.CreateCommand();
        cmd.CommandText = "SELECT ProjectID FROM tblProject";
        var reader = cmd.ExecuteReader();

        ArrayList temparray = new ArrayList();


        while (reader.Read())
        {
            temparray.Add(reader[0]);
        }
        sqlc.Close();
        return temparray;
    }

If instead of querying the database I have the service just return static data then it works fine. What could cause my service not to be able to connect to the database when the rest of the code behind for the app works?

Answer 1

A database connection from a hosted WCF Service is considered a remote connection so make sure your connection strings specifies the authentication method. So try using Integrated Security=SSPI in your connection string and if that doesn't work make sure that your Application Pool's Identity is set to a domain account that has permissions on the SQL server. :)

Answer 2

You're probably not setting HTTP headers exactly as the service is expecting them, or in the order it's expecting them.

Here's how I'd debug it:

1. Create a quick test client using SvcUtil.exe.
2. Install Fiddler, turn it on.
3. Use the thin client to make a call to the service operation.
4. Use your web application to make a call to the same service operation
5. Look at the full HTTP request in Fiddler for each request. Compare them. Figure out where your AJAX call is different, and resolve the differences so it matches what the generated client uses.

Answer 3

This could be that you are making a POST but the service is expecting a GET.

Try specifying that the method should be a POST:

[WebInvoke(Method = "POST", UriTemplate = "", ResponseFormat = WebMessageFormat.Json)]

The other this that it could be is that you are using a trusted connection. This means that the security context is the identity of the application pool (if defaults are used). Then you will be making a connection to the database using a local account that does not have access on a database on a different machine.

The strange thing is that based on the error message it should be the first explaination, but based on your description it would be the second.