Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error message when scheduling a job using a linked Server

Posted on 2016-09-07
12
Medium Priority
?
122 Views
Last Modified: 2016-09-15
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.

SELECT * into WOMAST01B FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01 where 1=0')
SELECT * into WOLABO01B FROM OPENQUERY(ATQAV, 'SELECT * FROM wolabo01 where 1=0')

Open in new window

Capture.PNG
Capture2.PNG
0
Comment
Question by:maximus1974
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
12 Comments
 
LVL 8

Expert Comment

by:Dung Dinh
ID: 41788993
It looks like SQL Agent Job account has not been granted permissions on the LINKED SERVER. Be aware of this,  whenever you run any statement via SQL Agent Job, it uses SQLAGENTSERVICE account to execute your statements by default. In case, the account has not permission on the linked server, it will be failed.

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.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41789255
Is the Linked Server using the current user credentials or it has his own security credentials?
0
 

Author Comment

by:maximus1974
ID: 41790213
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?
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41795503
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?
0
 

Author Comment

by:maximus1974
ID: 41797225
Vitor responded.
0
 

Author Comment

by:maximus1974
ID: 41797236
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?
screenshot
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41797384
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.
0
 

Author Comment

by:maximus1974
ID: 41798699
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
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41799261
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.
0
 

Author Comment

by:maximus1974
ID: 41799863
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
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 41800034
If it's permissions, how come it works using all other means except vai the SQL agent job?
Because you're not using the same user for the other process, right?
Btw, do you also have an AD account for the SQL Server service?
And for the user that is running the job also? A SQL Login doesn't have access to network.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question