Data is a critical asset of every organization, and poorly-secured databases are too often to blame for security breaches. This article details SQL server security best practices, as well as essential security considerations for protecting your databases from malicious attacks.
Data security is comprised of three essential pillars – confidentiality, integrity and availability (CIA) and deals with specific processes to protect data from intentional and accidental access. Let’s break down the different areas and steps to take when approaching SQL Server security, one of the most popular relational databases in use today.
SQL Server security best practices
1. Ensure the physical security of your SQL Server
When it comes to SQL Server security, physical security cannot be overlooked. Physical security refers to limiting unauthorized access to data centers or other physical server components. For example, you can implement a locked room with restricted access using a smart card, fingerprint or face recognition. You can also configure a restricted network segment for SQL Server.
Data centers house an organization’s infrastructure such as routers, switches, servers, firewalls and storage devices. Physical security deals with protecting hardware, software and the network from any unauthorized access or natural disasters. It can involve the following areas:
- Securing the premises and equipment access for authorized personal only
- Maintaining access control systems
- 24x7x365 vigilance using on-site security guards or CCTV monitoring
- Uninterruptible power supply (UPS)
- Having a fire alarm system and an aspirating smoke detection system
- Having an active water leakage detector panel
- Rodent repellent systems
- Fire suppression systems
- Controlling and monitoring temperature and humidity
- Periodic hardware maintenance
2. Protect your operating system
SQL Server is installed on top of an existing operating system such as Windows or Linux. Therefore, operating system security plays a vital role in SQL Server security. Below are some recommendations for protecting your operating system:
- Apply regular operating system security patches and service packs
- Define an operating system patching policy that applies patches on lower environments followed by production patching
- Always use stable and supported product operating system versions. For example, Microsoft discontinued support for Windows Server 2003, therefore, you should not use it for database hosting
- Do not allow internet access on your database servers
- You should uninstall, stop or disable unused applications and drives to ensure fewer avenues for potential attacks
- Implement a firewall with restricted access to database servers so that only application servers requiring access to the database server should be allowed to pass traffic from the firewalls
- Open specific ports in the firewall. For example, by default, SQL Server runs on port 1433. Therefore, you can allow TCP port 1433 and 3389 for remote server access if no other application runs on the server. Similarly, the analysis service uses default port 2383 as a standard port. For a complete list of ports in SQL Server, refer to this documentation on ports used by SQL Server. You can also use SSL or TLS certificates to secure access to SQL Server. These certificates can encrypt data transfer between SQL Server and client applications. SQL Server configuration is required for a self-signed certificate or the certificate issued by the certificate authority (CA). You can refer to the article: How to set and use encrypted SQL Server connections for more details.
- Leverage the Extended Protection for Authentication option to prevent an authentication relay attack using the service binding and channel binding. To enable extended protection, go to the SQL Server Configuration Manager, expand the screen, right-click on Protocols and then go to Advanced, Extended protection. Note, by default, this is turned off.
Similarly, you can force the encrypted connection to SQL Server using the following option.
You can also refer to extended protection for more details.
3. Reduce your surface area
The SQL Server surface area is comprised of database engine features that provide additional functionality such as sending emails. These components might be a potential target to gain access to SQL Server for malicious activities. Therefore, you should disable the component and features in SQL Server that are not being used, as this will limit the chances of a potential attack. The major components that you can review and disable are listed below.
- Scan for startup procs
- OLE Automation Procedures
- CLR enabled
- Cross DB ownership chaining
- xp_cmdshell
- Database Mail XPs
You can refer to this article for detailed information on server configuration options.
4. Configure a server to listen on a different port
Microsoft SQL Server uses the default port 1433 for all database connections. It is a common security risk in many database environments because database professionals typically do not change the default port. It is a well-known port, and intruders can utilize this opportunity to access SQL Server. Therefore, you should use a non-default port to harden your SQL Server security. You can modify this using the SQL Server Configuration Manager.
5. Adjust SQL Server authentication
The protection of your data depends upon the ability to authenticate access to specific data. SQL Server provides two options for database authentication.
- Windows authentication
- Windows and SQL authentication (Mixed-mode)
To verify the server authentication model, right-click on the SQL Server instance and navigate to Security.
The Windows authentication uses active directory accounts for authentications. You can have a centralized policy control for password complexity, password expiration, account lockout and active directory groups in the active directory. Therefore, you should use Windows authentication instead of SQL Server authentication. Here, the user connects using a Windows account, and SQL Server validates the credentials using the Windows principal token. It uses the Kerberos security protocol for authentications. Refer to authentication mode for more details.
However, if you need to use the SQL Server logins, you can still enforce the password policy as highlighted below.
6. Remember service account permissions
SQL Services uses a Windows account to run its services. You should not use the high-privileged, built-in accounts such as Network Service or Local System. Similarly, for a domain service account, you should assign role-appropriate privileges.
Therefore, I would recommend referring to configure Windows service accounts and permissions for more specifics about SQL Server service accounts permissions.
7. Apply SQL Server patching in production
Microsoft releases regular service packs (SQL Server 2016 or earlier) and cumulative packs (SQL Server 2017 onwards) for fixing known issues and security issues. Therefore, you should always plan to implement SQL Server patching on the production instances. However, do not directly apply patches on production instances. Always apply them first in the test environment, validate and plan for production deployment.
You can refer to the latest updates for Microsoft SQL Server to find details on the latest service packs and cumulative packs.
8. Secure your backups
When it comes to SQL Server security, securing your backups is critical. Typically, database professionals do not consider all the requirements for securing database backups. Database backup is the process of creating a copy of the operational state, architecture and stored data of a database. Therefore, it is equivalently important to protect it. It means restricting access to backup files and encrypting them properly. When it comes to securing backups, here are a few reminders.
- Do not provide everyone with rights on the backup folder to create, view, modify and delete backup files
- Use database backups with encryption; refer to this article on backup encryption for more details
9. Remember SQL Server encryption and data masking techniques
A key area in SQL Server security is encryption. You can use various encryption mechanisms to protect sensitive data in your SQL Server database. The different encryption options are as follows.
- Always Encrypted: The always encrypted technique helps to encrypt sensitive data inside the 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 protects confidential data.
- Transparent data encryption (TDE): The TDE encrypts data at rest. 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 and social security numbers.
- Static data masking: Static data masking replaces the sensitive data using the defined data transformation rules.
- Dynamic data masking: Dynamic data masking helps to limit sensitive data exposure to non-privileged users.
- Row-level security: The row-level security restricts the data row access.
10. Make the system administrator’s password complicated
If you are using SQL authentication, it creates a login SA with the sysadmin permissions. To safeguard your SQL Server, do the following.
- Rename the login named SA to a different name
- Disable the account if you don’t plan to use it
- Use a complex password
- Do not allow applications to use the SA account in the connection strings
11. Audit database logins
Auditing is often overlooked when it comes to SQL Server security. You should do regular SQL Server auditing for failed logins. You can use the default login audit mechanism for reviewing the accounts. For example, suppose any user is trying to connect to SQL Server with a high privilege account. In that case, you can see the login failure and incoming request (client) IP address. This can help you capture and eliminate suspicious activity.
You can use the extended events, SQL trace, change data capture, triggers (DDL, DML or Logon), database or server-level audit specifications for the SQL Server audit.
12. Be mindful of server-level and database permissions
Database professionals should be careful when assigning server-level or database-level permissions. Sometimes, we see that developers get sysadmin at the server level or the database owner’s permissions at the database level. These are the highest permissions a user can have on an instance or database level, respectively.
- Refer to fixed server-level roles to understand fixed server-level roles and their capabilities.
- Refer to database-level roles to better understand fixed database-level roles.
13. Turn off the SQL Server browser service
SQL Server uses the browser service for the named instance. It listens to all incoming requests for SQL Server connections. It uses UDP port 1434 and responds to the requests with the TCP/IP port number required to connect to SQL Server. Therefore, you can turn off the browser service and explicitly define the port number in the application strings. This avoids port number exposure to the incoming connection requests and helps with the security of SQL Server.
You can refer to the article How SQL Server browser works to better understand the SQL Server browser service.
Further SQL Server security considerations
As outlined, SQL Server security is an ongoing process with various factors and steps. You must regularly revisit SQL Server instances, security policies and update them routinely on both your operating system and SQL Server levels. By applying these best practices on a regular basis, you will help to create a more secure and non-disruptive database service for your business.
