none
Permissions required to access network share when executing R code via sp_execute_external_script RRS feed

  • Question

  • Hi there

    We are using SQL Server 2016 with R Services (In Database) and are having issues trying to load an R script from a location on our network. For example: source("\\MyServer\MyRDirectory\HelloWorld.R").

    We get this error:  cannot open file 'V:\HelloWorld.R': No such file or directory

    We believe it is a permissions issue as we have no problems accessing this file if copied to a local drive. My understanding is when using sp_execute_external_script, the code runs under the context of one of the local R users residing in the local SQLRUserGroup. As it is a local account, we had to play around with permissions to figure out a way around this. Essentially you have create a share and add the Everyone group to the share. You may also need to create an user account with the same name on the server that hosts the share. To test that the local R account could gain access to the share, we reset the password for the account and then logged onto the SQL server as that user. I then accessed the share and had no problems (initially i was being prompted for credentials but fixed this by creating an account with the same name on the server hosting the share). However, even though i can connect to the share when logged in the user, when running my script, it still fails with the SAME error :(   

    Has anyone experienced this and found a resolution?? We are pulling our hair out with this issue! 

    P.S. I have also tried mapping a local drive to the share under this local user account just in case there is an issue with accessing files via UNC, but still get the same error!

    EDIT: Running the following R script in SQL Server 2016 (SSMS):

    DECLARE @rscript AS NVARCHAR(250)
    SET @rscript = '
    source("V:\\HelloWorld.R")'

    EXECUTE sys.sp_execute_external_script
    @language = N'R'
    ,@script = @rscript


    Thursday, February 1, 2018 9:59 AM

All replies

  • Neile,

    Have you disabled the local Windows firewall rule that is installed by default? The outbound rule is named "Block network access for R local user accounts in SQL Server instance ..." and blocks all outbound network traffic for the local user accounts.

    https://docs.microsoft.com/en-us/sql/advanced-analytics/r/security-considerations-for-the-r-runtime-in-sql-server

    Bob

    Thursday, February 1, 2018 3:24 PM
  • Thanks Bob - i have! I had come across that article before and totally thought i was on to something but alas, it didn't work :( 

    Thanks for the suggestion though! it feels like this should just work, really not sure what I'm missing.... others have posted about this issue but unfortunately didn't post any resolutions....

    Thursday, February 1, 2018 4:26 PM
  • Neile,

    I'm guessing that you're running into issues where the network share is not allowing local users (not part of your domain) to retrieve files on the network. I believe that you need to give "Everyone" read permission on the file share for the local user to see contents. However, I've never tried to do exactly this myself. The link below might also help.

    https://social.technet.microsoft.com/Forums/windows/en-US/e50abc9a-2141-42ba-a6ee-b228a01acfe8/how-to-grant-network-access-to-local-user?forum=winservergen

    You might also consider instead of using a UNC share on a separate computer putting all of the files into a separate folder on the SQL Server machine itself and setting that up as available for file sharing with other users.

    Good luck!

    Bob

    Friday, February 2, 2018 3:23 PM
  • Hi Bob

    Thanks for the assistance. I had already granted Everyone read/write on the network file share. I can log in to the SQL server as that local user, and i can connect to that network share. At first it prompted me for credentials, but by creating a local user account with the same name on the server where the file share is hosted, i got around the problem and then could access that share by the user with no problems. But still the R script failed. With no other solutions coming to mind I've decided to scrap the network share idea and just set up a local share on the SQL server itself. Just seems annoying though that you cannot access resources outside of the local server... silly really.

    Thanks for your interest!

    Monday, February 5, 2018 11:02 AM
  • I work for Microsoft on the team that supports ML Server.

    While I haven't done this directly with SQL, my guess is..

    For the mapped drive - mapped drives are for a specific _user_ and not for the entire system. When you map a drive for a user - it is only that user that can see it.

    You may be expecting that your user credentials are getting passed through SQL and your R code is being executed as _your_ user - but it isn't. It is getting executed as accounts that are set up specifically for this purpose. 

    This link describes the issue basically...

    https://stackoverflow.com/questions/182750/map-a-network-drive-to-be-used-by-a-service

    As far as trying it by a UNC path - this should work, but you only included code above giving an example of your mapped drive, so I don't know exactly what you were doing. What I can say though is, you can run it in R manually (outside of SQL) and make sure it works - with the UNC path... while a mapped drive may work when your user runs the Rgui, that is because it is running as your user and can access it... but try the UNC path and make sure it works.

    One thing to note though, make sure you are specifying it like this because you have to escape the \'s in the UNC path

    source(“\\\\Host\\directory\\file”)

    I realize you said you moved forward on things, but I saw the post and wanted to share this info. Feel free to update this post if you have any questions.

    edit: But if your mapped drive requires authentication, say, for example, it uses your Windows user credentials, this may not work. Windows does not allow you to pass user/pass on a UNC path like Linux does, so you could issue a 'system' command from R to use the windows 'net use' command to add a username/pass, but there are a lot of issues with managing that, and you kind of have to do it blindly. Let me know if that is the case, I can provide more info.. but it is far from ideal. This is a limitation of Windows and the nature of how R and SQL work together.


    • Edited by jsomers (MSFT) Monday, February 12, 2018 7:11 PM more info
    • Proposed as answer by jsomers (MSFT) Monday, February 26, 2018 4:42 PM
    Thursday, February 8, 2018 8:04 PM
  • I have the same issue and cannot seem to work around it.  I have a 3 line R script that loads the pdftools library, decodes a PDF file, and returns the results.  The script works either through T-SQL using sp_execute_external_script and a local path to the PDF file, or through the R console window using the same script and UNC paths, but I cannot get the rights set properly to use UNC paths through sp_execute_external_script.  

    I have tried changing the services to use the known good domain account, including the LaunchPad service.  I have removed all the generic accounts from SQLRUserGroup and put in only the one domain account, but I still get the same "The user name or password is incorrect" and then when I try to force in some credentials via a mapped drive (net use) and some persisted credentials, it says "The system cannot find the path specified".

    Any thoughts?

    Monday, March 25, 2019 8:07 PM