R Engine in SQL Server cannot create file to local storage

Question

Dear everybody!

I did all the recipes expressed on the following link:
https://www.mssqltips.com/sqlservertip/6425/how-to-export-data-from-sql-server-to-excel
but I still face one fatal problem.
The R script tries to create the excel file but is denied the write permission to the local drive. I formatted the local drive with FAT32 file system but the problem persists. My operating system is Windows Server 2019. The error returned is the following from the messages section of SSMS:

Warning message:
In file.create(to[okay]) :
cannot create file 'e:\ table.xlsx', reason 'Permission denied'

here is my code:

DECLARE @rscript NVARCHAR(MAX);  
SET @rscript = N'  
    OutputDataSet <- SqlData;  
      
   library(openxlsx)  
   library(dplyr)  
     
   wb <- createWorkbook()  
   addWorksheet(wb, sheetName = mytname)  
   writeData(wb, mytname, OutputDataSet)  
   
   saveWorkbook(wb, file = paste(paste("e:\\",mytname),".xlsx", sep=""), overwrite = TRUE)  
';			  
  
DECLARE @sqlscript NVARCHAR(MAX);  
SET @sqlscript = N'  
    SELECT top 1 AddressID, AddressLine1 from [AdventureWorks2014].Person.Address  
	';  
	  
--SELECT top 1 AddressID, AddressLine1 from [AdventureWorks2014].Person.Address  
  
EXEC sp_execute_external_script  
    @language = N'R',  
    @script = @rscript,  
    @input_data_1 = @sqlscript,  
    @input_data_1_name = N'SqlData',  
    @params = N'@mytname nvarchar(20)',  
    @mytname = N'table';  
GO  

I truly appreciate your help. :)

Image of the error:

Answer 1

What permissions have you set up? I don't think FAT32 is a good idea. The Launchpad process has fairly limited permissions, and I have now idea how you set up permissions in FAT32. I would say stick to NTFS, and then we can work from there.

Answer 2

Hi Erland,

Actually, I formatted FAT32 because I did the same thing on my personal computer. I created a new NTFS partition first for the R engine to save the excel files on and the "permission denied" scenario persisted contrary to the fact that I gave full control permission to "everyone" group and "NT Service\MSSQLLaunchpad" user. Then I formatted the partition with FAT32 and it worked! I aimed to execute the same plan on my operational environment server but it failed. This is where the idea of FAT32 came from. And I don't believe FAT32 has the ability for the permissions to be set on.

Answer 3

Hi @ amomen-8749,

Could you please try to give enough permission on that folder to the Windows group SQLRUserGroup?
Please refer to R Script permissions error and this doc which might help.

Best Regards,
Amelia