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.
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?
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)
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
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?
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$
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...
That articles states (under "Accessing the Network") you still use the <domainname>\<machinename>$ aka machine account in the domain.
You can't have it both ways...