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!
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.
- drop linked server using sp_dropserver
exec sp_dropserver @server = 'DBServer' go
Re-create the linked server using the same code
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