September 21, 2012 at 9:45 am
after doing some researching online...found two different Microsoft sql server 2008 R2 security best practices docs....
one is recommending to use windows groups and second one's recommendation .....
' Use Windows Logins rather than Windows Group to control access to SQL Server '
Gentlemen, i am very interested to hear Your opinions in this matter.
September 21, 2012 at 10:18 am
What two documents?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 21, 2012 at 11:46 am
one is sql server 2008 r2 best practice whitepaper - > use individual win accounts
and second .....i can't find it now...but do remember it was microsoft's sql 2005 BP whitepaper or some kinf of presentation doc
September 21, 2012 at 12:02 pm
Links?
Personally I would go with Logins based on Windows (Active Directory, AD) Groups in some cases and Logins based on individual Windows Accounts in others. It really will depend on the shop.
I see organizational barriers (e.g. the DBAs do not have control over AD) prevent shops from leveraging AD Groups. There is also the risk of people being added to an AD group and inadvertently being granted access to data they should not have access to. This is common when AD admin responsibilities are handled in a group separate from the DBAs and change control procedures are lacking. SQL Logins based on individual Windows Accounts are generally more work to maintain but give more granular control to the DBA.
That said, Windows Groups can considerably reduce DB admin overhead and in some shops are the way to go. Starting in SQL Server 2012 Database Users based on Logins that are based on Windows Groups can have a default schema which lifts one of the big technical barriers to adoption.
My go-to doc is this one:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 22, 2012 at 1:26 pm
OP, the documents you refer to must be about using groups to grant permissions to server roles and permissions or built in database roles and permission. Roles and permissions that will allow the user to cause havoc and Ragnarok to the server or database. Can you please provide us with links to these documents? I'm curious to see if my assumptions are correct.
Not using AD groups to grant "normal" user access to databases would be impossible in the systems I manage where the users accesses the database directly. There is no way I could administer individual access for 50.000 employees.
September 26, 2012 at 1:20 am
This highlights something I heard in a presentation that Whitepapers and Best Practices can be dangerous things 🙂
September 28, 2012 at 8:58 am
There is also the risk of people being added to an AD group and inadvertently being granted access to data they should not have access to.
Security best practice is that one set of staff should control the permissions given to an object, and another set of staff should control who can use a given set of permissions.
In the SQL world, this means a DBA might grant permissions that a given Windows Group has on a database, and the Security team would control what people have membership in the Windows Group.
Therefore, what is given as a potential disadvantage of using Windows Groups is actually what should be done to comply with security best practice. A DBA should not be the person who authorises a given individual to have access to SQL Server.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 28, 2012 at 9:08 am
EdVassie (9/28/2012)
There is also the risk of people being added to an AD group and inadvertently being granted access to data they should not have access to.
Security best practice is that one set of staff should control the permissions given to an object, and another set of staff should control who can use a given set of permissions.
In the SQL world, this means a DBA might grant permissions that a given Windows Group has on a database, and the Security team would control what people have membership in the Windows Group.
Therefore, what is given as a potential disadvantage of using Windows Groups is actually what should be done to comply with security best practice. A DBA should not be the person who authorises a given individual to have access to SQL Server.
I agree with that in theory. In my experience however, in practice, when a new person is brought into an environment the request usually looks a lot like this:
Jane just got hired and we need her to have the same permissions as her predecessor Bill.
So Jane is added to a bunch of AD groups, some of which might be sysadmin (or have a strong set of perms) on a lot of database instances in the environment, and the Jane may not really need to be a sysadmin from the get-go. She may need some vetting or some training before that level of permission should be granted.
The risk here is that there is a new sysadmin on the scene and they not only had no hand in that process, but unless they're ultra-paranoid and have a script setup to run something like xp_logininfo on all their logins based on AD groups periodically to recognize new ones, they won't even know they just inherited a new sysadmin.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 28, 2012 at 9:14 am
Our case is more on the simple side….
We’ll have 10-20 people per group with same read only permission for all dbs.
In some cases, permissions for some members of the group might be more granular… and if I’m not mistaken…
More granular (per object) permission supersedes ‘high level’ Group access rights; am I right?
I understand that how it’s done is per environment specific, so for me would be just interesting to hear
Other people opinions that based on their experience.
September 28, 2012 at 9:29 am
SD1999 (9/28/2012)
Our case is more on the simple side….We’ll have 10-20 people per group with same read only permission for all dbs.
In some cases, permissions for some members of the group might be more granular… and if I’m not mistaken…
More granular (per object) permission supersedes ‘high level’ Group access rights; am I right?
Not exactly. Database permissions for the Login associated with the Windows User (if exists) are merged with permissions for that person's AD Group Memberships and then authorization checks are done. SQL Server can get confused though so the recommendation is to have people only be part of one group that has access to a specific resource, i.e. do not have a person be a member of multiple groups with overlapping sets of permissions.
Say this:
- you are in a Windows Group and that Group has a Login on an instance, and that Login has a Database User that has SELECT permissions on a table
- your Windows User also has a Login on the same instance, that Login does not even have a Database User
You can still select from that table.
This scenario also allows you to select from the table:
- you are in a Windows Group and that Group has a Login on an instance, and that Login has a Database User but it does not have SELECT permissions on a table
- your Windows User also has a Login on the same instance, that Login also has a Database User that has SELECT permissions on a table
There is a precedence when it comes to DENY. DENY takes precedence over GRANT at any level of the permission hierarchy. The only exception to that is when you're in an ownership chaining scenario.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 3, 2012 at 6:33 am
opc.three (9/28/2012)
I agree with that in theory. In my experience however, in practice, when a new person is brought into an environment the request usually looks a lot like this:Jane just got hired and we need her to have the same permissions as her predecessor Bill.
Speaking as a former AD and security architect, this isn't a SQL Server issue. And trying to "fix" it by granting permissions to individual user accounts only makes things worse. This is a procedure and documentation issue on the security administration side. If you do things by individual user accounts, then here's the scenario you open up:
Bill was in a department where he had a lot of rights across 20+ SQL Servers. Of course, all were tied to his user ID and therefore his permissions are based on being a member fo that department. Bill just got promoted to a team lead or department head of a different group. His access needs to change. The DBAs don't notified. An audit reveals that Bill has permissions he shouldn't have. DBA team gets hammered.
Now, extrapolate that for multiple users per month in any decently sized organization and it's easy to understand why the recommendation is for Windows security groups. Whoever is handling security administration at the AD level should be receiving these notifications and making changes as part of a process. If that's not happening right, that's where things are broke.
Your best bet as a DBA is to hold the line on Windows group and push the problem back to its real source. Keep in mind that this problem isn't just affecting SQL Server, but every potential resource in your environment. Ensure your management knows and understands this.
K. Brian Kelley
@kbriankelley
October 3, 2012 at 8:42 am
K. Brian Kelley (10/3/2012)
opc.three (9/28/2012)
I agree with that in theory. In my experience however, in practice, when a new person is brought into an environment the request usually looks a lot like this:Jane just got hired and we need her to have the same permissions as her predecessor Bill.
Speaking as a former AD and security architect, this isn't a SQL Server issue. And trying to "fix" it by granting permissions to individual user accounts only makes things worse. This is a procedure and documentation issue on the security administration side. If you do things by individual user accounts, then here's the scenario you open up:
Bill was in a department where he had a lot of rights across 20+ SQL Servers. Of course, all were tied to his user ID and therefore his permissions are based on being a member fo that department. Bill just got promoted to a team lead or department head of a different group. His access needs to change. The DBAs don't notified. An audit reveals that Bill has permissions he shouldn't have. DBA team gets hammered.
Now, extrapolate that for multiple users per month in any decently sized organization and it's easy to understand why the recommendation is for Windows security groups. Whoever is handling security administration at the AD level should be receiving these notifications and making changes as part of a process. If that's not happening right, that's where things are broke.
Your best bet as a DBA is to hold the line on Windows group and push the problem back to its real source. Keep in mind that this problem isn't just affecting SQL Server, but every potential resource in your environment. Ensure your management knows and understands this.
That's a very fair counterpoint. I said it before, but using your idiom I'll say that there is no line to hold that works in all shops.
Personally I would go with Logins based on Windows (Active Directory, AD) Groups in some cases and Logins based on individual Windows Accounts in others. It really will depend on the shop.
If I had to say one thing about Groups I would try to avoid adding Windows Group to the sysadmin Role. I do look to have the application team base their security on Windows Groups and have that carry through into the database.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 3, 2012 at 9:07 am
opc.three (10/3/2012)
K. Brian Kelley (10/3/2012)
opc.three (9/28/2012)
If I had to say one thing about Groups I would try to avoid adding Windows Group to the sysadmin Role. I do look to have the application team base their security on Windows Groups and have that carry through into the database.
You might get away with that in a small shop, say 5-10 people, but not one much larger.
I'm a perfect example as to why. In Dec 2001 I moved from the DBA team to the server team to be the infrastructure architect. We had 50+ SQL Servers at the time. By basing sysadmin role membership on Windows groups, the change was made to a domain group and my access was altered. In 2009 we managed well over 150 instances. I switched back to being a senior DBA because I wanted to go back to SQL and get my hours back in whack. Again, change was made to the domain groups and my access was immediately altered. Now we manage over 200 instances of SQL Server. We just hired a full-time DBA. No one would want to have to go touch each of those instances.
K. Brian Kelley
@kbriankelley
October 3, 2012 at 9:27 am
K. Brian Kelley (10/3/2012)
opc.three (10/3/2012)
K. Brian Kelley (10/3/2012)
opc.three (9/28/2012)
If I had to say one thing about Groups I would try to avoid adding Windows Group to the sysadmin Role. I do look to have the application team base their security on Windows Groups and have that carry through into the database.You might get away with that in a small shop, say 5-10 people, but not one much larger.
I'm a perfect example as to why. In Dec 2001 I moved from the DBA team to the server team to be the infrastructure architect. We had 50+ SQL Servers at the time. By basing sysadmin role membership on Windows groups, the change was made to a domain group and my access was altered. In 2009 we managed well over 150 instances. I switched back to being a senior DBA because I wanted to go back to SQL and get my hours back in whack. Again, change was made to the domain groups and my access was immediately altered. Now we manage over 200 instances of SQL Server. We just hired a full-time DBA. No one would want to have to go touch each of those instances.
I would agree with you if it were difficult to touch 200 instances. That's what CMS, multi-query windows and PowerShell are for. For the knowledge that no one can become a sysadmin straightaway via the compromising of AD (knowingly or unknowingly) I would happily retain that responsibility within the domain of the DBA group.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 3, 2012 at 1:27 pm
opc.three (10/3/2012)
I would agree with you if it were difficult to touch 200 instances. That's what CMS, multi-query windows and PowerShell are for. For the knowledge that no one can become a sysadmin straightaway via the compromising of AD (knowingly or unknowingly) I would happily retain that responsibility within the domain of the DBA group.
That's incorrect.
1) I can reset a DBA's password.
2) I can dump the SAM and use a rainbow table to determine a DBA's password.
3) I can come into SQL Server as System since likely as a Domain Admin I have local Administrator rights on the system.
4) During patching, as an admin I can restart SQL Server in single user mode and put myself in.
5) During patching I can ensure SQL Server is stopped and copy the database files off (unless you're using TDS).
6) I can compromise one of the lesser services' registry info that has access into SQL Server (Full Text, VSS Writer).
7) If you're not using encryption on the backups or using TDS, I can simply grab the backups.
So you're adding a ton on work unnecessarily to not use Windows groups. Also, you're adding to the complexity of the overall security posture. The more complex you make it, the greater the chance for a failure. For instance, what happens if your PowerShell script fails against a particular instance because there's other maintenance being done to the server in question? Are you periodicially re-running the script with the appropriate personnel to ensure that everyone is everywhere like they should be and no one has been retained that shouldn't be? If you aren't, you've got the potential for a gap. Again, this is why a Windows group is recommended. I remove from AD and when the login attempts to access SQL Server the security token is passed. If the user isn't in the group any longer, he no longer gets sysadmin access. It doesn't matter what was going in to the server at the time I pulled the user from the group.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply