SQL Server Security: Best Practices 2021

Introduction

In today’s world where digital data is increasing exponentially by the day, the administration and querying of that data are also becoming more and more complex. This huge amount of data is stored in the form of collections within a database. However, in order to create, read, update or delete that data, the database administrators needed the ability to query the database.  

The ability to query multiple records within a database using a single command is of paramount importance and was immediately identified by the modern computing world. This is where (Structured Query Language) SQL technology came into place and was adopted as the primary querying language for interacting with databases.

In this article, we will discuss SQL Server Security in detail.

What is Microsoft SQL Server?

SQL stands for Structured Query Language. It is used for relational databases. A SQL database is a collection of tables that stores a specific set of structured data. The adoption of SQL technology by every industrial sector and compliance standard organisations such as ISO lead to the creation of Microsoft SQL Server.

SQL Server is a SQL database engine created by Microsoft that serves as a relational database management system (RDBMS). SQL Server is one of the market leaders in database technology thanks to its wide range of data querying features. Like all other RDBMS software, SQL server also has SQL as its underlying technology.

What is SQL server security?

SQL Server security is the encryption capability built into the database engine. Databases are used to store a wide variety of data. For example, a social media web application may use a Microsoft SQL Server database to store login credentials. A banking application may use it to store financial authentication information and transactional information. A healthcare institution may store these databases to store medical records or other sensitive data.

Why is securing SQL servers important?

Due to the storage of above mentioned sensitive data in SQL Server databases, it is important that the security of these servers is ensured. SQL Servers are usually subjected to many cyber attacks. An attacker may gain hold of a SQL database by exploiting an SQL injection. Or the attacker may gain access directly to SQL Server database shell by connecting to port 1433 which is the default MSSQL Server port because of easily guessable passwords.

Organisations that store internet users data in their databases have a compliance obligation to ensure that the user’s data stays unharmed. Internet users who register on web applications in order to avail themselves a certain service, trust that organisation with their data. An example can be the cardholder data that is processed by an e-commerce web application.

If that data is compromised, the organisation may not only suffer financial damage from the cyberattack but also lose its credibility and be blacklisted by regulatory authorities to conduct any further business. So every organisation that uses database technology to store users data, needs to maintain the CIA triad i.e maintain the confidentiality, integrity and availability of that data.

SQL server cyber threats

SQL Servers face many cyber threats and attackers come up with new ways of exploitation every day. Some of these cyber threats are internal and more dangerous than others and must be prevented at all costs. In this section, few cyber threats and their attack methodology is discussed

Unnecessary database privileges

SQL Server database users can have different levels of privileges. Abuse of privilege is one of the most common ways for data leakage or database compromise. Statistics show that more than 75% of the attacks on company databases took place because some ex-employee took vendetta on the company by using their unnecessary privileges that should have been revoked.

SQL Injection

SQL injection is a web application attack in which a malicious actor enters a malicious payload in the web application to modify the original backend SQL query to something else that benefits the attacker. SQL Injection can grant attackers access to the data that should not be available to them.

This data can include data that belongs to other users of the web application including their login credentials, Personally Identifiable Information (PII), Card Holder Data (CHD, for a banking web application) etc. It can also allow attackers to directly login into accounts that do not belong to them, this type of SQL injection attack is also known as Boolean SQL injection.

While SQL injection may not seem a direct attack to a SQL Server database since it exploits vulnerabilities in a web application, If attackers manage to find SQL injection in a web application, it typically implies that cybercriminals can get unlimited access to any data stored in the database. 

Attackers typically accomplish their goal by terminating the backend query text string and adding their new payload at the end of the command. The inserted commands manipulate the query into returning data that the initial query was not intended to return. Since there may be additional strings after the attacker’s payload, attackers typically use the comment symbol to make the later query command irrelevant.

Impact of a SQL Injection attack

If a malicious cybercriminal successfully exploits SQL injection against a SQL Server via a web application, the consequences can result in the attacker gaining unauthorised access to sensitive data such as passwords, credit card details, medical records etc. The impact is not limited only to data leakage but the companies under attack also suffer reputational damage and high monetary fines by regulatory authorities. 

In some cases, SQL injection attacks can be escalated to perform a denial of service attack which compromises the availability of services or to gain persistent remote control of the server that may stay undetected for a long period of time.

Insufficient auditing

Data security and protection compliance authorities require the companies to maintain database audit trails. All events that take place in the database must be recorded and registered with appropriate amounts of details. Failure in complying with audit trail requirements can leave a company blind during a cyber attack when unauthorised data is being queried from SQL server databases. 

Insecure storage of database backups

Backing up SQL Server databases at regular intervals is a recommended practice. However, if the backups are not stored at a secure SQL server enforced with proper access controls and the backups are compromised, the attackers can find an easy win without having to exploit the SQL server at all and gaining unrestricted access to all the data. Statistics show that numerous database compromises have occurred due to the insecure storage of database backup.

