0

I have a linked server (Server B) set up on Server A with permissions set as follows:

enter image description here

This means that there are five Windows Logins (of which my windows account is one) who can access the linked server on Server A and the permissions they will have when using the linked server are the permissions they have set on Server B.

If I log into SSMS as myself on my local machine (which isn't the server), connect the object explorer to Server A, right click on the Server B linked server and select "Test connection" I get the following error:

enter image description here

However, when I run the following code on Server A, the command completes successfully:

DECLARE @name SYSNAME = 'ServerB'
EXEC sp_testlinkedserver @servername  = @name

I can also verify that the linked server works by querying it as below:

SELECT TOP 1 1 FROM [ServerB].[MyDb].[dbo].[MyTable]

which returns 1 as expected.

Why does the connection test in the GUI fail when clearly everything is working as it should be?

  • "However, when I run the following code on Server A, the command completes successfully:". Are you still running the query with SSMS from your machine or are you connected to the Server A VM when running the query? The guid should still be executing EXEC sp_testlinkedserver N'Servername' when testing the connection – Randi Vertongen Nov 6 at 11:49
  • I am running the query in the same instance of management studio, on my local machine – SEarle1986 Nov 6 at 12:04
  • What is returned when you run this: select auth_scheme from sys.dm_exec_connections where session_id=@@spid – Randi Vertongen Nov 6 at 12:19
  • select auth_scheme from sys.dm_exec_connections where session_id=@@spid returns KERBEROS On both ServerA and ServerB – SEarle1986 Nov 6 at 12:25
  • Is the service account on ServerA set as trust this user for delegation to any service? – Randi Vertongen Nov 6 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.