0

I created a linked server, then I tried to query the table from the server. I received the following message:

OLE DB provider "SQLNCLI11" for linked server "DBServer" returned message "Invalid authorization specification".

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI11" for linked server "DBServer" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "DBServer".

--My local host login credential: WSSERVER2012\Administrator

-- under Server Objects-->Linked Servers -->DBServer (database AP displayed)

The code I used to created linked server:

EXEC sp_addlinkedserver   
   @server='DBServer', 
   @srvproduct='',
   @provider='SQLNCLI', 
   @datasrc='localhost'
go

The code I used to query the table

USE AP;
go

SELECT 
VendorName, 
CustLastName, 
CustFirstName,
VendorState AS State,
VendorCity AS City
FROM DBServer.AP.dbo.Vendors AS Vendors
    JOIN DBServer.ProductOrders.dbo.Customers AS Customers
    ON Vendors.VendorZipCode = Customers.CustZip
ORDER BY State, City

Please review the configuration setting for linked server and advise what configuration I need to change in order to query the able listed under the linked server. Thanks!

linked server general tab enter image description here

linked server security tab enter image description here

linked server server option tab enter image description here

I did some search online and used the following steps to make the query work. Connection was made from local host to the linked server.

  1. drop linked server using sp_dropserver

exec sp_dropserver @server = 'DBServer' go

  1. Re-create the linked server using the same code

  2. From Server Objects-->Linked Servers->right click on the 'DBServer' Choose properties-->under the Security tab select Be made using the login's current security context

enter image description here

  • 1
    So, first question, why did you choose "without a security context"? – Aaron Bertrand Mar 5 '17 at 18:26
  • @AaronBertrand, I don't have in depth knowledge on how to configure linked server properties in order to make a connection. Could you suggest the proper way to configure the linked server properties. Shall I use openquery clause to make the connection? – user2061466 Mar 6 '17 at 5:08
  • If you're using mixed authentication, I'd try creating a SQL user on the remote server, granting it the permissions you need it to have on the database(s) in question, then using that to connect (fourth option at the bottom of the security page), or explicitly mapping logins on the local server to this remote server login, and setting the "not be made" option. Using the first suggestion, all users will be able to query the remote server, with the permissions you specified; the second suggestion will limit access to the specified users, instead of everyone. – RDFozz Mar 6 '17 at 15:49
  • @RDFozz. thanks for the reply. I did some online search resolved my issue by removing the linked server, re-create the linked server, choose the third options under the security tab. – user2061466 Mar 11 '17 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.