One of our blog readers recently encountered an issue while trying to fetch data from two SQL Server instances on the same machine using a linked server. They followed the usual steps of creating a linked server in SQL Server Management Studio, but encountered an error during the connection test. The error message indicated an authentication failure and an invalid authorization specification.

This is a common issue that many users face when working with linked servers. Fortunately, the resolution is straightforward and can be easily implemented. In this blog post, we will discuss the steps to resolve this authentication error and successfully create a linked server.

The “Invalid authorization specification” error message suggests that the linked server settings to connect to the server are not correct. To fix this, we need to go back to the linked server properties and navigate to the “Security” tab. Here, we have two authentication options to choose from:

1. Use the current security context
2. Be made using this security context

If we choose the first option, the linked server will use the current security context of the user executing the query. However, if we need to use a specific SQL Login, we should select the second option and provide the account and its password.

Here is an example of the T-SQL command to add a linked server login using the first option:

USE [master]
GO
EXEC MASTER.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQL16NODEB\SQL2014', @locallogin = NULL , @useself = N'True', @rmtuser = N''
GO

And here is an example of the T-SQL command to add a linked server login using the second option:

USE [master]
GO
EXEC MASTER.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQL16NODEB\SQL2014', @locallogin = NULL , @useself = N'False', @rmtuser = N'sa', @rmtpassword = N'sa'
GO

By following these steps and providing the correct authentication details, you should be able to successfully create a linked server and resolve the authentication error.

We hope this blog post helps you in resolving similar issues with linked servers. If you have encountered this error in your environments or have any other SQL Server-related questions, feel free to reach out to us.

Reference: Pinal Dave (https://blog.sqlauthority.com)