Unable to query sharepoint list via openrowset remotely

Question

I have been working on a way to query a SharePoint list from SQL. I was able to get this going using OPENROWSET and the Microsoft.ACE.OLEDB.12.0 provider. The code I'm using is..

select *
from openrowset ('Microsoft.ACE.OLEDB.12.0',
                 'WSS;IMEX=1;RetrieveIds=Yes;DATABASE=http://siteName/subSite/;LIST=listName;',
                 'select top 5 id, email from list') a

https://www.connectionstrings.com/sharepoint/

This works perfectly for me when I'm doing it through SSMS on the SQL Server. But, when I try to run the same code from SSMS remotely. It does not work. the window keeps spinning. Checking on SPID for this query I see that it has a wait type of just OLEDB. no matter how long you let it sit, it will never get the data. I found some articles about adding TRUSTED_CONNECTION=YES to the string and that made no difference.

I have turned on "Ad Hoc Distributed Queries" on the sql server but i feel like i am missing some other security setting somewhere to make this happen. since it works when running the script locally but not remotely.

Any pointers would be great!

Thanks!

Answer 1

Try this:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE WITH OVERRIDE
GO