Back

Blindly Discover SQL Server Instances with PowerUpSQL

In this blog I’ll show how PowerUpSQL can be used to blindly discover SQL Server instances on a system, network, or domain. This is an essential first step if you’re planning to search for sensitive data on SQL Servers, or plan to use SQL Servers as a means to escalate privileges on the domain.

Importing PowerUpSQL

Before we get started, you’ll have to get the PowerUpSQL module imported. Below are some basic instructions. For more options visit the GitHub project here.

  1. Download PowerUpSQL.https://github.com/NetSPI/PowerUpSQL
  2. Import the Module
    PS C:\> Import-Module PowerUpSQL.psd1

Alternatively, you can load it with the PowerShell command below.

PS C:\> IEX(New-Object System.Net.WebClient).DownloadString("https://raw.githubusercontent.com/NetSPI/PowerUpSQL/master/PowerUpSQL.ps1")

Discover SQL Server Instances with PowerUpSQL

Below is an overview of the PowerUpSQL discovery functions that can be used for enumerating SQL Server instances from different attacker perspectives. Each of them support the PowerShell pipeline so that they can be used with other PowerUpSQL functions.

Get-SQLInstanceLocal

This command should be used if you’ve already compromised a system and would like a list of the local SQL Server instances. It uses the local registry entries to find them. Below are a few example commands.

# Get a list of local SQL Server instances
PS C:\>Get-SQLInstanceLocal    

ComputerName       : SQLServer1
Instance           : SQLServer1\SQLEXPRESS
ServiceDisplayName : SQL Server (SQLEXPRESS)
ServiceName        : MSSQL$SQLEXPRESS
ServicePath        : "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -sSQLEXPRESS
ServiceAccount     : NT Service\MSSQL$SQLEXPRESS
State              : Running

For more instance information pipe the Get-SQLInstanceLocal into Get-SQLServerInfo:

# Get a list of server information for each local instance
PS C:\>Get-SQLInstanceLocal | Get-SQLServerInfo

ComputerName           : SQLServer1
Instance               : SQLServer1\SQLEXPRESS
DomainName             : NETSPI
ServiceName            : MSSQL$SQLEXPRESS
ServiceAccount         : NT Service\MSSQL$SQLEXPRESS
AuthenticationMode     : Windows and SQL Server Authentication
Clustered              : No
SQLServerVersionNumber : 12.0.4213.0
SQLServerMajorVersion  : 2014
SQLServerEdition       : Express Edition (64-bit)
SQLServerServicePack   : SP1
OSArchitecture         : X64
OsMachineType          : WinNT
OSVersionName          : Windows 8.1 Pro
OsVersionNumber        : 6.3
Currentlogin           : Domain\User
IsSysadmin             : Yes
ActiveSessions         : 1

Get-SQLInstanceScanUDP

If you’re starting from an unauthenticated local network position then this function can come in handy. It returns SQL Server instances on the network from a UDP scan. It accepts a piped list of computer names or IP addresses.

The example below shows how to run the UDP scan and display output to the console:

PS C:\>Get-Content c:\temp\computers.txt | Get-SQLInstanceScanUDP –Verbose –Threads 10 

ComputerName : SQLServer1
Instance     : SQLServer1\SQLEXPRESS
InstanceName : SQLEXPRESS
ServerIP     : 10.1.1.12
TCPPort      : 50213
BaseVersion  : 12.0.4100.1
IsClustered  : No

ComputerName : SQLServer1
Instance     : SQLServer1\Standard
InstanceName : Standard
ServerIP     : 10.1.1.12
TCPPort      : 50261
BaseVersion  : 12.0.4100.1
IsClustered  : No

ComputerName : SQLServer2
Instance     : SQLServer2\AppName
InstanceName : AppName
ServerIP     : 10.1.1.150
TCPPort      : 58979
BaseVersion  : 10.50.4000.0
IsClustered  : No

The example below shows how to run the UDP scan and save the list of enumerated SQL Servers to a file for later use:

PS C:\>Get-Content c:\temp\computers.txt | Get-SQLInstanceScanUDP –Verbose –Threads 10 | Select-Object Instance -ExpandProperty Instance | Out-File c:\temp\test.txt

