Command shell setting
Job details
| Name: | Command shell setting | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Premium package: | Security and compliance package | |
| Description: | Checks if the xp_cmdshell is enabled, as a security best practice it is recommended to only enable it for the duration of the actual task that requires it. | |
| Long description: | checks if the xp_cmdshell is enabled, as a security best practice it is recommended to only enable it for the duration of the actual task that requires it. | |
| Version: | 1.2 | |
| Default schedule: | 5 1 1 * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| return status | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the “xp_cmdshell” parameter is enabled. |
| disable cmd shell setting | NO | If set to “YES“ the alert will disable “xp_cmdshell” (if it is enabled) by running sp_configure stored procedure. |
| history threshold | 365 | The maximum number of days to keep statistics for in the historic tables. |
Job Summary
- Purpose: The purpose of this job is to monitor and potentially modify the configuration of the ‘xp_cmdshell’ setting in SQL Server, ensuring it adheres to best practices for security.
- Why: This job is significant because ‘xp_cmdshell’ can execute shell commands on the OS, which could be a security risk if not properly managed. Monitoring this setting helps maintain strict control over SQL Server security configurations, minimizing potential vulnerabilities.
- Manual checking: You can manually check the ‘xp_cmdshell’ setting status in the database by using the following SQL command:
SELECT value_in_use FROM sys.configurations WHERE name = 'xp_cmdshell'Job Details
- “Version”: 1.2 (with an upgrade path from 1.1 included)
- “Company”: dbWatch.com
- “Default Schedule”: Every day at 1:05 AM with configuration checks every hour.
- “Acknowledgements”: Required, implying any changes or results the job determines are acknowledged by a user or script.
Dependencies
The job ensures certain required dependencies are in place with clean-up on failure:
- Main Procedure: ‘dbw_cmd_shell_setting_proc’ is the core component managing the monitoring and optional adjustment of the setting.
- Current Status and History Tables: These are ‘dbw_cmd_shell_setting_info’ and ‘dbw_cmd_shell_setting_histr’, which log current settings and historical changes respectively.
Implementation
The job implementation involves:
- Creating necessary database structures to log and manage settings.
- A procedure (‘dbw_cmd_shell_setting_proc’) that:
- Checks the current setting of ‘xp_cmdshell’.
Report Templates
The report consists of various parts:
- “Current configuration”: Lists current settings directly from the server’s configurations.
- “Last day configuration history” and “Aggregated statistics”: These show comprehensive views of parameter history over time, aiding in audit and compliance needs.
The output in these reports includes whether ‘xp_cmdshell’ was enabled or disabled, frequency of checks, and the respective dates and times these parameters were recorded.
Security and Compliance
- Compliance Type: Configuration and Encryption Settings
- Security Check: Monitoring critical system settings concerning command shell access, ensuring only necessary, temporary enablement as per security best practices.
Upgrade and Maintenance
- Versioning: Detailed steps for upgrading from version 1.1 to 1.2, highlighting changes in stored procedures and error handling.
- Compatibility Check: Ensures this job is compatible with instance types of SQL Server version greater than 2005, including Azure SQL Managed Instances, emphasizing broad applicability and importance in diverse environments.