SQL Server Security Hardening: Authentication, Permissions, and Audit Configuration
Retool's new app builder is where AI-generated code ships safely
Building apps with AI is easy. Getting them to production safely is another story.
SQL Server deployments accumulate insecure configurations over years of compatibility trade-offs and emergency access grants. The sa account with a blank password on Express installations, xp_cmdshell re-enabled for a legacy ETL job, the public role with excessive permissions, and SQL Audit writing to the same volume as the data files are all common findings. This guide covers the hardening steps that close those gaps without breaking legitimate application functionality.
Authentication Mode and Account Hardening
SQL Server supports two authentication modes: Windows Authentication Only and Mixed Mode (Windows + SQL authentication). Mixed Mode is required when applications connect with SQL logins rather than Windows identities, but it expands the attack surface.
Disable the sa account
The sa account is a target in every SQL Server brute-force and credential-stuffing attack. If no application uses it, disable it: ALTER LOGIN sa DISABLE. If it must remain enabled for legacy reasons, rename it (ALTER LOGIN sa WITH NAME = [new_name]) and set a 25+ character random password that is stored in your PAM vault.
Switch to Windows Authentication where possible
Windows Authentication uses Kerberos/NTLM under the hood and benefits from Active Directory policies: password complexity, lockout, MFA enforcement. If your application tier connects with SQL logins, migrate to service accounts using Windows Authentication and Integrated Security=SSPI in the connection string.
Enforce account lockout for SQL logins
For any SQL logins that must remain, enable CHECK_POLICY and CHECK_EXPIRATION: ALTER LOGIN [app_user] WITH CHECK_POLICY=ON, CHECK_EXPIRATION=ON. This maps the login to the Windows password policy on the host: lockout after failed attempts.
Audit all logins at startup
Configure Login Auditing to 'Both failed and successful logins' in SQL Server Management Studio (Security > Properties > Login Auditing) or via sp_configure. This writes to the SQL Server error log and is the first place you look during incident response.
Review and restrict server roles
Audit who holds sysadmin: SELECT name FROM sys.server_principals WHERE IS_SRVROLEMEMBER('sysadmin', name) = 1. Remove service accounts, application accounts, and developer logins from sysadmin. Application accounts should use database-level roles scoped to their schema, not server-level roles.
Principle of Least Privilege at the Database Level
The PUBLIC role in SQL Server grants permissions to every login by default. Review what the PUBLIC role can access and what direct grants have accumulated over the lifecycle of the database.
Audit the PUBLIC role
Run: SELECT * FROM sys.database_permissions WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID('public'). Remove any permissions beyond the minimum required. The public role should not have EXECUTE permissions on sensitive stored procedures or SELECT on sensitive tables.
Use schemas to enforce application boundaries
Group tables by application domain into schemas (dbo.HR, dbo.Finance, dbo.App). Grant application service accounts EXECUTE or SELECT on specific schemas: GRANT SELECT ON SCHEMA::HR TO [app_readonly]. This prevents application credential compromise from accessing unrelated data.
Eliminate EXECUTE AS OWNER escalation chains
Stored procedures with EXECUTE AS OWNER run with the schema owner's permissions regardless of who called them. Audit: SELECT name, execute_as_principal_id FROM sys.procedures WHERE execute_as_principal_id IS NOT NULL. Replace EXECUTE AS OWNER with EXECUTE AS specific low-privilege accounts where the elevation is not intentional.
Remove CONTROL and IMPERSONATE grants
The CONTROL permission on a database is equivalent to db_owner. The IMPERSONATE permission allows a user to adopt another identity. Audit both: SELECT * FROM sys.database_permissions WHERE permission_name IN ('CONTROL', 'IMPERSONATE'). These should be absent on application-tier service accounts.
Briefings like this, every morning before 9am.
Threat intel, active CVEs, and campaign alerts, distilled for practitioners. 50,000+ subscribers. No noise.
Network and Surface Area Hardening
SQL Server's surface area configuration controls which features are exposed. Reduce it to the minimum set required by running applications.
Disable xp_cmdshell
xp_cmdshell executes operating system commands from T-SQL and is a primary post-exploitation target. Disable it: EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE. Verify: SELECT value_in_use FROM sys.configurations WHERE name = 'xp_cmdshell'. Check for applications that depend on it before disabling: they should be re-engineered to use a dedicated Windows service account job instead.
Disable OLE Automation and CLR integration if unused
OLE Automation Procedures (Ole Automation Procedures) and CLR integration expand the attack surface significantly. Disable both: EXEC sp_configure 'Ole Automation Procedures', 0; EXEC sp_configure 'clr enabled', 0; RECONFIGURE. CLR integration allows arbitrary .NET assemblies to run in the SQL Server process: leave disabled unless a specific approved assembly requires it.
Enforce encrypted connections
Force TLS encryption for all client connections in SQL Server Configuration Manager: SQL Server Network Configuration > Protocols > Force Encryption = Yes. Install a certificate from your internal CA on the SQL Server. This prevents network-path attackers from reading queries and results in plaintext.
Restrict SQL Server to specific network ports and bindings
Change the default TCP port from 1433 to a non-standard port (reduces automated attack noise, though not a security control). More importantly, bind SQL Server to the specific network interface used by applications, not 0.0.0.0. Use the Windows firewall to allow SQL connections only from application-tier hosts, not the entire network.
Disable SQL Server Browser Service
The SQL Server Browser Service responds to UDP port 1434 broadcast queries advertising instance names and ports. Disable it on production instances where clients know the connection details: services.msc > SQL Server Browser > Stop + Disabled.
Encryption at Rest: Transparent Data Encryption
Transparent Data Encryption (TDE) encrypts database files at rest, protecting against an attacker who obtains physical media or database file copies without the TDE certificate.
Enable TDE on all databases storing PII, PAN, or PHI
TDE is applied at the database level. Enable: CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert; ALTER DATABASE [DatabaseName] SET ENCRYPTION ON. Verify: SELECT name, is_encrypted FROM sys.databases WHERE is_encrypted = 1.
Back up the TDE certificate to a secure offline location
A TDE-encrypted database cannot be restored without the certificate and its private key. BACKUP CERTIFICATE TDECert TO FILE = 'C:\Backup\TDECert.cer' WITH PRIVATE KEY (FILE = 'C:\Backup\TDECert.pvk', ENCRYPTION BY PASSWORD = 'VaultStoredPassword'). Store the backup in your secrets manager, not on the SQL Server host.
Verify the encryption state does not leave tempdb unprotected
When TDE is enabled on any database, tempdb is automatically encrypted. Verify: SELECT name, encryption_state_desc FROM sys.dm_database_encryption_keys. All databases including tempdb should show ENCRYPTED.
SQL Server Audit Configuration
SQL Server Audit provides event-level logging to the Windows Security log, a file, or the Application log. Configure it to write to the Windows Security log (read by your SIEM) or a network share outside attacker reach.
Create a Server Audit writing to the Windows Security Event Log
CREATE SERVER AUDIT SQLServerAudit TO APPLICATION_LOG WITH (ON_FAILURE = CONTINUE); ALTER SERVER AUDIT SQLServerAudit WITH (STATE = ON). Writing to the Windows Security log integrates with existing SIEM Windows event collection: events appear as Event ID 33205.
Audit privileged operations at the server level
CREATE SERVER AUDIT SPECIFICATION PrivilegedOpsAudit FOR SERVER AUDIT SQLServerAudit ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), ADD (LOGIN_CHANGE_PASSWORD_GROUP), ADD (SCHEMA_OBJECT_CHANGE_GROUP), ADD (SERVER_PERMISSION_CHANGE_GROUP) WITH (STATE = ON).
Audit sensitive table access at the database level
For tables with PII or financial data: CREATE DATABASE AUDIT SPECIFICATION SensitiveDataAudit FOR SERVER AUDIT SQLServerAudit ADD (SELECT, INSERT, UPDATE, DELETE ON Schema.SensitiveTable BY PUBLIC) WITH (STATE = ON). This fires an audit event on every DML operation against the table.
Protect audit logs from tampering
If writing to file, set the audit file path to a network share where the SQL Server service account has write-only access. The account should not have read or delete. This prevents an attacker with SQL Server service account access from clearing audit evidence.
The bottom line
SQL Server's default installation optimizes for compatibility, not security. The three highest-value hardening actions are: disable the sa account or rotate it to a 25+ character random password stored in a PAM vault, disable xp_cmdshell, and enable SQL Audit to the Windows Security Event Log. Those three changes close the most commonly exploited SQL Server attack surface without requiring application changes, and every additional control in this guide incrementally reduces the residual risk.
Frequently asked questions
Should SQL Server use Windows Authentication or Mixed Mode?
Windows Authentication is preferred because it leverages Active Directory's password policies, MFA support, and Kerberos encryption without storing SQL credentials in the database. Mixed Mode is required when legacy applications authenticate with SQL logins and cannot be migrated. If Mixed Mode is necessary, disable the sa account, enforce CHECK_POLICY and CHECK_EXPIRATION on all SQL logins, and store credentials in a PAM vault rather than application configuration files.
What is the fastest way to audit SQL Server permissions?
Query sys.database_permissions and sys.server_permissions joined to sys.server_principals and sys.database_principals: SELECT dp.name, dp.type_desc, o.name AS object_name, p.permission_name, p.state_desc FROM sys.database_permissions p JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id LEFT JOIN sys.objects o ON p.major_id = o.object_id ORDER BY dp.name. This reveals every permission granted in the current database. Run the equivalent server-level query for server permissions and role memberships.
Does TDE protect against SQL injection attacks?
No. TDE protects database files at rest: it encrypts the physical pages written to disk. An attacker using SQL injection queries the running database through the application's authenticated connection, at which point the data is decrypted and available. TDE is specifically a defense against stolen backup files, compromised storage media, or direct file system access to database files. SQL injection requires application-layer defenses: parameterized queries, stored procedure-only data access, WAF rules, and least-privilege application accounts.
How do I find SQL Server instances I don't know about on my network?
Scan for TCP 1433 and UDP 1434 across your RFC 1918 ranges using nmap: nmap -sU -p 1434 --script ms-sql-info and nmap -p 1433 --script ms-sql-info. Supplement with Active Directory discovery: SQL Server instances joined to the domain register service principal names (SPNs) that you can enumerate with setspn -T domain.local -Q MSSQLSvc/*. EASM tools like Shodan and Censys will surface internet-exposed instances. Shadow SQL Server instances installed by developers or line-of-business applications are a common finding.
What SQL Server configuration settings does the CIS Benchmark require?
The CIS SQL Server Benchmark covers authentication (Windows Auth only, sa disabled), surface area (xp_cmdshell disabled, OLE Automation disabled, CLR disabled unless required), audit (login failures and successes, privileged operations), encryption (TDE enabled, forced encrypted connections), and permissions (public role reviewed, CONTROL and IMPERSONATE grants removed, sysadmin role minimized). The benchmark applies scores for each control and is the most widely used compliance reference for SQL Server hardening in audit and PCI DSS environments.
How do I harden SQL Server when I cannot change the application?
Focus on the controls that are transparent to the application: enable TDE (application sees no change), enable forced encrypted connections (application's connection string handles TLS automatically), enable SQL Audit to Windows Security log (no application change), apply Windows firewall rules restricting access to application-tier IPs only (transparent to the app), disable xp_cmdshell (application should not be calling it), and restrict the sa account. Schema-level least privilege and removing public role permissions require testing against the application before production deployment.
What should be in the SQL Server incident response runbook?
The runbook should cover: (1) isolate: identify the connection source from sys.dm_exec_sessions and sys.dm_exec_connections, kill the session, and block the source IP at the firewall; (2) preserve: backup SQL Audit logs to an off-host location before the attacker can clear them; (3) scope: query sys.dm_exec_query_stats for recently executed queries and audit logs for data access patterns; (4) assess: review sys.database_permissions for any grants made during the incident window; (5) recover: restore from a TDE-encrypted backup, rotate all SQL and Windows credentials, review application connection strings for stored credentials, and patch the vulnerability that enabled the initial access.
Sources & references
Free resources
Critical CVE Reference Card 2025–2026
25 actively exploited vulnerabilities with CVSS scores, exploit status, and patch availability. Print it, pin it, share it with your SOC team.
Ransomware Incident Response Playbook
Step-by-step 24-hour IR checklist covering detection, containment, eradication, and recovery. Built for SOC teams, IR leads, and CISOs.
Get threat intel before your inbox does.
50,000+ security professionals read Decryption Digest for early warnings on zero-days, ransomware, and nation-state campaigns. Free, daily, no spam.
Unsubscribe anytime. We never sell your data.
Founder & Cybersecurity Evangelist, Decryption Digest
Cybersecurity professional with expertise in threat intelligence, vulnerability research, and enterprise security. Covers zero-days, ransomware, and nation-state operations for 50,000+ security professionals every morning.
Win a $2,495 Black Hat pass.
Full-access to Black Hat USA 2026 in Las Vegas. Subscribe free to enter.