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://blog.sqlauthority.com)
25 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
What is the provide you are using?
Thank you! :-) really helped me.
Thanks for letting me know Dima.
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.
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!
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”
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)
I have explained that “Invalid authorization specification” can be fixed by going to “security” tab.
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
The error is clear.. do you have DLL in that location?
I am still getting the error even after choosing the 3rd option. What do you need to check?
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.
never mind. I was trying to connect to an SQL2000 server, not 2008. Installing SQLNCLI10 did the job…
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?
better to clean-up replication and reconfigure.
how to use linked server i have created now i want get one table recorde to other sql server instance(database server)
use four part naming.
HAI DAVE THANK YOU SO MUCH THIS BLOG HELPED ME ALOT
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.
Hi Pinal,
I am getting below error -error number 7373
Cannot set the initialization properties for OLE DB provider “MSOLAP” for linked server “GCBI”.
OLE DB provider “MSOLAP” for linked server “GCBI” returned message “The following system error occurred: “. (.Net SqlClient Data Provider)
could you please help how to solve the issue
Thanks Pinal! this resolved my issue immediately! great fix
hello I am having a slightly different problem, am running a job from a different server using the ‘OPEROWSET’ one of the SP completed successfully(a select statement), but the second SP keeps failing with the following error:
The OLE DB provider “SQLNCLI11” for linked server “(null)” indicates that either the object has no columns or the current user does not have permissions on that object. [SQLSTATE 42000] (Error 7357). The step failed
The only difference between the two SP is that one is a select statement and the one failing is a delete statement.
Also when I execute the SP directly it returns only one table with 0 value, but when I run the script that I used in creating the SP it returns multiple tables with different values as expected. could someone help me with this please.
Thank you, your topic solved my issue :)
I know this is meant as a quick solution to an authentication problem, but using that bottom option is a REALLY BAD OPTION. I’ve lost count of the number of sites I log into as an unprivileged user where I can bounce around from linked server to linked server, elevating my credentials as I go and gain access to the whole environment.
Linked servers are available to ANYONE who can log into the SQL Instance. A linked server is a stored connection string into another environment. Make sure least privilege applies.
Better yet – set up a one to one mapping in the box at the top of the security screen so only people you want to use the linked server can, and then apply least privilege on the other side.