It’s a good practice to make backups of proprietary databases at defined periods of time. However, surprisingly database backup files are often left completely unprotected from attack. As a result, there are numerous security breaches happening through database backup leaks.

Database vulnerabilities and misconfigurations

It is so common for organisations to suffer from database attacks due to a human error made in SQL Server misconfiguration. Database administrators leave SQL server databases configured to default settings and configuration parameters. These misconfigurations are low-hanging fruit for malicious actors who are quite seasoned in their profession and know-how to exploit these misconfigurations against a company.

Unmanaged sensitive data. 

The data stored in the SQL Server database is made up of sensitive information and hence an accurate inventory of it must be maintained. Unattended sensitive data added on a daily basis may fall easy prey to attackers if similar security controls are not enforced on the databases storing that data. 

Security expertise of database administrators

One of the other major reasons for databases to get compromised is the lack of security knowledge. SQL Server comes with sufficient security controls that can be implemented to harden the database server. However, if the security personnel are not knowledgeable enough to implement those security controls, the SQL server remains unprotected.

Denial of service attack

Denial of service attack against a SQL server database engine does not cause sensitive data leakage. However, it still has a significant impact since it causes the data to be made unavailable to all users. 

So if a user tries to log in to a web application and the database associated with the web application is under a denial of service attack, the user will not be able to authenticate to the web application. The company may face downtime which results in loss of time and money.

Microsoft SQL Server security best practices

No matter how experienced a database administrator is. SQL Server security is always a serious concern for them. The main reason for this concern is that Microsoft SQL Server is one of the most used database management systems which makes it a high-rewarding target for attackers.

Due to the above reasons, securing the SQL server is very important for any organisation and must follow certain best practices to remain protected. DBAs can achieve this goal by implementing the variety of security features incorporated in SQL Server. 

Preventing SQL Injection attacks

Statistics reported in the year 2019 that 77% of the web applications attacks are SQL injection attacks. The attackers utilise SQL injection by inserting arbitrary SQL payloads into the application and gain access to unauthorised data.

SQL injection attacks can be prevented by validating the input fields in a web application. Web developers should write parameterised queries and make sure that certain special characters that make up an SQL query are not allowed during input validation.

Run multiple SQL server security audits 

Data protection regulatory authorities require companies to perform SQL Server security audits. These audits help identify any gaps that were left during the process of securing the SQL server. These audits also help in finding forensics evidence regarding a potential cyber attack.

Before starting the auditing activity, it is important to identify what is in the monitoring scope of an audit.

To begin with, define what you want to audit. You may want to monitor the following in your server audits. Activities that can be included in an audit include user logins, server configuration changes, schema changes, permission changes etc.

Auditing not only fulfils compliance requirements but also improves the performance metrics of a company’s database and network. For example, if a query fails to run, an audit can help identify the underlying cause. Does the error imply a security threat? Is the query not optimised? 

Similarly, failed login attempts, unusual and frequent modifications, deleting of databases and tables and assigning superuser privileges to a previously unknown user are all implications of a potential breach. All these indicators can be identified by enabling and performing regular security audits.

Enforce the usage of strong passwords

All the database administrators must be instructed to use strong passwords for their SQL server account. Usage of strong passwords is an important step in securing the SQL server as it helps prevent brute-forcing and dictionary attacks against the database server.

The password policy should enforce the usage of at least 12 characters including uppercase, lowercase, numerical and special characters along with avoiding the usage of easy to guess passwords like “password” or “123456”.

It is also a good practice to change the passwords regularly and store them safely in a password manager with a strong master encryption key.

Keep things simple

Having unnecessary software and extra applications allows hackers to exploit your SQL database server. Further, multiple applications are tough to manage and soon get outdated. 

We all know how outdated or unpatched applications introduce security holes in the operating system, inviting attackers to run unauthorised code on a SQL server via an authorised path (SQL injection).

Limit the installation of the SQL database components and features to the ones that are necessary for certain tasks. This will reduce the resource utilisation by the database and simplify the administration, thus minimising the security risk. 

Apply the principle of minimal privilege

The principle of least privilege is a well-known phrase used in the field of security. It implies that an account should only be given the privileges that are required for a user account to perform its compulsory operations. 

In order to implement this principle, it is important to identify what users should be given which privileges on the database. It is also important to run the SQL database service as a separate user so that if the SQL server is compromised, the attacker does not gain administrative privileges on the server.

Robust backup strategy

Database backups at regular intervals should be taken and stored in different locations. Database backups should be stored in an encrypted format with best practices of cryptographic controls in place.

Monitoring the SQL server

SQL Server monitoring tools should be used in order to scan the database application processes in real time and monitor the changes as well. Email notifications should be sent to relevant personnel informing them of database events that are worth noting. 

These monitoring tools should promptly detect SQL errors, sessions and any event pertaining to unauthorised changes or data leakage.

Also since the monitoring tool is monitoring sensitive software like SQL servers, the monitoring tool itself is also under the attacker’s radar. So in order to ensure complete security, the monitoring tool itself should be implemented by following recommended best practices.