Linked servers are a useful feature in SQL Server that allow you to access data from other database servers. However, they can sometimes cause issues, such as the “Invalid authorization specification” error. In this blog post, we will discuss this common error and how to resolve it.

Recently, a reader contacted me about an error they were encountering with a linked server. The error message they received was:

The linked server has been created but failed a connection test. Do you want to keep the linked server?
ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server, Error: 7416)

Upon investigating the error, I discovered that it was caused by an incorrect setting in the security tab of the linked server configuration. Specifically, the reader had chosen the first option, which resulted in the error. To resolve the issue, I advised them to use either option 2 or option 3.

This error serves as a reminder that even small configuration settings can have a significant impact on the functionality of linked servers. It is important to carefully review and select the appropriate options when setting up a linked server.

If you have encountered a similar linked server error, I encourage you to share your experience in the comments below. By sharing our experiences, we can learn from each other and help troubleshoot common issues in SQL Server.