Audit failed SQL Server logins – Part 1 – distributed queries, brute force attacks, and SQL injections

Failed SQL Server logins are common in various scenarios. Accidently mistyped credentials (user name or password), changed permissions, or expired password are some of the benign reasons for failed SQL Server logins. On the other hand, there are malicious failed logins – unauthorized attempts to access confidential data stored on a SQL Server instance, that are more of a concern

Motives for SQL Server attack attempts can also vary, depending on the information that is the target of the attack and the skills of the attacker, such as:

• Gaining access to the confidential information such as credit card numbers, or financial transactions
• Malicious access and data change/deletion, simply because the attacker wants to state “I can”
• Cross site scripting attacks (XSS) in which malicious scripts are injected into otherwise benign and trusted web sites

The most common attack methods vary on the attacker skills and vulnerability of the target system

Ad hoc distributed queries

SQL Server uses two functions as parts of ad hoc distributed queries to connect to the remote data sources that use OLE DB: OPENROWSET and OPENDATASOURCE

The OPENROWSET function includes all information required to access remote data. When the function is called, the call contains the user name and password required for data access, among other parameters. Here is an example:

SELECT a.*
FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=Remote1;UID='
 manager ';PWD=' MyPass '', pubs.dbo.customers) AS a
ORDER BY a.fname

Similarly, the OPENDATASOURCE function contains login information (the user name and password) in its connection string. If such query gets in the wrong hands, e.g. by interception, the login information embedded into the query can be maliciously reused

These ad hoc distributed queries should be used only to reference rarely accessed OLE DB data sources. Otherwise, a linked server should be used for the data sources that will be accessed more than several times

Brute-force attacks

Brute-force attacks consist of systematically checking all possible passwords until the correct one is found

There are two authentication modes used in SQL Server: Windows authentication and mixed mode (enables both Windows authentication and SQL Server authentication)

The first mode is less vulnerable to brute-force attacks as the attacker is likely to run into a login lockout (the Account Lockout Policy feature) after a finite number of attack attempts. Every production environment, if using Windows Authentication mode, should utilize the lockout policy feature, as it makes brute-force attacks impossible

When it comes to SQL Server authentication brute-force attack vulnerability, the situation is not so favorable. SQL Server Authentication has no features that allow detecting when the system is under a brute-force attack. Moreover, SQL Server is very responsive when it comes to validating the SQL Server authentication credentials. It can easily handle repeated, aggressive, brute-force login attempts without negative overall performance that might indicate such attacks. This means that the SQL Server Authentication is a perfect target for password cracking via brute-force attacks

Also, brute-force methods are evolving with each newly introduced encryption and password complexity method. For example, attackers that use rainbow tables (the pre-computed tables for reversing the cryptographic hash values for every possible combination of characters) can easily and quickly crack any hashed password

In order to protect your SQL Server from brute-force attacks, you should consider the following:

• Don’t use SQL Server Authentication mode – force the attacker to hit the login lockout via Windows Authentication
• In case you need to use SQL Server Authentication mode, disable or remove the SA login – that way the attacker must guess and pair both the user name and password

SQL injection attacks

Using a SQL injection attack, an unauthorized user “injects” SQL code in fields where SQL Server expects data values, and uses an available database connection to access your data. This type of attack can be extremely damaging because it lets the intruders execute commands directly against your database

A SQL injection attack utilizes the way dynamic queries are created – by concatenating, instead of validating and using the submitted parameters

Here’s an example of a simple validation of a user authentication:

SELECT FROM tblUser WHERE userid = ‘” + Request.QueryString[“UID”] + “’ AND password = ‘” + Request.QueryString[“PSW”] +

It can be injected with the following, by passing the value for UID:

whatever ‘ OR 1 = 1 ––

The resulting dynamic SQL will be:

SELECT FROM tblUser WHERE userid = ‘ whatever ‘ OR 1 = 1 ––’ AND password =

The dynamic SQL will be executed, as the WHERE condition is always true (1 = 1):

SELECT
FROM tblUser
WHERE userid = ' whatever '
	OR 1 = 1

A real SQL injection attack can be more complex and cause more damage than the previous example. The following will not just pass the validation, but will also insert an additional DROP TABLE command:

‘ whatever ‘ OR 1 = 1; DROP TABLE tblUSER –

Other actions the attacker might take include creating a new user login, querying against known tables, or even locating and defining other tables and SQL Server objects

To prevent the SQL injection attacks, you should consider the following:

• Use an input validation technique to authenticate user input against a set of defined rules for length, type, syntax
• Ensure that authenticated users have the least permissions necessary to perform their tasks
• Use parameters and stored procedures instead of concatenating

Auditing failed logins

In the next part of the Audit failed SQL Server logins sequel, we will describe and analyze the native SQL Server tools and methods to investigate failed login data in order to detect the potential attack attempts

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.

Useful resources:
Choose an Authentication Mode
Configure Your SQL Server Attack Surface
Rainbow Hash Cracking
Cheap GPUs are rendering strong passwords useless

This article is part of a series

See all parts         See next part