SQL SERVER - Error read Excel files with OPENROWSET

Question

Hello people. My apologies for the simple question, but I don't know what else to do.
I installed SQL SERVER Express 2019 on station "Station1" and created a user named "db_user" with SQL authentication.
On another station "Station2", I can connect in server's ip using "SSMS". I can make queries on the tables without problems. When I try to run an insert script (from Station2) using OPENROWSET an error appears "The OLE DB provider "Microsoft ACE.OLEDB.12.0" for linked server null.
I have already installed the programs "AccessDatabaseEngine_X64", and run the commands "sp_configure 'show advanced options',1" , "ad hoc distibuted queries',1" , "dbo.sp_mset_oledb_prop.... alowinprocess',1" and dynamicparameter', 1" before running INSERT. I was unsuccessful.
I try use my domain user (user_domain) with all permissions released and it still doesn't work.
Does anyone know what to do to get what I need to do, which in short is to log in via "SSMS" from "Station2" on the server installed on "Station1" and run OPENROWSET insert commands on "Station2" to feed the DataBase.
Remembering that the xlsx file is in a network directory "X:\Temp". I did the test by copying the file to "Station2" in "C:\Temp" , changing the path in the script, and it didn't work either
Thank you for your help.

Answer 1

Hi @Anderson Prospero ,

According to your description, you need to reconfigure below two points in your environment.

• The data file must be shared between the two computers.
• The SQL server login user has the read and write permission on data source.

Quote from MS document;

To use BULK INSERT or INSERT...SELECT * FROM OPENROWSET(BULK...) to bulk import data from another computer, the data file must be shared between the two computers. To specify a shared data file, use its universal naming convention (UNC) name, which takes the general form, \Servername\Sharename\Path\Filename. Additionally, the account used to access the data file must have the permissions that are required for reading the file on the remote disk.

If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data.

Please also check the other considerations from above MS document.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Rather than using a mapped drive, you need to use a UNC path. That is, \FILESERVER\Share\Temp\Excelfile.xlsx. SQL Server runs in a separate process, and does not see your drive mappings. And even less, if you set up these mappings on Station2...

I should say that even if you use the file share, you may still get an error. IOn that case, try copying the file to C:\temp on Station1. C:\temp on Station2 is quite pointless, since SQL Server does not see that drive.