Sql-server – How to set the working directory to a mapped network drive for R language in SSMS

rsql serversql-server-2016

So I'm messing around with the new "Integration with R" feature in SQL 2016. However, having trouble setting the working directory to a mapped network drive (Z:) within SSMS.

Here's my code:

EXEC sp_execute_external_script
@language = N'R',
@script = N'setwd("Z:/")
          mytextvariable <- getwd();
          OutputDataSet <- as.data.frame(mytextvariable);',
@input_data_1 = N'SELECT 1 as Temp1'
WITH RESULT SETS (([col] char(20) NOT NULL));

Getting the error message:

"Error in setwd("Z:/"): cannot change working directory
Calls: source -> withVisible -> eval -> eval -> setwd"

I've tried changing the drive letter to local, physical drives on the server and it works fine, just doesn't work for the mapped network drive.

Best Answer

Each user session on Windows has its own set of network drives. Just because your user session has the drive mapped doesn't mean the session being run by the SQL Server service has that drive mapped.

You can check if the mapped drive is visible to SQL Server by running this:

EXEC sys.xp_cmdshell 'net use';

This will output something similar to:

output  
New connections will be remembered.  
NULL  
NULL  
Status       Local     Remote                    Network
-------------------------------------------------------------------------------  
OK           Z:        \\machine\share           Microsoft Windows Network  
The command completed successfully.  
NULL  
NULL

In order to successfully run the xp_cmdshell extended stored procedure, you may need to enable it, using this code:

EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE
GO
EXEC sys.sp_configure 'xp_cmdshell', 1;
RECONFIGURE
GO

You can determine what account SQL Server is using by looking at the output from this command:

EXEC sys.xp_cmdshell 'whoami';

The account referenced in the above output must have access to the share, as well as the file system referenced by the share.

I don't have access to R here, however you might try mapping the drive, prior to running the R script, like so:

EXEC sys.xp_cmdshell 'net use Z: \\server\share /user:[DOMAIN]\Username <password>';
EXEC sys.sp_execute_external_script @language = N'R',
    @script = N'setwd("Z:/")
          mytextvariable <- getwd();
          OutputDataSet <- as.data.frame(mytextvariable);',
    @input_data_1 = N'SELECT 1 as Temp1'
WITH RESULT SETS (([col] char(20) NOT NULL));

The above code will cause the drive to be mapped for SQL Server's session until SQL Server is restarted. Be advised, it's not a great idea to save the password in a script unless the script's location is guaranteed safe, otherwise an attacker might see the password.

Related Question