Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

What Windows Server Groups Should I Audit on my SQL Servers?


By:   |   Read Comments   |   Related Tips: More > Auditing and Compliance

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

I'm responsible for the security of my organization's SQL Servers. Lately I've been looking outside of SQL Server and wondering what I should be checking. Someone mentioned the Administrators group and that made me think about other groups on the server. What groups should I be auditing and why?

Solution

Outside of SQL Server, at the local server, there are several built-in groups you should audit the membership of:

  • Administrators
  • Power Users
  • Remote Desktop Users

Administrators

There are several sayings, but they all basically mean this, "If I am an administrator of your server, it's not your server anymore." As an administrator I can do just about anything, to include bypassing controls that might normally keep me out of a SQL Server instance. As a result, knowing who is a member of this security group is critical. Audit it frequently.

Power Users

I have found as I've talked to auditors and DBAs that quite a few aren't familiar with Power Users. If you're dealing with a newer server operating system, Windows Server 2008 or above, then the Power Users group doesn't have any more rights than the normal Users group. However, if you're still running Windows Server 2003 servers, do note that on that OS the members of the Power Users group can control services. This means they can stop services, set them to disabled, change the login for a service, and other such things which can cause a denial of service for a SQL Server. Therefore, if you have an older OS, audit Power Users.

On the newer OSes, if you are dealing with an application installed on your SQL Server that is not certified as part of the Windows Logo program, investigate whether or not that application uses the Power Users group. If it does, then an administrator may have changed the security on the server to allow Power Users to have the same rights it did on legacy OSes. If that's the case, you'll need to audit this group even if you're on Windows Server 2008 and above.

Remote Desktop Users

Normally users cannot log on to a server through Remote Desktop, only administrators can. However, if a user is a member of Remote Desktop Users, then it is possible for the user to connect if RDP connectivity/functionality is enabled. When you look at Microsoft security patch ratings, the ones that require a login in order to exploit tend to be rated less than Critical. Some organizations roll Critical patches ahead of Important ones. If there are users in Remote Desktop Users, then those users might be able to take advantage of this dichotomy for patch deployment. Also, consider that there's always the possibility of stray information lying in a file somewhere a logged on user can access, information that shouldn't be exposed. Obviously, if Remote Desktop Users is kept member less, that reduces this concern. If it's not, then you have to consider that type of issue. Because of these risks, I always recommend auditing Remote Desktop Users.

SQL Server Specific Groups

For most SQL Server configurations, there are local groups corresponding to the SQL Server install. For instance:

SQL Server Specific Groups

This is a test system which has every version from SQL Server 2005 to 2012 installed. You likely won't see as many groups on one of your servers.

If you look at the descriptions for the groups, you'll see that each one gives specific permissions and/or access. As a result, they should be audited regularly.

What Else?

Any created groups are immediately suspect in my book. Know why they are there, what access they have, and who the members are. Some of the groups many not be of concern, the problem is you won't know until you investigate.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools