Microsoft SQL Server Security: Logins, TDE and Audit

SQL Server instances usually hold an organisation's most valuable structured data, yet many still run with mixed-mode authentication, shared application logins in sysadmin, a domain account as the service identity and no audit trail. The platform has strong controls built in: Windows and Microsoft Entra authentication, virtual and managed service accounts, granular server roles introduced in SQL Server 2022, Transparent Data Encryption, Always Encrypted and SQL Server Audit. This guide explains how identity works in SQL Server, how to limit administrative and application access, how to run the services and SQL Server Agent jobs with least privilege, which encryption and audit settings matter, and the misconfigurations seen most often. It goes deeper than the vendor-neutral Database Security Guide and is written for DBAs, Windows administrators and security engineers.

Key takeaways

Use Windows or Microsoft Entra authentication wherever possible. Microsoft describes Windows authentication as much more secure than SQL Server authentication, which cannot use Kerberos and sends the password at every connection.
Keep sa disabled and sysadmin small. Microsoft's best practice is that the sa login should not be used or enabled; grant DBAs CONTROL SERVER rather than sysadmin because sysadmin ignores DENY.
Run services as virtual accounts or gMSAs. The default NT SERVICE\MSSQLSERVER virtual account with its per-service SID is the right identity for a stand-alone instance; use a gMSA where network access or clustering is needed, and change it only through SQL Server Configuration Manager.
Back up the TDE certificate before you need it. TDE encrypts data files, logs, backups and tempdb, but a lost certificate means a backup that can never be restored.
Audit to a protected target. SQL Server Audit to a file or the Windows Security log, covering logins, permission changes and access to sensitive tables, is what lets you reconstruct an incident.

How identity and access work in SQL Server

SQL Server separates logins (server-level principals) from users (database-level principals mapped to logins). Permissions are granted on securables at server, database, schema and object level, and roles group permissions at each level.

Authentication	How it works	Strengths	Weaknesses	Use for
Windows authentication	Windows token validated by AD with Kerberos or NTLM; SQL Server never sees the password	Domain password policy, lockout, groups, Kerberos with a registered SPN	Needs domain trust; NTLM fallback if SPNs are wrong	Default for staff and Windows-hosted applications
SQL Server authentication	Login and password hash stored in the instance	Works across untrusted domains and non-Windows clients	No Kerberos, fewer policies, password sent at connection time, shared logins	Legacy and third-party apps only, with `CHECK_POLICY` and `CHECK_EXPIRATION` on
Microsoft Entra authentication	SQL Server 2022 and later, usually through Azure Arc; supports MFA, service principals, managed identities and access tokens	MFA for humans, passwordless managed identities for workloads	Not supported on failover cluster instances; requires Arc or manual setup	Hybrid estates already using Entra ID

Authentication mode: Windows Authentication mode disables SQL logins; Mixed mode enables both. Windows authentication cannot be disabled.
The sa account: in Windows mode setup creates sa but disables it. Microsoft warns that sa is well known and often targeted and should not be enabled unless an application requires it. If a SQL sysadmin login is unavoidable, create one with a unique name. Brute force of sa is a classic route in (T1110.001 Password Guessing, T1078.001 Default Accounts).
Kerberos: register SPNs for the service account (MSSQLSvc/host.domain:1433 and the named-instance form) so domain clients use Kerberos rather than NTLM. See the Kerberos Security Guide.
Groups, not individuals: Microsoft recommends placing AD users in AD groups, mapping groups to SQL Server roles and granting those roles the minimum permissions.

Admin roles and privileged access

Role or permission	What it allows	Treat as
`sysadmin`	Any activity; permissions cannot be denied; members are `dbo` in every database and can tamper with audits	Tier 0 for the instance; break-glass and a few DBAs only
`CONTROL SERVER`	Near-equivalent to sysadmin but respects `DENY`	Preferred grant for DBAs, with targeted denies
`securityadmin`	Manages logins, grants server permissions, resets SQL login passwords	Microsoft: equivalent to sysadmin
`serveradmin`, `setupadmin`, `bulkadmin`, `dbcreator`, `diskadmin`, `processadmin`	Configuration, linked servers, `BULK INSERT` (can elevate in some conditions), database creation and restore, disk files, killing sessions	Privileged; grant rarely and review
`##MS_LoginManager##` (2022+)	Create, delete and modify logins without granting permissions	Replacement for securityadmin for provisioning tools
`##MS_ServerStateReader##`, `##MS_DefinitionReader##`, `##MS_DatabaseConnector##` (2022+)	Read DMVs, read metadata, connect to any database without a user	Least-privilege roles for monitoring tools
`##MS_SecurityDefinitionReader##` (2022+)	Includes `VIEW ANY CRYPTOGRAPHICALLY SECURED DEFINITION`, which exposes password hashes	Trusted security staff only
`db_owner`	Full control of a database, can alter database audit specifications	Privileged within the database; never for application logins
`public`	Every login; permissions granted to it reach everyone	Grant nothing to public that is not meant for all

Separate admin identities: DBAs should use dedicated admin accounts, from privileged access workstations, with MFA for machine-level access such as RDP, as Microsoft's best practice recommends. See the Privileged Access Management Guide.
Windows administrators: local administrators on the host can usually gain sysadmin by restarting the instance in single-user mode, so host admin rights are effectively database admin rights. Keep the host admin group as small as the sysadmin role.
Review queries: list role members with sys.server_role_members joined to sys.server_principals, and list server permissions from sys.server_permissions, on a schedule.

Integrations, API tokens and service accounts

Service identities

Account type	Example	Network access	Use when
Virtual account (default)	`NT SERVICE\MSSQLSERVER`, `NT SERVICE\MSSQL$INST1`, `NT Service\SQLSERVERAGENT`	As the computer account `DOMAIN\HOST$`; cannot authenticate to remote shares directly	Stand-alone instances
Managed or group managed service account	`DOMAIN\sqlsvc01$`	Yes, automatic password management	Resources outside the server, Always On, failover cluster instances (gMSA)
Domain user account	`DOMAIN\svc-sql`	Yes	Only where MSA or gMSA is impossible; password and SPN maintenance are manual
Local System	`LocalSystem`	Acts as the computer	Avoid: very high privilege
Local Service	`NT AUTHORITY\LOCAL SERVICE`	Anonymous	Not supported for the Database Engine or Agent

Per-service SIDs: each service gets a SID derived from its name, used in ACLs to isolate its files and registry keys. Microsoft says not to grant additional permissions to the service account or service groups.
Granted privileges: setup grants the Database Engine SID Log on as a service, Replace a process-level token, Bypass traverse checking and Adjust memory quotas for a process. A service account in Domain Admins adds nothing the engine needs and makes any SQL injection or xp_cmdshell abuse a domain compromise.
Changing the account: use SQL Server Configuration Manager, not the Services console, because it also updates the local security store that protects the service master key.
gMSA: see the Group Managed Service Accounts (gMSA) Guide.

Application logins and automation

One identity per application: each application gets its own Windows group, gMSA, Entra managed identity or service principal, mapped to a database role with only the permissions it uses. Shared SQL logins in db_owner or sysadmin are the most common finding in assessments.
Connection strings: SQL login passwords in web.config, appsettings.json or pipeline variables are secrets; move them to a vault or replace them with managed identities. See the Secrets Management Guide.
SQL Server Agent proxies: without a proxy, CmdExec and PowerShell job steps run as the Agent service account. Create proxies mapped to credentials for low-privilege Windows accounts, grant each proxy only the subsystems it needs, and remember only sysadmin can create proxies. Non-sysadmins need SQLAgentUserRole, SQLAgentReaderRole or SQLAgentOperatorRole in msdb.
Linked servers: a linked server configured to connect with a fixed remote login lets every local user act as that login on the remote instance. Map local logins explicitly or use the current security context with Kerberos delegation, and inventory linked servers as NHIs. See the Service Account Security Guide.

Logging and detection

SQL Server Audit has three parts: the audit object (destination, created disabled), one server audit specification per audit, and database audit specifications per database. Server-level audits work in all editions, and database-level audits in all editions from SQL Server 2016 SP1.

