DBA Scripts: Get Database Mail and xp_cmdshell Configuration

---

xp_cmdshell lets any login with EXECUTE permission on it run arbitrary operating system commands from inside SQL Server. It’s genuinely useful for a handful of legacy automation tasks, and it’s also one of the first things a penetration tester checks for, because a SQL injection vulnerability combined with xp_cmdshell enabled is a direct path to the underlying OS. It’s disabled by default for exactly this reason, and it’s surprising how often it gets left switched on from an old migration or a “just for now” fix that was never reverted.

This script audits xp_cmdshell, CLR execution, Database Mail, connection encryption enforcement, and active NTLM authentication in a single query, the security surface area that’s easy to forget about once it’s configured.

---

Why Database Mail and xp_cmdshell Matter

Each of these settings expands what’s possible from inside a compromised or malicious SQL session, or reflects how securely clients are connecting:

• xp_cmdshell — direct OS command execution. The highest-impact setting on this list if enabled without a real operational reason.
• CLR enabled / CLR strict security — allows running .NET assemblies inside SQL Server. Strict security (on by default since SQL 2017) requires assemblies to be signed or marked SAFE, closing off a class of CLR-based privilege escalation.
• Database Mail XPs — enables sending email from T-SQL. Lower risk than xp_cmdshell, but still worth knowing is active.
• Force encryption — whether the server requires encrypted connections. Without it, connections can silently fall back to unencrypted, exposing credentials and data on the wire.
• NTLM connections — active sessions authenticated via NTLM instead of Kerberos. NTLM is weaker and doesn’t support delegation; a high count often points at a Kerberos/SPN misconfiguration.

---

When to Run This Script

• Routine SQL Server health checks
• Security reviews and compliance audits
• After a migration, where legacy settings sometimes get carried over without review
• Investigating a suspected compromise or reviewing after a penetration test

---

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-DatabaseMailAndXpCmdShell
Category    : security
Purpose     : Security surface area audit — xp_cmdshell, CLR, Database Mail, force encryption, and active NTLM connections.
Author      : Peter Whyte (https://sqldba.blog/script-check-xp_cmdshell-clr-database-mail-configuration/)
Requires    : VIEW SERVER STATE, sysadmin (for xp_cmdshell value_in_use and registry access)
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    name,
    CAST(value        AS VARCHAR(20)) AS configured_value,
    CAST(value_in_use AS VARCHAR(20)) AS running_value,
    description
FROM sys.configurations
WHERE name IN (
    'xp_cmdshell',
    'clr enabled',
    'clr strict security',
    'Database Mail XPs'
)

UNION ALL

-- Force encryption: 1 = all connections must encrypt; 0 = encryption optional
SELECT
    'force encryption'                                                              AS name,
    '0'                                                                             AS configured_value,
    ISNULL(
        (SELECT TOP 1 CAST(value_data AS VARCHAR(20))
         FROM   sys.dm_server_registry
         WHERE  registry_key LIKE N'%SuperSocketNetLib%'
         AND    value_name   = N'ForceEncryption'),
        '0'
    )                                                                               AS running_value,
    'ForceEncryption — 1 = all connections must encrypt; 0 = unencrypted allowed'  AS description

UNION ALL

-- Active user sessions authenticated via NTLM (Kerberos is preferred for Windows auth)
SELECT
    'ntlm connections'                                                              AS name,
    '0'                                                                             AS configured_value,
    CAST(
        (SELECT COUNT(*)
         FROM   sys.dm_exec_sessions    AS s
         JOIN   sys.dm_exec_connections AS c ON c.session_id = s.session_id
         WHERE  c.auth_scheme     = 'NTLM'
         AND    s.is_user_process = 1)
    AS VARCHAR(20))                                                                 AS running_value,
    'Active user sessions using NTLM authentication (Kerberos preferred)'           AS description

ORDER BY name;

The script reads relevant flags from sys.configurations, checks the ForceEncryption registry value via sys.dm_server_registry, and counts active NTLM sessions from sys.dm_exec_connections, returning one row per setting.

---

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Audit xp_cmdshell, CLR, Database Mail, encryption, and NTLM usage:
.\run.ps1 Get-DatabaseMailAndXpCmdShell

# To run against a remote sql server:
.\run.ps1 Get-DatabaseMailAndXpCmdShell -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/security/Get-DatabaseMailAndXpCmdShell.sql
• powershell/wrappers/security/Get-DatabaseMailAndXpCmdShell.ps1

---

Example Output

This lab instance has xp_cmdshell enabled, exactly the kind of finding this script exists to surface. Force encryption is off, and there are 2 active NTLM sessions worth reviewing.

---

Understanding the Results

• xp_cmdshell (running_value = 1) — enabled. Confirm there’s a genuine operational reason and that access is restricted (it requires EXECUTE permission via sp_addrolemember or explicit grant, not automatically available to every login). If there’s no current reason, disable it.
• clr enabled / clr strict security — CLR enabled with strict security on is the safer combination. CLR enabled with strict security off is worth a closer look at what assemblies are actually loaded.
• Database Mail XPs — enabled means T-SQL can send email. Confirm the mail profile and any automated jobs using it are expected.
• force encryption (running_value = 0) — connections can negotiate down to unencrypted. Worth enabling on any instance handling sensitive data, especially over untrusted networks.
• ntlm connections — a non-zero count isn’t automatically a problem, but a persistently high count usually means Kerberos delegation isn’t configured correctly (missing or misconfigured SPNs).

---

How to Fix Security Surface Area Findings

-- Disable xp_cmdshell if there's no active operational need
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

-- Require encrypted connections (also needs a valid certificate configured
-- in SQL Server Configuration Manager — this alone isn't sufficient)
-- Set via SQL Server Configuration Manager > Protocols > Force Encryption = Yes,
-- then restart the SQL Server service.

Disabling xp_cmdshell and Database Mail XPs takes effect immediately, no restart. Force encryption changes need a service restart and a properly configured certificate to actually work end to end.

---

Best Practices

• Disable xp_cmdshell unless there’s a documented, current operational reason to keep it enabled.
• Review this security surface area as part of every routine health check, not just during a dedicated security audit.
• If xp_cmdshell must stay enabled, restrict who can execute it and audit its usage.
• Investigate a persistently high NTLM connection count as a Kerberos configuration issue, not just background noise.

---

Related Scripts

You may also find these scripts useful:

• Security (hub)
• Audit Specifications, DDL Triggers, and Proxy Credentials
• Linked Servers (includes Linked Server Security)
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

Summary

Security surface area settings like these rarely get attention outside of a dedicated audit, but they’re exactly the kind of thing that quietly gets left in a risky state after a migration or a one-off troubleshooting session.

Run this script as part of routine health checks, and treat any enabled setting without a clear, current operational reason as something to close off.