Level 8: Using External Programs with SQL Server Agent

In the previous level of this Stairway, you learned how to monitor job activity and view job history with the SQL Server Agent Job Activity Monitor. The Job Activity Monitor is a great tool to use for real time monitoring and administration of your SQL Server agent jobs. For Level 8 in this stairway, you are going to look back into another capability of SQL Server Agent – the ability to shell out to the operating system and run programs outside of the SQL Server environment. Some of those programs will be command line (either traditional cmd applications/scripts or PowerShell scripts), or even ActiveX scripts, but you can actually shell out and run nearly any program, as long as the program doesn’t expect direct user input. You will look at how to run cmdexec and PowerShell scripts, and we will have a brief discussion of when each subsystem would be appropriate. You will learn how to call other programs from SQL Server Agent.

Built-in Job Subsystems

There are several built-in job subsystems, as you have seen in previous articles. In this article, you will examine three of the built-in job subsystems that are capable of running scripts and/or programs within the scope of Windows Server, rather than within the context of the SQL Server environment itself. These three subsystems include:

• Operating System (CmdExec)
• PowerShell
• ActiveX Script

When a program or script is launched from one of these three subsystems, a separate process is created within Windows (i.e. not directly part of SQL Server Agent), the script or program is run, and then information is passed back to the SQL Server Agent job that started the process or script.

The Operating System (CmdExec) subsystem

The first subsystem we’ll review is the Operating System (CmdExec) subsystem. The CmdExec subsystem opens up a command prompt, as if you had logged into the Windows Server machine running SQL Server. From that point you can run anything you could otherwise type yourself in a command prompt window. This includes any batch file, script, or even program that exists on your server, and of course if you could type in a UNC path and access a file, so could the CmdExec subsystem.

But what about security?

Of course, the big caveat about all of this is permissions – what will work, and under what security context? By default, when you create a job in the CmdExec subsystem (or the others, as you will see), the job will run in the security context of the SQL Server Agent Service Account (as shown in Figure 1). You may also notice that this is a drop-down form value, so other options are possible. You will examine these proxy accounts in level 10 in the Stairway. Another important note: you must be a sysadmin login role member in SQL Server to be able to run jobs that impersonate (i.e. use the security rights of) the SQL Server Agent service account.

Figure 1: Job Security in a CmdExec job step

Creating a CmdExec job step

To create a CmdExec job step, create a new job (we can call it ShellOut), then add a New Job Step. As shown in Figure 1, we’ll name the step s1. Change the Type of job step to “Operating system (CmdExec)”, and leave the Run as: field at the default value (“SQL Server Agent Service Account”). For our first job, simply enter “dir c:\” as shown in Figure 1 for the command text. Click OK, then OK again to save the job. Run the job (right click on the job “ShellOut” and select “Start Job at Step”, then once the job has run right-click and select View History. Click on the job step output as shown in Figure 2 to see that a DIR was run against your C drive.

Figure 2: The Log File Viewer showing the results of a CmdExec job

As you might imagine, this is a trivial command but you could easily imagine running complex batch scripts, or as mentioned before, even start a program (for example, “start notepad” would run notepad). A word of caution is in order, however. If you launch a program such as notepad, it will be running in a virtual hidden desktop, waiting for input from a userAnd because the desktop is hidden, no user can supply any input, not even a command to exit the program. In other words, your job step won’t ever complete. You will have to find the notepad process in a program such as Task Manager, and kill the process for the job step to return. Running programs from the CmdExec subsystem enables many interesting scenarios as long as the program will naturally return control back to SQL Server Agent when it’s complete.

The PowerShell subsystem

The PowerShell subsystem was added to SQL Server with the SQL Server 2008 release. It will function with either PowerShell 1.0 or PowerShell 2.0, depending upon which version is installed on your server. When you create a job step and select the PowerShell subsystem for your job step type, you will have options similar to the CmdExec subsystem. You can enter the text of a PowerShell script, or call an existing PowerShell script (.ps1). When you start a PowerShell session from within SQL Server Agent, the SQL Server PowerShell provider and cmdlets are pre-loaded for you.

There are many additional considerations with PowerShell dealing with script signing and security, which are too extensive to go into for this article. However, you can read about them in this TechNet article from the Windows PowerShell Owner’s Manual: http://technet.microsoft.com/en-us/library/ee176949.aspx.

With that caveat, PowerShell could easily become your favorite subsystem for scripting work. For ordinary tasks within SQL Server, running a Transact-SQL script is usually simpler. However, repetitive tasks, or as in the case of CmdExec, any task that leaves the SQL Server environment is much simpler from the PowerShell subsystem.

To show an example, re-open your ShellOut job, and add a job step, s2. Select PowerShell for the Type, and then enter the following script:

$server = new-object( 'Microsoft.SqlServer.Management.Smo.Server' ) “(local)”
foreach ($database in $server.databases) 
{
$dbName = $database.Name
Write-Output "Database: $dbName" 
}

Figure 3: The Job Step for the PowerShell subsystem

This script will simply log into your local SQL Server (change the instance name if using a named instance), and then loop through and get the name of each database on the server. You can easily imagine backing up the database, or examining its properties, for example. The other thing to notice is that you had to log in and make a database connection – so nothing is stopping you from logging into any SQL Server in your organization. To try this out, click OK, the OK again. If you are prompted to, fix up the job step s1 so that it will flow properly into step s2. Run the job, and view the output as you did for the CmdExec subsystem step. You will see a list of your databases on your server has been output.

What’s really interesting about the PowerShell subsystem, however is that you can query information from the operating system, or active directory, etc. and then run just about any script you can think of to act upon your server. Do you want to query the ports SQL Server is listening on? You can use WMI through PowerShell. Do you want to get some information from the registry? You get the idea. There are many, many PowerShell scripts posted on the Internet (including many articles here at SQLServerCentral).

The ActiveX Script subsystem

The ActiveX Script subsystem allows you to run ActiveX scripts, which can use VBScript or Jscript to complete tasks in the operating system. This subsystem is included here for completeness, but you should not use ActiveX scripts from SQL Server Agent. The subsystem is deprecated, meaning it will be removed from a future version of SQL Server.

Which subsystem should I use?

If you have an existing job using one of these subsystems, you should probably continue to let it work unless you have a compelling reason to change it. However, if you are starting a new job and/or job step, the PowerShell subsystem offers the most compelling capabilities. Additionally, Microsoft has clearly moved in the direction of PowerShell as the standard scripting language for all Microsoft server products. An investment of your time learning PowerShell will pay dividends well beyond SQL Server Agent.

What’s Next

SQL Server Agent’s CmdExec, PowerShell, and ActiveX subsystems allow you to leave the confines of SQL Server to perform many tasks, including running batch files or external programs. Additionally, with PowerShell you can access and control just about any Microsoft product. PowerShell is the recommended subsystem to use for new work.

In our next level, we will start looking at SQL Server Agent security. Up until now, this Stairway has assumed you are a sysadmin server role member. The next step will examine the world from non-sysadmin users’ perspective with SQL Server Agent, and also look more into the security context for jobs.