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

Question

0.00/5 (No votes)

See more:

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 have tried:

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

Posted 12-Oct-22 12:07pm

ahmed_sa

Updated 12-Oct-22 12:24pm

Add a Solution

Comments

Richard MacCutchan 13-Oct-22 3:44am

You need to find out which account is being used when the SP starts the Python script. The permission denied error is clearly telling you that whichever account it is, it does not have permission to access that file. This is not something that we can fix for you.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2022-10-12T12:24:00

Solution 1

What account is running the Python scripts? Are these Jobs running on the SQL Server?

On the 192.168.7.9 machine, is there a network share setup holding the files you're trying to read? What's the name of that share? In your code, it seems to be named "Import". NO, this is NOT the name of the folder on your 192.168.7.9 machine drive. The folder name and the share name are usually the same, but can be very different.

Next, is the account running the scripts on the remote server setup to have Read permissions to the share? Does it also have permissions to the folder on the drive the share is exposing?

Is your network setup as in an AD domain and are BOTH of these machines in that domain?

Posted 12-Oct-22 12:24pm

Dave Kreskowiak

Comments

ahmed_sa 12-Oct-22 20:30pm

I appreciate your support
1-What account is running the Python scripts? Are these Jobs running on the SQL Server?
yes running on sql server 2019 with account admin
2-On the 192.168.7.9 machine, is there a network share setup holding the files you're trying to read? What's the name of that share?
yes there are network shared setup exist and i can expolore it and open it manually
3-Is your network setup as in an AD domain and are BOTH of these machines in that domain?
yes
I try it by openrowset and read data from server 7.9 shared path
to server 7.7 by using openrowset
4-is the account running the scripts on the remote server setup to have Read permissions to the share?
yes have permission read

Dave Kreskowiak 12-Oct-22 21:05pm

1 - There is no such thing as account "admin" unless you created it. What is the name of the account that is running the code?

The reason you're getting "Access Denied" in the Python code running on the SQL Server is because the account running the code, most likely, does NOT have Read/Write permissions to either the Share or the Folder, or both. Those permissions must be setup properly for this to work.

Another possibility is the file your code is trying to open is locked by another process and has exclusive access to the file. The process that has the file open has to close it before another process (your code) can open the file.

ahmed_sa 12-Oct-22 20:36pm

also it is not job it is python script running on sql server

Dave Kreskowiak 12-Oct-22 21:08pm

So I take it this means you are running the code either from a CMD Prompt or some environment you launched when logged into the machine?

ahmed_sa 12-Oct-22 21:35pm

thanks for support
sure file not open
and if account i run python from it not have permission
i use this account with open rowset and read data
so can you give me more detail please

Dave Kreskowiak 12-Oct-22 21:50pm

What account did you use to login to SQL Server Management Studio to run this:

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)';

ahmed_sa 14-Oct-22 5:44am

using account sobhy i can connect to another server and read data using openrowset
so if there are way to read data from server to server this meaning i can access it
so why message display access is denied
can you help me please

Dave Kreskowiak 14-Oct-22 7:29am

So you're saying you can connect to a 3rd machine and query data on it?

ahmed_sa 14-Oct-22 7:54am

i can't over python script
but using openrowset using same path i need to read from it
it read success
but using python can't

ahmed_sa 14-Oct-22 8:00am

you can connect to a 3rd machine and query data on it?
yes

Dave Kreskowiak 14-Oct-22 8:19am

OK, that just means the permissions on the server you're trying to connect to are not setup correctly.

ahmed_sa 14-Oct-22 9:01am

so please how to setup correctly

Dave Kreskowiak 14-Oct-22 10:23am

You don't know how to setup permissions on a folder or on a share, do you?

ahmed_sa 14-Oct-22 12:02pm

I have permission on folder every one and system and account create running service

Dave Kreskowiak 14-Oct-22 12:04pm

But apparently not the account you're running the Python code under. You have to add that account and give it at least Read and Write permissions.

You have to do the same thing on the Share you setup for that folder too.

ahmed_sa 14-Oct-22 12:12pm

are netservice/launchpad must add permission read and write for path i need to access
or not needed

Dave Kreskowiak 14-Oct-22 12:24pm

No, they don't have anything to do with this.

ahmed_sa 14-Oct-22 14:02pm

All application package permission for shared path I need to access AND read it is necessary
now shared path not have all application package so must add it or not

Dave Kreskowiak 14-Oct-22 17:17pm

:smack: All you need to do is add the ONE account you're running the code as. That's it!

ahmed_sa 15-Oct-22 11:20am

last question are using python on sql server 2019 support create and write and read file remotely on shared path meaning reading and writing excel file from remote server
or not support

Dave Kreskowiak 15-Oct-22 12:21pm

The filepath has nothing to do with the ability to read/write Excel files.

Since you're using Python, yes, you can read/write Excel files, so long as you have the appropriate module for doing so loaded by your Python code.

It does not matter if you run the code yourself or if SQL Server is running the code.

ahmed_sa 15-Oct-22 17:48pm

why this paths display
are this paths below have issue
are paths mention must have permission
and what permission must have
also sql installing on d drive
are this issue
or must sql install on pth it have operating system

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

Dave Kreskowiak 15-Oct-22 20:59pm

Sigh... The paths you posted are the scripts that are having problems and throwing the errors. It's showing you the line numbers in those scripts that are having problems.

Beyond that, I don't know what to tell you. I've said everything I can and you still don't understand what's going on or even how to describe what you want to do to other people.

ahmed_sa 12-Oct-22 21:37pm

account is i login with it name sobhy