April 17, 2007 at 2:58 pm
I'd like to run a command on my local SQL 2005 server "SQL_Local", to start a job on a remote SQL 2000 server on another domain "SQL_Remote". I have successfully set up a linked server, and can query data on "SQL_Remote" from "SQL_Local", but can't figure out how to get the right connection to start the remote job.
I was trying to execute something like this:
exec SQL_Remote.msdb.dbo.sp_start_job @Job_name = [Test_Job]
but get:
Could not connect to server "SQL_Remote" because 'Your_login' is not defined as a remote login at the server.
It works for my select statement across servers.
Any thoughts about what I'm missing or doing wrong ?
May 20, 2008 at 3:35 pm
Was actually reading about scenario like this from MS the other day.
First, can you perfom simple select like SELECT * FROM dbo.SomeTable?
What is the security role of the login your using to access the linked server?
MS recommended using a delegate account to work with linked servers to simplify security.
Another thought is that SQL 2005 SQL agent do run job's against remote servers. Once again you have to ensure the account it authenticates with will have sufficient rights to perform the action..by default it will pass the sql agent account (the local server is using) to the remote server for authentication. you can bypass that and enter in a differnt account..such as a sql server account which would negate the fact you are crossing domains.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply