Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

When reading Excel file from shared path on a remote server, an "access denied" error disp

Status
Not open for further replies.
ahmedsa2018

ahmedsa2018

Programmer
Apr 25, 2018
67
EG
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

Python: 
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:

Code: 
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

Code: 
 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
 
Sort by date Sort by votes
Maybe nobody else here is using SQL server with python in similar configuration like you, so we cannot help you.
And It's actually not a python topic, but a topic how to set the SQL server so that it's python scripts have access to the other machine.

There are several possibilities you could try:

1) Here you got an advice to set permission for Launchpad service. Have you tried it ?

2) Have you tried the sharing I suggested here in my post on 10 Aug 22 15:25 ? You need to go first to the server \\192.168.7.9 and allow sharing for everyone on the folder \Import\8. Then you could try what I posted 10 Aug 22 15:25.

3) you wrote above: "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 ... and it read the Excel file successfully."
Then if this works for you, then you can create a temporary table on your MS SQL sever, read into the temprary table the data from the excel file located on remote machine (\\192.168.7.9\Import\10\test\testData.xlsx) and then change your python script so that it reads the data from the database table instead of the excel file.

4) you wrote above "When I execute reading to Excel file on local server, it is working from path D:\ExportExcel\testData.xlsx."
Then using OS command you can try to copy the file from \\192.168.7.9\Import\10\test\testData.xlsx to D:\ExportExcel\testData.xlsx everytime before executing your SQL server + python process.
 
Upvote 0 Downvote
thanks for support
really i get good support from this forum
but i try more but ican't get support for remotly reading excel file
because it give me access permission denied
so i don't know how to solve this issue
last thing i try from same server not from sql using python
and it read data success
but from sql it give me access is denied
 
Upvote 0 Downvote
Which from the four approaches I pointed out above have you tried?
 
Upvote 0 Downvote
let me start explain from this post what i need
goal is how to read data and write to shared path remotly from another server on domain
so iw ill start test from local read data on same server (D:\ExportExcel\testData.xlsx)
ressult success
access and read shared path from same server \\192.168.7.7\ExportExcel\testData.xlsx
error access is denied

from same machine i using python pysharm without sql
to run
import pandas as pd
df = pd.read_excel(r"\\192.168.7.7\ExportExcel\testData.xlsx",engine='openpyxl')
print(df)
it run success reading

so How to solve my issue
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Jongskie M.
  • Locked
  • Question
Copying current file to destination 1
Replies
5
Views
1K
mikrom
mikrom
Loomis
  • Locked
  • Question
Stepping through a MySQL table
Replies
2
Views
755
Loomis
Loomis
ahmedsa2018
  • Locked
  • Question
How to keep columns header on excel without change after export data to excel file?
2
Replies
28
Views
4K
mikrom
mikrom
ahmedsa2018
  • Locked
  • Question
How to modify python script to append data on file using sql server 2019?
Replies
6
Views
820
mikrom
mikrom
ahmedsa2018
  • Locked
  • Question
Directory path display false as not exist although it exist with full control permission ?
2
Replies
23
Views
2K
ahmedsa2018
ahmedsa2018

Part and Inventory Search

Sponsor

Back
Top