Part 1: SQL Server Security Best Practices

SQL Server security is perhaps one of the most overlooked facets of database server maintenance. Without taking the necessary precautions, an instance of SQL Server can be ripe for abuse and failure.

The SQL Database Security: User Management guide covers the practical aspects of managing users, groups, roles, and permissions within SQL databases to strengthen and restrict database security. This guide marks the beginning of a series focusing on SQL Server security best practices, addressing essential maintenance and security concerns.

SQL Server Security: Infrastructure

A critical aspect of SQL Server security involves the physical protection of the location housing the SQL Server database. Physical security measures for SQL Server encompass safeguarding the data center environment and the server infrastructure itself. This includes controlling access to the data center through methods such as human guards, key cards, smart cards, facial recognition software, and fingerprint readers.

Data centers must secure not only the servers hosting SQL Server but also other critical infrastructure components like modems, routers, storage arrays, and physical firewall devices. Physical security protocols entail managing hardware devices, software (including firewalls and operating systems), and network infrastructure to protect against human threats, hackers, and potential natural disasters such as floods, hurricanes, or power outages.

Responsibilities for physical security personnel typically include managing 24/7 security surveillance, monitoring climate control to prevent equipment damage from extreme temperatures, implementing fire detection and suppression systems, deploying water leakage detection mechanisms, ensuring critical equipment is connected to Uninterruptible Power Supplies (UPS), and scheduling both hardware and software maintenance.

One advantage of hosting SQL Server in the cloud is that the cloud infrastructure provider assumes responsibility for the physical security of the server hardware.

SQL Server Security: Operating System and Applications

Another critical security consideration is the operating system (OS) on which SQL Server operates, supporting both Microsoft Windows and various Linux distributions. Safeguarding the OS from threats like hackers, viruses, and bugs is essential to maintain SQL Server’s functionality, access control, and data integrity.

Firstly, it’s crucial to regularly apply OS upgrades and security patches as they become available. Testing these updates in non-production environments before deployment ensures stability and identifies any potential issues. When an OS reaches its end-of-life, timely migration to a supported version is necessary to maintain security.

To enhance security, it’s advisable to restrict public internet access to servers hosting SQL Server. Implementing robust OS-level firewalls with defined rules helps control inbound and outbound traffic, limiting access to SQL database servers. Access can also be restricted to specific applications only. Popular firewall solutions for Linux include UFW, nftables, and FirewallD. Additionally, consider using Linode Cloud Firewalls for Linode Compute Instances hosting SQL Server.

Furthermore, removing unnecessary or unused applications and OS features (e.g., email or FTP services) reduces potential security risks.

Lastly, leveraging SQL Server’s Extended Protection for Authentication feature mitigates authentication relay attacks by enforcing service and channel binding. This enhances overall security posture against potential threats.

Securing Server Ports

Another crucial security practice is to close all unnecessary server ports through your firewall and selectively open ports as needed. For instance, SQL Server typically operates on port 1433 by default. Therefore, it’s advisable to allow TCP port 1433 (and 3389 for remote server access) if no other applications require these ports on the server. Similarly, Microsoft Analysis Services commonly uses port 2383 as its default port. Ensure to audit and enable only essential ports used by your development stack.

Consider changing SQL Server’s default listening port (1433) to enhance security. Leaving it unchanged makes this well-known port vulnerable to potential hacking attempts. Utilizing a non-default port strengthens SQL Server’s security posture. You can easily modify this setting using the SQL Server Configuration Manager tool.

SQL Server Add-on Features

SQL Server consists of database engine features that may not be needed by every installation. Some of these components can be a potential target used by hackers to gain access to a SQL Server instance. Therefore, it is good common practice to disable the add-on components and features in SQL Server that are not used. This limits the chances of any potential hacker attack. Some of the features you may consider disabling are the following:

• OLE Automation Procedures: This allows SQL Server to utilize Object Linking and Embedding (OLE) for interacting with other Component Object Model (COM) objects. From a data security perspective, this integration presents higher vulnerability to attacks.

• Database Mail XPs: enables the Database Mail extended stored procedures in the MSDB database.

• Scan for startup procs: an option to scan for automatic execution of stored procedures at Microsoft SQL Server startup time.

• Common language runtime (CLR) integration feature: provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

• Windows (not Linux) process spawned by xp_cmdshell: Has the same security rights as the SQL Server service account, and spawns a Windows command shell and passes in a string for execution. Any output is returned as rows of text.

• Cross-database Ownership Chaining (also known as cross-database chaining): A security feature of SQL Server that allows users of databases access to other databases besides the one they are currently using. Again, if you do not need a particular SQL Server feature, you can disable it.

SQL Server Encryption

A huge area of security for SQL Server is encryption. SQL Server supports several different encryption mechanisms to protect sensitive data in a database. The different encryption options available are as follows:

• Always Encrypted Option - The Always Encrypted option helps to encrypt sensitive data inside client applications. The always encrypted-enabled driver automatically encrypts and decrypts sensitive data in the client applications. The encryption keys are never revealed to the SQL Server database engine. It does an excellent job of protecting confidential data.

• Transparent Data Encryption (TDE) - TDE offers encryption at the file level. TDE solves the problem of protecting data at rest, and encrypting databases both on the hard drive and consequently on backup media. It does not protect data in transit or data in use. It helps to secure the data files, log files, and backup files.

• Column-Level Encryption - Column-level encryption helps to encrypt specific column data; for example, credit card numbers, bank account numbers, and social security numbers.

Data Masking

Data masking is a technique used to create a version of data that looks structurally similar to the original, but hides (masks) sensitive information. The version with the masked information can then be used for a variety of purposes, such as offline reporting, user training, or software testing.

Specifically, there are two types of data masking supported by SQL Server:

• Static Data Masking - Static Data Masking is designed to help organizations create a sanitized copy of their databases where all sensitive information has been altered in a way that makes the copy shareable with non-production users. With Static Data Masking, the user configures how masking operates for each column selected inside the database. Static Data Masking then replaces data in the database copy with new, masked data generated according to that configuration. Original data cannot be unmasked from the masked copy. Static Data Masking performs an irreversible operation.

• Dynamic Data Masking - Dynamic data masking helps to limit sensitive data exposure to non-privileged users. It can be used to greatly simplify the design and coding of security in your application. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to specify how much sensitive data to reveal with minimal impact on the application layer.

Conclusion

Ensuring database security is a critical aspect of database design, operations, and maintenance. It encompasses physical security, maintenance of operating systems and applications, disabling unnecessary features, port management, encryption, and data masking. When implemented effectively, these measures collectively safeguard SQL Server databases, maintaining operational integrity and protecting against attacks.

This guide marks the beginning of a series focusing on SQL Server security best practices. Explore Part 2: SQL Server Security Best Practices for further recommendations in this series.