An SQL Server instance is a complete SQL server and you can install many instances on a machine but you can have only 1 default instance. It represents a separate and independent database environment that can store and manage data. In this article, I will teach you how to verify if the xp_cmdshell feature is enabled or disabled in MSSQL Server. Please see how to fix “There has been a runner system failure: failed to start the process exec “pwsh”, and how to fix “Unable to connect to MSSQL Server after changing the Server name“.
Recently, there was a question raised on the Veeam Community Forum “Does Veeam installation process enable xp_cmdshell under MSSQL?”. Having answered this question, I wanted to show you guys the steps to verify this.
Note: Veeam Backup & Replication typically does not enable the
xp_cmdshellfeature in Microsoft SQL Server during the installation process.
What is xp_cmdshell?
The xp_cmdshell is an SQL server extended stored procedure. It enables you to run command shell commands from within SQL Server. Please be aware that enabling it can introduce security risks.
This option is disabled by default. Also, to limit access to using xp_cmdshell only, members of the sysadmin server role have default rights.Veeam Backup & Replication primarily interacts with SQL Server databases to manage its configurations and maintain records but does not enable or use xp_cmdshell. See the VBR Database for more information. If you are having concerns related to security reasons. Follow the steps below to verify!
Is xp_cmdshell Enabled?
To verify if “xp_cmdshell” is either enabled or disabled in SQL Server. Please follow the steps below. Please see How to uninstall Microsoft SQL Server on Windows, How to uninstall Microsoft SQL Server on Windows, and how to uninstall Microsoft SQL Server Management Studio.
Using SQL Server Management Studio (SSMS)
Open SQL Server Management Studio. as shown below
Connect to the SQL Server instance where you want to check the xp_cmdshell configuration.
In the Object Explorer, expand the server node, right-click on the instance name and select FACETS as shown below.
Here you will see a list of facets for various SQL Server features. Select Server Security.
You can find “xp_cmdshell” in the list.
If it’s set to “True,” it means the feature is enabled; if it’s set to “False,” it’s disabled.
If you wish to enable it, please take all necessary precautions before doing so. To enable it, click on the property value and select True. Click on OK and apply.
Note: xp_cmdshell is a powerful feature and is disabled by default. xp_cmdshell can be enabled and disabled by using Policy-Based Management or by executing sp_configure.
There is not a need to enable show advanced options or use reconfigure, the GUI takes care of this automatically.What is T-SQL?
T-SQL is an extension of SQL. SQL is a programming language. T-SQL contains procedural programming and local variables, while SQL does not. T-SQL is proprietary, while SQL is an open format.
Enable or Disable XP_CMDSHELL via T-SQL
Determine the status of xp_cmdshell using T-SQL queries
You can also check the status of xp_cmdshell using T-SQL queries. Open a new query window in SSMS and run the following command:sqlCopy code
EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell'; The first command enables the display of advanced options, and the second command displays the status of xp_cmdshell.
Look for the “config_value” column in the result. When it is 1, the feature is enabled. When it is 0, it is disabled.
Configure xp_cmdshell
To enable or disable xp_cmdshell, you can use the following T-SQL commands:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;Disable xp_cmdshell
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;After executing these commands, make sure to restart the SQL Server service for the changes to take effect.It’s important to note that enabling xp_cmdshell can pose security risks as discussed above as it allows for command execution on the operating system from within SQL Server.
FAQs relating to xp_cmdshell Permissions
Malicious users oftentimes attempt to elevate their privileges by using xp_cmdshell.
While I advocate the use of xp_cmdshell with caution. There are legitimate use cases for it in SQL Server. Some common scenarios include:
– Running batch scripts for maintenance or data import/export tasks.
– Interacting with the operating system to perform backups or file manipulations.
– Automating administrative tasks.
– Integrating with external tools or processes that require command-line execution.
Conclusion on xp_cmdshell
However, this should not be a concern anymore, Veeam no longer installs SQLExpress during installation, but rather PostgreSQL by default. Therefore, I recommend checking the documentation or reaching out to Veeam support for the most up-to-date information regarding their software’s installation and configuration requirements.
I hope you found this post useful on how to verify if the xp_cmdshell feature is enabled or disabled in MSSQL. If you have any questions, please let me know in the comment session.