SQL Server python Machine Learning cannot access filesystem

▲
1
▼

SQL Server 2017 using sp_execute_external_script (SQL Server Machine Learning) to run a python script in the context of the database. python script needs to load a file from filesystem. Everything (the database and the filesystem) is local on my PC. Error trace:

Msg 39004, Niveau 16, État 20, Ligne 1

Une erreur de script « Python » s'est produite lors de l'exécution de « sp_execute_external_script » avec HRESULT 0x80004004.

Msg 39019, Niveau 16, État 2, Ligne 1

Une erreur de script externe s'est produite :

Error in execution. Check the output for more information.

Traceback (most recent call last):

File "", line 5, in

File "C:\PROGRA~1\MICROS~3\MSSQL1~2.MSS\MSSQL\EXTENS~1\MSSQLSERVER201701\B2E71FC7-8E08-4D86-8B82-38AD564DBC9D\sqlindb.py", line 43, in transform

im = Image.open(adresse_image)

File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER2017\PYTHON_SERVICES\lib\site-packages\PIL\Image.py", line 2410, in open

fp = builtins.open(filename, "rb")

PermissionError: [Errno 13] Permission denied: 'C:\Users\aubertl\Downloads\plage_corse_2.jpg'

SqlSatelliteCall error: Error in execution. Check the output for more information.

Message(s) STDOUT provenant du script externe :

Express Edition will continue to be enforced.

SqlSatelliteCall function failed. Please see the console output for more information.

Traceback (most recent call last):

File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER2017\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 406, in rx_sql_satellite_call

rx_native_call("SqlSatelliteCall", params)

File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER2017\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 291, in rx_native_call

ret = px_call(functionname, params)

RuntimeError: revoscalepy function failed.

python
sql-server
asked on Stack Overflow Jun 15, 2019 by Ludovic Aubert

3 Answers

▲
2
▼

By default python services run under "NT Service\MSSQLLaunchpad". File is located in the "C:\Users\aubertl" folder, which is by default not accessible by MSSQLLaunchpad.
I would advice to create another directory, like C:\PytrhonScripts" and grant read access to this folder to MSSQLLaunchpad.

answered on Stack Overflow Jun 15, 2019 by Piotr
▲
2
▼

From Grant all application packages permissions on file folder:

Option 1: Interactive

  • Launch Windows Explorer and right click on the target file folder.
  • From context menu, select Properties.
  • Switch to Security tab.
  • Click Edit button.
  • Click Add... to add ALL APPLICATION PACKAGES privileges.
  • Tick Modify and/or Full Control to allow Read/Write to this directory.

Can also add Everyone to prevent any further issues.

Option 2: Command line

Adapted from StackOverflow answer, run this in an Administator command prompt:

icacls "X:\DataDir" /grant "ALL APPLICATION PACKAGES:(OI)(CI)F" /T
answered on Stack Overflow Jan 15, 2020 by Contango • edited May 14, 2020 by Contango
▲
0
▼

right click on directory -> grant access to -> Add "everyone". And then the script runs and produces the desired output. The user the service runs with (NT Service\MSSQLLaunchpad) cannot be added apparently, so this is a workaround.

answered on Stack Overflow Jun 17, 2019 by Ludovic Aubert

User contributions licensed under CC BY-SA 3.0