It always seems that when I give a talk on performance there are 100+ people packed in the room but when it comes to security there’s 10 to 15 people. No one likes patches, unless you’re talking about a puppy named patches or something that’s not related to updates; yet, it’s so important that we all do them.

GDPR is the European law that is intended to empower individuals with the ability to control the surface area of their individual data. GDPR is short for General Data Protection Regulation. This law carries heavy penalties for non-compliance.

Classification could be the first step in understanding your data. Think of this step as placing your data into buckets that are defined by various levels, such as financial or health care.If you don’t classify your data how do you know what’s sensitive and what’s not?

The latest version of SQL Server Management Studio (SSMS) includes tools for data classification. This tool is used to help you identify and classify the column for all types of scenarios; not just GDPR.

It’s very easy to run and use. From Object Explorer, Right-Click a database and choose Tasks. Next click Classify Data…

I’ve spent the last couple years writing, improving, and using this tool at my customers. This health check now has more than 300 data points and even pulls out bad practice and concerns from your procs. As of today, I gift it to the community. #SQLFAMILY

Where to find the tool:

Navigate to www.SQLDATAPROS.COM and click the download free button. Note that there is no registration. If you’d like to send me your email, please feel free. You can also follow me on Twitter at @SQLSME or find me on LinkedIn. A link to my profile is on my blog at www.SQLTECHBLOG.com.

This will take you to my dropbox where you will need to click the download button.

If you don’t have a dropbox account, you can click the “No thanks, continue” button on the bottom to bypass signup.

That’s it! It’s that simple. The only other thing you need to worry about is having .NET 4.5 or higher and possibly the ReportViewer 2015 files. There is no install for this. If you don’t want it anymore, simply delete the files.

You can also get the ReportViewer (2015) and SQLCLRTypes prerequisites from the application zip file on DropBox. This should make finding the items easier since they will be in the zip in a prereq folder. Be sure to install them to allow the tool to run.

How do I use this thing?

Unzip the files to any directory you like. After that, simply open the FitDB application.

Once the application is open, type an instance name into the instance box and then click Test Connection.

Once the connection is successful, the databases list will be populated. Deselect any databases you want to ignore.

Next right click a category on the right and click “Collect Data”.

After you’ve collected data, click the “Reporting” button to view any issues that were found.

If you get an error about “Microsoft.ReportViewer.Common” then you don’t have the Report Viewer 2012 installed. Be sure to grab it from the link referenced above.

Drill into the bar chart or click “View All Data” to get a detailed explanation of each issue.

Please be kind and remember this is being released for the first time and is a BETA.

I wish everyone in the #SQLFamily a happy 4th!

Corrections:

• Fixed a crash on launch from non-USA date formatting
• Corrected blog post link to ReportViewer.2015 from RV 2012. The application uses 2015, not 2012.

I’ve been to hundreds of clients across many verticals and it never ceases to amaze me when I see SQL Server logins that have either a blank password or one that matches the name. What’s even more worrisome is that 60% of these logins were a member of the sysadmin builtin role and 90%+ were granted the DBO role for their database.

A while back I posted some code to the Microsoft TechNet Gallery that can help you identify these logins.

Check it out here: https://gallery.technet.microsoft.com/Identify-SQL-Server-33925ae3

If you like it or find it helpful, be sure to give the gallery a 5 star rating.  : )

A couple considerations are:

• SQL Server passwords are case sensitive.
• The script compares the same case as the login
• The script compares the password to ‘’ (blank)
• Common passwords are not compared but could easily be with a quick tweak. You could also use the brute tool against a suspect login but it’d be better to take the library from it and add it to the query.

It’s easy enough to use an UPPER or a LOWER to the query and just about as easy to add in a list of common passwords. Leave some comments and let me know if you’d like to see a modified version that does a bit more.

Brute force attacks are named as such due to their design. This type of attack has no real “brains”, like you see with social engineering techniques. A password file is fed into a login tool and away it goes. Hammering and hammering until it is out of potential passwords or is successful.

In June of 2015, I added two tools to the Microsoft Technet Gallery which can be used to help achieve better SQL Server security. The first tool is a T-SQL script designed to reveal any SQL Server authenticated logins have either a blank password or a password matching the login name. The second is a console application designed to demonstrate a brute force attack.

The Brute tool

Start by downloading the tool here: https://gallery.technet.microsoft.com/SQL-Server-authenticated-79c063c6. The download includes the full C# source code.

Once the source is downloaded, you can either choose to open the file in Visual Studio (2013 or later) or navigate to the debug directory, where you’ll find the executable. The two files needed to run the demo are Brute.exe and Library.txt.

• Brute.exe – Simulates an attack on the server.
• Library.txt – Contains a list of sample passwords to feed the brute application.

Running the demo

The Brute application has a few switches. To view which are available, run the application with the -? switch.

Switches

• Help                            -?
• Server name -crack:[server name]
• User name -U:[user name]
• Show Library -List

Example

BRUTE -crack:SQL01 -U:Joe

Brute force in action

For the purposes of this blog I have created a user named Steve. To run the demo, we drop to a command prompt and execute the following:

Brute –crack:. –U:Steve

Now that we’ve been attacked, where do we look?

The easiest place to find this type of attack is in the SQL Server error logs. The error most commonly seen is 18456. Though this can appear for many reasons, you’ll want to examine the error reason and look for “Password did not match”. It’s worth noting that this can occur from normal activity, where a user simply mistypes the password; however, in the case of a brute force attack you’ll see hundreds or thousands of these errors.

Now that we know what to look for, how can we protect ourselves?

One simple method is to ensure our SQL authenticated accounts do not have weak passwords and will lock out when too many attempts are made.

You can do this by checking the Enforce Password Policy box when the instance is on a domain.

Too many failed attempts at logging in will result in the following:

While a locked account may cause interruptions in the environment, it’s better than the data being compromised.

In the next part, we discuss implementing SQL Server security best practices using Policy Based Management. Stay tuned!