Windows Authentication and Network Service account as a db_owner

Question

There is a number of commercial products out there, that give you a windows based installers for configuring your app and the back end SQL Server DB. Typically it will ask if you want to connect to the DB with Windows or SQL Server authentication. Most of them make a recommendation to use Windows Auth and then configure your DB with the Network Service account assigned to the db_owner database role. I understand that Windows Authentication is more secure because you don't have to store credentials in web.config and send them over the wire when authentication to SQL Server, but is that a secure configuration for production environments, where the Network Service account is a db_owner? Any specific risks we should be aware of?

Answer 1

Yes, the application (and any user of it) can potentially execute anything that dbo can execute against the database. DROP TABLE, SELECT * FROM PASSWORDS, etc.

If you setup preventative measures against SQL Injection and have written your application with the appropriate application layer security, then this using Windows Auth with dbo is probably going to be ok.

If you are working with very sensitive data, do not trust the application (yet), or want to be as safe as possible, then you will have to implement security at the database layer.

For example, user x has access to tables views a and b and view c, and user y only has access to view c. Your application will have to connect as the appropriate user to access the correct object.

Answer 2

Thanks StingyJack,

I hear what you are saying, They would have to log in to the DB as a Network Service user first though. Is there an easy way to do that?

What I am really trying to figure out is whether there are any inherent risks associated with the fact that it is the default Network Service account that is assigned the db_owner role.

Answer 3

Logging into the DB as network service (domain\machine$) will be very difficult (I've no idea but some l33t haxor probably could) unless you are a service on the web box.

There is no password, it's not interactive and it has very limited rights (unless "local system").

The main issue is the SQL injection attacks. These apply to any connection though to the db server.

The extra risk in having db_owner is the DROP TABLE, even DROP DATABASE type attacks. Without db_owner, it's still dangerous eg "SELECT * FROM usertable WHERE 1=1" attacks.

Unfortunately, you have no choice with commercial or 3rd party apps to use stored procs, least permission etc.

You may be able to reduce rights after install though.

Answer 4

Using NETWORK SERVICE as a db_owner is probably OK for a lot of environments.

If you want to have a higher degree of security, simply create a separate Windows Account, grant it the minimum access it needs in SQL Server, and then change the application to run under the context of this new account.

The specific risks would be:

• One poorly written application run under the context of NETWORK SERVICE can allow unauthorized access to all the other data that NETWORK SERVICE has access to. You mitigate this risk by creating separate accounts for all applications.
• db_owner is likely more access than the application really needs, which means more potential for abuse/exploitation if you are compromised. You can reduce this quite a bit by picking common sense privileges to grant. Take it too far and you will have diminishing returns and more support headaches, though.