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. 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.
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 (https://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:
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): C:frameworkmsf3modulesauxiliaryadminmssql
Metasploit Pro – Windows (Commercial Version) C:metasploitappspromsf3modulesauxiliaryadminmssql
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.
Many companies have been in this dilemma before, “if I update and publish this new policy our organization is immediately out of compliance, but no one will make any changes without the policy.” Pondering this, “Yossarian was moved very deeply by the absolute simplicity of this clause of Catch-22 and let out a respectful whistle. (p. 46, ch. 5)” For those that suffer through this during your Policy Update sessions, there a few ways to break out of this cycle: 1. Establish a Grace Period when policies are updated. This is usually established within a policy about policies (feel like the definition of recursion?). Some organizations will issue policies with a Published Date and next to it an Effective Date. This reminds readers about the Grace Period while reinforcing the expectation that compliance is required in the near future.
a. Pros: Staff can work towards compliance by the established deadline without the label of ‘Non-Compliant.’ Project plans, budgets, and resources can be lined up to tackle the changes.
b. Cons: Effective dates may be too soon for some large changes, but having different effective dates for some projects but not everything leads to confusion. If the timeframes don’t run in parallel with budget cycles then there may not be enough available funds for changes that require fiscal resources. The other concern is that during the Grace Period, there may be the perception of having two active policies which may lead to some confusion.
2. Establish, or merge with an existing, Exception Process for non-compliant areas when the policies are published. If there are areas of non-compliance when the policies are updated then an exception must be immediately requested for a temporary acceptance. Part of this exception process will be to establish a plan of attack for reaching compliance.
a. Pros: The exceptions help to prioritize the identified non-compliant areas which may make it easier to see the total cost of compliance; this method is easier for organizations that have strong Project Management departments.
b. Cons: It may be overwhelming for the team reviewing all the exception requests. Especially for those that can’t assess all associative risks (such as business versus IT risks). There will also be overhead to track all the exceptions and the deadlines. Continual exception requests will have to be managed appropriately.
3. Establish a Hybrid Approach. This method takes a little from each above with tweaks to meet the needs of your organization. For example, establishing a short Grace Period for new / updated policies and anything that will need longer must be identified immediately and go through the Exception Process.
a. Pros: A sooner effective date will meet with regulatory requirements quicker. There may be a smaller Exception handling team yet the organization still receives the benefit of using Project Management to handle the outliers.
b. Cons: It is easy for this method to slide more into the Exception Process without the constant enforcement of the effective dates. A shorter Grace Period may result in an unexpected amount of Exception requests depending upon the policy.
Regardless of the method, the most successful implementations negate the Cons listed above with two major factors: (1) Management’s full support (which includes enforcement) and (2) communication. Lack of those two elements often will leave you with a feeling that the wheels are spinning, but you aren’t moving. Of course funding, or the lack thereof, is like a car with no gas – it’s only great if you want to go where you already are. The corporate culture may also dictate which approach is more likely to succeed. Proactive organizations usually try for the Grace Period method while reactive organizations are better suited for the Exception Method. This isn’t a slight against one or another, but in those instances the culture has established tools and workflows designed for one or the other. For example; reactive cultures are usually found in healthcare, especially hospitals, since that’s the name of the game: reacting to the events around them. Financial institutions tend to be more proactive due to many of the existing regulations (SOX, GLBA, etc.). It’s not to say that you won’t find Proactive healthcare institutions (which some are trying to be) or reactive financial organizations. Hopefully adoption of one of the above methods helps during your next Policy Update cycle so you can make changes happen; as behaviors, controls, and other requirements usually won’t change just because they can. “Catch-22 says they have a right to do anything we can’t stop them from doing.
 Heller, Joseph. Catch-22. Simon & Schuster, 1961.
Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.
YouTube session cookie.
Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.
Analytics cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.
Preference cookies enable a website to remember information that changes the way the website behaves or looks, like your preferred language or the region that you are in.
Unclassified cookies are cookies that we are in the process of classifying, together with the providers of individual cookies.
Cookies are small text files that can be used by websites to make a user's experience more efficient. The law states that we can store cookies on your device if they are strictly necessary for the operation of this site. For all other types of cookies we need your permission. This site uses different types of cookies. Some cookies are placed by third party services that appear on our pages.
Discover how NetSPI ASM solution helps organizations identify, inventory, and reduce risk to both known and unknown assets.