Error Accessing SQL Server from an ASMX Web Service

Question

I am accessing my server through remote desktop connection and have configured a webservice in IIS. I am able to see the methods but when I click on the button to "Invoke" I get the following error:

System.Data.SqlClient.SqlException: Login failed for user 'SOLDev\Server02$'. at ShareWare.Web.Service.WebAPI.Reservation.GetInfo()

Why is it taking the machine name as the user? My windows authentication user is User1Dev. Also my directory security in IIS is setup as follows:

1. Option "Enable anonymous access" -- it's disabled
2. Option "Integrated Windows Authentication" -- checked off

I am using .NET framework 2.0

Answer 1

Your web service connects to the SQL using Windows authentication as the principal running the service. In this case it appears to be BUILTIN\System or BUILTIN\Network Service, both of which authenticate in the domain as the machine account, ie. 'SOLDEV\Server02$' which corresponds to a machine named Server02 in the domain SOLDEV.

If you wish to authenticate on the SQL Server with your own login, then the IIS must flow the authentication information, in a process called Constrained Delegation. See Configuring Constrained Delegation for Kerberos (IIS 6.0). or How To: Use Protocol Transition and Constrained Delegation in ASP.NET 2.0.

If you want the web service to authenticate to SQL Server as itself, then you must grant login permission to the web service principal on SQL: CREATE LOGIN [SOLDEV\Server02$] FROM WINDOWS.

Answer 2

It's because the web service is running as the Network Service id, not as the logged in user. You probably also need to have <identity impersonate="true" /> in your web config if you are planning to use the user's credentials to connect to SQL Server.