Adding R Services to an existing SQL Server instance on Windows Server Core

Reading Time: 5 minutes

Previously, I have had issues when I’ve added R Services to an existing SQL Server instance on Windows Server core. So, I thought this post would help others.

To be thorough, I have checked this on two separate combinations of Windows Server Core and SQL Server.

Firstly, a custom Windows Server 2016 and SQL Server 2016 environment. Secondly, a customized Windows Server 2019 and SQL Server 2017 environment. Although most of this applies to both versions.

I have done this post in the order of tasks that you must to do from the start. In reality, it’s based on the Microsoft documentation here and tweaked to work properly on Windows Server core.

However, you might have already installed the feature and patched the server to get the Launchpad service to work. If this is the case and you simply want the provided SQL sample on the original install guide to work you can jump straight to the NTFS permissions section here.

SQL Server version

Before you add R to an existing SQL Server installation, you must make sure you know what version of the database engine you are currently running. So that you know which updates and R cab files are required.

Of course, you can do this in a few different ways. For example, running ‘Select @@version’ within the SQL instance. Make a note of the Service Pack and Cumulative Update version. For example, the below.

Microsoft SQL Server 2016 (SP2-CU3) (KB4458871)

Installer

It’s important to try and use an install set that is the same level of Service pack as your current install. Otherwise, you could end up installing multiple patches to get the SQL Launchpad service to work. Which is something discussed in a previous post here.

I know some companies have a central installer for SQL Server and then have all the updates in another location. Hence, if you are in such an environment be prepared to run multiple updates from that location after the install.

R cab files

When you install R Services via the command line you need to include the location of cab files. You need to make sure you have the right cab files for the installer you are using. In addition, for whichever SQL updates you must do afterwards. To get these cab files you can go to the list here.

Based on the original Microsoft command line reference here, you can then add the feature several ways from the command line. For example, using the below PowerShell.

Start-Process -verb runas -Filepath "d:\setup.exe" -ArgumentList '/q /Action=Install /IAcceptSQLServerLicenseTerms /FEATURES=AdvancedAnalytics /INSTANCENAME="INSTANCE2" /IACCEPTROPENLICENSETERMS /MRCACHEDIRECTORY="d:\setup\R"' -wait –verbose

Afterwards, you can reapply patches and reference the same folder for the R cab files. For example, the below PowerShell command runs SQL Server 2016 SP2. It references the same location you used for the R files previously.

Start-Process -verb runas -Filepath "d:\Updates\SQLServer2016SP2-KB4052908-x64-ENU" -ArgumentList '/qs /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances /MRCACHEDIRECTORY="d:\setup\R"' -wait –verbose

Logs

If the updates finish within seconds, then you either have a super-fast server or more likely a problem. If you have a problem best thing to do is to navigate to the setup logs. Usually, they are stored in “C:\Program Files\Microsoft SQL Server{SQL VERSION NUMBER}\Setup Bootstrap\Log” by default.

Also, if you have opened a remote desktop session on the server to do the install you can open the log using notepad. To clarify, yes notepad really works in Windows Server core, simply type at the command prompt. Afterwards, you can then navigate to the log files and see why it has failed.

When you are manually installing updates is that you might need to restart the server after some of the updates. Usually, the logs will indicate if a reboot is required.

Once you have done this the SQL Launchpad service will start to work. Now, according to Microsoft official documentation here it should be the case of enabling the execution scripts and finish off.

However, with Windows Server core you probably have some other work to do to get R Services working. Which I will cover next.

NTFS Permissions

Folder permissions can cause problems when adding R Services to an existing SQL Server instance. For instance, if you go to the Microsoft website about this here, you can see it references the %Binn folder. Because this is the folder that has the R executables on.

Now, it is important that whichever account runs the SQL Launchpad service has read and execute access to this folder. This usually starts with the text MSSQLLaunchpad.

More importantly, for the sample script from Microsoft to work though it also needs Read and Execute permissions on the R_Services folder.

In addition, you might be doing this by directly on the server by using a remote desktop connection. If so, I recommend using “icacls” from the command line. You can use ‘/?’ after the command for help to use it.

Afterwards, I recommend running the execution script from Microsoft here to see if it works.

You might have to also add read permissions for the SQLRUserGroup to the Binn and R_Services folders. The group gets created during the R Services install. If it’s a default instance it’s usually called SQLRUserGroup, however do check.

There are many ways to get the name for this account, for example:

1. Log onto a management server.
2. Open Computer Manager.
3. Connect to the server in question.
4. Navigate to Groups.
5. Find the group that starts with SQLRUserGroup.

User rights

Another common issue I have found with customized versions of Windows Server core when doing these types of things are user rights issues.

I discussed in a previous post about a very useful Powershell Module that you can use called UserRights. You can download it here.

Download and copy the UserRights PowerShell module onto the server locally into one of the server’s modules folders. For example, you can copy the module to the WindowsPowershell folder in the root of the server’s Documents folder (e.g. c:\users\adminaccount\documents).

Afterwards you can go into PowerShell on the server and run the below to import it for use.

Import-Module UserRights

After the module is imported, you can run the commands in PowerShell to ensure the user rights are correct.

For example, below I have shown how to do it for a named instance called INSTANCE1. I have done this to remind you that you need to use a backtick (`) before any dollar signs if it is in quotes. Change the underlined server name to be the name of the actual server you are working on before use.

Grant-UserRight -Account "YOURSERVER\SQLRUserGroupINSTANCE1" -Right SeInteractiveLogonRight
Grant-UserRight -Account "NT Service\MSSQLLaunchpad`$INSTANCE1" -Right SeChangeNotifyPrivilege
Grant-UserRight -Account "NT Service\MSSQLLaunchpad`$INSTANCE1" -Right SeIncreaseQuotaPrivilege

Remember that if you are on a corporate network you might have to organise getting these user rights applied as part of a Group Policy. So that these rights are automatically added and are not removed.

Execution script

After you have done that, you can complete the install using the rest of the Microsoft documentation from the ‘enable script execution’ step found here. It is very important to remember to restart the services after the enabling script execution step. Otherwise it will not work.

Once the sample script Microsoft provides work you know it’s working and you can relax.

Final word

Well that’s all for this post about adding R Services to an existing SQL Server instance. As you can probably tell from the steps, a lot of this can be automated, and I certainly recommend looking to do that.

Let me know if you have any thoughts about this post. Either with a comment or by reaching out to me elsewhere.