Sinister SQL Queries and How to Catch Them

SQL Server is more than just a database—it's a powerful platform that can be leveraged by attackers for system access, persistence, and code execution. While organizations focus on protecting their valuable data, they often overlook the inherent capabilities within SQL Server that make it an attractive target for adversaries looking to establish footholds in Microsoft environments.

This blog explores the offensive capabilities built into SQL Server and provides defenders with practical detection strategies. We'll examine how attackers can abuse stored procedures, CLR assemblies, modify the registry, and maintain persistence—all while potentially evading common monitoring controls. Additionally, we'll provide attack simulations and security content that security practitioners can immediately leverage in their environments.

But before we dive into attack techniques and detection methods, let's understand the fundamental components that make SQL Server such a versatile tool for both administrators and attackers alike.

Understanding SQL Server Stored Procedures

When assessing SQL Server security risks, it's essential to understand the powerful built-in procedures that can be leveraged by both administrators and attackers. Two critical categories deserve special attention: system stored procedures (sp_) and extended stored procedures (xp_). These procedures provide administrative functionality but can present significant security risks when misused or left unnecessarily enabled. Before examining these procedures in detail, let's first understand the default security context in which SQL Server operates.

SQL Service Permissions

In SQL Server, the service account—commonly NT SERVICE\MSSQLSERVER for default instances—is configured with the principle of least privilege to enhance security. By default, this account has limited permissions, particularly concerning system-level operations like modifying the Windows registry. This restriction ensures that even if an adversary gains control over SQL Server, their ability to alter critical system configurations is constrained.

However, while direct registry modifications are restricted, the service account retains the capability to execute commands via tools like PowerShell or the command prompt. This means that an attacker could still run scripts or commands, potentially leading to unauthorized actions or data exfiltration.

It's important to note that the service account's permissions can be modified. For instance, if SQL Server is configured to run under a more privileged account, such as LocalSystem, it would inherit broader system access, including extensive registry modification capabilities. Therefore, it's crucial to carefully manage and monitor the service account's permissions to maintain a secure environment.

In summary, while the default configuration of the SQL Server service account limits certain high-risk actions like registry edits, it still allows command execution through PowerShell or CMD. Administrators should remain vigilant, ensuring that service accounts operate with the minimum necessary privileges and that any elevation of these privileges is justified and monitored.

OLE Automation Procedures (sp_OA*)

OLE Automation Procedures allow SQL Server to interact with the Windows operating system via COM objects, enabling file manipulation, registry modifications, and network requests. While designed for automation, these procedures introduce significant security risks when enabled. Attackers can abuse them to execute arbitrary commands, create backdoors, or establish persistence—all without needing xp_cmdshell.

Key characteristics of OLE Automation Procedures

• Disabled by default for security reasons
• Enable interaction with external Windows components
• Can be used to execute PowerShell scripts, modify the registry, or exfiltrate data
• Executed using system COM objects

Common (and potentially dangerous) examples

• sp_OACreate – Instantiates a COM object
• sp_OAMethod – Calls a method on an instantiated object
• sp_OADestroy – Cleans up an instantiated object

System Stored Procedures (sp_*)

System stored procedures, prefixed with `sp_`, are Transact-SQL procedures that come built into SQL Server. These procedures are stored in the `master` database and can be executed from any database. They serve various administrative and information-gathering purposes.

Key characteristics of sp_ procedures

• Always start with the prefix "sp_"
• Primarily written in T-SQL
• Execute within the SQL Server process
• Used for database administration and management tasks
• Stored in the master database but accessible from any database

Common examples

These are standard system procedures with legitimate administrative uses, though some can be misused in certain contexts when excessive permissions are granted.

• `sp_configure`: Modifies server-wide configuration settings
• `sp_help`: Provides information about database objects
• `sp_who`: Shows current user sessions
• `sp_adduser`: Adds a database user
• `sp_executesql`: Executes dynamic SQL statements

Extended Stored Procedures (xp_*)

Extended stored procedures, prefixed with `xp_`, are particularly sensitive from a security perspective because they can interact with the operating system outside of SQL Server's normal boundaries. These procedures are actually compiled DLL files (xpstar.dll, for example) that run as external code.

Key characteristics of xp_ procedures

• Always start with the prefix "xp_"
• Written in languages like C++ or C
• Execute outside the SQL Server process
• Can interact directly with the operating system
• Often have higher security implications

Common (and potentially dangerous) examples:

