Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have some Web Apps running in IIS 6. They are running in the Default AppPool which identitiy is Network Service.

The Apps connect to a local SQL Server 2005 Express Instance (SP2) and use a trusted connection. SQL Server runs also with Network Service identity.

Without granting explicit permissions, our webapps are able to query the DB.

How can this happen and which permissions does our app effectively have?

share|improve this question

3 Answers 3

up vote 1 down vote accepted

Some thought:

  • Does the SQL Server Express instance service run under Network Service, so has sysadmin rights?
  • Is the Network Service in the local admin group, so has sysadmin rights?
  • Is the Network Service account added to SQL Server as a login?
share|improve this answer
    
Yes, the instance is running as Network Service. The Network Service account is added as login. Does this mean, that any application running as Network Service has sysadmin rights? –  Louis Haußknecht Jun 9 '09 at 11:30
    
Yes, because the servie account used must be sysadmin –  gbn Jun 9 '09 at 11:44
    
I think we should switch to different accounts for both, the sql server and the IIS application pool. –  Louis Haußknecht Jun 9 '09 at 11:47
    
After removing the Network Service from the logins we can still connect to the db?! Weird. –  Louis Haußknecht Jun 9 '09 at 12:04
    
You'd need to check all SQL Server logins, groups and membership to find out why. I guess the permission path is via another group now –  gbn Jun 9 '09 at 12:52

This once again reinforces the case for not using the NETWORK SERVICE account for running SQL Server as you don't know what other services you are sharing the account with.

It would be best if you configured SQL Server to run under some other account. Special local account for SQL Server service works best - you can then use it to grant extra permissions to SQL Server, in case they are needed.

If after configuring SQL Server to run under different account and removing NETWORK SERVICE from logins you can still connect from IIS, then run this query while connected from IIS:

select * from sys.login_token

Then look for the WINDOWS GROUPS and WINDOWS LOGIN that have non-zero value in principal_id column. That group or login is how your IIS can connect to SQL Sever.

share|improve this answer
    
Good point! Will give this query a try. –  Louis Haußknecht Jul 23 '09 at 8:58

Maybe the permissions were granted to the IIS_WPG group on SQL Server? By default, the Network Service account (NTAUTHORITY\NETWORK SERVICE) is a member of this group.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.