asked
Scheduling a job with a linked server
Question: Where did I go wrong? 1- Established a linked server Sql Server S1 Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Build 7600: ) (Hypervisor) S2 Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Build 7601: Service Pack 1) (Hypervisor) 2- A SQL Server login was established on each server, rights galore. 3- Running under my login, synonym and Linked server works. 3- Tested linked server connection via SSMS ... connection successful. 3- Trying to run through a Sql Agent and receive this message: Date 8/20/2015 12:27:41 PM Log Job History (test) Step ID 1 Server DCHARPTSRVR Job Name test Step Name step1 Duration 00:00:00 Sql Severity 14 Sql Message ID 18456 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Executed as user: NT AUTHORITY\NETWORK SERVICE. Login failed for user 'DCHOUSING\jstokes'. [SQLSTATE 28000] (Error 18456). The step failed. the linked server derived script is attached[link text][1] [1]: /storage/temp/2722-linked-server.sql
You have several answers below. For each of those answers that are helpful you should click on the thumbs up next to the answers so that it turns green. If any one of the answers below lead to a solution to your problem, click on the check mark next to that one answer so that it turns green. This web site works best if you do the voting and marking of answers so that the next person who looks at your question knows what the solution was.
Have you verified that the account running SQL Server agent has the proper permissions on the destination server? Reg Jan
Check 2 things. 1. The login used in linked server has enough permission on destination server database. 2. SQL job owner account permission on source server.
Gentlemen - I appreciate your answers. I tried a sql server login on both servers to no avail. However. Using the Database Import Task , I was able to form a fundamental SSIS package. By exporting the script , I was able to to perform an Import data function via the command line. This seems to work. Thank you
Ok! When you run the attached linked server script it adds impersonate account as well, keep the impersonate account as windows authentication and change the remote login to SQL authentication login. I hope it works for you.
20 People are following this question.
