maximus1974
asked on
Error message when scheduling a job using a linked Server
Getting error message when running a job when scheduling a job using a linked Server. It fails when it runs the following statements below. Screenshots are attached. Both scripts run fine in a query window. Any insight will be appreciated.
Capture2.PNG
SELECT * into WOMAST01B FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01 where 1=0')
SELECT * into WOLABO01B FROM OPENQUERY(ATQAV, 'SELECT * FROM wolabo01 where 1=0')
Capture.PNGCapture2.PNG
Is the Linked Server using the current user credentials or it has his own security credentials?
ASKER
The linked Server has it's own credentials used ot authenticate against another network. I am linking an old Foxpro DBF database to SQL Server then importing the DBF into SQL. Currently, the SQL Server agent can authenticate on network it is on.
Does this mean the SQL agent will need access to authenticate against the linked Server on the other network? what happens to the jobs that run on it now which authenticate against a different network than the linked Server?
Does this mean the SQL agent will need access to authenticate against the linked Server on the other network? what happens to the jobs that run on it now which authenticate against a different network than the linked Server?
Apologizes for not returning to this question. I just missed the mail informing that you posted here.
So, if you have the credentials stored with the Linked Server then it should work. Did you try to open the Linked Server to perform a connection test?
So, if you have the credentials stored with the Linked Server then it should work. Did you try to open the Linked Server to perform a connection test?
ASKER
Vitor responded.
ASKER
When I run a query or insert statement from the query Window against the linked Server, it works fine so this proves I am able ot authenticate against the linked Server. However, when i attempt to schedule an import, I am getting invalid path or file name as illustrated in the attached screenshots above.
When you say to use the correct credentials in the SQL agent service, do you mean here?
When you say to use the correct credentials in the SQL agent service, do you mean here?
It shouldn't have nothing with the SQL Server Agent as you said the Linked Server has an user and password configured.
But just for sanity check and if you can change the SQL Server Agent to a non Local System account it will be a good test because Local System account doesn't have access on the network.
But just for sanity check and if you can change the SQL Server Agent to a non Local System account it will be a good test because Local System account doesn't have access on the network.
ASKER
screenshots and errors attached. Queries against the linked Server works via the query window, when manually importing via SISS, executing the package via integrated services. however, when scheduled via a SQL agent job is fails stating : invalid file name or path".
Capture.PNG
Capture2.PNG
Capture3.PNG
Capture4.PNG
Capture.PNG
Capture2.PNG
Capture3.PNG
Capture4.PNG
when scheduled via a SQL agent job is fails stating : invalid file name or path".That's a new information and very important one. This means it has nothing to do with the Linked Server but the permissions to access a file in the network.
A Local System account has no privileges in the network and that's why is returning that error. You need to provide an AD account that has access to the file through the network.
ASKER
I have since changed it to an AD accoutn with permission to the directory and i am receiving the same error. If it's permissions, how come it works using all other means except vai the SQL agent job?
new screenshot attached.
Capture.PNG
new screenshot attached.
Capture.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
From your attached files, you run the statements under System Administration account so they were run successfully. Please check your SQL Agent Service account and be sure that you must grant permissions appropriately to the account.
Another way, you can create Proxy Account in SQL Server to run SQL Agent Job instead of using SQL Agent Account by default.