Target	Notes
Binary file	Read with `sys.fn_get_audit_file`; restrict the folder ACL and ship files to the SIEM quickly
Windows Security log	Service account needs Generate security audits; Audit object access must be enabled for success and failure (or the application generated subcategory via `auditpol`)
Windows Application log	Any authenticated user can read and write it; avoid for security auditing

What to capture	Action group or source	Why
Failed and successful logins	`FAILED_LOGIN_GROUP`, `SUCCESSFUL_LOGIN_GROUP`	Password guessing and spraying against SQL logins (T1110.001, T1110.003); logins from unexpected hosts
Role membership and permission changes	`SERVER_ROLE_MEMBER_CHANGE_GROUP`, `DATABASE_ROLE_MEMBER_CHANGE_GROUP`, `SERVER_PERMISSION_CHANGE_GROUP`	Privilege escalation and persistence
Login creation and changes	`SERVER_PRINCIPAL_CHANGE_GROUP`, `LOGIN_CHANGE_PASSWORD_GROUP`	Backdoor logins, sa enablement
Configuration changes	`SERVER_OPERATION_GROUP`, error log entries for `sp_configure`	Enabling `xp_cmdshell`, CLR or OLE Automation (T1059.003, T1505.001)
Audit tampering	`AUDIT_CHANGE_GROUP`	sysadmin can alter or stop audits
Sensitive data access	Database audit specification with `SELECT`, `UPDATE` on named tables	Bulk reads (T1213 Data from Information Repositories)

Use ON_FAILURE = CONTINUE for most instances and SHUTDOWN only where regulation requires that no unaudited activity occurs, because a full disk then stops the service. Creating audits needs ALTER ANY SERVER AUDIT; reading audit files needs VIEW SERVER SECURITY AUDIT from SQL Server 2022. For broader detection content see the Database Security Guide.

Metrics

Authentication mix: percentage of instances in Windows Authentication mode, and count of enabled SQL logins.
Privileged membership: number of members of sysadmin and securityadmin per instance, with named owners.
Service identity: percentage of instances running as virtual accounts, MSAs or gMSAs rather than domain users or Local System.
Encryption: percentage of production databases with TDE encryption_state 3 and certificates with a non-null pvt_key_last_backup_date.
Audit coverage: percentage of instances with an enabled server audit shipping to the SIEM.

Hardening baseline

Encryption

TDE: key chain is DPAPI, service master key, database master key and certificate in master, then the database encryption key in the user database (AES_256). It protects data files, logs, backups and tempdb against stolen media; it does not encrypt master, model or msdb, FILESTREAM data, buffer pool extension files or data in transit. Back up the certificate and private key immediately and keep it after disabling TDE. Use EKM to hold the key in an HSM where required.
Always Encrypted: columns are decrypted only by the client driver, so DBAs and anyone with sysadmin cannot read them. Prefer randomised encryption, with secure enclaves where comparisons are needed. Use Dynamic Data Masking only where Always Encrypted is not possible; it is not a security boundary against privileged users.
Connection encryption: in SQL Server Configuration Manager set Force Encryption to Yes and Extended Protection to Required; from SQL Server 2022 use Force Strict Encryption for TDS 8.0 and update connection strings.

Surface area and configuration

xp_cmdshell: off by default; Microsoft does not recommend it in any environment. Also review CLR, OLE Automation procedures and Ad Hoc Distributed Queries in sys.configurations.
Features: install only what is needed; do not install SSMS or admin tooling on the server.
Network: restrict TCP 1433 (and named-instance ports and UDP 1434 for SQL Browser) to application and admin subnets; disable SQL Browser where clients use fixed ports. Instances exposed to the internet are routinely brute forced and exploited (T1190).
Patching: apply cumulative updates and security updates on a defined cycle (NIST SI-2). See the Vulnerability Management Guide.
Backups: keep a scheduled full backup secured separately from common administrator accounts, as Microsoft's ransomware guidance advises (T1486). See the Backup Security and Immutable Backup Guide.

Rollout order

