Login Issues with Linked Server

1

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

Trigger
USE [SQL2008R2]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[AddToTest]
ON [dbo].[TEST]
AFTER INSERT
AS
BEGIN
INSERT INTO [SVR2].[DBTEST].[dbo].[Test]
([Number])
VALUES
(1)

END

GO

Linked Server
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'

Error
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

2

If the password for sa is the same on both servers set up the linked server on the security page under For a login not defined in the list above, connections will:
Be made using the login's current security context
and remove sa from the list.

3

Which of the three EXEC commands is causing that error (try dropping the linked server and then recreating it one statement at a time).

You can;t use both of your [sp_addlinkedsrvlogin] commands, because they are basically the same credentials with different parameters, you will have to use one or the other (or have different named users for each of them)