blog

7 Best Practices for SQL Server Security

Rajendra Gupta

Published January 19, 2023

featured image for SQL Server security best practices, showing an ethernet cable

According to the 2021 Thales Data Threat Report, 45% of US companies experienced a data breach in 2020. That is a scary statistic, and the truth is the actual number could be even higher as not all breaches are detected. This statistic is evidence enough that the need for more robust database security has never been more critical.

This article overviews best practices for SQL Server security and key considerations for protecting your databases from malicious attacks.

How to secure your SQL Server database

Data security is an essential aspect of successful data governance, and it rests on three essential pillars – Confidentiality, Integrity, and Availability (CIA).

When approaching SQL Server security, protecting data from intentional and accidental access is crucial. Let’s break down the different focus areas and steps you can take to ensure SQL Server security.

1. Operating System Security

To keep your SQL Server database secure, it’s essential to have a safe and secure operating system. Here are some recommendations for protecting your operating system:

Regularly update your operating system with the latest security patches.
Use a network firewall to protect your network from unauthorized access.
Keep the anti-virus software up to date and scan your system regularly.
Disable Internet access on your servers.
Open limited ports for application connections, such as port 1433 for Microsoft SQL Server.

2. SQL Server upgrades

Microsoft releases periodic updates for supported SQL Server versions.

Hotfix: The hotfix covers the known issues to any problem in the SQL Server version.
Cumulative Update (CU): The Cumulative Updates contain hotfixes and improvements.

You should apply regular cumulative updates to both the SQL Server instance and the operating system.

Apply SQL Server updates to test or stage environment and do the testing for any breaking change in your application.
Plan for production upgrade with proper downtime in case of non-HA setups.

3. Follow the principle of least privilege

SQL Server runs under the local account or active directory account. Practice the following best practices:

Rotate the password regularly for the active directory user. For SQL Server on Linux, change the password for the AD account specified in the network.Privilegedadaccount in the mssql.conf utility. This password change does not require any SQL Server restart.
SQL Server has few in-built server and database level roles to delegate administrative privileges.
- Sysadmin: It is the highest permission role, and users can perform any activity within SQL Server.
- Serveradmin: The user can configure the SQL Server settings and shut it down.
- Securityadmin: The user in this role can manage logins, their properties, password, and permissions.
- Processadmin: The processadmin user can terminlate SQL Service.
- Setupadmin: It can add and remove linked servers and replication.
- Diskadmin: The user can manage the disk files.
- DBcreator: Users can create a new database or alter or drop any existing one.

You should provide high privileges such as sysadmin to authorized people and limit people’s access with custom roles and permissions.

4. Password security

We can add SQL Server logins and active directory users in SQL Server for accessing the database. In the active directory, password security is managed by the group policy, and the AD administrator manages these permissions. However, if you add a SQL login, you can have the following password security options:

MUST_CHANGE: Once you create a new SQL login and enable the MUST_CHANGE flag, the user has to change the password once they log in for the first time.
CHECK_EXPIRATION: Users need to reset their password after a certain period.
CHECK_POLICY: If you have an active directory (windows) policy on the computer running SQL Server, the AD policies will also be applied to the login.

5. Monitoring

Monitoring is always a critical aspect of securing the database. Do the following for monitoring SQL Server.

Continuously monitor the failed login attempts from the SQL Server error log. The error log gives the client’s IP address and login name from where the connections are oriented.
Monitor the permission changes, creation, or deletion of the database objects

6. Protect sensitive data

Protecting sensitive data is critical for the business and may bring various consequences if mishandled. The sensitive data can be:

Personal Information and Identification (PII) data
Critical business information such as financial data.

You can use the following tools in SQL Server for data protection:

SQL Data Discovery and Classification: SQL Server Management Studio provides a tool called Data Discovery and Classification to discover, classify, label, and report sensitive data.
Configure row-level security: You can configure the row-level security to restrict user access to specific rows while executing a query.
Dynamic Data Masking: Dynamic Data Masking enables users to limit sensitive data exposure by masking full or partial data. For example, masking the email id [email protected] as [email protected].
Configure Transparent Data Encryption: Transparent Data Encryption (TDE) protects database files and their backup from unauthorized access. It uses an encryption key and certificates to encrypt the data or backups.
Backup Encryption: DBA can use backup encryption by using an encryption algorithm and encryptor (asymmetric key or certificate).
Always Encryption: Always Encryption is a mechanism to protect customer-sensitive data. It encrypts data inside the client application. The encryption key or certificate is also not revealed to the database engine. Therefore, data can be viewed from the application. Users with high privileges, such as database administrators and system admins, cannot view the data. Therefore, Always Encryption reduces data theft from malicious insiders.
Static data masking: Static data masking uses the data transformation rules to obfuscate the data.

7. Configure the non-default port for SQL Server

SQL Server uses port number 1433 for database connections. It is a standard port and is widely known. Leaving SQL Server on the default port is a security risk. Therefore, always use a non-default port to harden the security.

Wrapping Up

Hopefully, this article has provided a good starting point for your own SQL Server security considerations. Remember, when it comes to information security, you’re only as strong as your weakest link. Having a holistic and comprehensive approach to security for any database is the best way to identify and mitigate weaknesses or gaps in your security practices.

Data encryption is one of the most important security measures for keeping your data as secure as possible. Transparent Data Encryption (TDE) is supported on SQL Server and is an excellent encryption feature, so be sure to check out the steps for enabling it.

Stay on top of all things SQL Server by subscribing to our newsletter below.