Package RCurl fails to connect to URL via sp_execute_external_script

0

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: Windows Defender Firewall with Advanced Security - Outbound Rules

r
sql-server
sql-server-2017
microsoft-r
asked on Stack Overflow Nov 4, 2018 by Dave Mason • edited Nov 6, 2018 by Dave Mason

1 Answer

1

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"

answered on Stack Overflow Nov 5, 2018 by Bob Albright

User contributions licensed under CC BY-SA 3.0