SQL / IIS Application Pool Identity

[Colin Bowern]

Hi,

I've got an ASP.NET web application which uses Windows security with a SQL
Server database. I want to use the application pool identity to make the
connection to the SQL server database. If I set the authentication tag to
None and impersonation to false I get the following:

===

Event Type: Error
Event Source: ExceptionManagerPublishedException
Event Category: None
Event ID: 0
Date: 10/15/2004
Time: 5:54:26 PM
User: N/A
Computer: IPDDFZ0025ATL2
Description:

General Information
*********************************************
Additional Info:
ExceptionManager.MachineName: (removed)
ExceptionManager.TimeStamp: 10/15/2004 5:54:26 PM
ExceptionManager.FullName: Microsoft.ApplicationBlocks.ExceptionManagement,
Version=1.0.1746.26470, Culture=neutral, PublicKeyToken=null
ExceptionManager.AppDomainName:
/LM/W3SVC/1518623831/Root-12-127423650871912556
ExceptionManager.ThreadIdentity:
ExceptionManager.WindowsIdentity: NT AUTHORITY\NETWORK SERVICE

1) Exception Information
*********************************************
Exception Type: System.Data.SqlClient.SqlException
Errors: System.Data.SqlClient.SqlErrorCollection
Class: 14
LineNumber: 0
Message: Login failed for user '(null)'. Reason: Not associated with a
trusted SQL Server connection.
Number: 18452
Procedure:
Server:
State: 1
Source: .Net SqlClient Data Provider
TargetSite: System.Data.SqlClient.SqlInternalConnection
GetConnection(Boolean ByRef)
HelpLink: NULL

StackTrace Information
*********************************************
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString
options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at InDIMENSIONS.Web.SmartForm.ContactForm.CreateContactRecord(String
inquiryType, String subject, String message, String name, String
emailAddress, String ipAddress, String fileName)

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.

===

Given that the app is running under the NETWORK SERVICE identity, would it
not use that to make the connection?

The application and SQL Server instance are on the same server, Windows
Server 2003.

The articles I've read on MSDN so far haven't been very clear about this.
Can someone provide an example or guidance on what I need to set to get this
scenario working?

Thanks!
Colin

 

[Paul Glavich [MVP - ASP.NET]]

Set the authentication tag in web.config to "windows" (this way it actually
used windows integrated authentication) and turn off (disable) anonymous
authentication in IIS management console for that virtual directory. This is
only for authentication though. simply changing to defined application pool
user (rigfht click on the app pool in IIS manager and select properties and
go to the Identity tab). You can select a user to use here, and that context
will be used to authenticate against SQL database. Note: Once you have
enabled windows intgrated auth above, disable impersonation in the
web.config, otherwise the user context/credentials will be used to connect
to sql, which will obviously be different for each user.

So,
- enable windows auth as I mentioned above. This is so the user
authentication is actually taking place.
- disable impersonation in the web.config
- change the user in the Identity tab of the properties of the Application
pool to use a user you would like to connect to the sql database. Ensure
that this user has correct access to your virtual directory for your web app
and any temporary and required system file areas.

 

[Colin Bowern]

Hi Paul,

So what I've set in the web.config is as follows:
---
<authentication mode="Windows" />
<identity impersonate="false" />
<authorization>
<allow users="*" />
</authorization>
---
If I set IIS virtual directory security to just Windows Integration
Authentication enabled I am no longer able to access the application as an
anonymous user.

If I enable Anonymous Authentication in addition to Windows Integrated
Authentication I am back to where I started - the inability to login to the
database using the application pool identity via SQL Server's Windows
authentication. The application pool is running under Network Service
identity which has been granted the rights to the SQL database.

Thanks,
Colin

 

[Paul Glavich [MVP - ASP.NET]]

Can you try changing the app pool identity to some specifically created
user. Call if 'testuser' for example. Give it rights to the virtual
directory to run the web app, use it as your app pool identity, and also
create that same user name with exactly the same password as a local user on
your sql database machine. Also, add that user as a login to your SQL
database and see how you go using the same web.config settings you have
defined below.