sql server - SSPI Connection in .Net 2.0 Web Service

Question

I'm trying to test a .Net 2.0 Web Service I created in C#. One of our servers is on windows 2000 and can currently only support .Net 2.0 . I can test it local to my machine and it works fine but when I move it to the target server for production it produces an error.

I want it to use Windows authentication, and have it defined as such in the web.config

The error I receive is as follows:

System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection.

This occurs when I try to test this web service on the target server (running locally on that server).

How can I associate an active directory username with the server/location the web service is running (I think that's what I need to do?)

This server can currently connect to MSSQL 2005 via classic ASP and ColdFusion.

Answer 1

If you are hosting in IIS, then you need to set the identity of the app-pool that the web app is running in. Note that the user you select needs access to logon as a service, and a few other things. You are likely to get "500 Server Unavailable" if the user is even slightly malconfigured, so tread carefully. Also note that you need to fully recycle the app-pool to try again!

(edit) - found my notepad ;-p

• Add the account to the "Log on as a service" policy-group (Local Security Policy (Local Security Settings) -> User Rights Assignment)
• Add the account to the "IIS_WPG" user-group (Computer Management -> Local Users and Groups)
• Grant the account "Read & Execute", "List Folder Contents" and "Read" permissions to the file structure (Explorer -> Properties)
• Either create a new application pool, or update the user account of an existing pool (Internet Information Services -> Application Pools)
• Set the application pool for the site / application (Internet Information Services -> Web Sites)