SQL Server Agent (8/12): Using SQL Server Agent external programs

SQL Server Agent is the core of all real-time databases. Proxies have a lot of non-obvious uses, so the knowledge of the system is useful for developers or DBAs. This series of articles will be popular to introduce its many uses.

In the previous article in this series, you learned how to use SQL Server Agent job Activity Monitor to monitor job activity and view job history. The Job Activity Manager is a powerful tool for real-time monitoring and management of your SQL Server Agent jobs. In the 8th installment of this series, you'll look back at another feature of SQL Server Agent-running the operating system command line and running the program in the SQL Server perimeter environment. Some programs are command lines (traditional cmd applications/scripts or PowerShell scripts), or even ActiveX scripts, but you can actually run a system command line that can run almost any program, as long as the program does not require the user's direct input. You'll learn how to run CmdExec and PowerShell scripts, and we'll simply discuss when to use these subsystems more appropriately. You learned how to invoke other programs from SQL Server Agent.

built-in operating subsystem

As you can see in previous articles, there are many built-in subsystems. In this article, you will examine the 3 built-in subsystems that can run scripts or programs in the operating system, rather than the context of the SQL Server environment itself. These 3 include:

• Operating System (CMDEXEC)
• PowerShell
• ActiveX scripts

But when a program or script starts from this 3 system, a new process is created in the operating system (for example, not part of the SQL Server Agent), the script or program runs, and the information is passed back to the SQL Server Agent job that started the process or script.

operating System (CMDEXEC) subsystem

The first one we want to talk about is the operating system (CMDEXEC) subsystem. The CmdExec subsystem opens a command line as if you have logged on to the operating system running SQL Server. From that point, you can run everything and enter it on the command line. This includes any batch files, scripts, or even programs that exist on your server, and of course you can access the files at the input UNC path, which is what the CmdExec subsystem can do.

But what about security?

Of course, all the important reminders about this are permissions-will it work, under what security context? By default, when you create a job (or other, you'll see it) in the CmdExec subsystem, the job runs in the security context of the SQL Server Agent service account (as illustrated in Figure 1). You'll also notice that this is a drop-down value, so there are other options as well. In the 10th chapter of this series, you will use the proxy account. Another important point to note: You must be a member of the sysadmin group in SQL Server to run the SQL Server Agent service account run job.

Fig. 1: Safety of operations in CmdExec operation steps

Create a CmdExec job step

In order to create a CmdExec job step, create a new job (we named Shellout), and then add a new job step. As illustrated in Figure 1, we name the step S1, modify the job step type "operating system (CMDEXEC)", and keep the run as default ("SQL Server Agent Service Account"). For our first job, simply enter "dir c: \" as shown in Figure 1 of the command text. Click OK, and then click OK to save the job. Run the job (right-click the job "Shellout", select "Job Start Step"), and once the job finishes running, right-click to select View History. Click the output of the job step, as shown in Figure 2, the dir command has been executed for the C drive.

Fig. 2: Log file View CmdExec job execution results displayed

As you can imagine, this is a simple command, and it's easy to think of running a complex batch script, or even starting a program (for example, starting Notepad), as described earlier. As a reminder, if you start a program such as Notepad, it will run on the virtual hidden desktop, waiting for user input. Because the desktop is hidden, no user can enter, or even quit the program command is not. In other words, your work steps will never end. You'll need to find the process for Notepad in the task manager, kill it, and get the job step back. Running the program from the CmdExec subsystem will be a lot of interesting scenarios, as long as the program returns control to SQL Server Agent normally, it will not end.

PowerShell Subsystem

PowerShell has been added since SQL Server 2008 was released. It supports PowerShell 1.0 or Powersher 2.0, depending on the version installed on your server. When you create a job step and select the PowerShell subsystem as 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. When you start a PowerShell session from SQL Serverdialing, the SQL Server PowerShell is loaded in advance for you.

There are a lot of things to consider when using PowerShell script signing and security, and this topic is too big to be discussed here. But you can refer to it under its user manual: Https://technet.microsoft.com/en-us/library/ee176949.aspx

By the way, PowerShell can easily become your scripting tool. For general operations in SQL Server, such as running T-SQL is easier.

In any case, repetitive work, or operations in cmdexec cases, is simpler for any operation to leave the SQL Server environment from the PowerShell subsystem.

to show an example, reopen your shellout job and add a job step S2. Select PowerShell as the job type, such as the following script:

 1   $server  = New-object (' Microsoft.SqlServer.Management.Smo.Server ') "(local)"  2  Span style= "color: #008080;" >3  foreach  ( $database  in   $server  .databases)  4  { 5   $dbName  =  $database  . Name  6  write-output  " database: $dbName   7 } 

Illustration 3:powershell sub-system job steps

This script will log in directly to your local SQL Server (modify the instance name if you want to specify the server), and then loop over the server to get the name of each database. You can easily think of database backups, such as checking their properties. Also note that you are logged in and connected to the database-so you can log in to any SQL Server in your organization. To test this, click OK and click OK again. If prompted, fix the job step 1 so that it will move correctly to the next step. Run the job, and view the same method as the output of the CmdExec subsystem step. You will see that the output has a list of database names on the server.

The PowerShell subsystem is really interesting that you can query information from the operating system, Active Directory, and then run any scripts that you want to operate on your server. Do you want to query the port number that SQL Server listens on? You use PowerShell to query WMI. Do you want to get information from the registration form? is also possible. There are a lot of PowerShell scripts on the network that you can search for yourself according to your needs.

ActiveX Scripting Subsystem

The ActiveX scripting subsystem allows you to run ActiveX scripts and use VBScript or JScript in the operating system. This includes this subsystem as a supplement, but you should not use ActiveX scripting from SQL Server Agent. This subsystem has been stripped, which means that future SQL Server will remove this feature.

which subsystem should I use?

If an existing job uses one of the subsystems, you should continue to use it unless you have enough reason to modify it. Anyway, if you start a new job or job step, the PowerShell subsystem provides you with powerful enough functionality. In addition, Microsoft has used PowerShell scripts as a standard script for all Microsoft products. Taking the time to learn about PowerShell will make your SQL Server Agent very powerful!

Next Trailer

SQL Server Agent Cmdexec,powershell and the ActiveX subsystem allow you to perform many tasks with SQL Server Agent, including running batch files or external programs. In addition, you can access and control any Microsoft product using PowerShell. For new tasks, it is recommended to use the PowerShell subsystem.

In the next article, we'll talk about SQL Server Agent security. So far, this series of articles assumes that you are a member of the sysadmin service group, and the next step is to talk about SQL Server Agent from the perspective of the non-sysadmin group members of SQL Server Agent, and to drill down into the security context of the job.

SQL Server Agent (8/12): Using SQL Server Agent external programs