SQL Server Security Hardening: A Practical Checklist for Production Servers

Very few of the SQL Server security incidents we see begin with a sophisticated zero-day exploit. Most have far more ordinary causes: an application login that was added to the sysadmin role, an sa password shared among several team members, port 1433 reachable from most of the network, or backups that a compromised account can delete without difficulty. Security hardening is the systematic work of closing these gaps before an attacker finds them.

In this guide, we cover SQL Server security hardening in the same order we follow on production systems. We begin with an inventory of existing permissions and then move on to authentication, service accounts, surface area, network access, encryption, auditing, patch management and protecting backups against ransomware. Each step includes the queries you can use to assess where your environment stands today.

Step 1: Build a Permission Matrix Before Making Changes

Applying the principle of least privilege requires a clear picture of who holds which permissions today. On servers that have been in service for several years, this inventory usually brings unexpected findings to light, most commonly logins belonging to former employees, vendor accounts that were added “temporarily” and never removed, and application logins that were granted sysadmin to work around an error quickly.

We recommend starting at the server level. The following query lists every login that belongs to a fixed server role, together with accounts that have been granted high-impact permissions such as CONTROL SERVER or IMPERSONATE ANY LOGIN directly:

-- Members of fixed server roles (sysadmin, securityadmin, ...)
SELECT r.name AS server_role, m.name AS login_name, m.type_desc, m.is_disabled
FROM sys.server_role_members AS rm
JOIN sys.server_principals AS r ON r.principal_id = rm.role_principal_id
JOIN sys.server_principals AS m ON m.principal_id = rm.member_principal_id
WHERE m.name NOT LIKE N'##%'

UNION ALL

-- Logins with high-impact server permissions granted directly
SELECT p.permission_name COLLATE DATABASE_DEFAULT, m.name, m.type_desc, m.is_disabled
FROM sys.server_permissions AS p
JOIN sys.server_principals AS m ON m.principal_id = p.grantee_principal_id
WHERE p.permission_name IN (N'CONTROL SERVER', N'IMPERSONATE ANY LOGIN', N'ALTER ANY LOGIN')
  AND p.state IN ('G', 'W')
ORDER BY server_role, login_name;

The database level deserves the same attention. Members of db_owner, db_securityadmin and db_accessadmin can grant themselves or other users almost any permission within the database, so these roles should be reviewed separately:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'SELECT ' + QUOTENAME(name, '''') + N' AS database_name,
       r.name AS role_name, m.name AS member_name, m.type_desc
FROM ' + QUOTENAME(name) + N'.sys.database_role_members AS rm
JOIN ' + QUOTENAME(name) + N'.sys.database_principals AS r ON r.principal_id = rm.role_principal_id
JOIN ' + QUOTENAME(name) + N'.sys.database_principals AS m ON m.principal_id = rm.member_principal_id
WHERE r.name IN (N''db_owner'', N''db_securityadmin'', N''db_accessadmin'')
  AND m.name <> N''dbo'' UNION ALL '
FROM sys.databases
WHERE state_desc = N'ONLINE';

SET @sql = LEFT(@sql, LEN(@sql) - 10);   -- drop the trailing UNION ALL
EXEC sys.sp_executesql @sql;

We suggest consolidating the results in a simple table that records each login’s purpose, its owner, the roles it currently holds and the roles it actually needs. This permission matrix becomes the foundation for the steps that follow. It’s also worth sharing with application owners, since they are best placed to confirm whether a login is still in use.

Replace broad roles with the narrowest role that meets the need

The findings we encounter most often, and the approach we recommend for each, are as follows:

• Application logins with sysadmin or db_owner: most applications only need to read and write data and execute stored procedures. You can create a custom database role with exactly those permissions, or combine db_datareader and db_datawriter with EXECUTE permission on the relevant schema.
• Monitoring tools running as sysadmin: for most monitoring tools, VIEW SERVER STATE is sufficient. On SQL Server 2022 and later, the granular fixed server roles such as ##MS_ServerStateReader## or ##MS_DefinitionReader## offer a more manageable alternative to granting individual permissions.
• Logins created for individual Windows users: we recommend replacing these with Active Directory groups. When an employee changes roles or leaves the organization, updating a single group membership is far more reliable than adjusting logins on every server.
• Logins with no identifiable owner: rather than dropping them immediately, disable them first, monitor for at least one full business cycle and remove them once you’re confident nothing depends on them.

Step 2: Secure Authentication and the sa Account

We recommend Windows (Active Directory) authentication wherever possible. Passwords are held in the directory, lockout and password policies are enforced centrally, and when a departing employee’s account is disabled, their access ends on every server at the same time. Keep mixed mode enabled only where an application genuinely requires SQL Server logins, and in that case verify that every SQL login is subject to the Windows password policy:

-- SQL logins: policy settings and obviously weak passwords
SELECT name,
       is_disabled,
       is_policy_checked,
       is_expiration_checked,
       PWDCOMPARE(N'', password_hash)  AS empty_password,
       PWDCOMPARE(name, password_hash) AS password_equals_login_name
FROM sys.sql_logins
ORDER BY name;

Any login with is_policy_checked set to 0, an empty password or a password identical to the login name should be addressed first.

Because the sa account is the first login that automated attack tools attempt, it shouldn’t remain in active use. Once you have confirmed that no application, job or linked server connects with it, you can disable the account and, optionally, rename it:

-- Is anyone connected as sa right now? Check before disabling.
SELECT session_id, host_name, program_name, login_time
FROM sys.dm_exec_sessions
WHERE original_login_name = N'sa';

ALTER LOGIN [sa] DISABLE;
-- Optional: ALTER LOGIN [sa] WITH NAME = [a_name_nobody_guesses];

Ownership is tied to the SID rather than the login name, so databases and Agent jobs owned by sa are not affected by this change.

It’s equally important to make sure that failed login attempts are recorded. In SSMS, you can set Login auditing to at least “Failed logins only” under server Properties > Security. A series of error 18456 entries from the same source in the error log is often the first indication of a brute-force or password-spray attempt.

Starting with SQL Server 2025, SQL login passwords are hashed with PBKDF2 (SHA-512 with 100,000 iterations). This makes offline cracking of stolen hashes considerably harder, at the cost of some additional CPU for each SQL login. Applications that use connection pooling are unlikely to notice the difference, but applications that open a new connection for every query may see noticeably longer login times. We therefore recommend reviewing connection pooling before upgrading to SQL Server 2025.

Step 3: Run Services Under Least-Privilege Accounts

If an attacker manages to execute code through the database engine, they inherit the permissions of the SQL Server service account. For that reason, the service account should never be a domain admin, a local administrator or an account shared by several services.

• Use a virtual account, a managed service account or a group managed service account (gMSA). With a gMSA, Active Directory rotates the password automatically without requiring a service restart, which removes the risk of a service password being exposed or mismanaged.
• Assign a separate account to each service. The Database Engine, SQL Server Agent, SSIS and SSRS should not share a single identity.
• Change service accounts only through SQL Server Configuration Manager. This ensures that permissions tied to the per-service SID and the related file system ACLs are updated correctly.
• Review Agent job owners and proxies. Jobs that run CmdExec or PowerShell steps under the service account can provide an easily overlooked path to the operating system.
• Keep DBA identities separate from everyday accounts. SQL Server administration should be carried out with a dedicated administrative account rather than the account used for email and daily work, and machine-level access such as RDP should be protected with multifactor authentication.

Step 4: Reduce the Surface Area

Every enabled feature is a potential point of misuse, so only the features that are actually required should remain active. You can review the settings that matter most from a security standpoint with the following query:

SELECT name, value_in_use
FROM sys.configurations
WHERE name IN (N'xp_cmdshell',
               N'Ole Automation Procedures',
               N'clr enabled',
               N'clr strict security',
               N'Ad Hoc Distributed Queries',
               N'cross db ownership chaining',
               N'external scripts enabled',
               N'Database Mail XPs')
ORDER BY name;

One point deserves emphasis here. Disabling xp_cmdshell doesn’t provide sufficient protection on its own, because any member of sysadmin can re-enable it with a single sp_configure call. The more effective control is to limit sysadmin membership to a small, known group, which is why the permission review in Step 1 matters so much.

We also recommend checking two database-level settings on every server:

-- TRUSTWORTHY should be OFF for user databases
SELECT name, SUSER_SNAME(owner_sid) AS owner_name, is_trustworthy_on, is_db_chaining_on
FROM sys.databases
WHERE database_id > 4;

-- In each user database: is guest allowed to connect?
SELECT DB_NAME() AS database_name, permission_name, state_desc
FROM sys.database_permissions
WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID(N'guest')
  AND permission_name = N'CONNECT';
-- Fix: REVOKE CONNECT FROM guest;  (never in master, msdb or tempdb)

A user database that is owned by a sysadmin login and has TRUSTWORTHY enabled can allow any member of its db_owner role to escalate to sysadmin. Unless a documented component requires this setting, it should be turned off.

Finally, stopping and disabling unused services also reduces the surface area in a meaningful way. SSRS, SSIS and Full-Text, as well as the SQL Server Browser on servers that host only a default instance, are the first candidates to review.

Step 5: Restrict Network Access

• Don’t expose SQL Server to the internet. A publicly reachable port 1433 quickly becomes a target for automated password-guessing attacks.
• Configure the firewall on an allow-list basis. Only application servers, ETL and reporting hosts and administrative jump hosts should be able to reach the SQL port; all other sources, including end-user workstations, should be blocked.
• Use static ports. Assigning a fixed port to each instance makes firewall rules precise. Where clients can include the port in their connection strings, the SQL Server Browser service (UDP 1434) can be disabled.
• Don’t treat “Hide Instance” as a security control. It only prevents the Browser service from advertising the instance; a port scan will still reveal it.
• Route remote administrative access through a VPN or jump host, ideally restricted by source IP address.

# Allow SQL Server only from the application subnet and the admin jump host
New-NetFirewallRule -DisplayName "SQL Server 1433 - app and admin only" `
  -Direction Inbound -Protocol TCP -LocalPort 1433 `
  -RemoteAddress 10.10.20.0/24, 10.10.99.15 -Action Allow

While reviewing network access, it’s also worth examining linked server definitions. A linked server that maps every local login to a sysadmin account on the remote server can turn the compromise of one server into the compromise of a second.

Step 6: Encrypt Connections and Data

Encryption in transit

Without TLS, credentials, queries and result sets travel across the network in clear text. To prevent this, install a certificate issued by a trusted certificate authority and enable Force Encryption in SQL Server Configuration Manager. On SQL Server 2022 and later, Force Strict Encryption is the recommended option. It enables TDS 8.0, in which the TLS handshake takes place before any TDS traffic, and it’s required for TLS 1.3. For environments that use Windows authentication, Microsoft also recommends setting Extended Protection to Required.

Before enforcing encryption, you can review the current state of client connections with the following query:

SELECT c.client_net_address, s.program_name, s.login_name,
       c.encrypt_option, c.auth_scheme
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions    AS s ON s.session_id = c.session_id
ORDER BY c.encrypt_option, c.client_net_address;

Connections with encrypt_option set to FALSE identify the clients that must be updated before encryption is enforced. Applications that use TrustServerCertificate=True in their connection strings do encrypt traffic, but because they skip certificate validation they remain exposed to man-in-the-middle attacks. This setting should be treated as a temporary measure during migration rather than a permanent solution.

Encryption at rest: TDE and backup encryption

Transparent Data Encryption (TDE) encrypts data and log files, tempdb and backups, preventing stolen disks or backup files from being attached or restored on another server. In addition to Enterprise edition, TDE has been available in Standard edition since SQL Server 2019. Backup encryption, introduced in SQL Server 2014, protects backup files independently and is a sound alternative where TDE cannot be applied, although it isn’t supported in Express or Web editions. If you use both, Microsoft recommends separate certificates for the database and for the backups.

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'<strong password>';
CREATE CERTIFICATE BackupCert WITH SUBJECT = N'Backup encryption certificate';

-- Back up the certificate and its private key IMMEDIATELY, to a different location
BACKUP CERTIFICATE BackupCert
  TO FILE = N'secure-sharekeysBackupCert.cer'
  WITH PRIVATE KEY (FILE = N'secure-sharekeysBackupCert.pvk',
                    ENCRYPTION BY PASSWORD = N'<another strong password>');

-- Encrypted, compressed, checksummed backup
BACKUP DATABASE SalesDB
  TO DISK = N'B:\Backup\SalesDB_FULL.bak'
  WITH COMPRESSION, CHECKSUM,
       ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupCert);

For sensitive columns such as national ID or card numbers, Always Encrypted offers stronger protection. Because the encryption keys reside only in the client application, the data remains encrypted even to database administrators. Dynamic Data Masking, by contrast, only masks values in query results; it’s a useful feature, but it shouldn’t be relied on as a security control.

Step 7: Audit Privileged Activity and Forward It to Your SIEM

Hardening reduces risk but cannot eliminate it entirely. Auditing ensures that unauthorized attempts are detected and provides the records you will need during a post-incident investigation. SQL Server Audit is available in all editions, and database-level auditing has been supported in all editions since SQL Server 2016 SP1. As a practical starting point, we recommend a configuration with two components.

1. Security changes and failed logins. These events are low in volume but high in value, so they should be enabled on every server at all times:

USE master;
CREATE SERVER AUDIT [Audit_Security]
  TO FILE (FILEPATH = N'D:\SQLAudit\', MAXSIZE = 256 MB, MAX_ROLLOVER_FILES = 40)
  WITH (ON_FAILURE = CONTINUE);
-- For SIEM collection you can also write TO APPLICATION_LOG or TO SECURITY_LOG

CREATE SERVER AUDIT SPECIFICATION [AuditSpec_Security]
  FOR SERVER AUDIT [Audit_Security]
  ADD (FAILED_LOGIN_GROUP),
  ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
  ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
  ADD (SERVER_PERMISSION_CHANGE_GROUP),
  ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
  ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
  ADD (DATABASE_PERMISSION_CHANGE_GROUP),
  ADD (LOGIN_CHANGE_PASSWORD_GROUP),
  ADD (BACKUP_RESTORE_GROUP),
  ADD (AUDIT_CHANGE_GROUP)
  WITH (STATE = ON);

ALTER SERVER AUDIT [Audit_Security] WITH (STATE = ON);

2. Statements run by people rather than applications. Application logins generate very high volumes of queries, whereas statements run directly by users are far fewer and are typically the ones that need to be explained after an incident. On SQL Server 2022 and later, BATCH_COMPLETED_GROUP captures the full text of each batch. Filtering the audit to non-application accounts is the most practical way to keep the volume under control:

USE master;
CREATE SERVER AUDIT [Audit_HumanActivity]
  TO FILE (FILEPATH = N'D:\SQLAudit\', MAXSIZE = 256 MB, MAX_ROLLOVER_FILES = 40)
  WITH (ON_FAILURE = CONTINUE)
  WHERE ([server_principal_name] <> N'AppLogin'
     AND [server_principal_name] <> N'CORP\svc_reporting');

CREATE SERVER AUDIT SPECIFICATION [AuditSpec_HumanActivity]
  FOR SERVER AUDIT [Audit_HumanActivity]
  ADD (BATCH_COMPLETED_GROUP)
  WITH (STATE = ON);

ALTER SERVER AUDIT [Audit_HumanActivity] WITH (STATE = ON);

Because batch text can include sensitive values, the audit files require the same level of protection as the data itself. On busy servers, we recommend sizing the audit files according to the expected volume before enabling this audit.

When you read the audit records, you can see who performed each action, from which machine and through which application:

SELECT event_time, action_id, succeeded, server_principal_name,
       client_ip, host_name, application_name, database_name, statement
FROM sys.fn_get_audit_file(N'D:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;

Audit files kept on the database server, however, offer limited assurance against anyone with the rights to delete them, which is why the records should be forwarded to a SIEM. You can either write the audit to the Windows Security log or have the SIEM agent collect the audit files. The Security log is the most tamper-resistant target, but it requires the service account to hold the “Generate security audits” right and the “Application Generated” audit policy to be enabled. Once the events reach the SIEM, we recommend defining alerts for situations such as a new sysadmin member, a stopped audit or a burst of failed login attempts.

Step 8: Patch on a Schedule and Stay on a Supported Version

Since SQL Server 2017, Microsoft no longer releases service packs; all fixes, including security fixes, are delivered through Cumulative Updates. These updates should be applied on a defined schedule and tested in a non-production environment first. In Always On environments, patching should begin with the secondary replicas. Before each update, we recommend reviewing the known issues in the release notes and avoiding builds with known serious defects.

The version in use matters as much as the patch level. Extended support for SQL Server 2016 ended in July 2026, and from that point security updates are available only through the paid Extended Security Updates program. An unsupported instance remains exposed to every vulnerability disclosed after that date, regardless of how well the rest of its configuration has been hardened. Since a significant share of attacks reach SQL Server through the operating system, Windows Server updates should be managed with the same discipline.

Step 9: Protect Backups Against Ransomware

Ransomware operators know that backups are what allow an organization to recover without paying. In many attacks, backup shares are located and deleted or encrypted first, and the databases are targeted only afterwards. Backups that can be deleted with a compromised domain account offer little protection in this scenario.

• Apply the 3-2-1 rule: keep at least three copies, on two different types of media, with one copy stored off-site. At least one of these copies should be immutable (object storage with an immutability policy, or WORM storage) or kept offline.
• Separate backup credentials: the SQL Server service account should be able to write backups but not delete older ones, and the backup target should not trust the same domain admin accounts as the database servers.
• Define an adequate retention period: attackers often remain undetected in a network for weeks, so retention should extend well beyond a single week.
• Test restores regularly: restore backups on a schedule and run DBCC CHECKDB against the restored copy. Without a successful restore, there is no assurance that a backup is usable. Our backup plan guide covers this in more detail.
• Protect the encryption keys: for encrypted backups to remain usable, their certificates must also be stored where an attack cannot reach them.

In our experience, a significant share of the organizations that approach Aryasoft for managed DBA services do so shortly after a ransomware attack or a major outage. The gaps we identify in these environments are usually related to the fundamentals covered in this guide: the absence of an immutable backup, restores that have never been tested, more privileged accounts than necessary and a lack of audit records that would show how the incident unfolded. One step beyond these controls, you also need to know which data is sensitive and who is accountable for it; we cover that in our data governance guide.

A Prioritized SQL Server Hardening Checklist

Not every hardening step carries the same level of risk. We recommend beginning with the steps that won’t affect the running system and scheduling the changes that require testing in a planned manner:

How Aryasoft Approaches SQL Server Hardening

Every engagement begins with a HELT360 health scan, a read-only tool developed in-house. HELT360 performs configuration checks without placing additional load on the server; every script it runs can be viewed in its panel, and all findings are reported with timestamps. Following the scan, we build the privileged-user and permission matrix together with the customer, prioritize remediation so that production is not disrupted, and document each change along with the reasoning behind it.

We apply the same standards to our own access to customer environments. Connections are made only from company-owned, centrally managed office computers that use multifactor authentication. USB storage devices are blocked on these machines, and every action is logged. On request, access can be restricted further through an IP-restricted VPN connection.