Big thanks to Eric Gruber for his work on this function!

Get-SQLInstanceFile

Sometimes you’ll already have a list of SQL Servers to target. For example, you may have already discovered SQL Server instances and saved them to a file for later use. 😉 This function loads those instances from a file so they can be fed through the PowerShell pipeline into other PowerUpSQL functions. It accepts a file containing one SQL Server instance per line.

Below are examples of the three formats it accepts.

  • Servername
  • Servername\Instancename
  • Servername,port

Below is a basic command example:

PS C:\> Get-SQLInstanceFile -FilePath C:\temp\instances.txt

ComputerName   Instance                      
------------   --------                      
SQLServer1    SQLServer1\SQLEXPRESS     
SQLServer1    SQLServer1\STANDARDDEV2014
SQLServer2    SQLServer2,1433    
SQLServer2    SQLServer2

The example below shows how to load a list of SQL Server instances from a file and attempt to log into each of the with the user “test” and the password “test”.

PS C:\> Get-SQLInstanceFile -FilePath C:\temp\instances.txt | Get-SQLConnectionTest -Verbose -Username test -Password test
VERBOSE: SQLServer1\SQLEXPRESS : Connection Success.
VERBOSE: SQLServer1\STANDARDDEV2014 : Connection Success.
VERBOSE: SQLServer2,1433 : Connection Failed.
VERBOSE: SQLServer2 : Connection Success.

ComputerName   Instance                  Status    
------------   --------                  ------    
SQLServer1    SQLServer1\SQLEXPRESS      Accessible
SQLServer1    SQLServer1\STANDARDDEV2014 Accessible
SQLServer2    SQLServer2,1433            Not Accessible
SQLServer2    SQLServer2                 Accessible

Get-SQLInstanceDomain

This function is useful if you’re already a domain user and looking for SQL Server targets on the domain. It returns a list of SQL Server instances discovered by querying a domain controller for systems with registered MSSQL Service Principal Names (SPNs). By default, the function will use the domain and logon server for the current domain account. However, alternative domain credentials can be provided along with and an alternative domain controller.

To run as alternative domain user, use the runsas command below to launch PowerShell before importing PowerUpSQL.

runas /noprofile /netonly /user:domain\user PowerShell.exe

To simply list SQL Server instances registered on the current domain use the command below.

Get-SQLInstanceDomain –Verbose

To get a list of SQL Server instances that can be logged into with the current Windows user you can use the command below. In large environments, I think you’ll be surprised to see how many SQL Servers normal domain users can log into.

Get-SQLInstanceDomain –Verbose | Get-SQLConnectionTestThreaded –Verbose –Threads 10 | Where-Object {$_.Status –eq ‘Accessible’}

Why do Domain User Accounts Have Unauthorized Access to so Many SQL Servers on the Domain?

It’s pretty common for people to doubt that members of the Active Directory group “Domain Users” would have any privileges on domain SQL Servers. However, in our experience it’s incredibly common in large environments for two reasons:

  1. Developers and SQL Server administrators have a habit of explicitly providing login privileges to all members of the Active Directory group “Domain Users”. This seems to happen a lot, because domain groups aren’t created for managing access to associated databases.
  2. When a SQL Server Express instance is installed on a domain system (and the TCP listener is enabled), a privilege inheritance chain exists that allows members of the Active Directory “Domain Users” group to log into the SQL Server instance with Public role privileges. This privilege chain is outlined in the blog “When Databases Attack: SQL Server Express Privilege Inheritance Issue“.

Due to the two common configurations described above, it’s often possible to gain a foot hold in SQL Server instances once any domain user is compromised. Naturally, this can lead to unauthorized data access, and provide the next step towards domain privilege escalation. Both topics will be covered in future blogs.

Wrap Up

In this blog I provided an overview of how SQL Server instances can be discovered with PowerUpSQL. I also provided some insight into why it’s common for standard domain users to have unauthorized access to some SQL Server instances on the domain. Hopefully the information will be useful to the red and blue teamers out there. Good luck and hack responsibly!

Discover how the NetSPI BAS solution helps organizations validate the efficacy of existing security controls and understand their Security Posture and Readiness.

X