×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

CODE --> 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 --> server

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 --> server

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

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

(OP)
can any one helping me solving this issue please

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

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 https://learn.microsoft.com/en-us/answers/question... you got an advice to set permission for Launchpad service. Have you tried it ?

2) Have you tried the sharing I suggested here https://www.tek-tips.com/viewthread.cfm?qid=181837...
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.

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

(OP)
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

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

Which from the four approaches I pointed out above have you tried?

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

(OP)
case number 3 this is actually what i tried

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close