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

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( DataServiceConf开发者_运维知识库iguration 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, ...

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.

Try to change the connection string attribute Integrated security to False

Quick solution with IIS Express

1. Create a firewall exception to allow HTTP requests through the firewall on the port that IIS Express is using.

2. Get the IP address of the development computer, if necessary, by running ipconfig.

3. Find the IIS Express configuration file, applicationhost.config, in the folder %USERPROFILE%\Documents\IISExpress\config. The USERPROFILE environment variable typically has a value of C:\Users\.

4. Open applicationhost.config with Notepad or another text editor and make the following changes.

5. Find the site element for the web service, WebServiceForTesting. If you don’t see the site element for the web service, you have to deploy the service at least one time to create the element.

6. Within the bindings section of the site element, copy the binding element and paste a copy directly below the existing binding element to create a second binding. In the new binding element, replace localhost with the computer’s IP address. Save the changes.

7. Run Visual Studio as administrator and open the Visual Studio solution.

8. In the phone app project, remove the service reference to the service if you have previously added it. Add a new service reference to the reconfigured web service. In the Add Service Reference dialog box, in the Address box, replace localhost with the IP address of your development computer. Click Go.

9. The second binding for the service in the WCF project is discovered and displayed. Click OK.

10. A new service reference that uses the IP address of the development computer is added to the Windows Phone project.