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.Get PooledConnection(SqlConnectionString
options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at InDIMENSIONS.Web.SmartForm.ContactForm.CreateConta ctRecord(String
inquiryType, String subject, String message, String name, String
emailAddress, String ipAddress, String fileName)

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

===

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

[Dominick Baier]

is the sql server on the same machine or on a different machine?

if on the same : grant NT Authority\Network Service access to the db

if on another machine : either use a domain account for the worker process or grant MACHINENAME$ access to sql



---
Dominick Baier - DevelopMentor
[url]http://www.leastprivilege.com[/url]

nntp://news.microsoft.com/microsoft.public.dotnet.framework.aspnet.security/<#MGkUMwsEHA.2556@tk2msftngp13.phx.gbl>

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.Get PooledConnection(SqlConnectionString
options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at InDIMENSIONS.Web.SmartForm.ContactForm.CreateConta ctRecord(String
inquiryType, String subject, String message, String name, String
emailAddress, String ipAddress, String fileName)

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

===

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



[microsoft.public.dotnet.framework.aspnet.security]

[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.


--
- Paul Glavich
Microsoft MVP - ASP.NET


"Colin Bowern" <colinbowern@nospam.indimensions.com> wrote in message
news:%23MGkUMwsEHA.2556@tk2msftngp13.phx.gbl...

> 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.Get PooledConnection(SqlConnec
tionString

> options, Boolean& isInTransaction)
> at System.Data.SqlClient.SqlConnection.Open()
> at InDIMENSIONS.Web.SmartForm.ContactForm.CreateConta ctRecord(String
> inquiryType, String subject, String message, String name, String
> emailAddress, String ipAddress, String fileName)
>
> For more information, see Help and Support Center at
> [url]http://go.microsoft.com/fwlink/events.asp[/url].
>
> ===
>
> 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
>
>

[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]" <glav@aspalliance.com-NOSPAM> wrote in
message news:eBytxFEtEHA.2956@TK2MSFTNGP12.phx.gbl...

> 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.
>
>
> --
> - Paul Glavich
> Microsoft MVP - ASP.NET
>
>
> "Colin Bowern" <colinbowern@nospam.indimensions.com> wrote in message
> news:%23MGkUMwsEHA.2556@tk2msftngp13.phx.gbl...

>> 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.Get PooledConnection(SqlConnec
> tionString

>> options, Boolean& isInTransaction)
>> at System.Data.SqlClient.SqlConnection.Open()
>> at InDIMENSIONS.Web.SmartForm.ContactForm.CreateConta ctRecord(String
>> inquiryType, String subject, String message, String name, String
>> emailAddress, String ipAddress, String fileName)
>>
>> For more information, see Help and Support Center at
>> [url]http://go.microsoft.com/fwlink/events.asp[/url].
>>
>> ===
>>
>> 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]]

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.

--
- Paul Glavich
Microsoft MVP - ASP.NET


"Colin Bowern" <colinbowern@nospam.indimensions.com> wrote in message
news:eikgjDUtEHA.3188@TK2MSFTNGP15.phx.gbl...

> 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]" <glav@aspalliance.com-NOSPAM> wrote in
> message news:eBytxFEtEHA.2956@TK2MSFTNGP12.phx.gbl...

> > 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.
> >
> >
> > --
> > - Paul Glavich
> > Microsoft MVP - ASP.NET
> >
> >
> > "Colin Bowern" <colinbowern@nospam.indimensions.com> wrote in message
> > news:%23MGkUMwsEHA.2556@tk2msftngp13.phx.gbl...

> >> 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.Get PooledConnection(SqlConnec

> > tionString

> >> options, Boolean& isInTransaction)
> >> at System.Data.SqlClient.SqlConnection.Open()
> >> at InDIMENSIONS.Web.SmartForm.ContactForm.CreateConta ctRecord(String
> >> inquiryType, String subject, String message, String name, String
> >> emailAddress, String ipAddress, String fileName)
> >>
> >> For more information, see Help and Support Center at
> >> [url]http://go.microsoft.com/fwlink/events.asp[/url].
> >>
> >> ===
> >>
> >> 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
> >>
> >>

> >
> >

>
>