Windows Integrated Authentication Conflict With MS-SQL 2000 DB Connection With Integrated Security

Question

Hi,

We are developing an intranet web application on .NET 2.0 platform. The application is using Integrated Windows Authentication for Single Sign On. The users are authorized to use diffent modules according to the Active Directory Groups they are in.

Up to the point where authentication and authorization is accomplished everything works fine. But the problem starts when application tries to connect to the database on MSSQL Server.

According to the security policies of our customer, no database user or password data should be kept in connection strings or in registry even if encrypted. So we are forced to use Integrated Security=SSPI in the connection string.

The Application Pool on IIS is configured by them with the Identity User who has access to the database.

When we deactivate Windows Integrated Authentication, we connect to the db with the Application Pool's Identity User. But when Integrated Authentication is on, the application is trying to connect to the database with logon user credentials.

We tried every combination of Integrated Authentication, with or without impersonation, to solve the problem.

Is there any way to solve this conflict?

Answer 1

Normally the way you are doing it should work. Do you have Kerberos with delegation enabled?

Maybe this helps:

• How to: Access SQL Server Using Windows Integrated Security

• http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

Edit: if both SQL and IIS are on the same machine you need to turn off impersonate:

• <authentication mode="Windows" />
• <identity impersonate="false" />