SQL Server R and Python Script Write to File Folder Permission Error

Using R script or Python script, I want to write data or text to a file folder. But when I execute my R script or Python script using sp_execute_external_script, I always get an error message indicating cannot open the connection, PermissionError or Permission denied, etc. I found the solution when I grant "All Application Packages" permission on file folder using Windows Explorer that will enable SQL Server Machine Learning Services R or Python Scripts to write to target file path.

Execute R Script to Write Data to Text File on SQL Server

I used SQL Server sp_execute_external_script procedure with following R script to write given string to a text file on a specific file folder.


EXEC sp_execute_external_script

@language = N'R',

@script = N'

Sys.setenv(LANG = "en");

ds <- as.data.frame("Hello Kodyaz.com")

write.csv(ds, file="c:\\my\\RScriptOutput.txt" )'

SQL R Script Code

Unfortunately, if you have not completed permission configurations for the file folder, you will probably get following error message as outcome. As I explain in this guide, the solution is demonstrated grant All Application Packages permission on file folder.

Msg 39004, Level 16, State 20, Line 0
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 0
An external script error occurred:
Error in file(file, ifelse(append, "a", "w")) :
cannot open the connection
Calls: source ... write.csv -> eval.parent -> eval -> eval -> write.table -> file
In addition: Warning message:
In file(file, ifelse(append, "a", "w")) :
cannot open file 'c:\my\RScriptOutput.txt': Permission denied
Error in execution. Check the output for more information.
Error in eval(ei, envir) :
Error in execution. Check the output for more information.
Calls: runScriptFile -> source -> withVisible -> eval -> eval -> .Call
Execution halted

Or if your system is in Turkish like mine, it is possible to see error messages "bağlantı açılamadı", "dosya açılamadı" and "Çalıştırma durduruldu", etc.

Python and R Script execution on SQL Server

Python Script to Write to Text File on SQL Server

Similar to R script, Python scripts can be used with SQL Server sp_execute_external_script procedure. For example, following Python script can be used to write string to text file on a specific file path.


EXEC sp_execute_external_script

@language =N'Python',

@script=N'

textfile = open("c:\\my\\PythonScriptOutput.txt", "w")

textfile.write("Hello Kodyaz.com")

textfile.close()'

SQL External Script Code

Again if following steps to configure file folder permissions for All Application Packages are not completed, again an SQL Server error will occur

Msg 39004, Level 16, State 20, Line 54
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 54
An external script error occurred:
Error in execution. Check the output for more information.
Traceback (most recent call last):
File "<string>
", line 5, in <module>
File "C:\SQL-SQL2019CTP21-ExtensibilityData-PY\Appcontainer1\31B..344\sqlindb_0.py", line 29, in transform
textfile = open("c:\\my\\RScriptOutput.txt", "w")
PermissionError: [Errno 13] Permission denied: 'c:\\my\\RScriptOutput.txt'
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 "C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019CTP21\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 605, in rx_sql_satellite_call
rx_native_call("SqlSatelliteCall", params)
File "C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019CTP21\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 375, in rx_native_call
ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.

Grant All Application Packages Permissions on File Folder

Launch Windows Explorer and right click on the target file folder.
From context menu, display Properties screen.
Switch to Security tab.
Press Edit button.
Press Add... button to add ALL APPLICATION PACKAGES privileges.

When "Select Users or Groups" dialog screen is displayed follow steps:
In Object Types, mark only "Other objects" and clear other options.
Unfortunately, there is a display error on the screen. It always shows "Built-in security principals" as selected option

On Locations... screen, choose the current PC

Then press on "Advanced..." button

and click on "Find Now" to search for "ALL APPLICATION PACKAGES" object from all available users and groups.

grant permission to All Application Packages for a file folder

Select ALL APPLICATION PACKAGES (since my settings is configured for Turkish "TÜM UYGULAMA PAKETLERİ") and press OK and OK then you can add Write option or Full control if required.

After you grant "All Application Packages" permissions, execution of following Python script on SQL Server via stored procedure sp_execute_external_script will be successfully completed and the file RScriptOutput.txt will be created on the target file folder.


EXEC sp_execute_external_script

@language =N'Python',

@script=N'

textfile = open("c:\\my\\PythonScriptOutput.txt", "w")

textfile.write("Python Script to write to text file")

textfile.close()'

SQL Server sp_execute_external_script