Even with security measures in place, SQL Servers can fall prey to cyberattacks. The threat actors target the servers to gain access to the sensitive data locked away in the SQL databases as well as the backup files. As per a report by Cyber Security Experts, “Personal data of nearly 4 million individual filers and 700,000 businesses in the South Carolina Department of Revenue (DoR) was breached in August 2012. The attacker found worthy loot in the form of DoR database backup and copied the 74 GB database.” The department’s major focus was on investing in antivirus technology aimed to block an initial attack. When this line of protection was breached, they became defenseless against such attacks. However, if the organization had invested in security protection measures such as backup encryption, it could have prevented such data breaches.

Even with security measures in place, it is not an easy task to protect the SQL databases as the attackers primarily look for vulnerabilities to target the databases. Therefore, it is pertinent for database administrators (DBAs) to keep an eye on the vulnerabilities in order to protect the SQL databases from being attacked and compromised. DBAs also need to make sure that databases in downstream environments, such as dev and QA are protected, if they contain sensitive information. Redgate’s Provision can help you quickly deliver masked database to these environments. 

In this article, we’ll be mentioning some common vulnerabilities in SQL Server. Also, we’ll discuss how to assess your SQL Server for potential vulnerabilities and perform a speedy recovery of databases if compromised.

Some common vulnerabilities in SQL Database

Following are some common vulnerabilities you need to be vigilant about to protect your SQL database from being attacked.

SQL Injection

SQL Injection, also referred to as SQLi, attacks allow hackers to manipulate SQL (Structured Query Language) queries to exploit vulnerable databases. Essentially, SQLi allows threat actors to send unauthorized data to a web application, connected to databases, as part of a query or command. Executing the query or commands enables attackers to gain access or make changes to the sensitive data stored in SQL databases.

In a nutshell, SQL Injection attacks occur due to improper security encoding (or input validations) in the application or web form. As user-applied input is the contributing factor behind the SQLi attacks, controlling and inspecting user input for attack patterns can help prevent such attacks. Also, configuring a web application firewall (WAF) can help identify SQL injection attacks. Note that this is important for any SQL database, not just SQL Server.

If you use SQL Monitor, this article explains how to capture SQLi attempts.

Human errors

As per the 2021 Verizon Data Breach Investigations Report (DBIR), human errors account for 85 percent of all data breaches. Unintended actions or lack of action by an organization’s employees and users may lead to security breaches. For instance, failing to use a strong password, not installing critical software security updates, and downloading a virus-infected attachment are some human errors that contribute to accidental database breaches.

Reducing instances of human errors and educating stakeholders on security basics and best practices help them in making better, informed decisions.

Weak passwords

Weak passwords are the most common contributor of database security breaches. According to a report published by the cybersecurity division of the Chinese company Tencent, “Thousands of Microsoft SQL Servers (MSSQL) exposed to the Internet were infected with the malware gang MrbMiner that used brute-force attacks to gain access to administrator accounts that are configured with weak passwords.” Therefore, it is crucial for organizations running SQL databases on the cloud or any network to use strong passwords to combat brute-force attacks.

Extensive user privileges and installation

Although SQL Server is a highly secure database platform, certain factors like installing all the SQL database components, assigning too many permissions and roles, using multiple AD groups, etc., can leave security gaps in the system and make databases vulnerable to attacks.

You must keep these considerations in mind:

• Limit installation to only the components required by a database to perform its tasks.
• Use a least privileged service account to prevent exposing your database to security attacks.
• Connect to SQL Server using Integrated Security (i.e., Windows Authentication).
• Use encryption features available in SQL Server to keep the data (mdf) and log (ldf) files secure against unauthorized access. The features include Transparent Data Encryption (TDE), Always encrypted, etc.
• Mask sensitive databases in downstream environments like development and QA.
• Disable the SA login account in SQL Server as every database administrator and hacker knows about it.

No encryption or poor encryption

If your database or data within the database is not encrypted, the hackers can track the points of interconnection between the network and the machine to compromise the database. So, to avoid such instances, it is important that DBAs keep the SQL database or the data within the database in encrypted form. It is applicable for both the primary database and the backup files.

Missing security updates and patches

Malware attacks remain a persistent threat for databases that are missing security patches or running on older versions of SQL Server. If DBAs forget to keep the SQL Server up-to-date with the latest security patches, then the server becomes vulnerable to malware attacks.

Weak audit trail

An audit trail helps organizations track and log events performed on a server level or database level. The audit trails can be analyzed for any possible security policy violations. Also, they can aid in forensic analysis in the event of a security breach. However, organizations having weak (or non-existent) audit records may fail to determine application errors or fraudulent practices, making the databases vulnerable to attacks.

Maintaining a strong audit trail in SQL Server can help your organization run smoothly and securely. But before audit trail implementation, try understanding SQL Server Security Audit Basics.

Denial of service attack (DoS)

DoS is a security threat that cybercriminals use to shut down a system or network, preventing users from accessing the SQL database and its data. DoS can be achieved by exploiting vulnerabilities to crash a database server and flood the machine with requests or malware that obstruct the database server from providing data to the intended users.

One of the biggest examples of DoS attacks is SQL Slammer. It is a 2003 computer worm that caused denial-of-service attacks and exploited a buffer overflow vulnerability in Microsoft’s SQL Server. After a decade of no activity, the SQL Slammer worm became one of the most prevalent attacks between November 28 and December 4, 2016, that infected unpatched SQL Server version 2000.

Reviewing the use of firewalls, upgrading your SQL Server versions, and applying security patches are some of the actions you can take to protect your databases against DoS attacks (like SQL Slammer).

How to assess SQL Server for potential vulnerabilities

For SQL administrators, it is crucial to incessantly check the databases for any potential vulnerabilities to improve database security. For a long time, DBAs had to use external tools or write customized scripts to determine, track, and remediate these vulnerabilities. Fortunately, SQL Server introduced a built-in solution, the Microsoft SQL vulnerability assessment (VA) tool.

This tool helps scan the database to discover, monitor, and address security issues in your environment. It uses a knowledge base of rules warning about deviations from Microsoft’s recommended best practices, such as unprotected sensitive data, granting excessive permissions, and misconfigurations. It highlights security issues that pose the biggest threats to your database and its valuable data.

You can run the VA tool in SQL Server Management Studio (SSMS) or manage it using PowerShell cmdlets. You run VA by right-clicking on the database you want to scan and then clicking on Tasks > Vulnerability Assessment > Scan For Vulnerabilities.

Figure 1 – Running SQL Vulnerability Assessment

After completion of the scan, a scan report (see the below image) is displayed, presenting issues detected in the database and their respective severities – High, Medium, and Low. It also suggests actionable remediation information to solve the issues.

Figure 2 – Vulnerability Assessment Results for SQL Database

(Source: https://docs.microsoft.com/en-us/sql/relational-databases/security/media/sql-vulnerability-assessment/3-ssmsscanresults.png?view=sql-server-ver15)

You can drill down each on failed security check displayed in the scan report to understand the reason behind the failure. Subsequently, you can use the actionable steps suggested in the report to resolve the issue.

Figure 3 – Failed Security Checks & Remediation Action

(Source: https://docs.microsoft.com/en-us/sql/relational-databases/security/media/sql-vulnerability-assessment/3-ssmsscanresults.png?view=sql-server-ver15)

How to test backups and restore the database

If your SQL database is compromised or attacked by threat actors and becomes inaccessible, backups can help restore the database to a point in time within the retention period.

But are you certain that the backups can be restored successfully?

A recent study shows that “58 percent of backups fail, leaving data irrecoverable in the event of an outage due to cyberattack”. Besides this, other factors like a damaged tape drive or disk drive may also prevent you from restoring the backup. This is why it is crucial to test backups frequently to ensure they are error-free and restorable.

Backup verification for database backup testing

There are several built-in backup verification methods available in SQL Server to test if the backups are valid and can be used to restore the database. Let’s discuss the two backup verification methods:

1. CHECKSUM

Create backups with CHECKSUM to check the integrity of the data in the backup (see the below example).

When used in the backup command, CHECKSUM tests the page checksums on the data pages being backed up. The backup operation stops if a bad checksum is found and returns a message identifying the bad page.

2. RESTORE VERIFYONLY

Another option is to use RESTORE VERIFYONLY to ensure whether a backup file is valid or not. This option helps validate that what was written to the backup file matches the CHECKSUM.

Both RESTORE VERIFYONLY and CHECKSUM options are available in SSMS as “Verify backup when finished” and “Perform checksum before writing to media”, respectively. The options help validate that the database backup is complete and can be restored when needed. However, RESTORE VERIFYONLY does not verify the structure of the data.

Figure 4 – Reliability features of back up

Use these options when scheduling to test backups automatically.

Database restore drills

The best way to know if your backups are good or not is to perform a regular database restoration drill. The drill requires performing two steps. The first step is to restore the database from regular backups, and the second step is to run consistency checks on the restored database. You can write a T-SQL script or create an SSIS package to restore the backups and perform consistency checks.

A better alternative is to use DBATools, an open-source PowerShell module that provides several commands to perform administrative tasks in SQL Server. The command Test-DbaLastBackup helps test the most recent full database backups. It identifies the last set of full backups and transaction logs and restores all the files. Once the backup is restored, a CHECKTABLE is applied to check the integrity of database objects. All of can be accomplished this using a single Test-DbaLastBackup PowerShell command.

See SQL Backup from Redgate for a high-speed database backup, verify, and restore tool. This tool helps automatically restore and validate the backups. It also helps keep database backups protected with 256-bit AES encryption.

Use high availability (HA) and disaster recovery (DR) solutions

Use the HA and DR solutions like Log Shipping, Database Mirroring, SQL Server Always On Availability Groups, etc., to perform database recovery. These solutions help maintain copies of the SQL Server databases at multiple locations. If any unforeseen event occurs, data availability can be obtained as a secondary replica takes over the primary database.

See SQL Server High Availability and Disaster Recovery Plan for detailed information about HA and DR options.

Conclusion

This article covered how organizations that rely upon Microsoft SQL Server for deploying databases as the major data stores are vulnerable to attacks. It also gives advice on how organizations can assess SQL Server databases for vulnerabilities and gave suggestions to resolve security issues using the built-in Microsoft SQL vulnerability assessment (VA) tool. Additionally, it recommended the way forward to test database backups and restore databases.

You can test backups using the backup verification methods: RESTORE VERIFYONLY and CHECKSUM. To restore the backup, you can use the Test-DbaLastBackup PowerShell command from DBATools. If you write your own script, you can use a SQL Agent job. Another alternative is Redgate’s SQL Backup. As a last resort, failover to a secondary database if the primary database is inaccessible.

Remember, what happened once can happen again. So, it’s important to keep your database and server safe. Here’s a series on SQL Server Security you can explore to protect and secure your SQL databases.

References Used:

A Review of Critical Security Challenges in SQL-based and NoSQL Systems from 2010 to 2019

Vulnerability assessment for SQL Server

Reducing Recovery Time When Restoring a Database