SQL Server Performance Forum – Threads Archive

SQLAgent authorization problem

Hi there, I have a DTSpackage on server SQL1, which basically copies tables from a remote server, SQL2, to the local server. Executing the package under my login(sa) is no problem, it runs smoothly. Running the package with the job under SQLServerAgent ends with:
SQL Server does not exist or access denied. It seems, that the SQLServerAgent account does not have rights on the remote server. SQLAgent runs under a local account with full Administrator rights; there is no thrust relation between the 2 servers. BTW, MSSQLServer runs under a different local account, however also with full Admin rights.
The servers are in 2 different domains, there is no thrust between the domains, each domain has its own firewall. Now I go to a third server in the same domain as SQL1. I save the package there, schedule it, and the job runs without a problem! Same setup as on SQL1: both MSSQLServer and SQLServerAgent run under different local accounts with full Admin rights. What may cause this problem? Do I miss something here?
All servers are W2K Standard Edition with the latest sp’s and patches, all have SQL2K with sp3a installed.
Kind regards $ TIA,
Hans Brouwer

On the source server ensure SQLAgent service account has necessary privielges on remote box to accomplish the task. If the network operation is involved its always better to use a domain account which will have admin privileges on involved boxes. Follow this KBAhttp://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 to reivew information about scheduling the DTS job and privileges required.# HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Tnx for the response, Satya, but it did not adress my question. Since the remote server is owned by another company a global account is unlikely to appear.
Tnx for the link; it packs together in 1 place what I already new. Kind regards $ TIA,
Hans Brouwer

…I go to a third server in the same domain as SQL1. I save the package there, schedule it, and the job runs without a problem!
As the both the SQL Servers on same domain where the local accounts with ADMIN privileges does have trust between them enabled the DTS package to run. Whereas, in the first instance as you confirmed the remote server doesn’t have any privileges with the current network and local SQLAgent is running with local account caused the issues.
HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>