SQL Server ML. External Python Script: Access Denied Error

Question

I am trying to make something useful with SQL Machine Learning Services and Python.

I am trying to include an external python script into the SQL code. For now I am testing on SQL Server Management Studio on SQL Server 2019 Dev Edition.

Folloing Niels Berglund, excelent article I am doing:

EXEC sp_execute_external_script
@language =N'Python', 
@script=N'
import os
os.system("C:\TEMP\ml.py")
OutputDataSet = InputDataSet
print("Say Hello from SQL Server Management Studio")
',
@input_data_1 =N'SELECT 42' 
WITH RESULT SETS (([TheAnswer] int not null));  
GO 

My ml.py script is just this:

#!"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\python.exe"
print("Say Hello from the external script")

When I run my T-SQL script I get this:

STDERR message(s) from external script: 
Access Denied

STDOUT message(s) from external script: 
Say Hello from SQL Management Studio


(1 row affected)

Completion time: 2020-12-23T08:51:59.9264251-05:00

I made sure the script has what I believe are the right permissions:

I even tried just assigning full control to Everyone. I do not see any error on either SQL or Windows Event Viewer.

I have tried with the following variations of os.system() without any success:

os.system("\"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\Lib\cust\ml.py\"")

os.system("\"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\python.exe\" \"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\Lib\cust\ml.py\"")

os.system("python.exe \"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\Lib\cust\ml.py\"")

os.system("python.exe C:\TEMP\ml.py")

Any idea is very welcome.

Thank you!

Answer 1

I was finally able to make this work by:

• Passing python /script location/ to os.system.
• Placing the script in the cust folder inside python services in my SQL Server instance. I am not overly happy about this. It seems to be a question of folder permissions but I much I tried to mimic them on another more convenient folder, I was not able to make it run successfully.

This is how it looks:

EXEC sp_execute_external_script
@language =N'Python', 
@script=N'
import os
os.system("python \"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\Lib\cust\ml.py\"")

OutputDataSet = InputDataSet
print("Say Hello from SQL Server Management Studio")
',
@input_data_1 =N'SELECT 42' 
WITH RESULT SETS (([TheAnswer] int not null));  
GO