SQL SERVER – Linked server creation error: OLE DB provider “SQLNCLI11” for linked server returned message “Invalid authorization specification”

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.

SQL SERVER - Linked server creation error: OLE DB provider "SQLNCLI11" for linked server returned message "Invalid authorization specification" linked-01

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)

SQL SERVER - Linked server creation error: OLE DB provider "SQLNCLI11" for linked server returned message "Invalid authorization specification" linked-02

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”.

SQL SERVER - Linked server creation error: OLE DB provider "SQLNCLI11" for linked server returned message "Invalid authorization specification" linked-03

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.

SQL SERVER - Linked server creation error: OLE DB provider "SQLNCLI11" for linked server returned message "Invalid authorization specification" linked-04

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://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Three Simple Guidelines for System Maintenance – Notes from the Field #098
Next Post
Interview Question of the Week #039 – What is Included Column Index in SQL Server 2005 and Onwards?

Related Posts

20 Comments. Leave new

  • Hello, This is AsgharSHAH, I am facing problem which is very strange. I have remotely SQL, so with the help of SSIS I perform ETL and fetch data on my Local Machine called ServerDWH, after that I create cube under SSAS, so that my BI data available under SSAS.
    For the front end I have Ruby on Rails and I can access SQLSERVER data/tables in Ruby on Rails. Bcoz it is very big data and just to reduce my effort in ROR, I have designed so many Stored Procedure by which I extract the information from DB. But if I use any Stored Procedure which is connected with my SSAS via LinkedServer then I am facing an error…
    TinyTds::Error: Cannot initialize the data source object of OLE DB provider “MSOLAP” for linked server “ServerDWHROR”.: EXEC sp_executesql N’execute ‘DailySales’
    One thing very interesting that sp ‘DailySales’ is running without any problem in SSMS. But problem when I call this SP remotely. Kindly Help. Regards

    Reply
  • Thank you! :-) really helped me.

    Reply
  • Hi! I get the same error but I need a linked server with Windows Authentication.
    How do I do that? Even when I specify my windows credentials it says that login failed for anonymous.

    Reply
  • Hi, I am facing a similar issue. I have created linked server by using option “Be made using this security context”. Linked server is working but sometime (not always) it give the following error:

    Cannot initialize the data source object of OLE DB provider “SQLNCLI11” for linked server “LINKED_SERVER_NAME”

    Please help!

    Reply
  • I am getting below error to create linked server on MySQL DSN. Same thing tried locally and it works but failed for live server.

    The OLE DB provider “MSDASQL” for linked server “MYSQL_DSN” reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 7
    Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “MYSQL_DSN”

    Reply
  • Archit Bhardwaj
    December 7, 2016 3:45 pm

    Hi Pinal, I’m also trying to add Linked Server. Both machines are on different servers. I used Public Ip but still getting below error.

    The OLE DB provider “SQLNCLI11” for linked server “103.18.72.50” reported an error. Authentication failed.
    Cannot initialize the data source object of OLE DB provider “SQLNCLI11” for linked server “103.18.72.50”.
    OLE DB provider “SQLNCLI11” for linked server “103.18.72.50” returned message “Invalid authorization specification”. (Microsoft SQL Server, Error: 7399)

    Reply
    • I have explained that “Invalid authorization specification” can be fixed by going to “security” tab.

      Reply
  • Hi Dave,
    I am getting below error .

    Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “TERADATA”.
    OLE DB provider “MSDASQL” for linked server “TERADATA” returned message “Specified driver could not be loaded due to system error 126: The specified module could not be found. (Teradata, C:\Program Files\Teradata\Client\14.00\ODBC Driver for Teradata nt-x8664\Lib\tdata32.dll).”. (Microsoft SQL Server, Error: 7303)

    Version of SQL: 2014 SP2

    Please help me in

    Reply
  • Jermaine Kelley
    April 27, 2017 1:51 am

    I am still getting the error even after choosing the 3rd option. What do you need to check?

    Reply
  • Hi, I’m having trouble creating a link to a SQL2008 database from an SQL2014 server.

    “SQL Server Native Client 11.00 does not support connection to SQL Server 2000 or earlier” – although it’s a 2008 server (so both are 64bit)

    Any ideas? I’ve tried using a local user als well.

    Reply
    • never mind. I was trying to connect to an SQL2000 server, not 2008. Installing SQLNCLI10 did the job…

      Reply
  • Hi Dave –

    When I tried to start the Snapshot Agent from the Publisher server, it runs to Error and shows this message in the details:
    ——————————————————————————————————————————–
    “Message: Validation failed for the publisher “SISSQLDB12” with error 21879 severity 16 message “Unable to query the redirected server “SISSQLDB12” for original publisher “PUBLISHER” and publisher database “PROD” to determine the name of the remote server: Error 7303, Error Message ‘Error 7303, Level 16, State 1, Message: Cannot initialize the data source object of OLE DB provider “SQLNCLI11” for linked server “[B56BE2AA-D820-XXXX-XXX]”.
    ——————————————————————————————————————————–
    The linked server “[B56BE2AA-D820-XXXX-XXX]” is not in my configured linked server so I am wondering where this came from.

    Can please help me?

    Reply
  • how to use linked server i have created now i want get one table recorde to other sql server instance(database server)

    Reply
  • HAI DAVE THANK YOU SO MUCH THIS BLOG HELPED ME ALOT

    Reply
  • Tirupathi Reddy Ogirala
    February 28, 2019 9:50 pm

    You need to create a New user Login on both servers and remote login to the server with that new user then create linked server. Don’t login with Windows Authentication. And check the ports are opened between the two servers.

    Reply

Leave a Reply

Menu