How to install machine learning on SQL Server 2022 named instance?

Question

How to install machine learning on SQL Server 2022 named instance?

Christoph Muthmann 181

I've installed SQL Server 2022 (EE RTM and with CU7) and machine learning on a default instance without any problem. Installing it on a named instance runs fine as well. (R 4.2.0 and R 4.2.3)

But when I run my test statement there are warnings on the named instance.

EXEC sp_execute_external_script @language =N'R', @script=N'print("Hello World from R")';

The result is not only the message, but some warnings (BTW very interesting the german word "Warnmeldung" eq. "Warning" on a system I thought to be installed in english)

STDERR message(s) from external script: 
Warnmeldung:
In normalizePath(path.expand(path), winslash, mustWork) :
  path[1]="C:/ProgramData/myinstancename/R/library": Access is denied
Warnmeldung:
package "methods" in options("defaultPackages") was not found 
STDOUT message(s) from external script: 
[1] "Hello World from R"

What is wrong with the installation?

Kind regards
Christoph

5 answers

Your answer

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

Access is denied suggests that there is a permission problem. I would guess that the account NT Service\MSSQLLaunchpad$INSTANCE does have permission on the folder in the error message.

Christoph Muthmann 181 Reputation points

2023-08-21T08:28:17.22+00:00

I've added the service accounts to the local administrator group, changed the service account to Local system and checked all the permissions. The warnings are still there.

Using processmonitor I've caught many results "ACCESS DENIED" from Process Name "rterm.exe" with operation "CreateFile" for the Path "C:\ProgramData" when using the named instance. When using the default instance I've seen no errors for this path.

There are no special permissions on the folder C:\ProgramData, neither for the default instance, nor for the named instance. All users have read & execute and write permissions.

BTW: Has anyone here managed to get this to work on a named instance using SQL Server 2022?
Christoph Muthmann 181 Reputation points

2023-08-21T08:54:17.1533333+00:00

I follow Erland's suggestion regarding permissions. R installation is working fine without errors or warnings.

I've added the service accounts to the local administrator group, changed the service to local system and checked all permissions. Nothing seems to be incorrect or has changed anything in the behaviour. So I still get the warnings on the named instance.

I've tracked this with process monitor. There are many events with "ACCESS DENIED" from process "rterm.exe" and operation "CreateFile" for the path "C:\ProgramData" when using the named instance.
When using the default instance I don't see any events like this.

The local users have read & execute and write permissions on this folder. There are no special permissions, neither for the default instance, nor for the named instance. Not on this folder and not on the subfolders or in c:\Program Files.

BTW: Has anyone here managed to get this to work on a named instance using SQL Server 2022?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-08-21T20:51:35.1333333+00:00

I've added the service accounts to the local administrator group,

Which service accounts? R is executed by the Launchpad process, so it is the permissions for the Launchpad process you should change.

Although, Access denied, can also mean that the file is locked by another process. But given the file path that seems less likely. But you could try rebooting the machine, and the first you do after restart is to run from SQL Server again.
Christoph Muthmann 181 Reputation points

2023-08-22T05:16:38.1733333+00:00

NT SERVICE\MSSQLLaunchpad$Instancename and NT SERVICE\MSSQL$Instancename
I've done a lot of rebooting and the behaviour is still the same.

I've switched from managed service accounts to dedicated domain accounts. Added them to the local administrator group, nothing works. But using the same domain accounts for the default instance is working without any problems. The default instance is working with managed service accounts as well!

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

I tried to install both Python and R for my SQL 2022 which is a named instance. None of them worked, and for R I got the same error message as you.

However, I was able to remove the error message for R.

I first made sure that NT Service\MSSQLLAUNCHPAD$Instance and ALL APPLICATION PACKAGES had permissions to C:\ProgramData\INSTANCE. If memory serves, ALL APPLICATION PACKAGES was there, but not the service account. I also reviewed the permissions on the specific folder, and maybe I added something there as well. Still no cigar. But then I also gave ALL APPLICATION PACKAGES and NT Service\MSSQLLAUNCHPAD$Instance permission on C:\ProgramData. When I did this, I selected "This folder only" to keep things locked down.

This removed the message about "Access is denied".

...However, when I run a simple test script, I get:

