SQL Server Security – Using EXECUTE AS Impersonation for RBAC

Securitysql server

I wrote a web application that is using AD authentication (Windows) and has it's own authorization module (like RBAC). Backend is SQL Server. A DBA in my team is not happy with us using a service account to talk to the database, he would rather have the actual user impersonated using EXECUTE AS at the database level. He would then use actual SQL roles mapped to lock down the access per user.

To me it sounds like a bad idea as I have to manage 100s of users at the SQL Server level and it just makes the attack surface bigger. What other arguments for or against this setup exist?

Best Answer

As a DBA by night, software developer by day, my preference is managing user security in the database (regardless if that's in addition to managing it elsewhere). SQL Server hooks directly into Windows Authentication and therefore supports both Active Directory User and Active Directory Group objects for security.

A lot of times its logical to map a specific AD Group to a specific database, schema, or subset of entities. E.g. you might use schemas to define related entities by application, and then grant read access to a certain AD Group and write access to another AD Group (with the respective SQL permissions) for one schema and visa versa for another schema as a way to separate users' data access across applications.

There's a lot of ways to splice how you can use security in SQL Server and again, coupled with AD Groups (or if AD groups aren't being used, then SQL roles can take their place) it becomes a lot more manageable than keeping track of individual permissions on each user.

Also from a security perspective, by having only one account that can access anything on the SQL Server you put yourself at higher risk because one account has more access than is needed and if became compromised would potentially be more damaging. One account being used in just as many instances as if you were using native Windows Authentication passed through to SQL Server is the same size of attack surface, and in fact even more risky assuming you're storing the dedicated account's password somewhere in the application. Whereas with Windows Authentication passed through to SQL Server, you don't need to store any credentials in the application.

Additional Info (from extended discussion):

Impersonation

Regarding Impersonation in ASP.NET Core, it is possible to still use legacy impersonation per Microsoft's docs (especially for connecting to a database): Configure Windows Authentication in ASP.NET Core (see the Impersonation section at the end)

Doing so is not really recommended for a few reasons, one being legacy security concerns, and another being it may limit your options in how you write your apps and the implementation is cumbersome in itself.

Alternative solutions for Impersonation are:

  • Disabling "Windows Authentication" and re-enabling "Anonymous Authentication" in IIS, and then scaling out your own login page for authentication so you can get a handle on the user (using modern encryption standards on the password.)
  • Implementing Token based authentication.
  • Adding the Identity role for Entity Framework Core as seen in this Microsoft example: Create an ASP.NET Core web app with user data protected by authorization

One Dedicated User vs Multiple Users / Groups Database Permissions Security Analogy

Premise: A house that has 200 doors and 1 skeleton key under the mat that opens them all is less secure than 200 different keys which each open only their own subset of door(s) that are kept on the key ring of each user's pocket.

One Dedicated User: The 200 door house has one main door you have to go through first. 200 people each have their own key to get through that door. Once you get through that door, the skeleton key for all 200 other doors in the house is under the mat on the inside of that door.

Individual User / Group Permissions: If you get rid of that skeleton key (individual user) and give all 200 users their own unique key that only opens that front door and specific doors in the house, now you've limited the scope of access each user has to the house and theoretically reduced your attack vector. Basically, all you've done is map each user's key to a limited set of doors. (You're no longer hiding a skeleton key under the mat.)

Think of Active Directory in the same way. Just because Active Directory stores a mapping of users to groups, doesn't mean there's an additional backdoor in your applications. The point of security threat is where your users actually authenticate which is at the application level. (Mapping permissions in SQL is almost exactly like mapping them in AD, except SQL can leverage the existing Groups in AD.)

Takeaway Point

While permission management can be a pain, it isn't any more complex to do it in the database if you're already doing it at the application level. This assumes the users have some sort of AD (or equivalent) permissions already implemented. If their permissions are already configured in AD then the heavy lifting is already done, it's just in the DBA's hands to map each relevant AD Group or set of Users to correlating SQL permissions, but it's really quite simple to do on the database side.

Related Question