SQL Server Agent Job failing while scheduleing LInked server SP

Question

SQL Server Agent Job failing while scheduleing LInked server SP

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

SQLDW21 SSC-Addicted Points: 403 More actions · Answer 1

Does any one know where can i find DETAIL documentation on how to configure LINKED server where I can gain knowledge on login authentication between two server on....??????

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

keyun21 (3/4/2010)
Does any one know where can i find DETAIL documentation on how to configure LINKED server where I can gain knowledge on login authentication between two server on....??????

Read Below post

http://msdn.microsoft.com/en-us/library/ms189266.aspx

http://msdn.microsoft.com/en-us/library/ms190479.aspx

http://www.sqlstuff.dk/post/Linked-Servers-in-SQL-Server-2005.aspx

http://www.databasejournal.com/features/mssql/article.php/3085211/Linked-Servers-on-MS-SQL-Part-1.htm">http://msdn.microsoft.com/en-us/library/ms188279.aspx

http://msdn.microsoft.com/en-us/library/ms189266.aspx

http://msdn.microsoft.com/en-us/library/ms190479.aspx

http://www.sqlstuff.dk/post/Linked-Servers-in-SQL-Server-2005.aspx

http://www.databasejournal.com/features/mssql/article.php/3085211/Linked-Servers-on-MS-SQL-Part-1.htm

And try to setup the Linked server and post the issue, if you faced any.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

keyun SSCrazy Points: 2397 More actions · Answer 3

Bhuvnesh,

Thank you for the links but what I am looking for is to have documents that talk about login rights.

For example: if you have readonly access to remote server and the server where you have created link you are in sysadmin group. how does the login/credential work.

Here is my Linked Server Properties:

Under "Security" tab - I have "Be made using the login's current security context" and i do not have any mapping on the same tab.

Now if i schedule script to run via "SQL Server Job Agent" it will faild so i think it has to do with server job agent bcz it run under different login. I tried to change run job under all available accounts but not having any luck.

So, i thought if i can find detail documentation where it talk about login pease that would be great...

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Run your job with the same accounts which is being used to run sql server agent.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 5

@Keyun21,

'Be made using the login's current security context' means 'The account used in the orginal server will be applied at the target server as well'.

To explain bit more, if you use a account A on Server1 then there should be a account called A in server2 and the rights provided for A in Server2 will be applied. In your case, rights provided to your agent account on server2 will be used.

As a quick fix, you can use the fourth option 'Be made using this security context' and provide a user id and password of a account in server2.

This will make all linked server connections use the account you provided.

Regards,
Raj

http://Strictlysql.blogspot.com

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

arr.nagaraj (3/7/2010)
As a quick fix, you can use the fourth option 'Be made using this security context' and provide a user id and password of a account in server2.

can you tell me when we can use other three options ?

OR any link or post where i can find details about these 4 options?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)