• `xp_cmdshell`: Executes operating system commands
• `xp_regread`: Reads Windows Registry values
• `xp_regwrite`: Modifies Windows Registry values
• `xp_fileexist`: Checks for file existence on the system
• `xp_dirtree`: Lists directory contents

The xpstar.dll: SQL Server's Extended Procedure Manager

`xpstar.dll` is a system DLL in SQL Server that implements many of the built-in extended stored procedures. Think of it as the "manager" for extended stored procedure functionality.

Key extended procedures in xpstar.dll:

• `xp_cmdshell`
• `xp_regread`
• `xp_regwrite`
• `xp_fileexist`
• `xp_dirtree`
• `xp_enumgroups`
• `xp_logininfo`

Default location:

How extended procedures are called:

1. When you call an extended stored procedure:
   
   

   EXEC xp_cmdshell 'whoami'

   
   
2.  SQL Server does the following:
   • Loads xpstar.dll if not already loaded
   • Finds the corresponding function in the DLL
   • Executes the function with provided parameters
   • Returns results back to SQL Server

Security Implications

These stored procedures introduce various risks that attackers can exploit for execution, persistence, and reconnaissance.

1. Operating System Access
   
   • xp_cmdshell executes OS commands, allowing command injection and system control.
   • sp_OACreate enables interaction with COM objects, allowing file manipulation and PowerShell execution.
2. Registry Manipulation
   
   • xp_regread and xp_regwrite modify system settings, potentially altering security configurations.
   • sp_OAMethod can write to the registry, enabling persistence techniques.
3. Information Disclosure & Reconnaissance
   
   • xp_dirtree and xp_fileexist allow file system enumeration.
   • sp_who and xp_logininfo reveal user sessions and credentials, aiding lateral movement.

Attackers use these functions to execute commands, establish persistence, and gather intel on SQL Server and the underlying system. Disabling unnecessary procedures and monitoring for suspicious executions are key to reducing risk.

Diving Deeper: Identifying and Leveraging SQL Server Execution Paths

After understanding the risks associated with stored procedures, it's important to identify what’s enabled in your SQL environment and explore additional execution mechanisms. Attackers frequently leverage extended procedures, startup procedures, and SQL CLR for persistence, privilege escalation, and OS-level execution. Here we will dive deeper and take a more hands on approach to identifying and learning more about SQL procedures. Note that this is not exhaustive and we will reference outside projects to assist with learning more as necessary. If you don’t have a lab environment yet with SQL, feel free to read through first and follow along again using SQLSSTT with the SQL Server Management Studio.

Finding Enabled xp_* Procedures

To enumerate all extended stored procedures (xp_*) in a SQL Server instance:

There are 53 enabled on this database in my test environment:

Figure 1: extended properties in SQL, Splunk 2025

For a full database-wide enumeration across all databases:

Figure 2: Query all databases at once, Splunk 2025

Many xp_* procedures provide system-level access, and defenders should feel comfortable in knowing how to identify risky or unnecessary procedures in their environment.

Startup Procedures: Persistence via Auto-Execution

SQL Server allows procedures to automatically execute at startup, making them a prime target for persistence. Attackers can use this feature to ensure malicious code runs each time the SQL service starts.

To check for enabled startup procedures:

Figure 3: Viewing startup procedures, Splunk 2025

To list currently enabled startup procedures:

Figure 4: Stored procedures in SQL, Splunk 2025

As outlined in “Hacking SQL Server Stored Procedures – Part 3: SQL Injection,” here is another way to query the stored procedures:

Figure 4: Stored procedures in SQL with command, Splunk 2025

If an attacker gains access to SQL Server, they can add a malicious stored procedure to startup, ensuring code execution even after reboots.

Registry & File Manipulation (xp_regwrite, xp_filewrite)

SQL Server provides built-in procedures that allow modification of Windows registry settings and file creation. These can be used for persistence, privilege escalation, or configuration changes.

Let’s take writing to the Windows Registry as an example:

Note that the process to modify the registry will be sqlservr.exe.

Figure 5: Default NT SERVICE\MSSQLSERVER account blocked from editing registry, Splunk 2025

Note here that by default the NT SERVICE\MSSQLSERVER service account does not have permissions to write to the registry and would 1. Need to be modified to an account that does or 2. Need another method to modify the registry, such as xp_cmdshell.

Registry modifications and file writes allow stealthy persistence mechanisms within SQL Server, and defenders should log and alert on usage of these procedures.

SQL CLR: Code Execution via .NET

SQL Server Common Language Runtime (CLR) allows developers to execute .NET code inside the database engine. While useful for extending functionality, it can also be abused by attackers to execute arbitrary system commands, bypass security controls, or create stealthy persistence mechanisms. CLR assemblies run within the security context of SQL Server itself, making them particularly dangerous as they can bypass restrictions, access resources available to the SQL Server, and provide persistence mechanisms that often escape regular monitoring. This combination of privileged execution context and reduced visibility makes SQL CLR a high-value target for attackers with database access.

Detection & Configuration

To check if SQL CLR is enabled:

Figure 6: CLR Disabled in SQL, Splunk 2025

To enable SQL CLR (requires sysadmin privileges):

Figure 7: Enabling CLR in SQL, Splunk 2025

Figure 8: Validating CLR is enabled, Splunk 2025

Starting with SQL Server 2017, Microsoft implemented stricter CLR security with the `clr strict security` setting enabled by default. This means assemblies must meet one of the following requirements:

1. Signed with a certificate that has a corresponding login with UNSAFE ASSEMBLY permission
2. Added to the trusted assembly list using `sp_add_trusted_assembly`
3. Run in an environment where unsafe CLR has been configured

An adversary with sufficient privileges has two main paths to load malicious assemblies:

Path 1: Adding to Trusted Assemblies

Path 2: Enable Unsafe CLR

Loading and Using CLR Assemblies

Once the security requirements are met, an assembly can be loaded:

The binary will need to be on disk.

Then you can create stored procedures that reference the assembly:

NetSPI provided another very detailed blog, “Attacking SQL Server CLR Assemblies,” that walks readers through these steps in more detail using a custom DLL.

Attack Simulation

After understanding SQL Server's key components like stored procedures, CLR capabilities, and execution paths, it's important to validate our detection strategies through practical testing. Let's explore some common attack techniques and how to simulate them safely in a test environment. These tests will help defenders verify their monitoring controls and understand the adversary's perspective.

SQLSSTT

To help defenders validate their monitoring controls and understand adversary tradecraft, we can use tools like SQL Server Security Testing Tool (SQLSSTT). SQLSSTT is a basic PowerShell-based security testing framework that helps identify common misconfigurations, vulnerabilities, and potential attack vectors in SQL Server environments.

SQLSSTT provides comprehensive testing capabilities, including:

• Basic connectivity and authentication testing
• System enumeration techniques
• xp_cmdshell execution validation
• Data exfiltration pattern detection
• Multiple execution methods (Invoke-Sqlcmd and sqlcmd.exe)

The tool is designed to help blue teams understand:

• How adversaries interact with SQL Server
• Common attack patterns and their signatures
• Various methods of executing commands
• Data exfiltration techniques

To get started, install SQL in a lab environment and then begin testing with SQLSSTT and/or SQL Server Management Studio. In addition, we will walk through the combined utilities with SequelEyes.

Atomic Red Team

To begin our testing, we will utilize Atomic Red Team T1105 test number 32.

This test has a prerequisite to install SQLCmd.exe via Winget, or you may manually download it direct.

Once the prerequisite is installed, run the following:

Figure 9: Atomic Red Team Test T1105

Upon success, the T1105.zip will be written to c:\.

Before we continue, let’s summarize what we’ve done:

1. We built a lab environment using SQLSSTT, allowing us to safely test SQL.
2. We ran the SQLSSTT testing to showcase different attack scenarios.
3. We introduced a secondary tool, SequelEyes, to build a frontend for SQL and query the database.
4. We manually ran multiple SQL tests to generate events for detection.
5. We discussed Atomic testing SQL.

Splunk Security Content

Effective monitoring of SQL Server environments requires visibility into both database and operating system-level activities. The security content described below focuses on using Windows Application Event Log for SQL events as it provides critical visibility into configuration changes, stored procedure executions, and other potential attack vectors without requiring complex database auditing configurations. Splunk offers several options for comprehensive SQL monitoring: the Splunk Add-on for Microsoft SQL Server collects and normalizes SQL Server logs, while Splunk DB Connect enables direct database queries for deeper visibility. By leveraging these tools along with the detection rules below, security teams can identify suspicious SQL Server activities early in the attack chain, before adversaries can establish persistence or execute malicious code.

Windows SQL Server Critical Procedures Enabled

This detection identifies when critical SQL Server configuration options are modified, including "Ad Hoc Distributed Queries," "external scripts enabled," "Ole Automation Procedures," "clr enabled," and "clr strict security." These features can be abused by attackers for various malicious purposes, e.g.:

• Ad Hoc Distributed Queries enables Active Directory reconnaissance through ADSI provider.
• External scripts and Ole Automation allow execution of arbitrary code.
• CLR features can be used to run custom assemblies.

