November 14, 2011
A few weeks ago I presented a webinar called “When Databases Attack”. It covered some SQL Server database configuration issues that are commonly overlooked and targeted by attackers. For those who are interested it can be viewed HERE. This is a response to some requests for script examples. In this blog I’ll provide a few scripts for finding sensitive data quickly in SQL Server. In the future I’ll provide scripts for other attacks as well.
Finding Sensitive Data
There are a lot of great tools available for finding data quickly on a SQL Server. Some are commercial and some are open source. Most of them can be useful when gathering evidence during PCI penetration tests or when simply trying to determine if sensitive data exists in your database. In this section I’m going to cover how to find and sample data from SQL Servers using my TSQL script, and the Metasploit module based on the script.
TSQL Script – FindDataByKeyword.sql
This script will search through all of the non-default databases on the SQL Server for columns that match the keywords defined in the script and take a sample of the data. For more information please refer to the comments in the script.
Important Note: This script does not require SYSADMIN privileges, and will only return results for databases that the user has access to.
- Download the “finddatabykeyword.sql” TSQL script from:https://github.com/nullbind/Metasploit-Modules/blob/master/finddatabykeyword.sql.
- Sign into an existing SQL Server using Management Studio.
- Open the “finddatabykeyword.sql” TSQL script. Next, set the “@SAMPLE_COUNT” variable to the number of rows that you would like to sample. If “@SAMPLE_COUNT” is set to 1, then the query will also return the total number of rows for each of the affected columns that contain data.
- Then, modify the @KEYWORDS variable to set words to search for. Each keyword should be separated by the “|” character.
- Execute the “finddatabykeyword.sql” TSQL script to sample data from columns that match defined keywords.
Metasploit Module – mssql_findandsampledata.rb
This is my first Metasploit auxiliary module. I recently wrote it with a little help from humble-desser and DarkOperator. The module is essentially a Measploit wrapper for my original TSQL script. Currently, this script will search through all of the non-default databases on the SQL Server for columns that match the keywords defined in the keywords option. If column names are found that match the defined keywords and data is present in the associated tables, the script will select a sample of the records from each of the affected tables. The sample size is determined by the samplesize option.
Before I provide an overview of how the module works, I would also like to thank Digininja. His original Interesting Data Finder module (http://www.digininja.org/blog/finding_interesting_db_data.php) was my starting point for this script. Although, I didn’t use much of his IDF module, I did borrow his method for auto sizing columns. So Thanks! I think it’s a good time to mention that I haven’t submitted this to the Metasploit code base yet, because I would like to finish a few additional options. So enjoy the sneak peak! Hopefully some one finds it useful. Below is an overview of how to use the Metasploit module:
- Download and install the Metasploit Framework. It can be downloaded from:
- Download the “mssql_findandsampledata.rb” module from:
- Copy the “mssql_findandsampledata.rb” file into Metasploit. Below are the locations it should be copied to for Metaploit Framework and Pro:
Metasploit Framework –Windows (Free Version):
Metasploit Pro – Windows (Commercial Version)
- Open a Metasploit console. Important Note: The pro version of Metasploit is not required.
Select the “mssql_findandsampledata.rb” auxiliary by typing: “use auxiliary/admin/mssql/mssql_FindandSampleData”
Set the required configuration parameters as illustrated below. Please note that enabling file output is not required. Also, IP ranges and cider notation can be set via RHOSTS.
Type “show options” to confirm you’ve entered your information correctly.
Type “exploit” to enumerate data from the remote SQL Server and write it to a file. If it fails confirm that the IP address, port, username, and password are correct.
Open file in excel for easy viewing and sorting.
Hopefully someone will find these scripts useful. If anyone has feedback or questions please feel free to email me. I always welcome the opportunity to improve scripts, approach, share knowledge etc. Also, next time I will be releasing a TSQL script and Metasploit module for attacking shared services accounts. In the mean time good hunting.