DBA Scripts: Get Database Mail and xp_cmdshell Configuration

An audit or a pen test report asks whether xp_cmdshell is on, or alert emails stopped arriving and you need to know whether Database Mail is even switched on. This script answers both in one result, alongside CLR, force encryption and live NTLM sessions.

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, operating system commands from T-SQL. A sysadmin caller runs them as the SQL Server service account; anyone else needs EXECUTE on it plus the ##xp_cmdshell_proxy_account## credential, and runs as that account (MS Docs). The highest-impact setting on this list, and one of the first things a penetration tester checks after finding a SQL injection.
• CLR enabled / CLR strict security, runs .NET assemblies inside SQL Server. Strict security, on by default since SQL Server 2017, treats every assembly as UNSAFE: it loads only if it is signed by a certificate or asymmetric key whose login holds UNSAFE ASSEMBLY, or is on the trusted assembly list. Marking it SAFE no longer protects anything (MS Docs).
• Database Mail XPs, whether T-SQL can send mail at all. 0, the default, means Database Mail isn’t available, so nothing that relies on it sends, Agent alert emails included (MS Docs). Lower risk than xp_cmdshell.
• 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

• An audit, compliance review or penetration test asks whether xp_cmdshell is enabled
• Alert or job notification emails stopped arriving
• Investigating a suspected compromise
• After a migration, where legacy settings sometimes get carried over without review
• Routine SQL Server health checks

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/dba-scripts-get-database-mail-and-xp-cmd-shell/)
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 four switches from sys.configurations, looks for the ForceEncryption value in sys.dm_server_registry, and counts NTLM user sessions in sys.dm_exec_connections, one row per setting. On SQL Server 2025 CU8 that registry view returns no ForceEncryption value at all, so the force encryption row prints 0 whatever the server is set to.

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

Nothing in this result is switched on that should not be: xp_cmdshell, CLR and Database Mail all read 0, and CLR strict security reads 1. A clean report is what you are checking for, not a failed run. The force encryption 0 happens to match this server’s registry, but the script would print it either way.

The row that still needs reading is ntlm connections, because it is the only one here that is not a switch. It is a live count of sessions authenticating with NTLM rather than Kerberos, so it changes every time you run it, and its configured_value of 0 is a placeholder rather than a setting.

Understanding the Results

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.

MS Docs covers xp_cmdshell and its proxy account, sys.dm_server_registry and sys.configurations in full.

Related Scripts

You may also find these scripts useful:

• SQL Server Agent Will Not Start, or “Agent XPs Component Is Turned Off” (Error 15281), the sibling surface-area switches 15281 also guards
• Security (hub)
• Sysadmin Members, who can turn any of these back on
• User Permissions Audit, whether a specific login can reach xp_cmdshell
• Audit Specifications, DDL Triggers, and Proxy Credentials, lists every server credential, which is where ##xp_cmdshell_proxy_account## shows up
• Service Broker Health and Database Mail Queue, when Database Mail XPs reads 1 and alerts still are not arriving
• 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.