1. Mistake: treating a disabled feature as sufficient protection. A DDL trigger executes when database-definition or instance-level configuration events occur, allowing a defensive response before a dangerous change completes. section 14.4
For example, a trigger can watch ALTER_INSTANCE events, inspect the submitted command, log an error when it finds sp_configure and xp_cmdshell, roll back the transaction, and support a P1 alert. This adds detection and delay, but remains defense in depth rather than a complete security solution. section 14.4
Preventing xp_cmdshell enablement
Create an ALL SERVER trigger for ALTER_INSTANCE, extract the command text with EVENTDATA(), test it with CHARINDEX(), raise a logged custom error, and issue ROLLBACK when the command targets xp_cmdshell. section 14.4
Listing 14.5 Creating a trigger to prevent xp_cmdshell execution
EXEC sp_addmessage 50001, 16, 'Attempt to disable xp_cmdshell' ;
GO
CREATE TRIGGER prevent_xp_cmdshell
ON ALL SERVER
FOR ALTER_INSTANCE
AS
BEGIN
DECLARE @Statement NVARCHAR(4000) ;
SET @Statement = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(4000)')) ;
IF (CHARINDEX('sp_configure', @Statement) > 0)
AND (CHARINDEX('xp_cmdshell', @Statement) > 0)
BEGIN
RAISERROR(50001, 16, 1, 'Attempt to disable xp_cmdshell') WITH LOG ;
ROLLBACK ;
END
END ;
GO
Use the executable T-SQL listing to inspect the trigger’s event scope, command matching, error logging, and rollback behavior. section 14.4
