Problem getting SQL Server 2016 R Services (In-Database) working

Question

SQL Server 2016 CU2 (Enterprise) here and one of my users has requested that we get R-Services installed.

I followed what appeared to be very simple instructions from this page

Set up SQL Server R Services (In-Database)

When I got to the part about using the simple test

exec sp_execute_external_script  @language =N'R',  
@script=N'OutputDataSet<-InputDataSet',    
@input_data_1 =N'select 1 as hello'  
with result sets (([hello] int not null));  
go  

it failed with

Msg 39021, Level 16, State 1, Line 6 Unable to launch runtime for 'R' script. Please check the configuration of the 'R' runtime. Msg 39019, Level 16, State 1, Line 6 An external script error occurred: Unable to launch the runtime. ErrorCode 0x80070057: 87(The parameter is incorrect.). Msg 11536, Level 16, State 1, Line 6 EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

So, I started my internet searching for known problems regarding R-Services and found this article - Known Issues for SQL Server R Services

• I made sure that R-Services had been brought up to date with the Sql Server version (2016, CU2).
• I read where long file names in the rlauncher.config file might cause problems, so I redirected my WORKING_DIRECTORY to C:\TEMP\R_SERVICES. However, I left the RHOME and MPI_HOME configuration entries untouched and still using long file names.
• I made sure the Windows group SQLRUserGroup had full authority to that working directory folder.

After every configuration change, I restarted both SQL Server and SQL Server Launchpad.

However, every time I try to run the sample T-SQL test for R-Services, it fails and I see this entry in the rlauncher.log file

[Error] Session creation failed with: Failed with 2 to get security for C:\TEMP\R_SERV~1\MSSQLSERVER01

I'm reaching out for help from others that might know what the problem is or any additional troubleshooting links.

Answer 1

We wound up having to copy two folders (MSSQLSERVER01 and MSSQLSERVER02) from our Sql Server install directory, which for us was located here

E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ExtensibilityData

We copied the above folders to the new WORKING_DIRECTORY (C:\TEMP\R_SERVICES) we had specified in the rlauncher.config file.

After that, we were able to run the sample R_Services testing query

exec sp_execute_external_script  @language =N'R',  
@script=N'OutputDataSet<-InputDataSet',    
@input_data_1 =N'select 1 as hello'  
with result sets (([hello] int not null));  
go  

Answer 2

If you upgraded to a monthly release of SQL Server 2016 and get ErrorCode 0x80004005 and the ‘unable to launch runtime’ message, you may want to check the NTFS permissions on the folder specified under WORKING_DIRECTORY in the rlauncher.config file. WARNING: it is dangerous to edit these permissions and / or change the path there. Please review https://msdn.microsoft.com/en-US/library/mt590869.aspx for details.

Answer 3

Nothing of the solutions worked to make LaunchPad service run, except removing the whole SQL installation, -reboot-, then redownloading the installer from web, instructed to download the whole media to my computer and re-installed, -another reboot-, in my case I used SQL Server 2017 Developer edition. I left out Standalone Machine Learning Server from the installation.