4

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?

3

Individual accounts are going to be much harder to administer. They give you more granular control over access, but you can not keep out your Active Directory administrators. Anyone who is granted local administrator rights on your server can gain sysadmin access to the instance. And, as a very good DBA told me once, if you can't trust your domain admins you've got bigger problems on your hands.

By abstracting your access to AD groups, you gain two benefits:

  1. Easier to manage access groups and rights based on roles, combined with stratified access control for other assets using those AD groups.
  2. Provide more consistent auditing, as you will only need to show access by AD group and then who is contained within that group.
  • Thanks for the input, Mike. It is helpful. One of the logins I intend to remove from the SysAdmin role is NT AUTHORITY\SYSTEM. I believe doing this will prevent the PsExec workaround in the article you referenced. That being said, I'm not looking to engage the domain admins in a cat and mouse game of control. The actual domain admins in my organization are very talented and conscientious. It's the others with elevated privileges (some of whom are in the domain admins AD group) I'm worried about (developers, customer support types, junior-level IT staff, etc). – Dave Mason Apr 22 '14 at 17:06
  • I'd suggest you request an AD group to be created for the sysadmin logins - but in an OU where only domain admins have rights to edit group membership etc. If your organisation has people in the domain admins group who's job isn't to be a domain admin then I think you have a bigger issue... – georgeb Apr 24 '14 at 11:13
0

I hate to answer my own question, but 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 was 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.