Enabling these features could indicate attempts to gain code execution or perform reconnaissance through SQL Server.

Figure 10: Windows SQL Server Critical Procedures Enabled, Splunk 2025

Windows PowerShell Invoke-Sqlcmd Execution

This detection identifies potentially suspicious usage of Invoke-Sqlcmd PowerShell cmdlet, which can be used for database operations and potential data exfiltration. The detection looks for suspicious parameter combinations and query patterns that may indicate unauthorized database access, data theft, or malicious database operations. Threat actors may prefer using PowerShell Invoke-Sqlcmd over sqlcmd.exe as it provides a more flexible programmatic interface and can better evade detection.

Figure 11: Windows PowerShell Invoke-Sqlcmd Execution, Splunk 2025

Windows SQL Server Configuration Option Hunt

This detection helps hunt for changes to SQL Server configuration options that could indicate malicious activity. It monitors for modifications to any SQL Server configuration settings, allowing analysts to identify potentially suspicious changes that may be part of an attack, such as enabling dangerous features or modifying security-relevant settings.

Figure 12: Windows SQL Server Configuration Option Hunt, Splunk 2025

Windows SQL Server Extended Procedure DLL Loading Hunt

This analytic detects when SQL Server loads DLLs to execute extended stored procedures. This is particularly important for security monitoring as it indicates the first-time use or version changes of potentially dangerous procedures like xp_cmdshell, sp_OACreate, and others. While this is a legitimate operation, adversaries may abuse these procedures for execution, discovery, or privilege escalation.

Figure 13: Windows SQL Server Extended Procedure DLL Loading Hunt, Splunk 2025

Windows SQL Server Startup Procedure

This detection identifies when a startup procedure is registered or executed in SQL Server. Startup procedures automatically execute when SQL Server starts, making them an attractive persistence mechanism for attackers. The detection monitors for suspicious stored procedure names and patterns that may indicate malicious activity, such as attempts to execute operating system commands or gain elevated privileges.

Figure 14: Windows SQL Server Start Procedure, Splunk 2025

Windows SQL Server xp_cmdshell Config Change

This detection identifies when the xp_cmdshell configuration is modified in SQL Server. The xp_cmdshell extended stored procedure allows execution of operating system commands and programs from SQL Server, making it a high-risk feature commonly abused by attackers for privilege escalation and lateral movement.

Figure 15: xp_cmdshell enabled, disabled, Splunk 2025

Windows SQLCMD Execution

This detection identifies potentially suspicious usage of sqlcmd.exe, focusing on command patterns that may indicate data exfiltration, reconnaissance, or malicious database operations. The detection looks for both short-form (-X) and long-form (--flag) suspicious parameter combinations, which have been observed in APT campaigns targeting high-value organizations. For example, threat actors like CL-STA-0048 have been known to abuse sqlcmd.exe for data theft and exfiltration from compromised MSSQL servers. The detection monitors for suspicious authentication attempts, output redirection, and potentially malicious query patterns that could indicate unauthorized database access or data theft.

Figure 16: Windows SQLCmd Execution, Splunk 2025

Windows Sqlservr Spawning Shell

This analytic detects instances where the sqlservr.exe process spawns a command shell (cmd.exe) or PowerShell process. This behavior is often indicative of command execution initiated from within the SQL Server process, potentially due to exploitation of SQL injection vulnerabilities or the use of extended stored procedures like xp_cmdshell.

Figure 17: Windows SQLServr Spawning Shell, Splunk 2025

Learn More

This blog helps security analysts, blue teamers and Splunk customers identify and defend against SQL attack techniques that can lead to system access, persistence, and code execution. By understanding these offensive capabilities, security teams can better protect their SQL Server environments against adversaries seeking to establish backdoors or gain elevated privileges. You can implement the detections described in this blog using the Enterprise Security Content Updates app or the Splunk Security Essentials app. To view the Splunk Threat Research Team's complete security content repository, visit research.splunk.com.

Feedback

Any feedback or requests? Feel free to put in an issue on Github and we’ll follow up. Alternatively, join us on the Slack channel #security-research. Follow these instructions If you need an invitation to our Splunk user groups on Slack.

Contributors

We would like to thank Michael Haag for authoring this post and the entire Splunk Threat Research Team for their contributions: Jose Hernandez, Patrick Bareiss, Lou Stella, Bhavin Patel, Eric McGinnis, Nasreddine Bencherchali, Teoderick Contreras and Rod Soto.