Stored procedure failing over linked server.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

One of my stored procedures is failing probably due to some security issue.
All it does is, get some data from a remote linked server and insert it in the local server.
Works fine in SSMS but if I try to schedule it using SQL Server Agent it fails.. all the time.
The SQL server Agent uses a local admin account and connects to the linked server using a SQL server login.
What do I need to fix to get this working?

Posted 14-Jun-12 7:05am

SalCon

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tim Corey · Answer 1 · 2012-06-14T07:22:00

Solution 1

I believe the reason why this works in SSMS and not with the agent is because in SSMS you are using a Windows account, which has login priviledges on both servers. The local admin on the local machine probably does not have access to the remote machine. I've tried getting around that but what I keep coming back to is using Windows domain accounts to access the remote data.

Posted 14-Jun-12 7:22am

Tim Corey

Comments

SalCon 14-Jun-12 13:29pm

Unfortunately I can only connect to the remote server using the SQL login and not the Windows login. :( . You are right that the local admin doenst have access to the remote server.