Audit SQL Server Extended Stored Procedures Usage

Problem

I am working on enhancing the security monitoring for our company’s SQL Server landscape, especially with specific instances that host highly sensitive data. Moreover, we aim to build comprehensive security auditing policies integrated with Security Information and Event Management (SIEM) solutions to detect internal and external threats. Additionally, part of the exercise is to audit the execution of sensitive extended stored procedures. Monitor these commands so attackers cannot exploit them to compromise databases.

Solution

Extended stored procedures use DLL files referenced by SQL Server. They provide an interface to the underlying operating system. These stored procedures can perform manipulation on the operating system level. It will require elevated permission, such as a SYSADMIN role, for execution. However, it’s still dangerous and requires monitoring for future forensic analysis. Also, the operating system level operations use the SQL Server service account privileges running the database engine.

To find and list logins granted the SYSADMIN role, run the following T-SQL statement:

SELECT * FROM master.sys.server_principals WHERE IS_SRVROLEMEMBER ('sysadmin',name)=1;

To list all extended stored procedures in your SQL Server instance:

SELECT * FROM master.sys.all_objects where type='X'
GO

Key SQL Server Extended Stored Procedures

The following are considered most important to monitor among the list of extended stored procedures as they can be weaponized for malicious activities, and operating system level changes can take place through them:

xp_cmdshell: Allows operating system commands to be executed with the power of service account running SQL Server database engine.
xp_delete_files: Deletes a file or group of files at the operating system level.
xp_delete_file: Deletes a file or group of files, including backups with file extensions .bak or .trn.
xp_regdeletekey: Deletes a Windows operating system registry key.
xp_regwrite: Modifies and writes values in a Windows registry key.
xp_copy_file: Copies a file or group of files at the operating system level, especially with backup file extensions (.bak, .trn)
xp_copy_files: Copies a file or group of files at the operating system level.

Setup Server Level and Database Level Audits

Let us first define a server-level audit specification:

USE [master]
GO
 
CREATE SERVER AUDIT [AUDIT-XPPROC]
TO FILE 
(FILEPATH = N'D:\SQLServer_2022\audit\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)
GO

Then, define a database-level audit specification under the “MASTER” database:

USE [master]
GO
 
CREATE DATABASE AUDIT SPECIFICATION [Audit_XP]
FOR SERVER AUDIT [AUDIT-XPPROC]
ADD (SCHEMA_OBJECT_ACCESS_GROUP)
WITH (STATE = ON)
GO

The audit type chosen to monitor extended stored procedure is SCHEMA_OBJECT_ACCESS_GROUP.

Audit Testing 1 – xp_copy_files

For the sake of simulation, I will copy a file “file1.txt” from the C:\tmp folder to the C:\tmp2 folder using the T-SQL statement below:

exec master.sys.xp_copy_files 'C:\tmp\file1.txt', 'C:\tmp2\';

To check the audit logs, you can execute the following T-SQL query:

SELECT action_id, succeeded, session_server_principal_name, server_principal_name, object_name,statement
FROM sys.fn_get_audit_file('D:\SQLServer_2022\audit\*', DEFAULT, DEFAULT) 
WHERE action_id='EX' and object_name  like 'xp_copy%'
GO

Unlike xp_copy_files, xp_copy_file will require an absolute path in the source and destination folders, as shown below:

exec master.sys.xp_copy_file 'C:\tmp\file1.txt', 'C:\tmp2\file1.txt';

If you don’t clearly specify the destination path, the following error will be thrown in SQL Server Management Studio (SSMS):

xp_copy_file() returned error 3, ‘The system cannot find the path specified.’

Audit Testing 2 – xp_regwrite

Another simulation will be manipulating the Windows Server registry keys for the SQL default port. In this example, we will be changing the default port from the traditional 1433 value to a different value such as 1466:

EXEC master..xp_regwrite
     @rootkey     = 'HKEY_LOCAL_MACHINE',
     @key         = 'Software\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP1',
     @value_name  = 'TcpPort',
     @type        = 'REG_SZ',
     @value       = '1466'

Querying the new values after modification through a T-SQL query:

SELECT *
FROM [master].[sys].[dm_server_registry] 
WHERE registry_key='HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP1';

The audit logs will capture it as expected:

SELECT action_id,succeeded,session_server_principal_name,server_principal_name,object_name,statement
FROM sys.fn_get_audit_file('D:\SQLServer_2022\audit\*', DEFAULT, DEFAULT) 
WHERE action_id='EX' and object_name='xp_regwrite';
GO

Making modifications to the Windows registry is another malicious technique an adversary may use to abuse system services or daemons to execute commands or programs. MITRE ATT&CK is a globally accessible knowledge base of adversary tactics and techniques based on real-world observations and includes it as a viable technique [ID: T1569].

Audit Test 3 – xp_cmdshell

A third simulation we can illustrate is the weaponization of xp_cmdshell, a popular and known extended stored procedure that enables operating system commands to be run remotely. For example, I will use it to delete a dummy file on the D: drive called “sample.txt.”

EXEC xp_cmdshell 'del D:\sample.txt';  
GO

Check the audit log entries by executing the following T-SQL query:

SELECT action_id, succeeded, session_server_principal_name, server_principal_name, object_name, statement
FROM sys.fn_get_audit_file('D:\SQLServer_2022\audit\*', DEFAULT, DEFAULT) 
WHERE object_name='xp_cmdshell';
GO

Another operation where I can use xp_cmdshell is to change a file’s permissions, which can potentially create a security hole at the operating system level. In the following command, I am granting full permissions to a Windows operating system account called “emad2” on the dummy file, sample.txt:

EXEC xp_cmdshell 'CACLS D:\sample.txt /e /p emad2:f';  
GO

The capabilities of performing remote operating system operations when an attacker compromises an account with the SYSADMIN role from the database system level is an attack surface that requires examination, review, and monitoring. As a best practice and following best security standards, you can follow the Center for Internet Security (CIS) SQL Server benchmark, which dictates that a SQL Server service account cannot be used as an administrator at the operating system level.

To check the service account currently running your database engine, you can execute the following T-SQL query:

SELECT servicename, service_account FROM sys.dm_server_services;

Summary

Security auditing for extended stored procedures will greatly improve your environment security posture, and these security audit logs should be integrated with SIEM solutions for real-time monitoring. You can use the Windows event subscription method to push the audit logs through native Windows operating systems event logs (preferably pushing the security logs to the security event log, in particular). Or you can push the audit logs in binary file format to be parsed through the built-in function (sys.fn_get_audit_file).

Security monitoring is essential to successfully detect ongoing attacks, especially the current sophisticated attack techniques that target database systems for data ex-filtration. In addition, security auditing greatly helps with forensic investigations to track what happened in the database system with details and timelines.

Next Steps

This was just an overview of extended stored procedures and their role in functioning at the operating system level. This is something you will need to audit to ensure it’s not maliciously exploited in an unattended manner. In the meantime, read the following related tips and resources:

Resources

Community

Subscribe