Understanding SQL Server Lockdown to Secure Database Environment

Security is the main concern these days in every sector. Database Security is also a major worry for any customer. SQL Server is designed to secure all your data stored in the databases but sometimes we fail to apply the right set of configurations and leave a hole in our system. Hackers and unauthorized aliens might use these loopholes to penetrate your system. Your data can be compromised or maligned, your system resources can be deleted to create an outage to stop your business activities, your clients’ data might be exposed to competitors, etc.

SQL Server Lockdown or Hardening

The whole process to secure your database environment is known as a database \ SQL Server lockdown. I will describe various parameters and configuration details that you can apply to secure your database environment.

Install Required Components Only

Let’s start lockdown practices with the SQL Server installation. SQL Server offers various components and features that DBAs choose for various requirements. Some of these components and features are given below:

1. Database Engine
2. Reporting Services
3. Integration Services
4. Analysis Services Engine
5. Notification Services
6. Documentation and Samples (Sample databases & codes)
7. Full-Text Search
8. Other features like replication, machine learning services, data quality services, etc.

It is a good practice to install only needed features because by doing so you are reducing or limiting the possibilities of surface attack. Additionally, you will have a low system resource utilization.

Install Latest Updates

Make sure to update your systems on time. If any new vulnerabilities are discovered, it is posted in security bulletins by your product vendors.

I am not talking about updating only SQL Server patches but everything that is running on your system starting from the operating system to any other application that is installed on your machine. Always apply patches to keep your software up to date to prevent any external threats or attacks.

Use Windows Authentication Mode

Always choose Windows authentication mode for user connections if you don’t have any dependencies to use the SQL Server authentication mode. It is more secure and authenticates the Windows login with the Windows Active directory during each login attempt. SQL Server has two types of authentication modes.

• Windows Authentication
• Mixed Mode (Windows + SQL Server)

Windows Authentication Mode: This is a default authentication mode for SQL Server. Windows authentication mode leverages local accounts, active directory user accounts, and groups when granting access to SQL Server. In this mode, you, as a database administrator, can grant domain or local server users access to the database server without creating and managing a separate SQL Server account.

Mixed Mode: Mixed authentication mode has both options. You can use Windows-based authentication as well as SQL Server login-based authentications. SQL Server authentication mechanism is based on accounts that are managed inside the SQL Server including the password policy. Mixed authentication might be required if you have some dependencies to support legacy applications.

You can follow the below steps to select or change the server authentication mode:

1. Launch SQL Server Management Studio and connect to your target SQL Server instance.
2. In SQL Server Management Studio, right-click the SQL Server instance and then click Properties.
3. Click the Security page in the left-side pane, you can see both authentication options in the right-side pane under the Server authentication section. If you want to secure your instance, make sure to switch to the Windows authentication mode. You just need to click the radio button of the Windows authentication mode and then click OK to apply the changes.

4. In the SQL Server Management Studio dialog box, click OK to acknowledge the need to restart SQL Server.

Windows authentication mode is a more secure choice. However, if mixed-mode authentication is required, you should provide complex passwords to SQL Server logins and apply security\lockout policies for better security. It is also recommended to disable SQL Server sa account if you are using mixed authentication mode. I will discuss this in the next section.

Rename/Disable the sa Account

Login sa is created by default while installing SQL Server, so this could be one of the potential reasons for attackers to hack or take control of this account. Remember this login has system administration privileges so anyone who has access to this account can manage your databases and SQL Server instances.

You should run the below command to disable the sa account.

--Disable sa login
USE MASTER
GO
ALTER LOGIN sa DISABLE;
GO

You can see its execution in the below figure.

Before renaming or disabling the sa account, verify that another account with administrative privileges exists on the SQL Server instance.

Change Default SQL Server Ports

We all know that SQL Server uses port 1433 for any client requests and communications. As this configuration is in the public domain, hackers can target this configuration to connect to your SQL Server instance.

