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] : /storage/temp/2722-linked-server.sql
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.