Added R Services to SQL Server 2016 Enterprise - Cannot get past Msg 39021

Question

SQL Server 2016 SP1 CU8

(Have already read the entirety of this answer Problem getting SQL Server 2016 R Services (In-Database) working, and all cited links)

We added R Services - In-database - from SP1 media, then re-ran the CU8 update, which detected and successfully patched the R Service.

The NT Service\MSSQLLaunchpad login has all 4 permissions stated for Local Policies => User Rights Assignment - viz:

Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
Bypass traverse checking (SeChangeNotifyPrivilege)
Log on as a service (SeServiceLogonRight)
Replace a process-level token (SeAssignPrimaryTokenPrivilege)

Server Windows Login MySQlServer\SQLRUserGroup is a server principal with public role membership.

All cited windows logins have express "Full" permissions to the ExtensibilityData folder, including all 20 of the MSSQLSERVERnn folders.

SQLRUserGroup has Full Control of the Working Directory. (Services Restarted).

select CAST(SERVERPROPERTY('IsAdvancedAnalyticsInstalled') as int) as IsMLServicesInstalled
     , CAST(value_in_use as int) as ExternalScriptsEnabled
     , COALESCE(SIGN(SUSER_ID(CONCAT(CAST(SERVERPROPERTY('MachineName') as nvarchar(128))
                                   , '\SQLRUserGroup'
                                   , CAST(serverproperty('InstanceName') as nvarchar(128)
    )))), 0) as ImpliedAuthenticationEnabled
     , coalesce((select cast(r.value_data as int)
                   from sys.dm_server_registry as r
                  where r.registry_key like 'HKLM\Software\Microsoft\Microsoft SQL Server\%\SuperSocketNetLib\Tcp'
                    and r.value_name = 'Enabled'), -1) as IsTcpEnabled
  from sys.configurations
where name = 'external scripts enabled';

returns all 1's.

This has been run and the services restarted, including LaunchPad:

exec sp_configure 'external scripts enabled', 1;
reconfigure with override;

declare @login_name nvarchar(255) = CONCAT(cast(SERVERPROPERTY('MachineName') as nvarchar(128)), '\SQLRUserGroup', CAST(serverproperty('InstanceName') as nvarchar(128)));
if SUSER_ID(@login_name) is null
begin
    set @login_name = QUOTENAME(@login_name);
    exec('create login ' + @login_name + ' from windows;');
end;

select 'Machine Learning Services has been configured for use. SQL Server service needs to be restarted!' as Message

Everything has been installed and configured with either a Windows Local Admin or SQL sysadmin login.

This:

EXEC sp_execute_external_script  @language =N'R',
                                 @script=N'OutputDataSet<-InputDataSet',
                                 @input_data_1 =N'SELECT 42'
      WITH RESULT SETS (([TheAnswer] int not null));
GO

Always ends with:

Msg 39021, Level 16, State 1, Line 1
Unable to launch runtime for 'R' script. Please check the configuration of the 'R' runtime.

Msg 39019, Level 16, State 1, Line 1
An external script error occurred:
Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).

Msg 11536, Level 16, State 1, Line 1
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.

The rlauncher.config doesn't have embedded spaces in the working directory:

RHOME=C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES
MPI_HOME=C:\Program Files\Microsoft MPI
INSTANCE_NAME=MSSQLSERVER
TRACE_LEVEL=1
JOB_CLEANUP_ON_EXIT=1
USER_POOL_SIZE=0
WORKING_DIRECTORY=M:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\ExtensibilityData

I've read every 'R' Install page in BOL and elsewhere I can web-search-find.

Finally, after four hours of head-bashing, I give up!

Can ANYONE, please offer assistance?

Answer 1

I know it is quite a while, but I had the very same error yesterday after adding SQL R system Services to an SQL2016.

Besides the need to manually

• add / grant "NT Server\MSSQLFDLauncher" "LogonAsService" (secpol.msc, or create a approriate Group)

• add / grant "\SQLRusergroup" "Allow logon locally" (secpol.msc) to make SQLServer laucher start at all,

it turned out in my environment that the long name (I had something very similar to "M:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\ExtensibilityData") was causing a fuss - despite it did not include a blank as described in some findings in WWW concerning this.

I did the following:

• created a short folder name "E:\MSSQL_R"

• xcopied the directories from e.g. E:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\ExtensibilityData there (/s/e/v to include empty dirs)

• Gave NT Service\MSSQLFDLaucher "FullControl" NTFS rights to this

• changed the "WORKING_DIRECTORY" path in rlauncher.config to point there

et voila - test query worked after that.