You can change the SQL Server port by following the below steps.

1. Login to your target machine where SQL Server is installed.
2. Launch the Server Configuration Manager tool.
3. Expand the SQL Server Network Configuration node from the left-side pane and select Protocols for the SQL Server instance to be configured.
4. In the right pane, right-click the protocol name TCP/IP and choose Properties.
5. In the TCP/IP Properties dialog box, select the IP Addresses tab. There is a corresponding entry for every IP address assigned to the server.
6. Clear the values for both the TCP Dynamic Ports and TCP Port for each IP address except for the IP addresses under IPAll.
7. In the IPAll section for each instance, enter a new port that you want SQL Server to listen on.
8. Click Apply and restart the SQL Server Services.

Disable SQL Browser Service

SQL Server Browser services are installed with the SQL Server instance installation. This service redirects your connection to the port on which SQL Server is running. If this service is running, it will be one step easier for attackers because it allows them to connect to SQL Server without passing its port number. So, it’s better to disable this service after changing the port.

Disable xp_cmdshell Option

SQL Server offers an extended stored procedure xp_cmdshell to run and execute operating system commands from SQL Server. It should be disabled to prevent any security risk. You can find this extended stored procedure in the server level configurations or by running sp_configure T-SQL stored procedure. You can run the below T-SQL statements to disable this configuration.

-- Enable advanced options to display configure xp_cmdshell
sp_configure 'show advanced options', '1'
RECONFIGURE

-- Disable xp_cmdshell
sp_configure 'xp_cmdshell', '0' 
RECONFIGURE
GO

Use Data Encryption or Masking

SQL Server offers various encryption methodologies to protect your data. Encryption is recommended if your users access data over the public network. SQL Server has multiple options to implement the right set of encryptions considering your business need. This is the list of encryption or data masking options we can use to secure our data:

• TDE (Transparent Data Encryption)
• Secure data using Encryption keys
• Always Encrypt
• Dynamic Data Masking

Remove BUILTIN\Administrators from SQL Server

We should always remove the BUILTIN\Administrators account from the SQL Server instance to restrict unwanted access to the database instance. The best way is to grant the users access to SQL Server individually. You can also add a group of people in the Windows group and grant them access to SQL Server. You can use the below T-SQL statement to drop this login from the SQL Server instance.

You can also drop this login using GUI through SQL Server Management Studio. You just need to connect to your SQL Server instance, then expand the instance level security folder. Here you can right-click this login to remove it from the SQL Server instance.

--DROP Login
DROP LOGIN [BUILTIN\Administrators]

Strong Passwords and Adequate User Privileges

Always assign adequate rights to every user, they should be minimal, considering their role while accessing the databases. Also, we should never use weak passwords for any login or the same login and password. Always use the password policy for any SQL login.

Configure SQL Server Login Auditing

SQL Server has provided an auditing feature to capture details about all failed and successful logins. This is a very useful configuration if you want to audit who is connecting or trying to connect to your databases. We can configure the SQL Server alert which will send mail notification on each failed and successful login to SQL Server. You can follow the below steps to enable login auditing.

• Connect to your target SQL Server instance in SQL Server Management Studio
• Right-click the SQL Server instance name and then click Properties.
• Click the Security tab in the left-side pane. You will get Login auditing in the right-side pane as was shown in the first screenshot of this article
• Choose the desired option and close the Server Properties page
• Restart the SQL Server service to apply this change

Remove Unused SQL Server Logins

Always keep an eye on your SQL Server logins. You should be attentive in removing all login accounts which are no more in use. I know it’s difficult to manage manually, but you can configure a mail alert to send a mail about all SQL Server logins and their privileges.

Conclusion

I have explained some of the possible configurations that we can apply to secure our databases from external threats. Consider all the above points while deploying the database hardening policies to the environment.

Please share this article and give your feedback in the comment section so that we can improve.

Tags: database security, lockdown Last modified: October 13, 2022