SQL server 2016 comes with built-in R capabilities. There's a few extra steps post installation to make sure users are able to run R scripts on the server. If not configured correctly users may run into the error pictured below.
The error "SQL Server was unable to communicate with the LaunchPad service" directs our attention to the LaunchPad service. The description in Services.msc gives us a better idea of the service's purpose:
Service to launch Advanced Analytics Extensions Launchpad process that enables integration with Microsoft R Open using standard T-SQL statements. Disabling this service will make Advanced Analytics features of SQL Server unavailable.
Next step is make sure the service is running. In the scenario above LaunchPad was not running. Additionally, we could not get the service to come online on the first attempt.
Consulting the error log indicates the account requires the "Allow log on locally" permission. The 20 windows users created during installation (yes I said 20) need this permission.
Assign the rights using the local security policy manager or contact Windows Server admins to perform the change.
Next attempt to start the LaunchPad service from SQL configuration manager. Make sure the service is running as NT Service\MSSQLLaunchpad
Finally a couple quick things to check before testing an R script:
- Verify the configuration "external scripts enabled" is set to 1
If the run value is set to 0 run the following script:
sp_configure 'external scripts enabled', 1 GO RECONFIGURE WITH OVERRIDE GO
- Ensure the user has proper permission to execute scripts, this can be achived with the following script:
USE dbName GO ALTER ROLE [db_datareader] ADD MEMBER [databaseuser] GO USE dbName GO GRANT EXECUTE ANY EXTERNAL SCRIPT TO [databaseuser]
- After running through the configuration steps test a sample script.
@script=N'OutputDataSet=InputDataSet', @input_data_1 =N'select 1 as [hello world]' with result sets (([hello world] int not null));