Deploying SQL Server on Linux is becoming increasingly common, especially on CentOS systems. It offers stable performance and reduces Windows licensing costs. However, database security is crucial. Without proper security hardening, a compromise could result in data leakage, business interruption, and even financial loss. This article details how to configure SQL Server security on a CentOS server, helping you build a solid database defense system.

Overall approach to SQL Server security on CentOS:

The goals of security hardening are: reducing the attack surface → increasing the cost of compromise → enhancing data protection capabilities → ensuring auditability.

Generally, focus on account and permission security, network access security, system and patch management, data encryption and backup, and logging and auditing.

Practical SQL Server Security Setup on CentOS Servers:

1. Installation and Initial Configuration Security

SQL Server on CentOS is typically installed using official Microsoft repositories:

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/8/mssql-server-2019.repo
sudo yum install -y mssql-server
sudo /opt/mssql/bin/mssql-conf setup

During installation, it is recommended to set a complex password (at least 12 characters, including uppercase and lowercase letters, numbers, and special symbols) and avoid using the "sa" account for daily operations.

2. Disable or rename the "sa" account

"sa" is the most commonly targeted account by hackers. It is recommended to disable or rename it:

ALTER LOGIN sa DISABLE;
ALTER LOGIN sa WITH NAME = [new_admin_name];

Alternatively, create a new administrator account and delete the "sa" user.

3. Change the default port number

SQL Server listens on port 1433 by default. You can reduce the likelihood of being scanned by changing the port number:

sudo /opt/mssql/bin/mssql-conf set network.tcpport 15345
sudo systemctl restart mssql-server

At the same time, open the new port in the firewall and close the old port:

sudo firewall-cmd --permanent --add-port=15345/tcp
sudo firewall-cmd --permanent --remove-port=1433/tcp
sudo firewall-cmd --reload

4. Enable the firewall and whitelist

Allow only specific IP addresses to access SQL Server:

sudo firewall-cmd --permanent --zone=public --add-rich-rule='
rule family="ipv4" source address="192.168.1.100" port protocol="tcp" port="15345" accept'
sudo firewall-cmd --reload

This effectively prevents external scanning and unauthorized connections.

5. Enable Encrypted Connections

Configure SSL/TLS encrypted transmission in SQL Server:

Generate or purchase an SSL certificate, configure the certificate and key in /etc/ssl, modify the SQL Server configuration file, and enable encryption:

Example:

sudo /opt/mssql/bin/mssql-conf set network.tlscert /etc/ssl/certs/mssql.pem
sudo /opt/mssql/bin/mssql-conf set network.tlskey /etc/ssl/private/mssql.key
sudo /opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2
sudo systemctl restart mssql-server

6. Principle of Least Privilege

Create separate accounts for different business systems and grant permissions as needed. Avoid granting sysadmin privileges to non-DBA users. Use roles to manage permissions and avoid direct authorization.

Example:

CREATE LOGIN app_user WITH PASSWORD = 'StrongPass123!';
CREATE USER app_user FOR LOGIN app_user;
ALTER ROLE db_datareader ADD MEMBER app_user;
ALTER ROLE db_datawriter ADD MEMBER app_user;

7. Regularly update SQL Server and CentOS patches

sudo yum update mssql-server
sudo yum update

You can check for updates regularly via crontab:

0 3 * * 0 yum update -y mssql-server

8. Enable Logging and Auditing

Enable SQL Server auditing to record logins, queries, permission changes, and other operations:

CREATE SERVER AUDIT Audit_SQLSecurity
TO FILE (FILEPATH = '/var/opt/mssql/data/audit/', MAXSIZE = 1 GB, MAX_ROLLOVER_FILES = 10)
WITH (ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT Audit_SQLSecurity WITH (STATE = ON);

Analyze logs regularly to identify unusual behavior.

9. Data Encryption and Backup

Enable Transparent Data Encryption (TDE) to protect data on disk, password-encrypt backup files, and store backups in a secure location, away from the same server as the production database.

Example:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongTDEPass!';
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert;
ALTER DATABASE MyDatabase SET ENCRYPTION ON;

Additional security recommendations:

Prohibit direct root access to the database. Use a regular Linux account to run database processes to avoid privilege escalation vulnerabilities. Also, perform regular security scans. We recommend using tools like nmap and sqlmap to identify port and SQL injection risks. Also, enable SELinux or AppArmor to restrict SQL Server's access to system resources. It's best to enforce a password policy with password expiration, complexity, and history to prevent weak passwords.

FAQs:

Q1: Is SQL Server secure by default on CentOS?

A: Not completely. The default installation does not encrypt transmission, does not restrict access IP addresses, and the "sa" account still exists, requiring manual security.

Q2: Can changing the port number prevent attacks?

A: Changing the port number does not completely prevent attacks, but it can reduce the likelihood of being detected by large-scale scanning tools and is considered a "reducing exposure" method.

Q3: Is it necessary to disable the "sa" account?

A: Disabling or renaming the "sa" account is strongly recommended, as it is a common attack target.

Q4: Will enabling encrypted transmission affect performance?

A: There will be a slight performance impact (1%-5%), but it significantly improves security, so enabling it is recommended for production environments.

Q5: Will the database log take up a lot of space?

A: Yes, so you need to set up a log archiving strategy, such as monthly log backups and clearing expired files.