SQL Server security hardening (Linux and Windows: assessment, network, TLS, authentication, permissions, audit, TDE)

This is not an installation guide: it finds and closes the gaps of a running SQL Server 2019 / 2022 / 2025 instance (Linux or Windows). First a read-only Security assessment lists the gaps by severity and tells you which card each finding leads to; then you close the areas one by one or all of them in order: listen address and firewall, Force Encryption + certificate and validation on the client, disabling sa and password policy (CHECK_POLICY), roles and an application user without DDL, attack surface (xp_cmdshell, TRUSTWORTHY, guest), Server Audit, TDE (certificate backup mandatory), encrypted backups, service account and file permissions, CU tracking. TDE and backup encryption are available in Standard and Enterprise (not in Express). At the end the assessment runs again.

Topology: Single SQL Server instance (Linux or Windows) + application server; Always On AG in the notes

Scenarios

• Security assessment: what are my gaps? (read-only) — The scan to run first: sa, password policy, empty passwords, sysadmin members, public/guest permissions, xp_cmdshell, unencrypted connections, audit, TDE and certificate backups, ports and firewall; it changes nothing.
• Full hardening (everything in order) — Newly installed or never hardened instance: all areas are applied in a safe order; SQL Server is restarted once or twice (a maintenance window is needed).
• Network and connectivity: who can connect from where? (listening, firewall, Browser) — SQL Server listens on all interfaces, 1433 is open to everyone, SQL Browser runs without need. Changing the listen address requires a restart.
• Encrypt connections (Force Encryption, certificate, client validation) — Connections are unencrypted or use a self-signed temporary certificate; clients use TrustServerCertificate=true. Changing the certificate requires a restart.
• Authentication and passwords (sa, CHECK_POLICY, Windows authentication) — sa enabled, no password policy on SQL logins, empty/weak passwords; a personal DBA account and disabling sa. No downtime (only switching to Windows authentication requires a restart).
• Least privilege and attack surface (roles, public, guest, xp_cmdshell) — Application user is db_owner or sysadmin, extra permissions on public/guest, xp_cmdshell/TRUSTWORTHY enabled; role design and a permission report. No downtime.
• Audit: who did what? (Server Audit, Database Audit Specification) — The auditor wants a record of logins, permission and schema changes; Server Audit to a file (or the Windows event log), forwarding to a central system and retention. No downtime. No license required in any edition (2016 SP1+).
• Encrypt data at rest (TDE) — TDE against a disk, data file or backup being opened on another server: DMK, certificate, DEK. Done online; the certificate backup is MANDATORY (if it is lost, the database and its backups cannot be opened). License: available in SQL Server 2019+ Standard and Enterprise, not in Express.
• Backup security (encrypted backups, certificate, permissions) — Backups are unencrypted, the backup directory is open to everyone, passwords are in plain text in scripts; BACKUP … WITH ENCRYPTION, certificate backup and directory permissions. No downtime. Backup encryption is available in Standard and Enterprise, not in Express.
• Operating system side (service account, file permissions, SELinux) — The service runs as LocalSystem or an administrator account, data/backup directories are open to everyone, SELinux is disabled; changing the service account requires a restart.
• Patches and version: am I on a supported version and the latest CU? — Version, CU level and end of support (2016 ended in July 2026); links to the CU patch guide for applying updates. Read-only.

Before you start

• SQL Server is running; mssql-tools18 is installed and … is sysadmin. sqlcmd is under /opt/mssql-tools18/bin/ (not on the PATH; the guide uses the full path). Before running the script, read the password into a variable without echoing it: read -rsp 'Password: ' SQLCMDPASSWORD; export SQLCMDPASSWORD and sudo --preserve-env=SQLCMDPASSWORD bash <script>.sh. Installation: SQL Server installation on Linux.
• An administrator PowerShell session; the logged-on Windows account is sysadmin in SQL Server (sqlcmd -E). Installation: SQL Server installation on Windows.
• Two separate sessions are open: in one you apply the steps, in the other a sysadmin connection (sqlcmd) stays open. After disabling sa, the firewall, Force Encryption and service changes, test a new connection in a separate session; do not close the old session. Last resort: a local DAC connection (sqlcmd -A) always works from the server itself; if you are completely locked out, in single-user mode (…systemctl stop mssql-server; sudo -u mssql /opt/mssql/bin/sqlservr -m…net start … /m…) a local administrator gets in as sysadmin.
• Maintenance window: SQL Server will be restarted in the areas you select (listen address, certificate/Force Encryption, service account, authentication mode).… In an AG apply on the secondary replicas first, then switch the primary with a failover and apply on the former primary.…
• List of connecting applications (server, login, driver). The assessment step shows the ones currently connected.
• A current full backup and master/msdb backups exist (backup guide). With TDE and backup encryption, data cannot be recovered without the certificate backup.

Phases of the guide

• Security assessment (read-only) — List the findings inside the instance · Check the operating system side · Check the operating system side
• Before you start: fallback point — Take a copy of the logins, settings and configuration · Take a copy of the logins, settings and registry
• Authentication — 1. Diagnosis — Show logins, policy and authentication mode
• Authentication — 3. Apply — Create the application and reporting logins with a policy · Create the application and reporting logins with a policy · Change empty passwords and passwords equal to the login name · Change empty passwords and passwords equal to the login name · Prepare personal DBA access · Verify that the DBA AD group is sysadmin …
• Authentication — 4. Verification — Verify the authentication state · Try logging in as sa from the application server · Try logging in as sa from the application server
• Permissions — 1. Diagnosis — Show server and database permissions
• Permissions — 3. Apply — Reduce the attack surface (sp_configure) · Disable TRUSTWORTHY and guest access · Set up roles, schema and users · Remove the application user from db_owner · Permission report
• Permissions — 4. Verification — Verify that the application user cannot run DDL
• TLS — 1. Diagnosis — Show the encryption state of connections
• TLS — 3. Apply — Create a test CA and a server certificate · Create the key and the certificate signing request (CSR) · Verify the certificate and the key · Configure the certificate and Force Encryption (mssql-conf) · Restart SQL Server · Create or import the certificate …
• TLS — 4. Verification — Verify that all TCP connections are encrypted
• TLS — 5. Afterwards: certificate renewal — Certificate renewal procedure
• Network — 1. Diagnosis: who connects from where? — Show listening addresses and client sources
• Network — 3. Apply — Restrict the listen address (mssql-conf) · Restrict the listen address (TCP/IP IPn) · Open the port in the firewall only to allowed networks · Open the port in the firewall only to allowed networks · Disable SQL Browser (default instance) · Restart SQL Server for the listen address …
• Network — 4. Verification — Test the connection from the application server (new session) · Test the connection from the application server (new session)
• Audit — 1. Diagnosis — Show existing audits and login auditing
• Audit — 2. Safety net — Prepare the audit directory · Prepare the audit directory
• Audit — 3. Apply — Define the Server Audit and server-level events · Record data access in the application schema · Script that exports the audit records · Password file, cron and rsyslog for the export
• Audit — 4. Verification — Make a change and see it in the audit record
• Backup — 1. Diagnosis — Show the encryption state of backups and the certificates · Backup directory permissions · Backup directory permissions
• Backup — 3. Apply — Create the backup certificate and back it up immediately · Create the backup certificate and back it up immediately · Take an encrypted backup and verify it · Fix the backup directory permissions · Fix the backup directory permissions
• Operating system — 1. Diagnosis — Service account, permissions and SELinux · Service accounts and directory permissions
• Operating system — 3. Apply — Lock the mssql account and fix directory permissions · Keep SELinux Enforcing and confine SQL Server · Switch the service account to a virtual account or gMSA · Fix data directory permissions
• TDE — 1. Diagnosis — Show the encryption state and the certificates
• TDE — 2. Safety net — Full backup before TDE
• TDE — 3. Apply — Create the DMK and the TDE certificate, back them up immediately · Create the DMK and the TDE certificate, back them up immediately · AG: load the TDE certificate on the other replicas · Create the database encryption key and enable TDE · Monitor until encryption finishes
• TDE — 4. Verification — Verify the encrypted database and the certificate backup
• Patches and version — 1. Diagnosis — Show the version and CU level · Pending updates on the operating system side
• Patches and version — 5. Afterwards — Record the patch schedule
• Final assessment: are the gaps closed? — Run the security assessment again · Check the operating system side again · Check the operating system side again