Machine Learning: Testing your installation of R and Python in SQL Server 2017

One of the wonderful additions to SQL Server in 2016 was the R language. In SQL Server 2017, Python was also added and the combination of both with SQL Server rebranded to Machine Learning Services.

Why would you want these installed? The most common answer is to enable you to run predictive analytics.

But I've found that at many sites, getting R and/or Python installed turned out to be more complicated than it seemed.

NOTE: If you are using SQL Server 2022 or later, see this article instead: https://blog.greglow.com/2024/03/04/fix-getting-r-and-python-to-actually-work-on-sql-server-2022/

Once you have the features added (the in-database options and not the standalone options) for R and Python, you need to enable the execution of external scripts. That's easy enough:

You need to restart the SQL Server service after doing this.

Now you can try to execute this script to see if the features are working:

If all is good, you'll see that it worked. Both values would be returned.

If you haven't changed the default SQL Server configuration though, I don't think that's what you'll see. More likely, you'll see this:

Msg 39012, Level 16, State 1, Line 4
Unable to communicate with the runtime for 'R' script. Please check the requirements of 'R' runtime.

STDERR message(s) from external script:
Fatal error: cannot create 'R_TempDir'

So, why does that happen? It's SQL Server's attempt at telling you that R doesn't like paths with spaces in them, and that's what the default configuration has. (I have no idea why).

If you open Notepad (or your favorite editor) as an administrator, navigate to this file:

The file rlauncher.config holds the configuration for the R feature.

That path for the working directory isn't going to work. Now you can change the values in it to 8.3 filenames like they have for RHOME in the first line, or, my preference, point it to a different temp folder.

I then make sure that C:\Temp actually exists, and that there's an ExtensibilityData folder under that. I then copy in all the folders from the original folder:

These are used for different processes running from the SQL Launch Pad.

Then restart both the SQL Server service and the SQL Launch Pad service and try your script again.

If you still have no luck, chances are high that the security isn't correct for the folders. Ensure that the SQL Launch Pad service account has full control on the ExtensibilityData folder and all sub-folders. By default, the service account will be MSSQLLaunchPad but you can check it in SQL Server Configuration Manager.

I'd restart both services again just for good luck, and then hopefully you'll see a response from both the queries.

Then you're ready to start investigating Machine Learning in SQL Server.

 

 

 

19 thoughts on “Machine Learning: Testing your installation of R and Python in SQL Server 2017”

  1. Hi Greg,

    I did the changes in configuration files then granted permission to MSSQLLaunchpad on the folder, drive but still getting the same error. Any help on this would be really appreciated.
    Thanks,
    Himanshu Singh

  2. Does not work, with SQL SERVER 2022, maybe something changed. Same error. Can you write an article with this SQL Version.

    1. Hi Jaden, that's interesting. But can you give us a hint by what you mean with "does not work". What have you tried, and what now happens?

        1. That's interesting. Those 80004005 errors indicate a file or folder permission error. Does the service account that SQL Server is running as, have permission on the filesystem?

          And 80070003 I've only ever seen as part of Windows Update corruption issues. Does Windows Update run cleanly on the machine?

          1. Windows Update looks fine and up to date.

            Yes my "NT Service\MSSQLLaunchpad$SQLEXPRESS" has full access to the folders written in the rlauncher config file. I do not understand why Microsoft ship a non-functional product like this.

            Can you create a new manual, but with SQL Server 2022 and Windows 11. I think something changes there, maybe a new bug. Would be great und usefull. Now R and Python does not run.

          2. Not sure when I'll get time to test it, as I don't have R and/or Python on a SQLEXPRESS instance anywhere right now. But will add it to my list to test, and will write it up if I find what's missing.

          3. Hi Jaden, looking at this again. I asked previously if the service account that SQL server is running as, has permission on the filesystem. You mentioned that your NT Service\MSSQLLaunchpad$SQLEXPRESS account has full access to those folders. But that's not the same thing as asked. Any idea if the service account that SQL server is running as, has permission on the filesystem?

          4. One thing that is changed is that the runtimes are no longer shipped with the product and need to be installed separately. Have you done this as per this article? https://learn.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022?view=sql-server-ver16

            I've also found another issue where if you have a named instance like .\SQLEXPRESS when you are following the instructions linked above, in the RegisterRext.exe command line, make sure you just use the name of the named instance, not the path to it i.e, instead of "MSSQLSERVER" in the command, use "SQLEXPRESS".

            I'm creating an updated blog post.

          5. It seems like Microsoft made some changes in the latest SQL Server version, or maybe it's related to the R Runtime. As a result, this manual is no longer up to date and needs to be updated. Unfortunately, it didn't work for me either. Greg, it would be awesome if you could update your blog post to reflect these changes.

          6. As I mentioned to Jaden, not sure when I'll get time to test it, as I don't have R and/or Python on my machine at present. But will add it to my list of things to test.

          7. By the way, I know Jaden was using SQLEXPRESS. Which version of SQL Server are you using? What exactly is the error when you try to use it?

          8. One thing that is changed is that the runtimes are no longer shipped with the product and need to be installed separately. Have you done this as per this article? https://learn.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022?view=sql-server-ver16

            I've also found another issue where if you have a named instance like .\SQLEXPRESS when you are following the instructions linked above, in the RegisterRext.exe command line, make sure you just use the name of the named instance, not the path to it i.e, instead of "MSSQLSERVER" in the command, use "SQLEXPRESS".

            I'm creating an updated blog post.

Leave a Reply

Your email address will not be published. Required fields are marked *