Assess: run SQL vulnerability assessment (delivered through Microsoft Defender for SQL) or the CIS Microsoft SQL Server Benchmark against a sample of instances and record the baseline.
Fix identity first: disable sa, move the service to a virtual account or gMSA through Configuration Manager, register SPNs and confirm Kerberos with sys.dm_exec_connections.auth_scheme.
Reduce privilege: replace application sysadmin and db_owner grants with custom database roles; test in a non-production copy with the application's real workload; keep the old grants scripted for rollback.
Switch to Windows Authentication mode once no SQL logins remain in use (watch SUCCESSFUL_LOGIN_GROUP events for two business cycles first).
Enable audit, then encryption: enable the server audit, then TDE per database during a maintenance window, verifying that backups restore on a second server with the certificate.
Enforce connection encryption after confirming client drivers support it; rollback is setting Force Encryption back to No and restarting the service.

Small estates can do this per instance with scripts and a checklist. Large estates should manage logins, roles and audit specifications as code, use Azure Arc or a CMDB to inventory instances, and alert on drift from the baseline.

Common misconfigurations

Mixed mode with sa enabled and a weak password: still the first thing scanners try on exposed TCP 1433.
Application logins in sysadmin: any SQL injection in the application becomes full instance control, and with xp_cmdshell enabled, operating system command execution as the service account. See the SQL Injection Prevention Guide.
Service account in Domain Admins or local Administrators: turns an instance compromise into a domain compromise.
Treating securityadmin as harmless: it can grant its members almost anything.
TDE without certificate backup: a server rebuild or migration leaves encrypted backups unrecoverable.
Linked servers with a fixed sysadmin remote login: one low-privilege login on the first instance becomes sysadmin on the second.
Audits to the Application log or not shipped off the server: readable and writable by any user, and erasable by sysadmin.

Framework mapping

Framework	Reference	SQL Server controls
NIST SP 800-53 Rev. 5	AC-2, AC-3, AC-6; IA-2, IA-5; SC-8, SC-12, SC-28; AU-2, AU-9, AU-12; SI-2	Logins and roles, least privilege, authentication, connection encryption, TDE and key management, SQL Server Audit and its protection, patching
NIST CSF 2.0	PR.AA-01, PR.AA-05; PR.DS-01, PR.DS-02; PR.PS-04	Identity management, least privilege, data at rest and in transit, log generation
CIS	CIS Microsoft SQL Server Benchmark; CIS Controls v8.1 Controls 3, 4, 5, 6 and 8	Configuration baseline, data protection, secure configuration, account and access management, audit logs
ISO/IEC 27001:2022 Annex A	A.8.2; A.8.5; A.8.15; A.8.24	Privileged access, secure authentication, logging, use of cryptography
MITRE ATT&CK	T1110.001; T1078.001; T1190; T1505.001; T1059.003; T1213; T1486	sa guessing, exposed instances, stored procedure persistence, command shell, data theft, ransomware

Practitioner checklist

Set Windows Authentication mode where possible; otherwise enforce CHECK_POLICY and CHECK_EXPIRATION on every SQL login.
Disable or rename sa and keep sysadmin and securityadmin membership to named DBAs and a break-glass account.
Grant DBAs CONTROL SERVER and monitoring tools the SQL Server 2022 ##MS_...## roles instead of sysadmin.
Run the engine and Agent as virtual accounts or gMSAs, changed only through SQL Server Configuration Manager, with SPNs registered.
Give each application its own identity mapped to a custom database role, and remove application logins from sysadmin and db_owner.
Use Agent proxies with low-privilege credentials for CmdExec and PowerShell steps, and inventory linked server logins.
Enable TDE on production databases, back up the certificate and test a restore on another server; use Always Encrypted for the most sensitive columns.
Force connection encryption (Force Strict Encryption on 2022 and later) and restrict TCP 1433 to known subnets.
Enable SQL Server Audit for logins, role and permission changes, configuration and audit changes, and ship it to the SIEM.
Keep xp_cmdshell, CLR and OLE Automation disabled unless an exception is approved and logged.

Microsoft SQL Server Security Guide: Authentication, Service Accounts, Encryption and Auditing