How do I properly host a WCF Data Service that connects to SQLServer in IIS? Why am I getting errors?

Question

I'm playing around with WCF Data Services (ADO.NET Data Services). I have an entity framework model pointed at the AdventureWorks database.

When I debug my svc file from within Visual Studio, it works great. I can say /awservice.svc/Customers and get back the ATOM feed I expect.

If I publish the service (hosted in an ASP.NET web application) to IIS7, the same query string returns a 500 fault. The root svc page itself works as expected and successfully returns ATOM. The /Customers path fails.

Here is what my grants look like in the svc file:

public class AWService : DataService<AWEntities>
{
    public static void InitializeService( DataServiceConfiguration config )
    {
        config.SetEntitySetAccessRule( "*", EntitySetRights.All );
        config.SetServiceOperationAccessRule( "*", ServiceOperationRights.All );
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
    }
}

Update: I enabled verbose errors and get the following in the XML message:

<innererror>
<message>The underlying provider failed on Open.</message>
<type>System.Data.EntityException</type>
<stacktrace>
   at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(
...
...
<internalexception>
<message>
Login failed for user 'IIS APPPOOL\DefaultAppPool'.
</message>
<type>System.Data.SqlClient.SqlException</type>
<stacktrace>
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, ...

Answer 1

It looks to me like this is a SQL authentication error, IIS is running its appPool under a user that does not have access to your SQL server, when you ruin in Visual Studio (locally) it will be a different user. Check the user that the IIS on the server is using and make sure it has rights to do what you want in SQL.