By: Jeremy Kadlec | Comments (7) | Related: More > Data Transformation Services
Problem
Many of the objects (Execute SQL Tasks, ActiveX Scripts, Connection Objects, etc.) in SQL Server 2000 Data Transformation Services (DTS) Packages require authentication to SQL Server when extracting, transforming and loading data. It is quick and easy to just add your SQL Server user name and password. Some even say 'better yet I know the sa password, so I will add it'. Wrong!
In the short term embedding the user name and password may be quick and easy, but in the long term when passwords need to be changed or employees leave the organization, the password management problem quickly becomes unwieldy. The first alternative that is typically in the right direction is to use a trusted connection in the DTS objects when authenticating. This ensures that the user or service account that is executing the DTS Package has sufficient privileges to SQL Server and the underlying objects. Unfortunately, depending on the application calling (web server, legacy system, etc.) the DTS Package or the overall application architecture, a trusted connection may not be feasible.
Solution
One alternative at your finger tips when authenticating to SQL Server from DTS is a UDL file or Microsoft Data Link file. The UDL file will store the necessary connection string that DTS needs to authenticate to SQL Server. If you setup a standard location for the UDL files on each of your servers and have all of your DTS Packages reference the UDL files then you have the ability to change passwords in 1 location and have all of the applications use the latest set of credentials. This is a big win for organizations subject to legislation or audits with security requirements such as SOX, HIPAA, SAS 70, etc. Nevertheless, organizations should change passwords on a regular basis (i.e. quarterly or semi-annually) and especially when key employees leave the organization.
The known caveat with the UDL files is that the contents are stored in clear text including the user name and password. To protect this sensitive information, access control lists (ACLs) need to be setup on each of the directories that support the SQL Server UDL files. In addition, access to these directories should be audited as well as ensure your backup application is leveraging an encryption algorithm when backing up contents of the directory.
A second caveat with the UDL files is that when the DTS Designer is being used by Developers or DBAs as DTS Packages are being developed that the same UDL file needs to be referenced. The first option is to have the Developers\DBAs create the UDL file in the same location as the SQL Servers with the same path, file name, user name, provider, etc. as the development SQL Server. A second option is to use a UNC file to the development SQL Server and then change the path for the UDL file as the DTS Package is promoted between the development, test and production environments.
UDL File Creation
| ID | Directions | Screen Shot |
| 1 | File Creation - Navigate to the directory where the UDL file will be stored with Windows Explorer. In the directory and create a new text file. Rename the text file to have a 'udl' file extension. confirm you would like to change the file extension. Then double click on the file to configure the UDL file. | |
| 2 | Provider Tab - Select the needed driver to support the DTS Package's access to SQL Server. Keep in mind that the functionality may differ between the various drivers i.e. OLE-DB vs. ODBC. |  |
| 3 | Connection Tab - Configure the server name, authentication type and database. Be sure to press the 'Test Connection' to validate that all of the parameters are accurate. |  |
| 4 | Advanced Tab - Based on the Provider that is selected, the additional settings are available to fine tune the parameters available to the UDL file. |  |
| 5 | All Tab - This interface is a summary of the UDL file configurations with the ability to select any option and click on the 'Edit Value' button change a single value. |  |
Referencing a UDL File - Execute SQL Task
| ID | Directions | Screen Shot |
| 1a | Previous Connection Properties - The screen shot on the right displays the connections options with a direct OLE DB provider. |  |
| 1b | UDL File Connection - Click on the 'Data Source' drop down list and select 'Microsoft Data Link' option. You will now have the option to specify a UDL file by browsing for the needed file in file system. Be sure to select the 'Always read properties from UDL file' option which will ensure at run time the current UDL file configuration will be used. Press the 'OK' button to save the configurations and notice that in the DTS Designer that the icon will change to the Microsoft Data Link icon. |  |
Referencing a UDL File - ActiveX Script
| ID | Directions | Code Sample |
| 1 | Change your existing code to reference the UDL file. The new code would look like the code on the right. | sCns="file name=C:\SQLServerudlfiles\sqlserverOLEDB.udl" |
Next Steps
- Take an inventory of how the DTS objects authenticate to SQL Server, which accounts are used and the provider\driver.
- Research the last time SQL Server passwords were changed in your environment that correspond to the DTS Packages.
- Based on the first two sets of information, determine if you are going to have issues when changing passwords.
- Find out if any legislation or upcoming audits will require SQL Server password changes and get ahead of the curve.
- If audits are pending or if you are concerned about the number of individuals that know critical DTS passwords in your environment, pull together a plan to test and implement a centralized password solution with UDL files.
- Standardize and enforce the DTS password policies to streamline future passwords changes.
- Seek out other applications in your environment that have hard coded user names and passwords and research if they can also leverage UDL files.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
