Question :
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.
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.
You can do this by giving the specified Windows account the necessary permissions to the servers/databases that the SSIS job connects to, and then running the DTS package under that Windows account.
For example, to run a SQL Agent job step with the Windows account, you have to create a SQL Server credential, then a SQL Server Agent proxy for that credential, and then assign the job step that runs the package to run under the proxy. I don’t recommend it, but for completeness will mention that if you are using a domain account for the SQL Server Agent account, you can simply give permissions to that account, and have the job step run with it. The problem with this is that you have one account with permissions to numerous objects and it becomes a security concern.
If you are using a Windows Scheduled Task, you need to configure that task to run with the specified account.
You’ll also need to change the connection strings to remove the SQL login and password, and specify ;Integrated Security=SSPI so that it will use Windows authentication.
This thread also mentions that a linked server can be used, but doesn’t provide any further details.