quote:

---

Handling Package Security in DTS
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.

DTS Package Passwords
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:

If you set an owner password, the package user needs the password to edit or run the package.


If you set a user password, you also must set an owner password. Package users with access only to the user password can run the package. However, they can neither open nor edit the package unless they have access to the owner password.
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

Enterprise Manager


How to set a DTS package password (Enterprise Manager)
To set a DTS package password in DTS Designer

On the Package menu, click Save or Save As.


In the Location list, click either SQL Server or Structured Storage File.


Do one of the following:
Enter an Owner password. Assigning an Owner password puts limits on who can both edit and run the package.


Enter a User password. Assigning a User password puts limits only on who can edit the package. If you create a User password, you must also create an Owner password.

See Also

Handling Package Security in DTS


Package Scheduling and Security Issues
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:

For packages created under a Microsoft Windows NT® 4.0 or Microsoft Windows® 2000 account, the job runs under the security context of the account that started SQL Server Agent.


If the job is owned by a login belonging to the sysadmin fixed server role, the security context of the package defaults to the account used to start the local SQL Server Agent. If the server is registered using Windows Authentication, the owner of the job is the account of the SQL Server Agent. If the server is registered using SQL Server Authentication, the owner of the job is that SQL Server login.


If the job is owned by a login that is not a member of the sysadmin fixed server role, the package runs under the context of the job step proxy account, with the rights and permissions of that account.
Ownership conflicts can generate the following types of problems:

File paths specified in the package may not be visible in a different security context. That is, a different user executing the package may not have access to the same share points as the package creator (for example, the user may not have the drive letters of the package creator mapped). To guard against this problem, use Universal Naming Convention (UNC) names rather than file paths when specifying external files.


The owner of the SQL Server Agent job that runs the package does not have permission to access the paths pointed to or connections made in the package. For example, the owner of the job may only have local server access. If this problem arises, view the security context of the job in SQL Server Enterprise Manager and log out of that instance of SQL Server. Then log back in to that same instance of SQL Server using the security context of the job and attempt to run the package.


For packages that call COM components in Microsoft ActiveX® scripts, the called components must exist on the same workstation on which the package is running. Also, the SQL Server Agent job account must have permission to run the job.
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.


Data Link Files and Security
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:

Use Windows Authentication for the connection. Windows Authentication does not require login information to be placed in the data link file. It only requires a flag indicating that a trusted connection will be used. This connection method is secure for data link files.


Do not use SQL Server Authentication for the connection. SQL Server Authentication requires you to place login and password information in the data link file. This information would not be secure.
Saving Package Security Information
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

Enterprise Manager


How to modify the persisting of authentication information (Enterprise Manager)
To modify the persisting of authentication information in a DTS package

On the Data Transformation Services (DTS) Designer design sheet, double-click a connection.


In the Connection Properties dialog box, click Advanced.


Under the Value column, click the value for the Persist Security Info property.


Do one of the following:
Type 0 to disable the saving of authentication information with the DTS package.


Type 1 to persist the saving of authentication information.

See Also

Handling Package Security in DTS

---