Extended procedures (xp_*) / surface area

Status: Reviewed
Stack: PowerShell → SQL / stored procs → Blazor
Depends on: SQL-CLI-09 (overview), SQL-CLI-01 (calling), SQL-CLI-09a (show advanced / Agent XPs / Ole Automation / xp_cmdshell config), SQL-CLI-07 (pipeline shapes)
Goal: Treat surface-area extended procs as policy, not convenience — know enablement via sp_configure, when any xp_* is justified, and the safer outsides (PowerShell on the box, Agent CmdExec + proxy, careful CLR, External Scripts).

Sources: Microsoft Learn (sql-server-ver17 / 2026). Expands SQL-CLI-09 §3.6 only — do not treat this as a rewrite of the overview. Config write path detail: SQL-CLI-09a. Calling: SQL-CLI-01. Staging → Blazor: SQL-CLI-07.

1. Scope map — surface-area XPs vs safer alternatives

Default posture for Rick’s AD/Windows shop: OS work stays in PowerShell on a jump box / the host, or in SQL Agent CmdExec / PowerShell job steps with a least-privilege proxy. The engine stores and transforms data; it is not a remote shell.

2. Decision table: when ANY xp_* is justified

Rule: If the work can run as PowerShell under a constrained identity without widening the SQL surface, do that. Enable advanced surface options only with a written exception.

3. Curated procs & enablement policy

All sp_configure writes need ALTER SETTINGS (sysadmin / serveradmin hold it). Pattern always:

USE master;
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
-- set the option …
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;  -- steady-state: leave advanced off
RECONFIGURE;

Confirm with sys.configurations (value / value_in_use / is_dynamic) — see 09a. These surface options are dynamic (no restart) unless Learn says otherwise for a specific option.

3.1 xp_cmdshell — highest risk

Break-glass enable (then disable):

USE master;
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
-- … minimal work …
EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;

Proxy (only if non-sysadmin must call it): create a locked-down domain/local account → EXEC sp_xp_cmdshell_proxy_account N'DOMAIN\LeastPriv', N'<password>'; — treat the password like any secret; rotate; do not leave broad GRANT EXEC hanging around.

3.2 Ole Automation procedures (sp_OA*) — prefer not

3.3 xp_dirtree / xp_fixeddrives / xp_fileexist — inventory usefulness vs caveats

| Proc | Usefulness | Caveat | | --- | --- | | xp_fixeddrives | Quick interactive free-MB per drive letter | Undocumented — fine for DBA peek; do not build Blazor/fleet contracts on it | | xp_dirtree | Recursive directory listing under a server path | Undocumented; host filesystem visibility; empty/limited for non-privileged callers | | xp_fileexist | File / parent-dir existence check | Undocumented; prefer sys.dm_os_file_exists (documented DMF where available) or PS Test-Path |

Policy: useful for interactive ops and some Agent pre-checks; never a public API for the app pool. Prefer host PowerShell or supported DMFs for anything that feeds dashboards.

3.4 xp_readerrorlog / sp_readerrorlog — still useful for CLI log scrape

EXEC sp_readerrorlog;                          -- current SQL log
EXEC sp_readerrorlog 1, 2;                     -- previous Agent log
EXEC sp_readerrorlog 0, 1, N'database', N'start';

(xp_readerrorlog is the related extended proc many scripts still call; prefer the documented sp_readerrorlog in new scripts.)

3.5 xp_logininfo — Windows group expansion

EXEC xp_logininfo N'BUILTIN\Administrators';
EXEC xp_logininfo N'CONTOSO\SqlAdmins', N'members';

3.6 Database Mail XPs / Agent XPs — configure gates (pointer)

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Agent XPs', 1; RECONFIGURE;           -- gate only
EXEC sp_configure 'Database Mail XPs', 1; RECONFIGURE;   -- gate only

These are legitimate surface toggles when you actually run Agent or Mail — they are not excuses to enable xp_cmdshell or Ole Automation.

4. Permissions + surface-area hardening checklist

Quick inventory (Blazor-friendly single set):

SELECT name, value, value_in_use, is_dynamic, is_advanced
FROM sys.configurations
WHERE name IN (
  N'xp_cmdshell',
  N'Ole Automation Procedures',
  N'Agent XPs',
  N'Database Mail XPs',
  N'clr enabled',
  N'Ad Hoc Distributed Queries',
  N'show advanced options'
);

5. Gotchas & anti-patterns (AD/Windows ops)

