xp_cmdshell in SQL Server

You might have run across several types of issues while using xp_cmdshell to work with dynamic SQL. You may learn more about these issues, their causes, and the recommended fixes in this post.

SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, search for ‘xp_cmdshell’ in SQL Server Books Online.

The code below uses sp configure to enable xp cmdshell. Each of these settings must be followed by the RECONFIGURE command in order to take effect.

— The below turns on advanced options and is needed to configure xp_cmdshell
EXEC sp_configure ‘show advanced options’, ‘1’
— The below enables xp_cmdshell
EXEC sp_configure ‘xp_cmdshell’, ‘1’

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file

  • The credentials for the SQL Server service account are required by XP CMDSHELL in order to access the filesystem and other resources. The service account won’t have access to the root of C:. Additionally, write rights must be granted for the folder where the file will be written.


  • The identical mistake occurs if you are running a script in your remote SQL Server while your file is in your local machine.
  • Check to see that the file is indeed present in the folder you are writing the result-set to.
  • Make sure the file-path is properly supplied.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s