Rectangle 27 2

If the SQL Server is on a different box than IIS then the identity of the IIS application pool has to be trusted for constrained delegation. See How To: Use Protocol Transition and Constrained Delegation in ASP.NET 2.0. If the IIS app is running as NETWORK SERVICE or SYSTEM then the IIS machine account has to be trusted for constrained delegation. The linked resource has all the steps to set this up.

If the SQL Server is on the same machine as IIS then there isn't any requirement afaik.

asp.net - Application pool identity in IIS and Integrated security to ...

asp.net iis windows-server-2003 application-pool
Rectangle 27 3

That articles states (under "Accessing the Network") you still use the <domainname>\<machinename>$ aka machine account in the domain.

So if both servers are in "foobar" domain, and the web box is "bicycle", the login used to the SQL Server Instance is foobar\bicycle$

If you aren't in a domain, then there is no common directory to authenticate against. Use a SQL login with username and password for simplicity

If the domains are trusted then you can use the machien account still (use domain local groups for SQL Server, into which add a global groups etc)

As for using app pool identities, they are local to your web server only as per article. They have no meaning to SQL Server. If you need to differentiate sites, then use proper domain accounts for the App Pools.

You can't have it both ways...

Hi sorry if I was not clear. IIS and DB servers are in different networks/domains (IIS server is in DMZ, while DB server is "inside"). Additionally, I like the app pool identity approach because it allows specifying which identity has access to which database...If I use machine account then every app would I would lose that granularity right?

Sorry, if I understood the articles correctly, the app pool identities are virtual accounts that are "injected" at the time the worker starts for a particular pool (app in my case). If I stop IIS, I am still able to add APP POOL\MyAppAppPool to SQL Server login on my local DB instance (same machine) but not on remote DB server (different machine)...From SQL Server standpoint login does not exist in eiter case but it allows me to create login on local machine but not on remote...

Of course you add it locally because it's the same machine. It has no meaning remotely. So you can't do it. As per article

Ok, let me ask this in another way...Our prod IIS server does not belong to any domain (it is in DMS on an isolated workgroup/different network). Our DB server is on "inside" and belongs to internal company domain. With this setup (without change) is it possible to use windows auth on SQL server to authenticate IIS app pool?

Configuring ASP.NET MVC app's IIS 7.5 Application Pool identity as log...

sql-server asp.net-mvc asp.net-mvc-2 windows-authentication iis-7.5
Rectangle 27 1

I imagine the problem is occuring because the IIS account is a local account which is not visible to the SQL Server box.

That's exactly the problem. The IIS AppPool account only exists on the web server. If you were able to add this account to SQL Server, you would be authorizing an IIS AppPool that is running on the same machine as SQL Server. (I suspect it might still fail.)

The most secure solution would probably be to do as you say - create an account on the domain, give that account appropriate permissions on the database, and run the AppPool using that account's credentials.

However, if you'd still like to do it, you need to authorize the computer that the AppPool is running on - ie, DOMAINNAME\ComputerName$ (note the $ at the end).

Take a look at this article for more information (specifically, the section titled Accessing the Network).

I think that's a bad idea, however, because it authorizes any program running as NetworkService to access the database - not just your web applications.

This is not strictly true. You should be able to setup account to use constrained delegation from the app pool identity via Network Service. The SID is the same on two different machines with the same AppPoolIdentity because the SID is a hash of the name (though I think you need a common machine key to ensure the same hash is generated).

@ErikFunkenbusch - How can you do this? Any details would be greatly appreciated! Thanks.

Configuring Integrated Security using IIS Application Pool Identity ac...

iis sql-server-2008-r2 integrated-security
Rectangle 27 0

We ended up making the user an administrator and that worked. That's probably too broad for sufficient security rights. We'll keep looking. But it does show that the issue was somehow related to user roles rather than a password issue.

asp.net - IIS application pool identity not allowing the server to sta...

asp.net iis iis-7
Rectangle 27 0

If the SQL Server is on a different box than IIS then the identity of the IIS application pool has to be trusted for constrained delegation. See How To: Use Protocol Transition and Constrained Delegation in ASP.NET 2.0. If the IIS app is running as NETWORK SERVICE or SYSTEM then the IIS machine account has to be trusted for constrained delegation. The linked resource has all the steps to set this up.

If the SQL Server is on the same machine as IIS then there isn't any requirement afaik.

asp.net - Application pool identity in IIS and Integrated security to ...

asp.net iis windows-server-2003 application-pool
Rectangle 27 0

We ended up making the user an administrator and that worked. That's probably too broad for sufficient security rights. We'll keep looking. But it does show that the issue was somehow related to user roles rather than a password issue.

asp.net - IIS application pool identity not allowing the server to sta...

asp.net iis iis-7
Rectangle 27 0

We have several application running on our intranet that use windows authentication. The way we handle this in our web.config is to specify our SQL connection string as follows:

<connectionStrings>
    <add name="ConnectionStringName" connectionString="Data Source=ServerName;Initial Catalog=DatabaseName;Trusted_Connection=true" providerName="System.Data.SqlClient"/>
</connectionStrings>

Also in the web.config is the following:

<system.web>
    <authentication mode="Windows"/>
    <identity impersonate="true" username="Domain\Username" password="password"/>
</system.web>

Using a domain account allows you to manage the account in the same way you manage other users accounts. Down side here is that the username and password are included in plain text in the web config.

This is not a good approach in my opinion. You should set it up to use a pass through so your are NEVER storing a windows domain account password anywhere in your code / configuration files.

If the App Pool uses a Windows Domain account, that will be the account passed through to the SQL Server using the connection string rhoadsce used right? So is there a need to impersonate a user?

sql server 2008 - IIS App Pool Identity vs. Windows Account - Stack Ov...

windows sql-server-2008 iis-7 windows-authentication application-pool
Rectangle 27 0

Well, it turns out that the problem was I was missing a server user for the app pool. The SQL script I had posted had only created a database user for the app pool identity, but there was no server login for the same identity. Once I added that user (and I had to type IIS APPPOOL\ASP.NET v4.0 manually and not try to select it as a object in the windows login selection) it all worked fine.

Thanks for your help Grzegorz, even though it was something totally trivial in the end!

wcf - Connection between IIS App Pool Identity and SQL Server - Stack ...

sql-server wcf iis-7
Rectangle 27 0

Does abc123_svc_OSAT have access to directory that hosts your ASPX files? The log files you are looking at are only for logging access to your website. You will want to check the event viewer to see the actual IIS error.

Looks like that user does have access to the ASP files. I found this error in the event viewer (thank you for pointing me there): "Application pool ASP has been disabled. Windows Process Activation Service (WAS) encountered a failure when it started a worker process to serve the application pool."

asp.net - IIS application pool identity not allowing the server to sta...

asp.net iis iis-7
Rectangle 27 0

That articles states (under "Accessing the Network") you still use the <domainname>\<machinename>$ aka machine account in the domain.

So if both servers are in "foobar" domain, and the web box is "bicycle", the login used to the SQL Server Instance is foobar\bicycle$

If you aren't in a domain, then there is no common directory to authenticate against. Use a SQL login with username and password for simplicity

If the domains are trusted then you can use the machien account still (use domain local groups for SQL Server, into which add a global groups etc)

As for using app pool identities, they are local to your web server only as per article. They have no meaning to SQL Server. If you need to differentiate sites, then use proper domain accounts for the App Pools.

You can't have it both ways...

Hi sorry if I was not clear. IIS and DB servers are in different networks/domains (IIS server is in DMZ, while DB server is "inside"). Additionally, I like the app pool identity approach because it allows specifying which identity has access to which database...If I use machine account then every app would I would lose that granularity right?

