KB Issue

SQL Server Management Studio Job may not work if the xp cmdshell is not enabled.

Issue Details

By default, [xp cmdshell] is disabled, which will cause the following error to occur during a [SQL Server Agent][Job].

SQLSTATE 42000 Error 15281

Date	6/18/2021 9:30:00 PM
Log	Job History (Transaction Backup)
Step ID	1
Server	SQL-06\SQL2019
Job Name	Transaction Backup
Step Name	Trans Backup
Duration	00:00:00
Sql Severity	16
Sql Message ID	3013
Operator	Emailed
Operator	Net sent
Operator	Paged
Retries Attempted	0

Message
Executed as user: Your-Domain\sqlagent. ...ponent '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. [SQLSTATE 42000] (Error 15281)

Recreate Issue

If the [XPCmdShellEnabled] is set to [False], this error will happen.

Resolve Issue

To enable the [xp cmdshell].

Right-click on the SQL Server Instance Name on the left side of the SSMS Tree.
Choose [Facets]
Choose [Surface Area Configuration] from the [Facets] Menu.
Change the value of the [XPCmdShellEnabled] from [False] to [True].
Click [OK]

Rerun your job, and this time it should complete without issue.