1. “We’ll leave cmdshell on; it’s just the lab.” — Labs become prod patterns; auditors don’t care.
2. Sysadmin runs cmdshell as the service account — often Local System / high-priv domain account → host takeover class risk.
3. Granting EXEC without a proxy — non-sysadmin then fails or you widen who is sysadmin instead. Wrong either way.
4. Ole Automation for “HTTP from SQL” — ancient pattern; replace with outside worker.
5. Building dashboards on xp_fixeddrives / xp_dirtree — undocumented shapes break; use PS/monitoring/DMFs.
6. xp_logininfo for universal groups — returns incomplete AD truth; you’ll mis-read membership.
7. Enabling Agent XPs and assuming Agent is running — gate ≠ service. Check the Windows/SQL Agent service (09d).
8. Blazor or SSRS “helper” proc that wraps xp_cmdshell — indefensible; move to Agent/PS.
9. Forgetting to disable after break-glass — automate the off path in the same change ticket.
10. Azure SQL Database — no classic Agent / classic surface model like on-box SQL; don’t copy-paste enable scripts expecting the same world (MI closer to box, still validate).

6. Blazor: never expose xp_cmdshell from the app pool

Staging shape sketch:

SELECT @@SERVERNAME AS ServerName, name AS OptionName,
       CAST(value AS int) AS ConfigValue,
       CAST(value_in_use AS int) AS RunValue,
       SYSUTCDATETIME() AS CollectedUtc
FROM sys.configurations
WHERE name IN (N'xp_cmdshell', N'Ole Automation Procedures',
               N'Agent XPs', N'Database Mail XPs');

7. Recipes (detection / hardening + labeled break-glass)

R1 — Surface-area inventory (sqlcmd)

sqlcmd -S "$SQL_SERVER" -E -d master -b -Q "
SET NOCOUNT ON;
SELECT name, value, value_in_use
FROM sys.configurations
WHERE name IN (N'xp_cmdshell', N'Ole Automation Procedures',
  N'Agent XPs', N'Database Mail XPs', N'show advanced options')
ORDER BY name;"

R2 — Assert cmdshell & Ole are off (gate check)

sqlcmd -S "$SQL_SERVER" -E -d master -b -Q "
IF EXISTS (SELECT 1 FROM sys.configurations
           WHERE name = N'xp_cmdshell' AND value_in_use <> 0)
  THROW 50001, 'xp_cmdshell is enabled', 1;
IF EXISTS (SELECT 1 FROM sys.configurations
           WHERE name = N'Ole Automation Procedures' AND value_in_use <> 0)
  THROW 50002, 'Ole Automation Procedures is enabled', 1;"

R3 — Disable xp_cmdshell (hardening)

sqlcmd -S "$SQL_SERVER" -E -d master -b -Q "
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;"

R4 — Disable Ole Automation

sqlcmd -S "$SQL_SERVER" -E -d master -b -Q "
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;"

R5 — ERRORLOG keyword scrape (allowed)

sqlcmd -S "$SQL_SERVER" -E -d master -b -Q \
  "EXEC sp_readerrorlog 0, 1, N'Login failed';"

R6 — Windows group members triage (xp_logininfo)

sqlcmd -S "$SQL_SERVER" -E -d master -b -Q \
  "EXEC xp_logininfo N'CONTOSO\SqlAdmins', N'members';"

R7 — Safer alternative: Agent CmdExec posture (prefer over cmdshell)

Use an Agent job step subsystem Operating system (CmdExec) or PowerShell, run as a proxy mapped to a least-privilege credential — see 09d for start/help procs and roles. Do not enable xp_cmdshell to “make the same thing easier.”

R8 — Safer alternative: PowerShell on the box (drive / path inventory)

# Run on the SQL host or via remoting — not via xp_cmdshell
Get-Volume | Select-Object DriveLetter, FileSystemLabel, SizeRemaining, Size
Test-Path -Path 'D:\Backups\AppDb.bak'

R9 — BREAK-GLASS enable xp_cmdshell, whoami, disable

# Ticketed exception only — enable → prove token → disable in same window
sqlcmd -S "$SQL_SERVER" -E -d master -b -Q "
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
EXEC xp_cmdshell 'whoami.exe';
EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;"

R10 — BREAK-GLASS controlled command with return code (then disable)

-- Only inside a change window; disable when done (R3 / R9 pattern)
DECLARE @result int;
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
EXEC @result = xp_cmdshell N'dir D:\Backups\*.bak', NO_OUTPUT;
SELECT @result AS CmdShellReturnCode;  -- 0 success, 1 failure
EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;

Prefer R7/R8. R9/R10 exist so ops has a documented break-glass — not a habit.

8. Pointers to 09 / 09a–d

Related stack: SQL-CLI-01 (calling / -b), SQL-CLI-06 (Windows auth / groups), SQL-CLI-07 (Collect→Stage→Blazor).

Learn anchors (ver17): xp_cmdshell (Transact-SQL), Server configuration: xp_cmdshell, sp_xp_cmdshell_proxy_account, Server configuration: Ole Automation Procedures, Server configuration: Agent XPs, Server configuration: Database Mail XPs, sp_readerrorlog, xp_logininfo, Surface area configuration, sys.configurations, sys.dm_os_file_exists (prefer over undocumented file XPs where available).