Issues
HighExecution
Risky Extended Procedures Accessible
Found 4 EXECUTE grant(s) on risky extended procedures to non-sysadmin principals.
Why: Extended procedures can access filesystem or registry and should be tightly controlled.
Attack: A non-admin executes xp_regread or xp_dirtree to discover secrets or files.
Verify: SELECT o.name, dp.name, perm.state_desc FROM master.sys.all_objects o JOIN master.sys.database_permissions perm ON perm.major_id = o.object_id AND perm.permission_name = 'EXECUTE' AND perm.state IN ('G','W') JOIN master.sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id WHERE o.name IN ('xp_regread','xp_regwrite','xp_dirtree','xp_fileexist');
Control ID: SA-014 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Relevant Section (Account / Surface Area / Audit / Encryption)
xp_dirtree → public
xp_fileexist → public
xp_fixeddrives → public
xp_regread → public
Remove EXECUTE grants on xp_regread/xp_regwrite/xp_dirtree/xp_fileexist for non-admins.
MediumAuthentication
Mixed Mode Authentication Enabled
SQL Server is configured for mixed mode authentication (Windows + SQL logins). This expands the attack surface compared with Windows-only authentication.
Why: Mixed mode introduces password-based SQL identities in addition to centrally managed Windows identities.
Attack: An attacker targets a weak or stale SQL login to bypass domain authentication controls.
Verify: SELECT CONVERT(int, SERVERPROPERTY('IsIntegratedSecurityOnly')) AS is_integrated_security_only;
Control ID: SA-069 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS Microsoft SQL Server Benchmark – Ensure Windows Authentication mode is used where feasible
Built-in admin principal name: sa
Built-in admin principal disabled: Yes
Prefer Windows-only authentication where possible. If mixed mode is required, disable or tightly control high-value SQL logins and enforce strong password policy.
MediumServer Permissions
Wide-Read / Recon Server Permissions Granted
Found 5 server permission grant(s) that enable broad recon (VIEW SERVER STATE / VIEW ANY DB / CONNECT ANY DB / IMPERSONATE ANY LOGIN).
Why: Recon permissions expose metadata and help attackers map the environment.
Attack: An attacker uses VIEW SERVER STATE/VIEW ANY DATABASE to enumerate targets and plan escalation.
Verify: SELECT pr.name, pe.permission_name, pe.state_desc FROM sys.server_permissions pe JOIN sys.server_principals pr ON pe.grantee_principal_id = pr.principal_id WHERE pe.permission_name IN ('VIEW SERVER STATE','VIEW ANY DATABASE','CONNECT ANY DATABASE','IMPERSONATE ANY LOGIN') AND pe.state IN ('G','W');
Control ID: SA-004 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Section 2.3
PerfTuningUser: CONNECT ANY DATABASE [GRANT]
PerfTuningUser: VIEW ANY DATABASE [GRANT]
public: VIEW ANY DATABASE [GRANT]
t-contosoPayCMS: VIEW ANY DATABASE [GRANT]
PerfTuningUser: VIEW SERVER STATE [GRANT]
Limit recon-style server permissions to administrators and monitoring accounts only.
MediumAuthentication
Weak Password Policies
Found 18 SQL login(s) without password policy or expiration enforcement.
Why: Disabling password policy or expiration weakens account hygiene.
Attack: Attackers exploit long-lived weak passwords to gain access.
Verify: SELECT name, is_policy_checked, is_expiration_checked FROM sys.sql_logins WHERE is_disabled = 0 AND (is_policy_checked = 0 OR is_expiration_checked = 0);
Control ID: SA-009 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Section 2.4
Policy OFF: t-contosoPayPayFac, qatst, t-contosoPayAcqKys, alice.turner, t-contosoPayMonitoring ...
Expiration OFF: t-contosoPayCMS, t-contosoPayMrc, t-contosoPayPayFac, t-hangfireapp, qatst ...
Enable CHECK_POLICY and CHECK_EXPIRATION for SQL logins.
MediumSurface Area
Risky Server Features Enabled
Found 1 surface area feature(s) enabled.
Why: Unused features like xp_cmdshell or OLE increase attack surface.
Attack: An attacker abuses enabled features to execute OS commands or access external data.
Verify: SELECT name, value_in_use FROM sys.configurations WHERE name IN ( 'xp_cmdshell','Ad Hoc Distributed Queries','Ole Automation Procedures', 'SQL Mail XPs','Database Mail XPs','clr enabled','external scripts enabled' );
Control ID: SA-010 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Section 3.1
remote access
Disable unused surface area features (xp_cmdshell, Ad Hoc Distributed Queries, Ole Automation, CLR, external scripts, mail) unless required.
MediumNetwork/Endpoints
Force Encryption Disabled
Force Encryption appears to be disabled for SQL Server network connections.
Why: Unencrypted connections expose credentials and data in transit.
Attack: An attacker sniffs network traffic to capture sensitive data.
Verify: DECLARE @force_encryption INT; EXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQLServer\\SuperSocketNetLib', N'ForceEncryption', @force_encryption OUTPUT; SELECT @force_encryption AS force_encryption;
Control ID: SA-026 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Section 5.1
Enable Force Encryption if supported by your certificate/OS configuration and client requirements.
MediumNetwork/Authentication
NTLM Authentication Detected (Kerberos Fallback)
Detected 9 NTLM-authenticated user session(s). This may indicate Kerberos fallback or SPN/delegation issues.
Why: NTLM is weaker than Kerberos and may indicate authentication downgrade.
Attack: An attacker leverages NTLM relay or downgrade to gain access.
Verify: SELECT c.auth_scheme, c.encrypt_option, c.net_transport, COUNT(*) AS session_count FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id WHERE s.is_user_process = 1 AND c.auth_scheme = 'NTLM' GROUP BY c.auth_scheme, c.encrypt_option, c.net_transport;
Control ID: SA-028 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Relevant Section (Account / Surface Area / Audit / Encryption)
NTLM via TCP: 9 (encrypt=FALSE)
Sample: CONTOSO\alice.turner @ SQLPERF-DEMO (Microsoft SQL Server Management Studio - Query)
Sample: CONTOSO\alice.turner @ SQLPERF-DEMO (Microsoft SQL Server Management Studio - Query)
Sample: CONTOSO\alice.turner @ SQLPERF-DEMO (Microsoft SQL Server Management Studio - Query)
Sample: CONTOSO\alice.turner @ SQLPERF-DEMO (Microsoft SQL Server Management Studio)
Sample: CONTOSO\alice.turner @ SQLPERF-DEMO (Microsoft SQL Server Management Studio - Query)
Review SPN configuration, delegation settings, and client connection settings to prefer Kerberos where applicable.
MediumNetwork/Encryption
Unencrypted TCP Connections Detected
Some active TCP connections are not encrypted (best-effort from sys.dm_exec_connections).
Why: Unencrypted TCP connections expose data in transit.
Attack: A network attacker captures plaintext data or credentials.
Verify: SELECT c.auth_scheme, c.encrypt_option, c.net_transport, COUNT(*) AS session_count FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id WHERE s.is_user_process = 1 AND c.net_transport = 'TCP' AND c.encrypt_option = 'FALSE' GROUP BY c.auth_scheme, c.encrypt_option, c.net_transport;
Control ID: SA-030 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Relevant Section (Account / Surface Area / Audit / Encryption)
SQL over TCP: 10
NTLM over TCP: 9
Require encrypted connections and validate TLS configuration on server and clients.
MediumAuthorization
Non-Standard Database Owners
Found 16 database(s) owned by accounts other than 'sa' or 'dbo'.
Why: Database owners have dbo privileges and should be limited to standard accounts.
Attack: Compromise of a non-standard owner allows full control of that database.
Verify: SELECT name AS database_name, SUSER_SNAME(owner_sid) AS owner_name FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') AND ISNULL(SUSER_SNAME(owner_sid), '') NOT IN ('sa','dbo');
Control ID: SA-036 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Relevant Section (Account / Surface Area / Audit / Encryption)
t-contosoPayCMS (owner: CONTOSO\alice.turner)
t-contosoPayMrc (owner: CONTOSO\alice.turner)
t-contosoPayPayFac (owner: CONTOSO\alice.turner)
T-Hangfire (owner: CONTOSO\alice.turner)
T-OrderDB (owner: CONTOSO\alice.turner)
t-contosoPayAcqKys (owner: CONTOSO\alice.turner)
t-HangfirePF (owner: CONTOSO\alice.turner)
t-contosoPayMonitoring (owner: CONTOSO\alice.turner)
WideWorldImporters (owner: CONTOSO\alice.turner)
DbaTools (owner: CONTOSO\brian.hale)
Review database owners and set to a standard owner (e.g., 'sa') if appropriate.
MediumMonitoring & Audit
SQL Server Audit Not Enabled
No enabled SQL Server Audit was found. This reduces visibility into security-relevant activity.
Why: Without auditing, security events may go undetected.
Attack: An attacker changes permissions without an audit trail.
Verify: SELECT name, is_state_enabled FROM sys.server_audits WHERE is_state_enabled = 1;
Control ID: SA-057 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Section 6.1
Configure SQL Server Audit and enable server/database audit specifications for key security events.
MediumMonitoring & Audit
Server Audit Specifications Not Enabled
No enabled server audit specification was found.
Why: Without audit specifications, important server events are not captured.
Attack: Privilege or configuration changes occur without logging.
Verify: SELECT name, is_state_enabled FROM sys.server_audit_specifications WHERE is_state_enabled = 1;
Control ID: SA-058 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Relevant Section (Account / Surface Area / Audit / Encryption)
Enable server audit specifications for login changes, permission changes, and schema changes as appropriate.
MediumEncryption
Backups Not Encrypted (Last 90 Days)
Some recent backups appear to be unencrypted over the last 90 days (best-effort detection).
Why: Unencrypted backups are a common source of data leakage.
Attack: An attacker obtains a backup file and reads sensitive data offline.
Verify: SELECT database_name, COUNT(*) AS total_backups, SUM(CASE WHEN encryptor_type IS NULL THEN 0 ELSE 1 END) AS encrypted_backups FROM msdb.dbo.backupset WHERE backup_start_date >= DATEADD(day, -30, GETDATE()) AND type IN ('D','I','L') GROUP BY database_name;
Control ID: SA-064 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Section 5.3
AI_TEST2: 0/82 encrypted (last 90d)
DbaTools: 0/82 encrypted (last 90d)
ContosoWorker: 0/78 encrypted (last 90d)
CallCenterDashboard: 0/82 encrypted (last 90d)
master: 0/82 encrypted (last 90d)
model: 0/82 encrypted (last 90d)
msdb: 0/82 encrypted (last 90d)
sample_test_db: 0/82 encrypted (last 90d)
T-DealerPortal: 0/82 encrypted (last 90d)
T-DataMart: 0/82 encrypted (last 90d)Show more (1 more)
... (+14 more)
Use `WITH ENCRYPTION` for backups and protect keys/certificates according to policy.
LowAuthentication
Built-in SA Login Not Renamed
The built-in SQL Server administrator login still uses the default name 'sa'.
Why: The default administrator name is predictable and gives attackers a known high-value principal to target.
Attack: An attacker focuses password spraying and audit evasion attempts on the well-known sa login name.
Verify: SELECT name, is_disabled FROM sys.server_principals WHERE sid = 0x01;
Control ID: SA-070 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS Microsoft SQL Server Benchmark – Ensure the built-in administrator account is renamed
Rename the built-in administrator login and keep it disabled unless a specific break-glass scenario requires it.
LowAuthorization
Modules Using EXECUTE AS
Found 43 stored procedures/functions using EXECUTE AS (review for privilege escalation paths).
Why: EXECUTE AS modules run under another principal and must be tightly controlled.
Attack: A vulnerable module executes with elevated rights and is abused for escalation.
Verify: SELECT s.name AS schema_name, o.name AS object_name, USER_NAME(o.execute_as_principal_id) AS execute_as FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.execute_as_principal_id IS NOT NULL AND o.type IN ('P','V','FN','IF','TF');
Control ID: SA-022 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Relevant Section (Account / Surface Area / Audit / Encryption)
Application.AddRoleMemberIfNonexistent EXECUTE AS OWNER
Application.Configuration_ApplyColumnstoreIndexing EXECUTE AS OWNER
Application.Configuration_ApplyFullTextIndexing EXECUTE AS OWNER
Application.Configuration_ApplyPartitioning EXECUTE AS OWNER
Application.Configuration_ApplyRowLevelSecurity EXECUTE AS OWNER
Application.Configuration_RemoveRowLevelSecurity EXECUTE AS OWNER
Application.CreateRoleIfNonexistent EXECUTE AS OWNER
DataLoadSimulation.ActivateWebsiteLogons EXECUTE AS OWNER
DataLoadSimulation.AddCustomers EXECUTE AS OWNER
DataLoadSimulation.ChangePasswords EXECUTE AS OWNERShow more (1 more)
... (+33 more)
Ensure EXECUTE AS modules are least-privilege and only trusted principals can EXECUTE them.
LowNetwork/Authentication
No Kerberos Connections Observed
Windows-authenticated sessions are present, but none are using Kerberos (best-effort from active connections).
Why: Kerberos not observed may indicate SPN/delegation misconfiguration.
Attack: Clients fall back to NTLM, reducing authentication strength.
Verify: SELECT c.auth_scheme, COUNT(*) AS session_count FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id WHERE s.is_user_process = 1 GROUP BY c.auth_scheme;
Control ID: SA-029 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Relevant Section (Account / Surface Area / Audit / Encryption)
Check SPNs, DNS, and delegation. If Kerberos is required, validate client and server configuration.
LowUser Management
Inactive Logins
Found 13 login(s) with no activity signals for 90+ days (based on modify_date; currently active sessions excluded). Last login date is unavailable here without SQL Server Audit. Enriched with DB user mapping signals.
Why: Unused accounts increase attack surface and should be disabled or removed.
Attack: An attacker compromises a dormant account for stealthy access.
Verify: SELECT sp.name AS login_name FROM sys.server_principals sp WHERE sp.type IN ('S','U','G') AND ISNULL(sp.is_disabled, 0) = 0 AND sp.name NOT LIKE 'NT %' AND sp.name NOT LIKE '##%';
Control ID: SA-046 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Relevant Section (Account / Surface Area / Audit / Encryption)
Threshold: 90+ days (configured in Settings)
Activity heuristic warning: SQL Server does not expose a reliable last login date here; this finding uses login modify_date unless SQL Server Audit is enabled.
Remove candidates (>=365d inactive + no DB user maps): 0
Manual review (>=180d inactive + mapped to 2+ DBs): 3 [CONTOSO\carol.weiss, t-contosoPayCMS, t-contosoAcq]
Never modified since creation: 10 [t-contosoPayPayFac, CONTOSO\ordtst, CONTOSO\qatst, qatst, t-contosoPayAcqKys (+5 more)]
Disable unused accounts first; remove only after confirming no dependencies (apps, jobs, linked servers, service accounts).
LowMonitoring & Audit
Database Audit Specifications Not Enabled (Current DB)
No enabled database audit specification was found for the current database.
Why: Database-level changes may not be audited without specs.
Attack: Schema or permission changes occur without detection.
Verify: SELECT name, is_state_enabled FROM sys.database_audit_specifications WHERE is_state_enabled = 1;
Control ID: SA-059 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Relevant Section (Account / Surface Area / Audit / Encryption)
Consider enabling DB audit specifications for schema/permission changes in sensitive databases.
LowMonitoring & Audit
Login Auditing Not Comprehensive
Login auditing is enabled but not set to audit both success and failure (AuditLevel=2).
Why: Auditing only success or failure reduces forensic completeness.
Attack: Attackers can test credentials without a complete audit trail.
Verify: DECLARE @audit_level INT; EXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer', N'AuditLevel', @audit_level OUTPUT, N'no_output'; SELECT @audit_level AS audit_level;
Control ID: SA-061 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Relevant Section (Account / Surface Area / Audit / Encryption)
Review login auditing policy; auditing both success and failure improves forensics (may increase volume).
LowEncryption
No TDE-Encrypted User Databases Detected
No user database appears to be in 'Encrypted' state (TDE). This may be fine depending on policy, but is worth reviewing for sensitive data.
Why: Data at rest may be unencrypted without TDE.
Attack: Stolen disks or backups expose plaintext data.
Verify: SELECT d.name, dek.encryption_state FROM sys.databases d LEFT JOIN sys.dm_database_encryption_keys dek ON d.database_id = dek.database_id WHERE d.name NOT IN ('master','model','msdb','tempdb');
Control ID: SA-063 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Section 5.2
AI_TEST2
DbaTools
ContosoWorker
CallCenterDashboard
sample_test_db
T-DealerPortal
T-DataMart
T-ContosoMT
t-contosoPayAcq
t-contosoPayAcqKysShow more (3 more)
t-contosoPayCMS
t-contosoPayMonitoring
... (+9 more)
Enable TDE for sensitive databases where appropriate; ensure key management and backups are handled securely.
InfoServer Permissions
Explicit Server DENY Permissions Present
Found 1 explicit DENY permission(s) at server scope. These may be intentional hardening controls, but they should be reviewed to avoid drift or unexpected access behavior.
Why: Explicit DENY entries may be intentional, but permission drift can make effective access difficult to reason about.
Attack: A stale or misunderstood DENY/GRANT combination creates an access path different from the documented model.
Verify: SELECT pr.name, pe.permission_name, pe.state_desc FROM sys.server_permissions pe JOIN sys.server_principals pr ON pe.grantee_principal_id = pr.principal_id WHERE pe.state = 'D';
Control ID: SA-071 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS Microsoft SQL Server Benchmark – Review explicit server permissions
sample_test_usr: CONNECT SQL [DENY]
Review explicit server DENY permissions and confirm they align with the intended least-privilege model.
InfoPatch Management
Patch Level Summary
SQL Server update level is reported. Verify against vendor advisories to ensure the instance is fully patched.
Why: Patch visibility supports vulnerability management and compliance reporting.
Attack: An outdated patch level can expose known vulnerabilities if not reviewed.
Verify: SELECT SERVERPROPERTY('ProductVersion') AS product_version, SERVERPROPERTY('ProductLevel') AS product_level, SERVERPROPERTY('ProductUpdateLevel') AS update_level, SERVERPROPERTY('ProductUpdateReference') AS update_reference;
Control ID: SA-045A | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Relevant Section (Account / Surface Area / Audit / Encryption)
Version: 15.0.4430.1
Level: RTM
Update Level: CU32
Update Ref: KB5054833
Edition: Standard Edition (64-bit)
Review the latest Microsoft SQL Server security/CU advisories for your major version and update if behind.
InfoEncryption
Always Encrypted Not Configured (Current DB)
No Always Encrypted column master/encryption keys were found in the current database.
Why: Sensitive columns may not be protected in use without Always Encrypted.
Attack: A privileged DB user or attacker reads sensitive columns in plaintext.
Verify: SELECT (SELECT COUNT(*) FROM sys.column_master_keys) AS cmk_count, (SELECT COUNT(*) FROM sys.column_encryption_keys) AS cek_count;
Control ID: SA-065 | Compliance: PCI-DSS; ISO 27001; SOC2; HIPAA | CIS: CIS SQL Server 2019 v1.3.0 – Relevant Section (Account / Surface Area / Audit / Encryption)
If you store highly sensitive columns, consider Always Encrypted or column-level encryption per policy.