Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server 2000 DTS Authentication with UDL Files


By:   |   Read Comments (7)   |   Related Tips: 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.


Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

Joel,

No problem and best wishes.

Thank you,
Jeremy Kadlec
Community Co-Leader


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

Entiendo , 

muchas gracias por tus atentas respuestas!

 

Saludos


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

Joel,

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

Joel,

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

Estimado,

version de windows : windows 7 Ultimate Service Pack 1

version de SQL      :Sql Server 2008 R2

 

Data Source que aparece: (adjunto link de foto)

 

http://i1083.photobucket.com/albums/j399/joelcastillo02/DataSource_zps0a7c5181.jpg

 


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

Joel,

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

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

 

Mis saludos


Learn more about SQL Server tools