I have a DTS package on a SQL server scheduled to run as a Job. This package connects to a remote SQL server to grab data and update local tables. In the past, we had a SQL login for the remote server, and the connection in the DTS was configured using "SQL Server Authentication" with that ID.
Due to security requirements, no more SQL logins are allowed, and all connections have to be made using Windows Authentication. We created a new domain acccount and granted the necessary access on the remote and local servers.
The question is: How can I setup my DTS or Job to connect to that remote server using that domain ID? Changing the identity of my local SQL Agent would create all sorts of problems and require other changes that I prefer not to make. Also, in the future, I might need a different ID to connect to other remote databases. Is this possible? Please help.
Thanks in advance.
Start Free Trial
