Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Replace xp_cmdshell command line use with SQL Server Agent


By:   |   Read Comments (17)   |   Related Tips: More > 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(NVARCHARGETDATE(), 121) ; 

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

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

        
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


Last Update:






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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, May 05, 2016 - 1:37:30 AM - Reto Back To Top

that's great. thank you! 

 


Friday, September 07, 2012 - 1:21:40 AM - Dave Back To Top

@BitBangerDBA:

Thanks, but my question refers to the article, not your code :)


Wednesday, September 05, 2012 - 2:20:50 PM - BitBangerDBA Back To Top

@Dave:

It is right there! When you execute xp_cmdshell, you can capture the output return code from your DOS command. For example, declare @rc int; exec @rc=xp_cmdshell 'copy c:\boot.ini c:\bootcopy.int';if @rc<>0 print 'Error on copy of boot.ini'


Wednesday, September 05, 2012 - 4:44:27 AM - Dave Back To Top

Very nice script, but how can the stored procedure indicate that e.g. a file copy command failed for whatever reason? I know that I can see it in the job history, but I want to get the success or failure of the @command as a result of the stored procedure? How do I go about it?


Wednesday, May 18, 2011 - 5:57:59 PM - BitBangerDBA Back To Top

All:

This is something I do from within a stored procedure. I first check to see if access to the xp_cmdshell is turned on, if not I turn it on. Then I do a Dir command to see if I have a backup file present. If not I back up the DB, otherwise, I rename the backup file with the "REN" command. Finally, I turn off the xp_cmdshell access. Of course you could just assume it is off, turn it on, use it and then turn it off again.

Regards,

 DECLARE @TMPTBL    TABLE (LINEOUT    NVARCHAR(4000)) --TEMP TABLE FOR OUTPUT OF "DIR" CMD

 declare @runval  int

 declare @cfgtbl  table
  ([name] nvarchar(35),
  [minimum] int,
  [maximum] int,
  [config_value] int,
  [run_value] int)

   INSERT INTO @cfgtbl
    EXEC sp_configure 'xp_cmdshell'
   SELECT @runval = [run_value] FROM @cfgtbl
   IF @runval = 0
    BEGIN
     SET @NEXSTR = N'EXEC SP_CONFIGURE ''xp_cmdshell'', 1; RECONFIGURE WITH OVERRIDE;'
     EXEC @RC=SP_EXECUTESQL @NEXSTR
     IF @RC <> 0
      BEGIN
       RAISERROR('USP_ARCHIVE_PROCESS: ERROR EXECUTING SP_CONFIGURE, EXITING.', 16, 1) WITH LOG
       RETURN(33)
      END
    END
   SET @EXSTR = 'DIR ' + @TGT_BACKUP_DIR + DB_NAME() + '_AP_FULL.BAK'
   INSERT INTO @TMPTBL
     EXEC @RC=XP_CMDSHELL @EXSTR
   IF @@ERROR <> 0
    BEGIN
     RAISERROR('USP_ARCHIVE_PROCESS: ERROR INSERTING OUTPUT OF DIR CMD INTO TMP TABLE, EXITING.', 16, 1) WITH LOG
     RETURN(34)
    END
   IF @RC > 1
    BEGIN
     RAISERROR('USP_ARCHIVE_PROCESS: ERROR EXECUTING DIR CMD.', 1, 1) WITH LOG
     -- BACKUP THE ARCHIVE DATABASE
     SET @EXSTR = 'BACKUP DATABASE ' + DB_NAME() + ' TO DISK=''' + @TGT_BACKUP_DIR + DB_NAME() + '_FULL_' + @DATEWORKFMT + '.BAK'''
     EXEC(@EXSTR)
     IF @@ERROR <> 0
      BEGIN
       RAISERROR('USP_ARCHIVE_PROCESS: ERROR BACKING UP THE ARCHIVE DB, EXITING.', 16, 1) WITH LOG
       RETURN (8)
      END
    END
   ELSE
    BEGIN
     IF EXISTS (SELECT *
         FROM @TMPTBL
         WHERE LINEOUT LIKE N'%FILE NOT FOUND%')
      BEGIN
       -- BACKUP THE ARCHIVE DATABASE
       SET @EXSTR = 'BACKUP DATABASE ' + DB_NAME() + ' TO DISK=''' + @TGT_BACKUP_DIR + DB_NAME() + '_FULL_' + @DATEWORKFMT + '.BAK'''
       EXEC(@EXSTR)
       IF @@ERROR <> 0
        BEGIN
         RAISERROR('USP_ARCHIVE_PROCESS: ERROR BACKING UP THE ARCHIVE DB, EXITING.', 16, 1) WITH LOG
         RETURN (8)
        END
      END
     ELSE
      -- SIMPLY RENAME THE BACKUP
      BEGIN
       SET @EXSTR = 'RENAME ' + @TGT_BACKUP_DIR + DB_NAME() + '_AP_FULL.BAK ' + DB_NAME() + '_FULL_' + @DATEWORKFMT + '.BAK'
       EXEC @RC=XP_CMDSHELL @EXSTR
       IF @@ERROR <> 0
        BEGIN
         RAISERROR('USP_ARCHIVE_PROCESS: ERROR RUNNING XP_CMDSHELL, EXITING.', 16, 1) WITH LOG
         RETURN (8)
        END
       IF @RC <> 0
        BEGIN
         RAISERROR('USP_ARCHIVE_PROCESS: ERROR RUNNING THE RENAME CMD, EXITING.', 16, 1) WITH LOG
         RETURN (36)
        END
      END
    END
   DELETE FROM @TMPTBL
   IF @runval = 0
    BEGIN
     SET @NEXSTR = N'EXEC SP_CONFIGURE ''xp_cmdshell'', 0; RECONFIGURE WITH OVERRIDE;'
     EXEC @RC=SP_EXECUTESQL @NEXSTR
    END


