AD Security Groups and Application Roles

Question

AD Security Groups and Application Roles

chris_barnhart

Ten Centuries

Points: 1095
More actions
January 24, 2017 at 9:07 am

#317828

Good Morning All,
I searched the forum and only found relevant posts from many, many years ago. My question would be, Is there a way to use AD Security Groups and grant them the various permissions needed on the various DBs AND the permissions are only effective if the Application Name is a particular name?
I've read up a bit on Application Roles and I don't believe they would be a feasible solution since going across DBs requires a Guest Account with proper permissions, and also I don't like the idea of having a Username and Password just for the application role that must be passed in the connection string.
We use AD security groups today and all have the proper permissions on the various tables on the various DBs, but I would just like to add the extra level of security granularity to each of those Group Permissions that mandate the Application Name be equal to the specified application name. I feel this will prevent users that have elevated access as a result of being in these AD Security Groups, from being able to use other applications or even SSMS to change or view data.
Is there anything else I've missed or did I misunderstand Application Roles? I've seen use of logon triggers to try and accomplish this, but it too comes with a few cons as well. Just making sure there isn't a magic wand out there that I've overlooked...I figured with 2016 Row Level security being able to apply security at row level with predicates of the data, one could possibly now predicate normal higher level permissions at a DB or Table level.
Thanks in advance,
Chris

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

chris_barnhart - Tuesday, January 24, 2017 9:07 AM
Good Morning All,
I searched the forum and only found relevant posts from many, many years ago. My question would be, Is there a way to use AD Security Groups and grant them the various permissions needed on the various DBs AND the permissions are only effective if the Application Name is a particular name?
I've read up a bit on Application Roles and I don't believe they would be a feasible solution since going across DBs requires a Guest Account with proper permissions, and also I don't like the idea of having a Username and Password just for the application role that must be passed in the connection string.
We use AD security groups today and all have the proper permissions on the various tables on the various DBs, but I would just like to add the extra level of security granularity to each of those Group Permissions that mandate the Application Name be equal to the specified application name. I feel this will prevent users that have elevated access as a result of being in these AD Security Groups, from being able to use other applications or even SSMS to change or view data.
Is there anything else I've missed or did I misunderstand Application Roles? I've seen use of logon triggers to try and accomplish this, but it too comes with a few cons as well. Just making sure there isn't a magic wand out there that I've overlooked...I figured with 2016 Row Level security being able to apply security at row level with predicates of the data, one could possibly now predicate normal higher level permissions at a DB or Table level.
Thanks in advance,
Chris

you've got a solid handle on it; there's no way to filter on appname; giving access to a role or user does not have a granularity to filter by hostname or application name....once you have permissions, the application you use to access can be anything...SSMS, Excel, Access, ODBC, etc.
like you identified already, you can try a logon trigger to prevent connections with application strings you can identify, but a savvy programmer knows that those can be spoofed to say anything , as they are variables in the connection string.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

chris_barnhart Ten Centuries Points: 1095 More actions · Answer 2

Thanks for the confirmation. I'm going to dig a bit more and see if there is any refinement available on the AD side to link application to security group. I also don't like the login triggers because it sounds like it has to be all inclusive for all logins versus an additional type that can be commingled.

We will be going from a level 1 to 2 SOC audit over the next year and I was hoping to tighten things up a bit more than just validating the security group. Some of the permissions granted for one application may allow them to do something they shouldn't if they use another in house application.