use Network Service account to access DB

It’s impressive that a DB access issue was encountered whenwe deployed DW on mikplatform.

1.  A web app adopts an Application Pool with Identity ApplicationPoolIdentity
2. A windows service uses NetworkService as Log On

And exception will be thrown whileaccessing Database,

               Login failed for user 'MIKFS\MIKFS03$'

 

This exception will still be there,even after the above account being added to the Logins of the DB Server,as sysadmin.

 

 

 

This kind ofconfiguration should work for most deployment, why not work on mikplatform?

 

After someinvestigation, I found the reason:

           The Web Server/WindowsService are installed on the same box of DB

 

There is adocument to discuss Network Service Account, https://www.iis.net/learn/manage/configuring-security/application-pool-identities

To brief:

1. ApplicationPoolIdentity account is a special Network Service Account (so called virtual account)
2. Process running as Network Service Account accesses network as the machine account,  e.g. 'MIKFS\MIKFS03$'

That means if the WebServer / Windows Service are not on the same box of the DB, this configurationwill work properly

3. If the Web Server / Windows Service are on the same box of the DB, this configure will not work.
   1. ApplicationPoolIdentity will be run as identity : IIS AppPool\<ApplicationPoolName>
   2. Network Service will be run as identity: NT AUTHORITY\NETWORK SERVICE

 

 

So, onmikplatform, we need to add the above 2 users DB server logins, and grantsysadmin role to them.

           Note that, for the IIS AppPool user, you need to type the full name.  Ifthe login name is added by “Search”, it will be converted to <HostName>\<appPoolName>,which will cause login failure.

 

 

Then everything works.

 

The most misleading part of theproblem is, sql server will raise an error message that Login failed foruser 'MIKFS\MIKFS03$',  if those 2 Network Service  account donot present in the DB Login.   That’s possibly because DB falls backthe Network Service account to machine account.   But the result is,the login still fails even the machine account is added.

 

 

BTW, I’d like to discuss about thesecurity practice on account settings.

The reason why Web Apps/WindowsServices are running as Network Service account, a relatively low-privilegedone, is that a software bug/system flaw can't be used by a malicioususer to take over the whole system.  So it becomes a good securitypractice.

For most systemdeployments,   

1．The DB and WebApp/Windows Service are not installed on the same box

2．Application Poolaccount/Network Service Account is added to DB login as machine account. e.g. 'MIKFS\MIKFS03$'

3．systemadmin role is not granted to a machine account Login, in DB server

 

4. in Database,  add this Login as a User

5. on Membership tab, only select reader and writer

6. on Owned Schemas tab, only check schemas except db_* and guest

 

               This security practicewill minimize the risk when a system is under attack, and protect the customer’sdata.  And dbo schema is not accessible for web app/windows application

               But currently in MIK, this practice cannot be applied.  Because dbo schema is intensively used by applications, especially the task engine.