using opendatasource

Question

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.

Answer 1

Hi RayMiller-7311,
Thanks for your reply.
I am guessing if this may be delegation or Kerberos issue.
We can using following query to check if the connection is using Kerberos:

 select session_id,auth_scheme from sys.dm_exec_connections  

If the connection is using Kerberos authentication, please make sure SQL Server service account was trusted for delegation in AD. Please refer to this doc which might help.
If the connection is not using Kerberos authentication, please check if both the following conditions are true:

• The client and server computers must be part of the same Windows domain, or in trusted domains.
• A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it's registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration hasn't been performed or fails, the Windows security layer can't determine the account associated with the SPN, and Kerberos authentication isn't used.

To register the SPN for SQL Server Service account, please refer to this doc which might help.

Best Regards,
Amelia

Answer 2

Hi RayMiller-7311,
Welcome to Microsoft Q&A.

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.".

Do you mean to use opendatasource in SQL Server (Server 1) to access data in Excel files (Server 2 network share)?
What the SQL Server Service account are you using? Please make sure the account has permission to read the file.
And please check if there are users or apps accessing shared files on server 2.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.