Linked server connection does not work from local

Question

I create a linked server on ssms on the actual server the file resides. The linked server is to query from a file. The test works fine on SSMS.

I then go to my laptop - connect ssms to same sql server and test the linked server and test fails.
What is the best way to figure this issue out? Has to be permissions???

Answer 1

Hi @Dan Marsh ,

Please try this OR open \Server Objects\Linked Servers\Providers in SQL Server Enterprise Manager：
USE [master]
GO

EXEC master.dbo.sp_MSset_oledb_prop
N'Microsoft.ACE.OLEDB.12.0',
N'AllowInProcess',
1
GO

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

A little unfortunate that you hid the @datasrc value entirely. But may I guess that the file resides on a file share?

In that case, there is a double hop when you connect from your laptop. That is, you log on to the SQL Server machine, and the this machine needs to pass your credentials to the file server. For this to work, you need to use Kerberos and have SPNs set up correctly.

A starting point is to run this query:

   SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid  

This should say KERBEROS and not NTLM.