Replace xp_cmdshell command line use with SQL Server Agent

By: K. Brian Kelley   |   Updated: 2010-05-19   |   Comments (19)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Security

Problem

I need to run something from the command-line, but based on best practices xp_cmdshell has been disabled. The task that needs to run is an internal process that will originate from within SQL Server. Is there a way to do this without using xp_cmdshell?

Solution

The use of xp_cmdshell is generally frowned upon and is now recommended to be disabled, unless it is absolutely necessary. There is a solution that works around this restriction via the use of SQL Server Agent.

Wherever SQL Server is installed, SQL Server Agent is installed with it (except for SQL Express). SQL Server Agent has the ability to run job steps which invoke the command shell. And in the case that the job is owned by a member of the sysadmin fixed server role, the job will execute as the service account under which the SQL Server Agent is running.

The SQL Server Agent service account must be a member of the sysadmin fixed server role for the SQL Server, so any SQL Server-related permissions are covered. It's also not unusual to see the SQL Server service account and the SQL Server Agent service account to be one and the same. If this is the case, then the use of a SQL Server Agent job is the equivalent of xp_cmdshell as far as overall access. I should point out that some recommend these accounts to be different. If that's the case, it would be the SQL Server Agent service account you would want to ensure has the appropriate permissions to do whatever it is you need to do at a command prompt. If the SQL Server Agent service account isn't appropriate, SQL Server 2005 and 2008 allow for the use of credentials to start the process as a different Windows account. SQL Server 2000 is limited to use a proxy account (a single account). We won't cover how to implement credentials or the proxy account here, because we'll focus on the process of building the job to do what we need.

We'll use the following stored procedures in order to setup and execute our job:

• sp_add_job
• sp_add_jobstep
• sp_add_jobserver
• sp_start_job

All of these exist in the msdb database, and I'm assuming that they will be called from a user database. That means we'll need to use a three part naming convention of msdb..[stored procedure]. Other than that, the steps are self explanatory:

1. We create the job which will perform our command line operation. So as not to clutter up the job list, we'll set it to delete automatically when the job completes successfully. Also, we'll build a name based on the time to differentiate multiple executions of our process. That way we can see what errors may crop up. We'll use sp_add_job to do all of this.
2. We will create a job step for our job which actually executes the command line operation. Not much more needs to be said here. We'll use sp_add_jobstep to do this.
3. We will assign our job to the current SQL Server so the Agent will know that it's a local job. This step is required. We'll use sp_add_jobserver to set the server. Default settings, with the exception of the job name, is all we need.
4. Then we'll execute the job. This will fire it immediately, giving us almost identical functionality to xp_cmdshell.

Here's an example stored procedure which does everything in the list. The command line operation I have it performing is simply a dir listing of C:\. You could even modify the job to take in the command as a parameter which defines what is being run. The @command parameter for sp_addjobstep is defined as NVARCHAR(MAX) for SQL Server 2005/2008. You would just need to define such a parameter in the stored procedure definition.

Here's our example procedure:

CREATE PROCEDURE dbo.usp_ExecCmdShellProcess 
AS  
    BEGIN 
        DECLARE @job NVARCHAR(100) ; 
        SET @job = 'xp_cmdshell replacement - ' + CONVERT(NVARCHAR, GETDATE(), 121) ;  

        EXEC msdb..sp_add_job @job_name = @job, 
            @description = 'Automated job to execute command shell script', 
            @owner_login_name = 'sa', @delete_level = 1 ;  

        EXEC msdb..sp_add_jobstep @job_name = @job, @step_id = 1, 
            @step_name = 'Command Shell Execution', @subsystem = 'CMDEXEC', 
            @command = 'dir c:\', @on_success_action = 1 ;  

        EXEC msdb..sp_add_jobserver @job_name = @job ;  

        EXEC msdb..sp_start_job @job_name = @job ;  

    END ; 
GO 

So based on the above stored procedure, once the stored procedure has been created we could just execute the stored procedure and it will create the job, run the job and then the job will be removed after successful completion.

And that's really all there is to it. We let SQL Server Agent take care of the command shell execution, thereby offloading it from SQL Server and removing the need to use xp_cmdshell. I will say that the usual warnings apply. Make sure that whatever is passed into the job step is something you intend to run. After all, it will be running on the same server as your SQL Server database engine and it will be running, potentially, as an account that has sysadmin rights with that SQL Server.

Next Steps

• Next time you have the need to run a command line task look at using this approach instead of using xp_cmdshell
• Take the next step and modify this stored procedure to accept the actual command to make this more dynamic
• Read these additional security related tips

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips