In this blog I’ll show how PowerUpSQL can be used to rapidly target and sample sensitive data stored in SQL Server databases associated with Active Directory domains. We’ve used the techniques below to discover millions of PCI, HIPAA, and other sensitive records living inside and outside of protected network zones. Hopefully PowerUpSQL can help you do the same.
Pro tip: Once you’ve obtained Domain Admin privileges, add yourself to the DBA groups and run through the process again. More access = more data. 🙂
Finding Sensitive Data on Domain SQL Servers
If you followed the instructions in the last section you should have a variable named “$Accessible” that contains a list of all accessible SQL Server instances. The command below uses that variable to perform a broad search across all accessible SQL Servers for database table column names that contain provided keywords. I’ve created an example showing one server, but in real environments there are often hundreds.
PS C:\> $Accessible | Get-SQLColumnSampleDataThreaded –Verbose –Threads 10 –Keyword “card, password” –SampleSize 2 –ValidateCC -NoDefaults | ft -AutoSize
VERBOSE: SQLServer1\STANDARDDEV2014 : START SEARCH DATA BY COLUMN
VERBOSE: SQLServer1\STANDARDDEV2014 : CONNECTION SUCCESS
VERBOSE: SQLServer1\STANDARDDEV2014 : - Searching for column names that match criteria...
VERBOSE: SQLServer1\STANDARDDEV2014 : - Column match: [testdb].[dbo].[tracking].[card]
VERBOSE: SQLServer1\STANDARDDEV2014 : - Selecting 2 rows of data sample from column [testdb].[dbo].[tracking].[card].
VERBOSE: SQLServer1\STANDARDDEV2014 : COMPLETED SEARCH DATA BY COLUMN
ComputerName Instance Database Schema Table Column Sample RowCount IsCC
------------ -------- -------- ------ ----- ------ ------ -------- ----
SQLServer1 SQLServer1\STANDARDDEV2014 testdb dbo tracking card 4111111111111111 2 True
SQLServer1 SQLServer1\STANDARDDEV2014 testdb dbo tracking card 41111111111ASDFD 2 False
Below is a breakdown of what the command does:
It runs 10 concurrent host threads at a time
It searches accessible domain SQL Servers for database table columns containing the keywords “card” or “password”
It grabs a two sample records from each matching column
It checks if the sample data contains a credit card number using the Luhn formula
It filters out all default databases
If you want to target a single server you can also use the command below.
To save time in larger environments you may want to be a little more picky about what servers you’re targeting during data searches. Especially if you’re searching for multiple keywords. Dumping a list of databases and their properties can give you the information you need to make better server targeting decisions.
Some key pieces of information include:
This is the most intuitive. Databases are often named after the associated application or the type of data they contain.
This tells us if transparent encryption is used. People tend to encrypt things they want to protect so these databases make good targets. 😉 Transparent encryption is intended to protect data at rest, but if we login as a sysadmin, SQL Server will do the work of decrypting it for us. A big thanks goes out to James Houston for sharing that trend with us.
Database File Size
The database file size can help you determine if the database is actually being used. The bigger the database, the more data to sample. 🙂
To dump a list of all accessible SQL Server databases you can use the command below. Once again, we’ll use the “$Accessible” variable we created earlier. Storing the accessible servers in a variable allows us to quickly execute different PowerUpSQL functions against those servers without having to run the discovery commands again.
Note: The example only shows a sample of the output for one record, but in most environments you would have a lot more.
Once the results are stored in the “$Databases” variable there a ton of ways to view the data. Below are some of the more common options. In the examples, the results are sorted by the database name alphabetically.
# Output results to display
$Databases | Sort-Object DatabaseName
# Output results to display in table format
$Databases | Sort-Object DatabaseName | Format-Table -AutoSize
# Output results to pop grid with search functionality
$Databases | Sort-Object DatabaseName | Out-GridView
# Output results to a csv file
$Databases | Sort-Object DatabaseName | Export-Csv -NoTypeInformation C:\temp\databases.csv
If you’re only interested in encrypted databases you can use the command below.
In this blog I showed how sensitive data could be targeted and quickly sampled from domain SQL Servers using PowerUpSQL. I also noted that databases that use transparent encryption tend to make good targets for review. Hopefully the scripts will save you as much time as they’ve saved us. Either way, good luck and hack responsibly!
PTaaS is NetSPI’s delivery model for penetration testing. It enables customers to simplify the scoping of new engagements, view their testing results in real time, orchestrate faster remediation, perform always-on continuous testing, and more - all through the Resolve™ vulnerability management and orchestration platform.
We help organizations defend against adversaries by being the best at simulating real-world, sophisticated adversaries with the products, services, and training we provide. We know how attackers think and operate, allowing us to help our customers better defend against the threats they face daily.
At NetSPI, we believe that there is simply no replacement for human-led manual deep dive testing. Our Resolve platform delivers automation to ensure our people spend time looking for the critical vulnerabilities that tools miss. We provide automated and manual testing of all aspects of an organization’s entire attack surface, including external and internal network, application, cloud, and physical security.
Our proven methodology ensures that the client experience and our findings aren’t only as good as the latest tester assigned to your project. That consistency gives our customers assurance that if vulnerabilities exist, we will find them.