question

RayMiller-7311 avatar image
0 Votes"
RayMiller-7311 asked RayMiller-7311 commented

using opendatasource

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.

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered RayMiller-7311 commented

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you mean to use opendatasource in SQL Server (Server 1) to access data in Excel files (Server 2 network share)? - YES. This works OK

The problem comes when I use ssms on server 2 to connect to sql server on server 1 to read the fileshare on server 2. In theory it shouldn't make a difference where I run ssms but it does. The service account has permission to read the file (it works if I run ssms on server 1).

And please check if there are users or apps accessing shared files on server 2. - there are no users/apps accessing the file I need to look at. Users can interrogate other files on the share using notepad, excel etc

0 Votes 0 ·
AmeliaGu-msft avatar image
1 Vote"
AmeliaGu-msft answered RayMiller-7311 commented

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



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

amelia,
I have proven it's a 2-hop issue, by executing the opendatasource through a sql server authenticated account (sql server uses the service account to access the remote fileshare in one hop). So I'll need to either
use a sql account to use opendatasource which isn't great
or set up kerberos.

I'll look at the two docs you suggested. Why are things never simple :)

0 Votes 0 ·