INF: How to Run a DTS Package as a Scheduled Job (269074)

MORE INFORMATION

Some terms used in this article include:

Terms	Description
DTS	Data Transformation Services
SQL authentication	A security system that is based on Microsoft SQL Server logins and passwords.
Standard security	See SQL authentication.
SQL Server authentication	See SQL authentication.
Microsoft Windows NT authentication	When a user connects through a Microsoft Windows user account, SQL Server verifies that the account name and password were validated when the user logged on to a computer running Microsoft Windows NT, Microsoft Windows 2000, Microsoft Windows 95 or Microsoft Windows 98.
Integrated security	See Microsoft Windows NT authentication.
Windows NT or Microsoft Windows 2000 authentication	See Microsoft Windows NT authentication.
Microsoft Windows NT account or login equals Windows login or account	Same as Microsoft Windows NT login account or Microsoft Windows 2000 login account.
SQL Agent	SQL Server Agent Service
SEM	SQL Server Enterprise Manager

Where is the DTS Package Running?

One problem that is frequently reported about DTS packages is the difference in behavior when a package is run from the SQL Enterprise Manager versus when the DTS package is scheduled as a job. When you run the package from the DTS Designer in SQL Enterprise Manager (SEM), the package is running on the computer where you are seated. If you are at the server (either physically, or through remote access software), the package is run on the server. If you are seated at a workstation and you have registered the SQL Server server in Enterprise Manager, then the package is run on the workstation. The security context of the package is that of the Windows NT account you used to log in to that computer. When the package is run as a scheduled job, the package is always run on the server.

Frequently, a developer creates and tests the DTS package interactively on their workstation through the DTS Designer in Enterprise Manager. After the DTS package is debugged, the package is then scheduled as a job. This changes the location of the package from the developer's workstation to the server. If the package was loading text data into SQL Server, the package fails unless the text file and the path to the file exist on the server. If the package was connecting to another server, the package fails if the security context of the job does not support the connection.

Who Owns the Job That Runs the DTS Package?

Packages are scheduled by creating a job that is managed by the SQL Agent service. This job, as any other scheduled job, has an owner. The owner can be either a SQL Server login or a Windows NT account.

To determine the owner:

• Double-click the job in Enterprise Manager and then look at the Owner drop-down combo box.
  
  -or-
  
  
• Run the msdb.dbo.sp_help_job system stored procedure.

SQL Server 7.0

The security context in which the job is run is determined by the owner of the job. If the job is owned by a login that is not a member of the Sysadmin server role, then the package is run under the context of the SQLAgentCmdExec account and has the rights and permissions of that account.

For SQLAgentCmdExec to be able to run jobs that connect to SQL Server, the SQLAgentCmdExec account must have proper Windows/NT permissions and be granted login access to SQL Server with appropriate database permissions. The SQLAgentCmdExec account generally has no rights outside of the local SQL Server computer. Therefore, any package that requires a connection to another computer fails, if it is scheduled as a job owned by a login that is not a member of the Sysadmin role.

SQL Server 2000

The security context in which the job is run is determined by the owner of the job. If the job is owned by a login that is not a member of the Sysadmin server role, then the package is run under the context of the account setup as the SQL Agent Proxy Account, and has the rights and permissions of that account.

For SQL Agent Proxy to be able to run jobs that connect to SQL Server, the SQL Agent Proxy account must have proper Windows/NT permissions and be granted login access to SQL Server with appropriate database permissions. For the jobs that execute a DTS package, the SQL Agent Proxy Account must have read and write permissions to the temp directory of the Account the SQL Server Agent is running under. For example, If the job is owned by an account (either a SQL Server login or a Windows NT authenticated login) that is a member of the Sysadmin role, the SQL Agent job runs under the context of the account used to start the SQL Agent service.

Also, if the job is owned by a Windows NT domain account and if the package is stored in the SQL Server or SQL Server repository (not as a file), you must start the SQL Server service by using an account from the same domain or an account from a trusted domain. For example, if the SQL Agent job is owned by an account from the USA domain, then the account used to start the SQL Server service must be either from the USA domain or a domain trusted by the USA domain. If the SQL Server is started using a local account, the package fails to run.

