IIS web application access SQL DB as service account

Question

I've setup a new Python site on iis using FastCGI handler. The site has windows authentication enabled in iis and the app checks that the AD user belongs to an active directory group when they access the site. If authorisation fails access is denied.

Windows authentication uses Kerberos but it is not a double hop.

However the web app reads/writes to a SQL Server database and the DB calls are made using the service account which runs the app pool. The service account has limited access to run the web app and can only access one single database which the web app uses.

I've read that impersonation would be better from a DB security perspective using constrained delegation. Although the app does log which user has accesed the db.

I wouldn't remember the URL now. but it was essentially stating that the SQL database is checking that the actual AD user who is using the web app has access to the database. As opposed to the database checking that the service account has access.

Is there any obvious security risks with the approach I'm using?

Answer 1

I was originally building an application where users would be able to change their contact information in AD with pass-thru authentication, but it got complicated pretty quickly when I started reading up on the double-hop problem.

I dumped that feature and decided to go with a challenge response instead when users wanted to update their information. In terms of the appPool user, I took the approach you are doing for the following reasons:

1. It is database agnostic. I can see impersonation being a pain in the butt if you decide to migrate to a different database flavour for whatever reason.

2. Not having to deal with ipersonation and kerberos delegation... Like seriously. I don't know if I would have had the same problem with C# or C++, but I never really got it to work with python.

3. You can secure your endpoints with authorization within the application if you need to control things granularly after the initial authorization. You can audit via logging the endpoints that hit the database. It looks like you have already implemented logging for that.

In terms of security, the service accounts that run these apps on IIS would have to be audited from time to time to ensure they are not overprivileged. This approach is fine if you're already logging the app activity anyways.