2 servers on the same vlan inside a dmz
server 1 - windows 2019 datacenter / sql server 2019 standard / service account = <domain>\<name1> / patched to date / ssms 18.9.1 / has a fileshare - fs1
server 2 - windows 2019 datacenter / fileshare server / fully patched / ssms 18.9.1 / has a fileshare - fs2
If i run an opendatasource command on server 1 under ssms
fileshare (fs1) on server 1 result = OK
file on server 1 using local addressing (x:...) result = ok
fileshare (fs2) on server 2 result = OK
file2 from server 2 on server 1 result = ok
If i run exactly the same commands on server 2 under ssms
fileshare ()fs1) on server 1 result = OK
file on server 1 using local addressing (d:...) result = ok - so the execution context is server 1
fileshare (fs2) on server 2 result = fails
OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)" returned message "The Microsoft Access database engine cannot open or write to the file '\\server 2\fs 2...\file2xlsx'. It is already opened exclusively by another user, or you need permission to view and write its data.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)".
NOTE: the xlxs file is NOT open. From server 1 i can use file explorer to open the xlsx file in notepad, so is accessible, besides it works from server1
I get similar results from my own PC but there is a firewall between me and server1 so that complicates things.
Is this something to do with 2 hop authentication and if so how do I work round it? Can anyone else think of anything? This has been bugging me for weeks.