Msg 39012, Level 16, State 14, Line 31 Unable to communicate with the runtime for 'R' script for request id: 6EB1E800-8635-4115-B810-E49C9E799CA3. Please check the requirements of 'R' runtime. STDERR message(s) from external script: Error: Script file C:\ProgramData\TJUTVÅ\Temp-R\Appcontainer1\7BE0FCDC-1A76-48F8-8F07-8296FB657158\7BE0FCDC-1A76-48F8-8F07-8296FB657158.R missing for pooled session Execution halted

I have not investigated what happens with the purportedly missing file.

Christoph Muthmann 181 Reputation points

2023-08-25T05:14:32.7866667+00:00

Hi Erland,
thanks for your great work and for clarifying things. I opened a support case and will update this thread as soon as there is news!
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-08-25T15:37:10.71+00:00

It seems that the documentation is iscomplete with regards to the permissions. It's a little strange that it works with your default instance. Would that suggest that the same files for a default instance are not in C:\ProgramData (which is an odd place anyway)?

I recall that I had problems during the CTP of SQL 2022 of getting Python to work with a default instance. I don't remember the details, but I think it was something misisng in the Docs rather than a bug. And I believed that it worked when I tested in a later build.

Let us what you get out of the support case!
Christoph Muthmann 181 Reputation points

2023-09-08T05:11:20.12+00:00

Microsoft has reproduced the issue for named instance. They are also facing same error as mentioned.

This is currently being further investigated.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-09-08T06:29:37.2666667+00:00

Thanks for the update, Christoph. Seems like we are not crazy, then!
Christoph Muthmann 181 Reputation points

2023-10-10T08:35:57.28+00:00

The workaround is to provide full access to MSSQLLaunchapd$<instancename> (Launchpad account) and "All application packages" to C:\programdata folder.
After a restart of the launchpad service, everything is working as expected.
There will be a fix in an upcoming CU which should include using the correct subfolder instead of setting the permissions.
In my opinion there can be a security issue if you have more than one named instance on a server with machine learning, because they would share the folder c:\programdata.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-10-10T19:40:26.5133333+00:00

Thanks Christoph! I had already applied that "workaround", but that only removed the permission error in my case. (See above for error message.)

With the CU fix, they will stop putting things in C:\ProgramData, is that correct?

But what happens if you already have installed R/Python with the wrong paths? (Not that this is any major issue in my case. I typically use Python to try to help someone here in Q&A. I don't engage in machine learning as such. I've since long stopped try teaching my machines anything. They never learn anyway.)
Christoph Muthmann 181 Reputation points

2023-10-11T06:34:11.54+00:00

Hi Erland,

I believe that we have to uninstall R/Python first. Remove the wrong permissions and apply the CU.

Then install R/Python again. I will do this for fun on my test box.

However, I will wait until the CU is available, before upgrading to SQL Server 2022 on my BI machines.

With the CU they use the correct subfolders for each instance instead of messing up c:\programdata.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-10-11T21:47:10.5566667+00:00

Are there a method to uninstall a language extension? Oh well, I guess we will find out.

Answer 3

Vahid Ghafarpour 23,385 Volunteer Moderator

Did you check the following article?

https://social.technet.microsoft.com/Forums/en-US/6ab7a0ca-836f-4d18-ac49-3239d610413d/getting-quotstderr-messages-from-external-scriptquot-message-showing?forum=MicrosoftR

Christoph Muthmann 181 Reputation points

2023-08-21T08:15:12.2333333+00:00

Yes, but this is SQL Server 2022 and the error belongs only to named instances.

Answer 4

RahulRandive 10,486 Volunteer Moderator

Hi @Christoph Muthmann

Please check below stackoverflow thread and mitigation steps for access denied installation error.

https://stackoverflow.com/questions/71129530/r-package-access-denied-installation-error

https://support.microsoft.com/en-gb/topic/general-problem-installing-any-r-package-0bf1f9ba-ead2-6335-46ec-190f6af75e44

Thank you!

Christoph Muthmann 181 Reputation points

2023-08-21T08:17:01.2+00:00

The installation is running fine without errors or warnings!
There are only warnings while using R but the correct results are returned as well.

Answer 5

ZoeHui-MSFT 41,491

Hi @Christoph Muthmann，

Access is denied

Seems like a permission issue, make sure your account has full permission to access the folders.

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.

Christoph Muthmann 181 Reputation points

2023-08-21T08:17:49.2233333+00:00

Yes, that is the same direction Erland is already pointing to.

Share via

How to install machine learning on SQL Server 2022 named instance?

5 answers

Your answer