Wednesday, May 18, 2011 - 11:37:53 AM - K. Brian Kelley Back To Top

When the job runs, there should be a job history. You'll see the results of the dir command in that job history.

In practice, you would put into place whatever it is that you actually wanted to execute form the command line. The reason I chose dir is because it doesn't do anything to the system, the command is in every command shell, and it makes an ideal way to test the concept and understand how it works.

 


Wednesday, May 18, 2011 - 8:23:22 AM - Dom Back To Top

Okay, I'm a novice at this.  I executed the script as it is written, and I actually expected to see the results somewhere in QA, at least in the message window.  Instead, it seems like the results are just lost.  Why would I ever run this?  Or is the idea that I would never run it as written for "DIR", but instead do something like "COPY from to"?


Monday, May 16, 2011 - 2:34:56 PM - Jeff Moden Back To Top

Job Step 1 enables xp_cmdshell; step 2 runs the process; step 3 disables xp_cmdshell. 

That will make for some interesting discussions insofar as concurrency goes. ;-)


Monday, May 16, 2011 - 9:51:37 AM - Ed Zann Back To Top

I also use BitBanger's method.  Job Step 1 enables xp_cmdshell; step 2 runs the process; step 3 disables xp_cmdshell.  I had to do this in response a recent network security audit.  It seemed like the simplest approach so that I didn't have to fuss with changing the actual process itself.  I would be interested if anyone sees a downside to this approach.


Monday, August 30, 2010 - 11:55:11 AM - BitBangerDBA Back To Top
While this approach works, it has several limitations. In general, when I need to use xp_cmdshell, I simply enable it, run the command and then disable it. This provides the output of the execution directly back to SQL Server as a record set. If a user or application needs this capability, it is easy and stright forward to set up a procedure and provide the permission archetecture to accomplish this. The other option is to either re-direct the command output in the command itself, or direct the job output to a file. Then you would need to read the file output to extract the information you desire.


Monday, June 07, 2010 - 12:40:39 PM - Jeff Moden Back To Top

[quote user="K. Brian Kelley"]

Jeff, it may have to be done to a file, but let me try a few things. This may make for a follow-on tip.

[/quote]

 Thanks Brian.  I do know how to use the DOS ">" redirection command to create a file... I was just hoping we could get output from the fine method you wrote the tip on because I can think of a lot of other things where such output may prove useful in the sense of confirming actions and the like.


Monday, June 07, 2010 - 12:37:46 PM - Jeff Moden Back To Top

[quote user="admin"]

Take a look at this tip: http://www.mssqltips.com/tip.asp?tip=1173 to see if this does what you are looking for to get a file list.

[/quote]

 Thanks for the response.  Yep... I know how to do it using xp_CmdShell.  Brian's method executes DOS commands without having to use xp_CmdShell and I was wondering if there a way to get the output from a "DIR" using his method to specifically avoid xp_CmdShell.


Monday, June 07, 2010 - 11:23:06 AM - K. Brian Kelley Back To Top

Jeff, it may have to be done to a file, but let me try a few things. This may make for a follow-on tip.


Monday, June 07, 2010 - 10:54:05 AM - admin Back To Top

Take a look at this tip: http://www.mssqltips.com/tip.asp?tip=1173 to see if this does what you are looking for to get a file list.


Friday, June 04, 2010 - 9:51:45 PM - Jeff Moden Back To Top

Hi Brian,

 Nicely done.  Owing up to the fact that I've not done such a thing in the past, let me ask about the example code you posted exactly like it is.  The code executes a "Dir" command.  Using your good code, is it possible to capture the output of that command in a table or would I just have to write it out to a file using "redirection" and import that?  If it's possible to capture the output of that command in a table, would you show me how please?  I could really use this for some of the things coming up because I really don't want to arm wrestle with a Systems DBA to get him to enable xp_CmdShell. ;-)

 Thanks for the help and the tip.


Thursday, May 20, 2010 - 2:29:29 PM - K. Brian Kelley Back To Top

As it is currently written, a member of sysadmin or one of the following roles:

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

It's intended more as a replacement for sysadmin's who need to run a command script on the SQL Server. If you wanted a less privileged user to have access to it, in SQL Server 2005/2008 you could create the stored procedure with an EXECUTE AS LOGIN clause, and that should give the appropriate permissions to do what the stored procedure needs to do without giving them too many rights throughout the whole SQL Server.

 


Thursday, May 20, 2010 - 1:48:00 PM - jerryhung Back To Top
Cool trick Question - what permission is needed to call the SP usp_ExecCmdShellProcess then? sysadmin? or SQLAgentUser role? - I would guess either encrypting the SP, or deny any editing helps to prevent unauthorized users to modify the command part

Learn more about SQL Server tools