Error access is denied when try read excel from shared path ?

Question

I work with SQL Server 2019 on server I face issue when I try to read an Excel file from shared path using python 3.10.

SQL Server exists on server 7.7 and files exist on another server on Active Directory domain 7.9.

When I execute reading to Excel file on local server, it is working from path D:\ExportExcel\testData.xlsx.

But when try to read the Excel from a remote server as below

EXECUTE sp_execute_external_script  
        @language = N'Python',  
        @script = N'import pandas as pd  
                    df = pd.read_excel(r"\\192.168.7.9\Import\10\test\testData.xlsx", sheet_name = "Sheet1")  
                    print(df)';  

I get an error:

Msg 39004, Level 16, State 20, Line 48  
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.  
  
Msg 39019, Level 16, State 2, Line 48  
An external script error occurred:  
  
Error in execution. Check the output for more information.  
  
Traceback (most recent call last):  
  
File "", line 5, in  
File "D:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\9D383F5D-F77E-444E-9A82-B8839C8801E3\sqlindb_0.py", line 31, in transform  
df = pd.read_excel(r"\192.168.7.9\Import\10\test\testData.xlsx", sheet_name = "Sheet1")  
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\util_decorators.py", line 178, in wrapper  
return func(*args, **kwargs)  
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\util_decorators.py", line 178, in wrapper  
return func(*args, **kwargs)  
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\excel.py", line 307, in read_excel  
io = ExcelFile(io, engine=engine)  
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\excel.py", line 394, in init  
  
Msg 39019, Level 16, State 2, Line 48  
An external script error occurred:  
  
self.book = xlrd.open_workbook(self.io)  
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\xlrd_init.py", line 111, in open_workbook  
with open(filename, "rb") as f:  
PermissionError: [Errno 13] Permission denied: '\\192.168.7.9\Import\10\test\testData.xlsx'  
  
SqlSatelliteCall error: Error in execution. Check the output for more information.  
  
STDOUT message(s) from external script:  
SqlSatelliteCall function failed. Please see the console output for more information.  
  
Traceback (most recent call last):  
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 605, in rx_sql_satellite_call  
rx_native_call("SqlSatelliteCall", params)  
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 375, in rx_native_call  
ret = px_call(functionname, params)  
RuntimeError: revoscalepy function failed.  

How to solve issue above please?

What I tried:

I try to open shared path on remote server; I can open it and create new file and read and write on same path

I tried to use another tool for reading as openrowset

 select *   
 from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=\\192.168.7.9\Import\10\test\testData.xlsx;HDR=YES','select * FROM [Sheet1$]')  

and it read the Excel file successfully.

Folder path and file have all permission like network service and owner and administrator and authenticated user and every one and all these have full control over all that .

Please - what could be the issue?

I have been trying for over 3 months to solve issue but can't.

Can anyone please help me?

It reading file and display content

Answer 1

try this ... replace your path at step 3

1. Open a command prompt as an administrator.
2. Run the following command:
3. icacls <excel path> /grant *S-1-15-2-1:(OI)(CI)F /t
4. Open the SQL Server Configuration Manager, select ‘SQL Server Services’. Find ‘SQL Server Launchpad’, right-click on it and select ‘Restart’.

Answer 2

try this ... replace your path at step 3

1. Open a command prompt as an administrator.
2. Run the following command:
3. icacls <excel path> /grant *S-1-15-2-1:(OI)(CI)F /t
4. Open the SQL Server Configuration Manager, select ‘SQL Server Services’. Find ‘SQL Server Launchpad’, right-click on it and select ‘Restart’.

Answer 3

PermissionError: [Errno 13] Permission denied: '\192.168.7.9\Import\10\test\testData.xlsx'

A clear error message, the service account executing the scrip don't have permissions to access/read the Excel file.