One of the blog reader sent me the below mail. I always find that using linked server have been of concern and trouble at a number of places. So when the mail landed – well I was not surprised that there was a problem. It is common and hence the resolution for the same is also common yet not discussed. So I thought this blog will bring the nuances that I thought was the resolution and which helped our blog reader.
Hi Pinal,
I have two SQL Server instances on same machine and I want to fetch data from each other. So I went ahead and created linked server. In SQL Server Management Studio, Server Objects > Right-click, Linked Servers, and then selected New linked server. I gave the remote server name as SQL16NODEB\SQL2014 and “Server type” as SQL Server as shown below.
While saving I got error as below
TITLE: Microsoft SQL Server Management Studio
——————————
The linked server has been created but failed a connection test. Do you want to keep the linked server?
——————————
The OLE DB provider “SQLNCLI11” for linked server “SQL16NODEB\SQL2014” reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider “SQLNCLI11” for linked server “SQL16NODEB\SQL2014”.
OLE DB provider “SQLNCLI11” for linked server “SQL16NODEB\SQL2014” returned message “Invalid authorization specification”. (Microsoft SQL Server, Error: 7399)
If I hit No, the linked server is not created. If I created Yes, linked server is created, but any query is failing with the same error.
What should I do?
Thanks,
<name hidden>
I asked to check and run
sp_testlinkedserver N'SQL16NODEB\SQL2014'
and as expected, it failed with the same error.
OLE DB provider “SQLNCLI11” for linked server “SQL16NODEB\SQL2014” returned message “Invalid authorization specification”.
Msg 7399, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
The OLE DB provider “SQLNCLI11” for linked server “SQL16NODEB\SQL2014” reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider “SQLNCLI11” for linked server “SQL16NODEB\SQL2014”.
The message “Invalid authorization specification” means that linked server settings to connect to the server are not correct. To fix that, we need to go back to linked server properties and go to “security” tab and choose the proper authentication method from last two.
If there is a SQL Login to be used, then we need to provide account and its password.
Here is the T-SQL command for 3rd option where @useself is set to true
USE [master] GO EXEC MASTER.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQL16NODEB\SQL2014', @locallogin = NULL , @useself = N'True', @rmtuser = N'' GO
Here is the T-SQL command for 4th option where @useself is set to false so we need to provide @rmtuser and @rmtpassword
USE [master] GO EXEC MASTER.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQL16NODEB\SQL2014', @locallogin = NULL , @useself = N'False', @rmtuser = N'sa', @rmtpassword = N'sa' GO
Hope this will helps and do let me know if you have ever got this error in your environments.
Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
