Integrating with SQL Server Agent

To eliminate some of the problematic Agent security practices such as encrypted command lines and so on, Microsoft introduced multiple Agent subsystems, which are dedicated environments for executing particular technologies, such as ActiveX Scripts, Analysis commands or queries, and SSIS packages. By default, only the sysadmin has rights to create jobs. To grant other users the rights to create jobs, the sysadmin creates proxy accounts, which are essentially wrapped credentials with Agent subsystem associations. Proxy accounts let sysadmins grant job creation permissions to other users while finely controlling the kinds of jobs those users can create.

Another problem in SQL Server 2000 DTS shows up when a user deploys a working package to the server but finds that it fails with errors showing that the package didn't have access to certain resources. SQL Server 2005 Agent provides a new Run As capability that lets a job step run under the credentials of a user or proxy. This capability means a developer can now create a package under the same credentials under which it runs on the server in Agent.

One more new role in SQL Server 2005 is the SQLAgentUserRole. A user must be in this role to create SQL Server Agent jobs, and users in this role can manage only jobs that they create. This role is important because it affects a common SSIS usage scenario. After they're built, tested, and deployed, a large percentage of packages reside on a SQL Server box in a back room somewhere and a SQL Server Agent job executes them. Because SQL Server Agent integration is important to the security of such packages, the following sections take a closer look at how it works.

Setting Up an Agent Job to Run an SSIS Package

There are several steps to creating an Agent Job to execute a package, as discussed in the following sections.

Create a Login

The SSIS Subsystem job step runs in the context of the SSIS proxy. The owner of the job needs to be given access to the proxy. The proxy has a credential associated with it and runs in the security context of the credential. The following section shows how to do it.

Create a New Login

The first step is to create a login for the user if one does not already exist. In some cases, this might not be a login for any person, but a dedicated login for running packages.

Open Microsoft SQL Server Management Studio.
Connect to the server where you want to create the user.
Open the Security, Logins node under the server.
Right-click on the Logins node.
Select the New Login menu option.
Type in the Login Name and other settings, as shown in Figure 18.9.

Figure 18.9. Creating a new login
Select the User Mapping node.
Put a check next to msdb.
With msdb selected, select the check box next to the database role for this login. This example uses db_dtsltduser.
Click the OK button.

Create a New User

Next, you should create a SQL Server user in one of the SSIS security roles.

Open the MSDB database node in the Object Explorer under System Databases.
Open the Security, Users node under MSDB.
Right-click on the Users node and select the New User menu option.
Type in the User Name.
Check the SQLAgentUserRole and one of the SSIS roles, for example, db_dtsltduser, as shown in Figure 18.10.

Figure 18.10. Creating a new user
Click the "..." button to browse for the login name you just created.
Click the Browse button.
Select the login name, as shown in Figure 18.11.

Figure 18.11. Selecting the login
Click the OK button three times.

Create a Credential

Next, the credential is a SQL Server object that references a windows account.

Right-click on the Credentials node under the server Security node.
Select the New Credential menu option.
Type in the credential name, windows account name, and password, as shown in Figure 18.12.

Figure 18.12. Creating the new credential

Create a Proxy and Associate It with the Credential

Next, create a proxy account and associate it with the credential so that the proxy can "run as" under the credential it is assigned.

Under the SQL Server Agent node, right-click on the Proxies node.
Select the New Proxy menu option.
Type in or browse for the credential name.
Select the SSIS subsystem, as shown in Figure 18.13.

Figure 18.13. Creating the new proxy
Click the OK button.

Create a New Job

After you've performed the previous steps, you shouldn't need to do them again unless you need to modify the settings in some way. The following steps are necessary whenever you want to create a new job to execute a package.

In the SQL Server Agent node, right-click the Jobs node.
Select New Job.
Type in the name of the job.
Click on the Steps tab and click the New button.
Give the step a name.
Select the SQL Server Integration Services Package type.
Select the proxy you just created for Run As.
Select the package source and name on the General tab.
Add any other settings you want on the tab.
Click the OK button.
Add any schedules or other Agent settings you want.
Click the OK button.

The settings in the dialog box should be as shown in Figure 18.14.

Figure 18.14. Creating the new job

With this setup, the job actually runs within the context of the login credential associated with the proxy that runs the package. To put it another way, suppose you used your account when setting up the preceding proxy. When you run the package, it's as if you logged into the server where Agent is running, opened the package, and ran it manually. This design gives you the ability to flexibly control under what security context a package executes.

Agent Subsystem Path

The path to each of the Agent subsystems is stored in the msdb.dbo.syssubsystems table. To see the path for the SSIS subsystem, type in the following query:

USE msdb SELECT subsystem, subsystem_dll FROM msdb.dbo.syssubsystems WHERE subsystem = 'SSIS'

On the sample machine, the query returned the following:

SSIS   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLDTSSS90.DLL

You shouldn't need to ever modify this table, but it's good to know that it's there.

Although Agent integration isn't strictly a security-related subject, I've placed this discussion here because it is the only way Microsoft supports securely scheduling package execution in a noninteractive way. Finally, the previous steps are minimal; there are many options and alternative steps that aren't discussed simply to save space. Try different options and test them. For example, use different SSIS roles and logins with different privileges. The possible combinations are virtually limitless.

Permissions Problems When Using Agent

The new Agent Subsystems and Proxy accounts have gone a long way toward eliminating this class of problem; however, calls for help along these lines still find their way to the SSIS forum and my Inbox on regular occasions. So if you're struggling with access to servers or shares, it is not all that uncommon.

The way this problem is typically manifested is something like this: You can run the package fine in your development environment, but then, when you deploy it to the server and attempt to run it through Agent, nothing works. Typically, you'll get errors about insufficient privileges or access denied errors.

As with most diagnostic problems, the first step is to simplify. Create a simple package to run in Agent that reproduces the problem. Often, you'll find that the package is attempting to access a folder or server to which the proxy has no privileges.

If that fails, use Run As to execute the package outside the development environment. Use your own credentials. For example, you can right-click on Command Prompt in the Accessories menu and select the Run As menu option, as shown in Figure 18.15. It is possible that the Run As feature is turned off by security policies. You should check to ensure it is enabled. Also, the Run As service must be started in the Services control panel to use this feature.

Figure 18.15. Launching the command prompt with Run As

A dialog box opens allowing you to select the credentials you want to use for the command prompt. From that point forward, any command you execute inherits the credentials/security context of the command prompt. You can run the package using DTExec or even launch the designer from the command prompt and it loads in that security context. If you can reproduce the problem in the same context that the package is running under on the server, you've found the problem. Likely, you need to modify the privileges of the account under which you are running the package on the server.