SQLServerCentral Article

How to (Somewhat) Increase SQL Server Security

,

Problem

Some time ago Argenis Fernandez(@DBArgenis) found and described a vulnerability that allows you to get into SQL Server with 'sa' rights. This method does not require a restart of the SQL Server service or the whole machine, the condition is a local administrator account on the server.

Reminder

SQL Server until 2008R2: Until SQL 2008R2, gaining access was easy because the database engine installer added an 'NT AUTHORITY' account to the engine by default and gave it sysadmin privileges. All you had to do was run a console on the server with elevated privileges, from there run PsExec.exe(from the SysInternals package) and select an application to run on the system account, such as Management Studio:

And we were already able to log into SQL Server with sa privileges:

For SQL Server 2012-2019: As of SQL 2012, Microsoft no longer adds the 'NT AUTHORITY' account to the sysadmin role by default:

But, thanks to DBArgenis,  getting sysadmin rights is still easy. We will use the system's Task Scheduler to get permissions. Let's prepare a create_login.sql file containing the code:

CREATE LOGIN sa_user WITH PASSWORD='confidential_pass',DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; 
EXECUTE sp_addsrvrolemember @loginame = 'sa_user', @rolename = 'sysadmin';
GO

We run Task Scheduler and create a new task:

Change the user in whose context it will run to SYSTEM:

The task now looks like this:

Go to the 'Action' tab and click 'New...'. In the 'Program/script' field indicate the path to the SQLCMD application, in our case it will be:

“C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\SQLCMD.EXE”

In the 'Add arguments(optional)' field, we specify the parameters for SQLCMD(server and code file):

-S SERVER-SQL\SQL2012 -i C:\install\create_login.sql

This looks like the following:

Double-click OK and run the task:

And...We got it! We have an account with sa permissions on the server:

This will be similar on other versions of SQL. Here you can see the various versions where I have tested this:

Here are the logins created:

Solution

Fortunately, it is very easy to protect against this type of practice. From the SQL Server side, you can see that the connection is on the 'NT AUTHORITY SYSTEM' login, which does not have sysadmin privileges and cannot add logins to the sysadmin role:

After a short analysis, we will come to the conclusion that the task run by Task Scheduler from the SYSTEM account is actually called on the SQL engine with the 'NT SERVICEWinmgmt' login credentials. This login is automatically added to the database engine during SQL Server installation. According to Microsoft documentation, the Windows Management Instrumentation(WMI) service must be able to connect to the database engine, so the service identifier (SID) is added to SQL Server and given sysadmin privileges.

Fortunately for us, the same documentation also gives the minimum permissions for WMI to work properly on SQL(without sysadmin):

  • Membership in the db_ddladmin or db_owner fixed database roles in the msdb database.
  • CREATE DDL EVENT NOTIFICATION permission in the server.
  • CREATE TRACE EVENT NOTIFICATION permission in the Database Engine.
  • VIEW ANY DATABASE server-level permission.

Now all we need to do is take the 'NT SERVICEWinmgmt' login away from the sysadmin and give it the appropriate permissions:

use [master]
ALTER SERVER ROLE [sysadmin] DROP MEMBER [NT SERVICE\Winmgmt]
GO
USE [msdb]
GO
CREATE USER [NT SERVICE\Winmgmt] FOR LOGIN [NT SERVICE\Winmgmt]
GO
USE [msdb]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [NT SERVICE\Winmgmt]
GO
use [master]
GO
GRANT CREATE DDL EVENT NOTIFICATION TO [NT SERVICE\Winmgmt]
GO
use [master]
GO
GRANT CREATE TRACE EVENT NOTIFICATION TO [NT SERVICE\Winmgmt]
GO
use [master]
GO
GRANT VIEW ANY DATABASE TO [NT SERVICE\Winmgmt]
GO

Before checking if everything works as we assumed, we remove the test login 'sa_user':

USE [master]
GO
DROP LOGIN [sa_user]
GO

And in the task let's add the output file(output.log) to the argument list:

-S SERVER-SQL\SQL2012 -i C:\install\create_login.sql -o C:\install\output.log

We run the task, and the task executed without error:

But you will no longer find 'sa_user' in logins:

And in the output file we will see:

Msg 15247, Level 16, State 1, Server SERVER-SQL\SQL2012, Line 1
User does not have permission to perform this action.
Msg 15007, Level 16, State 1, Server SERVER-SQL\SQL2012, Procedure sp_addsrvrolemember, Line 33
'sa_user' is not a valid login or you do not have permission.

Summary

This vulnerability affects Windows Server 2012, 2012R2 and 2016 and all versions and editions of SQL Server from 2012 to 2019. If you try to do this on Windows Server 2019(regardless of which SQL Server on board) you will get exactly the same message as when you take away sysadmin privileges:

User does not have permission to perform this action.

Does all this exhaust the topic and is it enough to feel safer? Unfortunately no. Each instance of SQL Server also has other logins, which are added by default with sysadmin privileges, just to mention 'NT SERVICESQLWriter' and the possibility of its replacement in the registry with, for example, SQLCMD, unfortunately in this case Microsoft does not mention anything about the minimum privileges needed for proper operation. I hope that the above text will help you at least a little in better securing SQL Server against unauthorized access.

Rate

5 (4)

Share

Share

Rate

5 (4)