0

I have created a linked server and wish to execute a job that queries the linked server and writes the result in a local database.Ihave tested the connection to the linked server and it was fine. I was also able get results by running a query in a new editor window. However, when I run the T-SQL command, it fails with invalid login credentials, why is that?

when creating the linked server, I chose the option of using the current login context to login to the remote server.

Any help would be appreciated.

l3utterfly
  • 111
  • 2
  • Too little information. Configuration settings needed. – Deer Hunter Jul 10 '13 at 03:51
  • What sort of configuration settings? (I'm new to SQL Server...) – l3utterfly Jul 10 '13 at 07:11
  • 1
    What is the difference between running a query in a new editor window and running the T-SQL command? Isn't that the same thing, or do you mean management studio for the former, and some other client for the latter? – Paul Kroon Jul 10 '13 at 17:59
  • As far as I know, no difference. But the former works, the latter doesn't – l3utterfly Jul 11 '13 at 09:57
  • To clarify, the former is running a T-SQL command in the editor window while the latter is running the custom T-SQL in the Server Agent job step. – l3utterfly Jul 11 '13 at 09:58

1 Answers1

0

It would still be helpful to know the version of SQL server, and if you have any non-default configuration settings (specifically the service user set for each SQL Server service), but your problem is because SQL Agent jobs don't run under your account like when your're using management studio (T-SQL).

This will likely point you in the right direction: http://support.microsoft.com/kb/811031. This may also help you out: https://stackoverflow.com/questions/667263/sql-server-2005-linked-servers-agent-jobs.

Generally, you need to focus on the user that the SQL Agent job runs as. You could set the linked server to always use a particular SQL auth username/password, but that might not be desirable. Also, different SQL Server versions do things differently, so feel free to post back that info if you're still having trouble.

Community
  • 1
Paul Kroon
  • 2,250
  • 1
  • 16
  • 20