Sorry, if I understood the articles correctly, the app pool identities are virtual accounts that are "injected" at the time the worker starts for a particular pool (app in my case). If I stop IIS, I am still able to add APP POOL\MyAppAppPool to SQL Server login on my local DB instance (same machine) but not on remote DB server (different machine)...From SQL Server standpoint login does not exist in eiter case but it allows me to create login on local machine but not on remote...

Of course you add it locally because it's the same machine. It has no meaning remotely. So you can't do it. As per article

Ok, let me ask this in another way...Our prod IIS server does not belong to any domain (it is in DMS on an isolated workgroup/different network). Our DB server is on "inside" and belongs to internal company domain. With this setup (without change) is it possible to use windows auth on SQL server to authenticate IIS app pool?

Configuring ASP.NET MVC app's IIS 7.5 Application Pool identity as log...

sql-server asp.net-mvc asp.net-mvc-2 windows-authentication iis-7.5
Rectangle 27 0

Well, it turns out that the problem was I was missing a server user for the app pool. The SQL script I had posted had only created a database user for the app pool identity, but there was no server login for the same identity. Once I added that user (and I had to type IIS APPPOOL\ASP.NET v4.0 manually and not try to select it as a object in the windows login selection) it all worked fine.

Thanks for your help Grzegorz, even though it was something totally trivial in the end!

wcf - Connection between IIS App Pool Identity and SQL Server - Stack ...

sql-server wcf iis-7
Rectangle 27 0

I imagine the problem is occuring because the IIS account is a local account which is not visible to the SQL Server box.

That's exactly the problem. The IIS AppPool account only exists on the web server. If you were able to add this account to SQL Server, you would be authorizing an IIS AppPool that is running on the same machine as SQL Server. (I suspect it might still fail.)

The most secure solution would probably be to do as you say - create an account on the domain, give that account appropriate permissions on the database, and run the AppPool using that account's credentials.

However, if you'd still like to do it, you need to authorize the computer that the AppPool is running on - ie, DOMAINNAME\ComputerName$ (note the $ at the end).

Take a look at this article for more information (specifically, the section titled Accessing the Network).

I think that's a bad idea, however, because it authorizes any program running as NetworkService to access the database - not just your web applications.

This is not strictly true. You should be able to setup account to use constrained delegation from the app pool identity via Network Service. The SID is the same on two different machines with the same AppPoolIdentity because the SID is a hash of the name (though I think you need a common machine key to ensure the same hash is generated).

@ErikFunkenbusch - How can you do this? Any details would be greatly appreciated! Thanks.

Configuring Integrated Security using IIS Application Pool Identity ac...

iis sql-server-2008-r2 integrated-security
Rectangle 27 0

I'm not sure about IIS6, but in IIS8... In the advanced settings for the application pool in questions, there is an identity section. Set this to the windows credentials you want to use. Also set 'Load application profile' to true.

Then in your application, you just need to setup your connections string to use a trusted_connection instead of an SQL server username and password.

Just remember to set the windows account to have a password which never expires, unless you'll remember to update the app-pool when it does change (or the pool will stop.)

iis 6 - Using IIS 6 App pool identity to connect to SQL server - Stack...

iis-6
Rectangle 27 0

When using the built-in application pool identity, any calls across the network are made using the computer account. You should be able to assign the computer account rights to the network share and NTFS to accomplish this.

iis - Application Pool Identity Windows Server 2008R2 Acces is Denied ...

iis windows-server-2008-r2 windows-server-2003 applicationpoolidentity
Rectangle 27 0

Does abc123_svc_OSAT have access to directory that hosts your ASPX files? The log files you are looking at are only for logging access to your website. You will want to check the event viewer to see the actual IIS error.

Looks like that user does have access to the ASP files. I found this error in the event viewer (thank you for pointing me there): "Application pool ASP has been disabled. Windows Process Activation Service (WAS) encountered a failure when it started a worker process to serve the application pool."

asp.net - IIS application pool identity not allowing the server to sta...

asp.net iis iis-7