What Determines the Owner?

Question: When you right-click the DTS package and you choose to schedule the package, how is the owner assigned?

Answer: The owner of the SQL Agent job depends on how the SQL Server is registered in the Enterprise Manager. If the SQL Server is registered using a Windows NT authentication, the owner of the scheduled job is the account used to start the SQL Agent service. If the SQL Server is registered in SEM using SQL Server authentication (for example, the SA login), the owner of the job is that same SQL Server login.

To change the ownership of the package:

1. Double-click the job in Enterprise Manager.
2. Click the General tab, and then click the Owner drop-down combo box.

You can also use the msdb.dbo.sp_update_job system stored procedure to change the ownership of the package.

How is the DTS Package Launched?

If you manually run a package by using the DTSRun.exe command line utility, the security context is that of the Windows account you used to log in to the computer. If you run the package by using DTSrun.exe through the xp_cmdshell extended stored procedure, the package is run in the context of the account used to start the SQL Server service, provided that the user that executed xp_cmdshell is a member of the Sysadmin role. If the user that executed xp_cmdshell is not an account in the Sysadmin role, then DTSRun.exe runs in the context of the SQLAgentCmdExec account.

If the SQL Server was started using the Local System account, the DTS package has no permissions outside of the computer that is running SQL Server.

If the SQL Server service is started under a Windows NT account, the package has the same rights and permissions as that Windows NT account. If that Windows NT account is a local machine account (as opposed to a domain account), the package does not have any rights outside of that computer. If the Windows NT account is a domain account, the package may be able to access resources on many different computers on that domain.

How are Windows NT-authenticated Connections Made?

Sometimes a DTS package contains an object that makes a connection to a data source using Windows NT authentication. The security context used for this connection is the same as the context of the package that is running. If the package is run from a command prompt by using DTSRun.exe, the credentials of the currently logged-in Windows NT account is used. If the package is run as a SQL Server Agent job, then the integrated security connection is made using the account you used to start SQL Agent (assuming that the owner of the package is a member of the Sysadmin role).

Common Problems

Here are a few other common problems you might encounter when you run DTS packages as scheduled jobs in SQL Agent:

Mapped Drives

If the package relies on the physical location of a file designated by a mapped drive letter, the package may fail when it is run as a scheduled SQL Agent job, regardless of who owns the package. SQL Agent is a Windows NT service and Windows NT services cannot see mapped drive letters. The mapping is part of the user's profile that is loaded when a user logs on to a Windows NT session. Services do not work with user profiles. Use a UNC path instead of a mapped drive letter. For additional information about why a service cannot use a mapped drive, click the following article number to view the article in the Microsoft Knowledge Base:

Relative Path

A relative path (or drive letter) is specific to the current location of the package (like C:\). If a package is designed on a workstation and is then scheduled, the location from which the package is run changes. The drive letter paths now reference a different physical location, that of the server. Unless the referenced files are also moved to the server, the package execution fails.

COM Components in ActiveX Scripts

If COM components (for example, calls to Microsoft ActiveX Data Objects (ADO), Remote Data Objects (RDO), or Decision Support Object (DSO) objects) are called in an ActiveX script, the called components must exist on the computer from which the DTS package is being run. If you run the package from the DTS Designer in SEM or DTSRun.exe, the components must exist on the computer at which you are seated. If the package is scheduled to be run by SQL Agent, the called components must be loaded on the computer hosting the SQL Server.

Package Security

DTS packages can have owner passwords and user passwords. These passwords affect who can edit and run the packages. Neither of these affect the security context in which the package is run.

SQLAgentCmdExec Permissions

If the job is executed under the context of the SQLAgentCmdExec account, and the SQLAgentCmdExec account does not have login rights to the SQL Server, the job may fail with the following error message: You need to grant the SQLAgentCmdExec account proper login and database permission rights to the SQL Server.