April 22, 2014 at 9:39 am
I've been struggling to find best practices information or any advice regarding Active Directory Groups that provide group members with the SysAdmin server role. I'm at a new job where I'm the only SQL Server DBA for 50 - 100 instances of SQL. There is one other (network) system administrator that will have the SysAdmin server role. There may be others in the future as well (may or may not be DBA's).
I'm picturing two options:
Option 1: create a group in Active Directory, add myself and any others to the group as needed, create SQL Server logins on each SQL Server instance for the AD group, and give the logins the SysAdmin server role.
This is convenient, and allows some flexibility. But it seems like a huge security risk. As a DBA, I don't have full control over who is in (or out) of the AD group. Currently, each SQL Server instance has a handful of logins with the SysAdmin role. I want to take SysAdmin away from all of them except for me, the network system admin, and sa. If I accomplish this task and implement Option 1, I end up no better than where I started from.
Option 2: use individual Windows Authentication (AD account) logins on each SQL Server and give each the SysAdmin server role.
This is not so convenient, but only two people/AD logins have SysAdmin privileges. As long as neither of us grant SysAdmin role membership to others, there should be a certain level of order.
Which option is preferred?
Are there other options I've overlooked?
April 23, 2014 at 6:20 am
Note that SQL Server logins do not map to AD groups. In option 1 you are still creating a Windows authenticated login, you are just associating it with an AD group instead of an AD user or a local system user/group.
If you are using Mixed Mode authentication you can setup SQL Server logins for specific individuals who will be the SAs. Otherwise the AD group is the way to go and just monitor group membership as well as permissions on the instance.
There is not going to be a 100% way to lock it down from AD Domain/Enterprise admins if the SQL instance is on a domain. Even if you lock it down where AD admins do not have access if they have local admin privileges on the box they can startup the instance in a way where they will have access (commonly used if access to an instance is forgotten/lost).
Joie Andrew
"Since 1982"
April 23, 2014 at 9:03 pm
I've decided Option 2 is the way to go for me. I finally found some "best practices" info, although it's related to PCI DSS compliance. It's a great read with some great recommendations. I can't implement all of them, and I may never pass a PCI audit, but I'll most definitely improve security. For what it's worth, here are the recommendations related to the SYSADMIN role:
A critical step in restricting access to cardholder data is to limit the number of privileged users assigned to the sysadmin server role. By default, the BUILTIN/Administrators group is not a member of the sysadmin role in SQL Server 2008 R2. The PCI DSS directly supports the best practice concept of “least privilege” access model; therefore, we recommended the following:
- Members of the sysadmin role should only login using individual Windows Logins
- The number of users assigned to the sysadmin role should be minimal
- The sysadmin role should be assigned based on job function
- Members of the sysadmin role’s Windows logins should be individually given access to SQL Server rather than through a mapped AD group
- Members of the sysadmin role should not be local Windows administrators
- Members of the sysadmin role should not have access to any folders or file shares on the server that the SQL Server service has access to such as, the folders containing the data and log files, and directories that databases or encryption keys could be backed up to
- A Windows local or domain administrator should not have sysadmin access to SQL Server
April 24, 2014 at 9:18 am
I agree with Joie that an AD group mapped to windows authentication on the instances is the best way to go for this. This about it - if the other sysadmin leaves the company you now need to remove that from 50-100 servers. While this is easy to do with Powershell or something like that, you need to have the access setup before hand for this to work. If you are using AD groups then you just remove him from the group once, and it effects all 50-100 instances.
The one MAJOR issue I have with AD groups is how they are managed. If you have a good company, the AD group should have an owner, and only that person should be able to request someone be added to the group. If your company allows people who do not fully understand what the group is used for to add users to any group, this could be a problem. I actively monitor our DBA group and any other sensitive to make sure no one gets added without us knowing.
- Tony Sweet
April 25, 2014 at 6:32 am
I'm wondering why Option 1 is such a concern? Only someone who has domain admin access should be able to edit the users in the AD group, and if someone with that level of access wanted access to your SQL server, there is precisely nothing you can do to stop them--they always have the recourse of starting the SQL server in single-user mode and adding any logins they want, or just changing your password and logging in using *your* sysadmin account! If you can't trust your domain admins then frankly you shouldn't be employing them.
April 26, 2014 at 12:45 pm
Tony Sweet (4/24/2014)
Think about it - if the other sysadmin leaves the company you now need to remove that from 50-100 servers. While this is easy to do with Powershell or something like that, you need to have the access setup before hand for this to work. If you are using AD groups then you just remove him from the group once, and it effects all 50-100 instances.
A counter point to this is that when an employee leaves the company, his/her AD account is typically disabled (and eventually deleted). It's a single action that removes all access (sysadmin or otherwise) from all the SQL servers.
April 26, 2014 at 12:57 pm
paul.knibbs (4/25/2014)
Only someone who has domain admin access should be able to edit the users in the AD group, and if someone with that level of access wanted access to your SQL server, there is precisely nothing you can do to stop them--they always have the recourse of starting the SQL server in single-user mode and adding any logins they want, or just changing your password and logging in using *your* sysadmin account! If you can't trust your domain admins then frankly you shouldn't be employing them.
Those are all valid points. However, it's not the system/domain/network admins I'm concerned about. It's the others in the organization they give privileges to that concern me. Let's say "Joe" in accounting needs to run reports and copy them to a network share. It's pretty easy to give him appropriate server/db privileges and rights to a UNC path on the network. But Joe has a laundry list of other various permissions he needs as well to perorm his duties. Someone higher up on the food chain says "Screw it! Just make Joe a domain admin so he can get his work done." So now I have to worry about Joe and any other non-IT types that are domain admins (or members of some other AD group that gets to log in to the SQL Servers as SYSADMIN). Joe may be an awesome accountant, but I don't want him on my SQL Server as a SYSADMIN. Do you?
April 26, 2014 at 12:59 pm
Thanks for your input, everyone. I wish I had worded my original post a bit differently. It's kinda silly to proclaim either option is right or wrong. They each have pros and cons and some trade-offs to consider. For me, Option 2 is a better choice. Your mileage may vary. More comments and/or other options are welcome. 🙂
April 26, 2014 at 1:17 pm
Usually, AD group memberships is controlled without the need for confirmation from a DBA - for a very good reason, since the SBA should not be concerned about a member being added/dropped from an AD group.
But that's exactly the reason why a the sysadmin priv should not be granted based on a AD group: the DBA team won't notice any change (at least not with additional effort and mostly as a reactive task, not a proactive).
The argument, that a specific user account would add additional effort in case of leaving the company is rather weak: if the account is disabled at the AD level, how would a user be able to connect to SQL Server (except using a local Windows account that should have been detected before...).
I, personally, advocate for user based sysadmin privs instead of group based. Just because of the harm that can be done.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply