I am trying to insert a record into a linked server, but keep getting a logon error
Data is coming into a SQL2008R2 server and I am trying to run a trigger to insert it into a SQL2016 Server
I created the link on the 2008R2 server and have tried to amend it so it runs under the sa account ( just to get the thing working. I have also tried it under other administration logons which are the same on both servers. )
Under management server I logged in under the sa account and have full access to both of the servers
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TRIGGER [dbo].[AddToTest]
INSERT INTO [SVR2].[DBTEST].[dbo].[Test]
EXEC master.dbo.sp_addlinkedserver @server = N'SVR2', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SVR2',@useself=N'False',@locallogin=N'sa',@rmtuser=N'sa',@rmtpassword=N'xxxxxx'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SVR2',@useself=N'True',@locallogin=N'sa',@rmtuser=N'sa',@rmtpassword=N'xxxxxx'
Login failed for user 'Admin'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'Admin'.
can anybody assist with this