By: Emad Al-Mousa | Updated: 2024-10-10 | Comments (2) | Related: > Security
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 track the execution of sensitive system stored procedures. These commands should be monitored because attackers can potentially exploit them to compromise data if permissions are elevated to the sysadmin role.
Solution
Extended stored procedures are DLL files referenced by SQL Server and 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 are being performed with the power of SQL Server service account 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
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.
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.
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.'
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].
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:
- Create Custom Audit Specifications using SQL Server Audit
- SQL Server Database Security Audit (Part 1): What to expect?
- SQL Server Security Audit (Part 2): Scripts to help you or where can you find more information
- Programming Database Engine extended stored procedures
- Windows Registry: Windows Registry Key Modification
About the author
Emad Al-Mousa is a Saudi IT professional with more than 18 years of experience with database systems, cybersecurity, and cloud computing.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-10-10
