Weird Windows integrated authentication problem between Web service and SQL Server

Topics: Data Access Application Block, General discussion
Feb 12, 2008 at 5:35 PM
We have a weird Windows Integrated authentication issue between a Web service using Data Application Block and a SQL Server. Here is our setup :

- Windows 2000 Domain controller
- 1 Web application server - Windows 2003 + ASP.NET 2.0
- 1 Web ASMX web service built with Service factory and using Data application Block v2.0.1.0 - Windows 2003 + ASP.NET 2.0
- 1 SQL Server 2005 using Windows Integrated authentication only - Windows 2003

Settings for successful Kerberos authentication have been applied on all tiers and it's working (SPNs, Trusted for delegation flag on machine account, etc.)

- The Web service is impersonating users with a username and password in Web.config file :
<identity impersonate="true" userName="DOMAIN\USER" password="PASSWORD" />

- The Web service is using stored procedures to read and update data

Problem :

When we access the Web app, the request is passed to the web service which uses a SQL Stored procedure to read data (using SELECT commands). Those requests are successful. But when we use a stored procedure which use 'UPDATE' commands, we receive the following error :

Login failed for user '.'. The user is not associated with a trusted SQL Server connection

We tried the same setup with a simple web app and Web service which communicate directly to SQL Server... and it's working great.

Anyone have ideas ?

Thanks a lot for you help

Mario