To view, edit, protect, schedule, and run Data Transformation Services (DTS) packages on your network, you need to understand issues that affect package access, permissions, and connections.
When you save a package to Microsoft® SQL Server™ or as a structured storage file, you can use DTS package passwords. You use DTS passwords in addition to the Windows Authentication or SQL Server Authentication passwords you use to connect to an instance of SQL Server. The following types of DTS package passwords are available:
It is strongly recommended you use DTS package passwords for all packages to ensure both package and database security. At a minimum, always use DTS package passwords when connection information to a data source is saved and Windows Authentication is not used.
To set a DTS package password
Usually, a package run from DTS Designer, the DTS Import/Export Wizard, the DTS Run utility, or from the command prompt executes under the security context of the user who is currently logged in. However, a package scheduled for execution runs under the security context of the SQL Server Agent job that runs the package. The owner of that job may or may not be the same as the user currently logged in. Consider the following types of ownership:
Ownership conflicts can generate the following types of problems:
For all of the above situations, copying external files used by the package onto the same server as the executing package may preempt package failures caused by ownership problems. In cases where COM components are used by a scheduled package, the called components must be loaded onto the same computer on which the instance of SQL Server is installed, and SQL Server Agent must have permission to use the objects. Otherwise, the package will not execute successfully.
Important If you schedule a DTS package with a user password instead of an owner password, the scheduled job will not report a failure unless the package is set to fail on the first failed step. This is because the user does not have permission to read the package status after the package is run. This behavior will not occur if the package is scheduled using the owner password.
Microsoft Data Link (.udl) files are unencrypted text files you can use to encapsulate a connection string in a package. It is strongly recommend you do not include password information in a data link file because the information would be visible to anyone viewing the text file. If you intend to use data link files to store a connection string, consider the following:
By default, the Windows Authentication or SQL Server Authentication information used to connect to a data source is saved along with the package. To control the persisting of the authentication information, use the Persist Security Info option in the Advanced Connection Properties dialog box in DTS Designer. This option only exists for SQL Server connections.
There may be reasons for disabling the persisting of the Windows Authentication or SQL Server Authentication information in a package. For example, suppose you want to create a package that will be tested in a different environment from the one in which the package was created. In that case, you may not want the security information from the connections saved along with the package because that information cannot be used to reconnect in the new environment. Make package connections with data links that resolve their settings from a data link file and use Windows Authentication for the connections. This increases package portability and maintains package security.
To modify the persisting of authentication information