SQL 2019 Linked Server (EXCEL)

Question

Hi,

I am trying to link a spreadsheet using MS Office 12.0 Access Database Engine via a linked server.

The spreadsheet is on a network share and is spaces in the name and macros enabled on it (xlsm)

The Linked server provider has "Allow inprocess" the SQL Man is running as administrator but i keep getting this error:

Cannot Initialize the data source object of OLE DB provider MS SQL ERROR 7399

I have OLEDB 16 install and if i try and do the exact same config as OLEDB12 i get:

Could not find installable ISAM MS SQL ERROR 7303

Both my SQL server and OLEDB are 64bit I am at a complete loss.

I have had this working on a backup Excel sheet but i cannot get this work on my existing instance or a brand new instance.

Can anyone assist and help resolve the issue as i am at a complete loss to troubleshooting and diagnostics.

Answer 1

That's a screenshot of NTFS permission on a local XLSM file, not the permissions on a share.

Answer 2

Hi @James Stirling ，

Please check if your account have the permission on the share, right-click the network share and go to properties > security and check it

You can see this article, when you use the domain login, select Be made using the login's current security context is a good choice
https://www.sqlshack.com/query-excel-data-using-sql-server-linked-servers/

-------------

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 3

Both Everyone and PhoneSystem have full permissions on the share

Answer 4

@YufeiShao-msft

The domain account has full permissions on the folder and sub folders and on the share.

With "Be made using the login's security context" I get the same error

The SQL service is using that DC account to Log On As.

Answer 5

@YufeiShao-msft

I am using the same computer and the same account.

I have kind of given up on this as not much feedback and only seen your reply.

But please see a capture when I227119-fileaccesscap.log had everything configured for testing.

You will have to remove the .log from the end of the file.