SQL Server 2000 DTS Authentication with UDL Files

By:   |   Comments (7)   |   Related: More > Data Transformation Services


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.


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. UDLFile Provider
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. UDLFile Connection
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. UDLFile Advanced
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. UDLFile All

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. DTSPackage CntProp OLEDB
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. DTSPackage CntProp UDLFile

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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeremy Kadlec 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

Comments For This Article

Monday, February 18, 2013 - 8:35:53 AM - Jeremy Kadlec Back To Top (22234)


No problem and best wishes.

Thank you,
Jeremy Kadlec
Community Co-Leader

Monday, February 18, 2013 - 8:31:55 AM - Joel Back To Top (22233)

Entiendo , 

muchas gracias por tus atentas respuestas!



Monday, February 18, 2013 - 8:23:05 AM - Jeremy Kadlec Back To Top (22231)


Based on building a quick SSIS Package, I did not quickly see a UDL file connection.

I would check out the other options I listed in my last post.

Thank you,
Jeremy Kadlec
Community Co-Leader

Monday, February 18, 2013 - 8:05:50 AM - Jeremy Kadlec Back To Top (22230)


Based on the image, you are using SSIS not DTS.  I am not sure if SSIS supports UDL files.  I need to research that a little bit.

Why not just use Windows Authentication for your connection objects?

If you need a configuration file for your Dev, Test and Prod environments, we have a few tips on that as well.  See this category of tips - http://www.mssqltips.com/sql-server-tip-category/128/integration-services-configuration-options/.

Thank you,
Jeremy Kadlec

Monday, February 18, 2013 - 7:13:38 AM - Joel Back To Top (22227)


version de windows : windows 7 Ultimate Service Pack 1

version de SQL      :Sql Server 2008 R2


Data Source que aparece: (adjunto link de foto)




Friday, February 15, 2013 - 7:43:30 PM - Jeremy Kadlec Back To Top (22172)


What version of Windows and SQL Server are you using (including the service packs)?

What Data Sources do you see?

Thank you,
Jeremy Kadlec
Community Co-Leader

Friday, February 15, 2013 - 4:59:02 PM - Joel Back To Top (22170)

Estimado, no me aparece en Connection Properties la opcion "Microsoft Data Link" que puedo hacer?


Mis saludos

get free sql tips
agree to terms