I'm trying to do some web scraping with Machine Learning Services using the RCurl
package for R on SQL 2017. Here's a sample of the sp_execute_external_script
code I've tried:
EXEC sp_execute_external_script
@language = N'R',
@script = N'library(RCurl)
library(XML)
url <- "http://www.anywebsite.com/"
tables = getURL(url)
tables <- readHTMLTable(tables, header = TRUE, as.data.frame = TRUE, stringsAsFactors = F)
OutputDataSet <- as.data.frame(tables[1])'
WITH RESULT SETS UNDEFINED;
I'm getting the following error
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: Loading required package: bitops Error in function (type, msg, asError = TRUE) : Failed to connect to www.anywebsite.com port 80: Bad access Calls: source ... getURL -> curlPerform -> .Call -> -> fun
Error in execution. Check the output for more information. Error in eval(expr, envir, enclos) : Error in execution. Check the output for more information. Calls: source -> withVisible -> eval -> eval -> .Call Execution halted
In the sp_execute_external_script
code, if I change the URL string from "http" to "https", the error is essentially the same, but it references port 443
instead.
Initial thought is I have a firewall issue. Indeed, if I turn off Windows firewall, sp_execute_external_script
succeeds.
So...what exe needs permission?
I've tried adding exceptions for sqlservr.exe
(database engine service) and launchpad.exe
(SQL Server launchpad). Neither attempt has succeeded.
Other Notes:
The R
code can be run successfully in RStudio, which has an exception in the firewall.
This is a Windows 10 environment with Windows Defender Firewall.
Update
As you see below, I've accepted an answer. Here's what it looks like in my Windows 10 environment:
You need to disable the Windows firewall rule that is automatically installed when Machine Learning Services is added.
The outbound firewall rule will have a name similar to "Block network access for R local user accounts in SQL Server instance MSSQLSERVER"
User contributions licensed under CC BY-SA 3.0