SQL Server – Windows Authentication in SQL DTS Packages

sql serversql-server-2008sql-server-2008-r2

We have a SQL server 2008 R2 and have legacy DTS packages running on the server. As of now, the subtasks inside DTS packages are using dynamic properties where SQL Login ID details have been given, which are used by the subtasks to login to another server in same domain and get the results from databases for processing.

Our need is to get rid of SQL logins and start using Windows Authentication for all the processes inside the DTS packages, which includes the subtasks to connect another SQL instance. However, I couldn't find any know way or option in the dynamic properties file to make use of Windows Authentication mode.

Best Answer

This issue relates to who is running the package. Use the newer SSIS packages. Easier, more secure, faster, and will work with your older stuff anyways. :)

A) Already available. Set the account that is running/created the package.

  • have the account created on the consumers/targets and your golden.

B) Changing Authentication can be done.

  • Use the SSIS package Script to call a Pre-authenticated script that uses the login you want to run the other steps. See Execute SSIS Package - Microsoft Docs
  • provide greater levels of security within the package.
  • can split security between groups, work with dynamic data streams, etc
  • Requires planning and greater effort to implement.

C) What you ask defeats the point of Domain Accounts..

  • You would have to assume, pretend, mimic another valid user. That sounds scary.
  • Not possible without some exchange of authentication. Domain Logins are meant to be pre-authorized, not inflight in a package.

End of the day, what is most important is the security points in your package. it won’t matter your package Authentication if everything else is not secure..

  • I.E. the network (how public or private the network is and its security)
  • the package encryption
  • the certificate
  • the stream encryption
  • the consumer’s security strength
  • Your own security
  • public vs private
  • SQL user vs Domain Account
  • and many more...

Personal Opinion:

I would make sure you use the right encryption level that is needed for your situation.

  • Don’t be too eager to use every security feature because it just takes one bad security hole to ruin everything.
  • Be reasonable to both time and the available features you have at hand. Maybe you just need a domain account created on the systems and that’s it.
Related Question