Fix: Getting R and Python to actually work on SQL Server 2022
One of my more surprisingly popular blog posts in the past talked about Machine Learning: Testing your installation of R and Python in SQL Server 2017. The problem is that SQL Server 2022 changed things.
Now the SQL Server team has published an article on how to make it work. What I’ve been finding though, is that people are struggling to follow that article. And more concerning, people using named instances of SQL Server (like SQLEXPRESS) couldn’t get it to work at all, no matter how much they tried.
It took me a while to work out what was needed, so I figured it was time to share it with others. I’ve also submitted a few pull requests to get that page updated. So, by the time you read this, it might already be updated.
What you need to do for R and Python on SQL Server 2022
You need to start by installing SQL Server and making sure you choose at least BOTH the Database Engine Services and the Maching Learning Services and Language.
Unlike earlier versions, the R and Python libraries are not installed or configured for you. If you try to execute R code at this point, even after configuring SQL Server, you will see this type of error:
Msg 39021, Level 16, State 13, Line 1
Unable to launch runtime for 'R' script for request id: 118E729F-3FE9-4599-94F2-3287CEF836F0. Please check the configuration of the 'R' runtime. See 'https://docs.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022?view=sql-server-ver16' for setup instructions.
Msg 39019, Level 16, State 2, Line 1
An external script error occurred:
Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).
The error for Python is similar:
Msg 39021, Level 16, State 13, Line 1
Unable to launch runtime for 'Python' script for request id: 462878C3-A04A-4BC9-B24E-4E1E104051DE. Please check the configuration of the 'Python' runtime. See 'https://docs.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022?view=sql-server-ver16' for setup instructions.
Msg 39019, Level 16, State 2, Line 1
An external script error occurred:
Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).
I’ve included both in case someone is searching for this. At least it tries to link to a solution, even though the link is still a docs link not a Learn link. (I’ll put in a pull request for that too)
Getting R to work
Let’s start by getting R to work.
You need to download and install the latest R 4.2. I got this from https://cran.r-project.org/bin/windows/base/old/4.2.3/ and I installed it with all default options.
You then need to install some standard R dependencies. You do this by running RTerm. I found it here:
I right-clicked and ran as administrator, just in case. Then you execute this code step one row at a time, making sure that each installs ok. Note that on the first one you install, you’ll probably be prompted for a CRAN mirror site. Pick one near you.
install.packages("iterators")
install.packages("foreach")
install.packages("R6")
install.packages("jsonlite")
Then there are some Microsoft packages that need to be installed. Execute these statements one at a time, making sure that each works. You can ignore warnings at this point. (And note this is two lines even though they show line-wrapped here)
install.packages("https://aka.ms/sqlml/r4.2/windows/CompatibilityAPI_1.1.0.zip", repos=NULL)
install.packages("https://aka.ms/sqlml/r4.2/windows/RevoScaleR_10.0.1.zip", repos=NULL)
Now the first challenge
The next step is where things went wrong. You need to find a program called RegisterRext.exe. For me, it was in this folder: C:\Program Files\R\R-4.2.3\library\RevoScaleR\rxLibs\x64
You open a command prompt (cmd.exe) as an administrator. Then you need to configure the instance of SQL Server with the version of R. The article says you do this by executing:
.\RegisterRext.exe /configure /rhome:"%ProgramFiles%\R\R-4.2.3" /instance:"MSSQLSERVER"
That works fine if you have a default instance of SQL Server and if it’s called MSSQLSERVER (the default name). Not so much if you have a named instance. I was trying this on a machine with a SQLEXPRESS named instance as I’d heard this was an issue.
The article didn’t give you any idea or example for a named instance. So I tried all the things I normally would for a named instance, including not knowing if I needed to escape the backslash:
- “.\SQLEXPRESS”
- “MACHINENAME\SQLEXPRESS”
- “.\\SQLEXPRESS”
- “MACHINENAME\\SQLEXPRESS”
and so on. I was about to give up when I finally just tried “SQLEXPRESS”. And that worked ! I understand why people are confused. Nowhere else in SQL Server land do you refer to a named instance that way.
So the command needed in that case was just:
.\RegisterRext.exe /configure /rhome:"%ProgramFiles%\R\R-4.2.3" /instance:"SQLEXPRESS"
Configure and test R in SQL Server
Then it’s time to test the SQL Server end. You need to start by executing:
EXEC sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;
And you then need to restart the SQL Server service.
Next you can see if it’s working. Execute the following in SQL Server:
EXEC sp_configure 'external scripts enabled';
That should show you 1 as a response.
But no cigar yet
All good so far. So, then I tried an actual R query, as suggested in the article:
EXEC sp_execute_external_script @script=N'print(R.version)',@language=N'R';
GO
Well, I didn’t get a great response with that. It should have worked at that point but instead, after quite a while, it returned this:
Msg 39012, Level 16, State 14, Line 1
Unable to communicate with the runtime for 'R' script for request id: BD9EDDE3-A1CF-4390-99C9-1EE08B7B5747. Please check the requirements of 'R' runtime.
STDERR message(s) from external script:
DLL 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Binn\sqlsatellite.dll' cannot be loaded.
Error in eval(ei, envir) :
DLL 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Binn\sqlsatellite.dll' cannot be loaded.
Calls: runScriptFile -> source -> withVisible -> eval -> eval -> .Call
Execution halted
STDOUT message(s) from external script:
Failed to load dll 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Binn\sqlsatellite.dll' with 1455 error.
OK, that looked a bit scary. I started spending ages trying to work out what on earth was wrong with sqlsatellite.dll.
And the weird thing? After a while, and a few more attempts, it just started working. No clue as to why it didn’t just work the first time. But I’ve now seen that on two machines, so it’s no coincidence. Be patient. Eventually it returned:
I then was able to execute something more complex:
EXEC sp_execute_external_script @language = N'R',
@script = N'OutputDataSet <- InputDataSet;',
@input_data_1 = N'SELECT 1 AS RValue'
WITH RESULT SETS ((RValue int NOT NULL));
And then R is working !
Python
Then it was time to see how I went with Python. Once again, not as smooth sailing as I’d hoped.
Again, you need to download Python installer. But the link for Python 3.1 that’s included in the article goes to a page that now tells you that it’s too old to have binaries available. (For Python apparently that means less than a year old). I started to see if it would work with Python 3.11 (the earliest with binaries) but eventually scared myself off, as I knew that wasn’t going to be good.
I searched for “Python 3.10 for Windows” and ended up with a link at the same site, and it did have binaries. That link was:
https://www.python.org/downloads/release/python-3100/
I downloaded it, but importantly, I chose a custom install**. (Very easy to miss on the first page)**. The main thing you need to change is the install folder. It will want to install it in one of your private folders by default, but instead, choose C:\Program Files\Python310 as the install folder. Near the end of the install I also chose hte option to Disable the path length limit. I doubt that’s needed here but I’ve run into it before so I decided to choose it.
Next in an admin command window (same one as before if it’s still open), you need to step through these three commands:
cd "C:\Program Files\Python310\"
python -m pip install -t "C:\Program Files\Python310\Lib\site-packages" dill numpy==1.22.0 pandas patsy python-dateutil
python -m pip install -t "C:\Program Files\Python310\Lib\site-packages" https://aka.ms/sqlml/python3.10/windows/revoscalepy-10.0.1-py3-none-any.whl
That took a little while but was all good.
Another challenge
You then need to set some permissions on folders. The instructions say to execute this command:
icacls "C:\Program Files\Python310\Lib\site-packages" /grant "NT Service\MSSQLLAUNCHPAD":(OI)(CI)RX /T
Again though, that assumes that the service that’s running your SQL Server Launchpad service is “NT Service\MSSQLLAUNCHPAD”.
Mine wasn’t.
You can find what it is (if you don’t remember from when you installed it), by opening (just Windows start/run) services.msc and looking for the service.
Being a standard SQLEXPRESS installation, mine was actually “NT Service\MSSQLLaunchpad$SQLEXPRESS”, so instead of the above command, I needed to execute:
icacls "C:\Program Files\Python310\Lib\site-packages" /grant "NT Service\MSSQLLAUNCHPAD$SQLEXPRESS":(OI)(CI)RX /T
That then worked ok. We also need to set one more permission (and it’s unrelated to your instances):
icacls "C:\Program Files\Python310\Lib\site-packages" /grant *S-1-15-2-1:(OI)(CI)RX /T
We’re not done yet
At this point, we’re back to needing to use a different version of RegisterRext.exe to configure the SQL Server instance for the version of Python.
The instructions say to execute:
cd "C:\Program Files\Python310\Lib\site-packages\revoscalepy\rxLibs"
.\RegisterRext.exe /configure /pythonhome:"C:\Program Files\Python310" /instance:"MSSQLSERVER"
Once again, that’s not going to work for my SQLEXPRESS named instance, so I needed to execute this instead:
.\RegisterRext.exe /configure /pythonhome:"C:\Program Files\Python310" /instance:"SQLEXPRESS"
Now it’s time to test Python in SQL Server
Because you’ve already enabled external scripts in SQL Server, if you’ve previously installed R, you don’t need to enable them again.
Instead, I tested Python:
EXEC sp_execute_external_script @script=N'import sys;print(sys.version)',@language=N'Python'
GO
And that worked:
As did a more complex query:
EXEC sp_execute_external_script @language = N'Python',
@script = N'OutputDataSet = InputDataSet;',
@input_data_1 = N'SELECT 1 AS PythonValue'
WITH RESULT SETS ((PValue int NOT NULL));
GO
And we’re finally done !
I hope that helps someone else get started. (I’ve also pushed pull requests to the Learn site to get some of this updated. Hopefully it will be by the time you read this).
2024-03-04