How to run an R script (which has database connection using integrated windows authentication) on a remote machine under local username? Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of How to run an R script (which has database connection using integrated windows authentication) on a remote machine under local username? without wasting too much if your time.

The question is published on by Tutorial Guruji team.

Consider the following scenario:

  • Database server: DBServer
  • R Server: RServer
  • Orchestrator Server: Server1

We have following R Script (DB.r):

lib.directory = "D:\RTest"
install.packages("RODBC", repos = "http://cran.us.r-project.org", lib = lib.directory)
library(RODBC, lib.loc = lib.directory)

db.string <- "driver={ODBC Driver 13 for SQL Server};server=DBServer;database=Databse1;trusted_connection=Yes;"
db.channel <- odbcDriverConnect(db.string)
close(db.channel)

Server1 executs the R script remotely on R Server using the following code:

PsExec.exe \RServer "C:Program FilesRR-3.4.3binRscript.exe" "D:RTestDB.r"

I get the following error:

[RODBC] ERROR: state 28000, code 18456, message [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.

How can we resolve this error without sending username and password as part of PsExec?

We are open to use any alternative way to replace PsExec.

Answer

The problem is not your code. You’re seeing the classic “double-hop” problem. While Server1 knows your identity when you’re logged onto your workstation using integrated Windows authentication also known as , the RServer doesn’t know your identity because what is passed to it from Server1 is not your identity token, but the machine account credentials of Server1 (Local System). Since anonymous access is probably not allowed onto RServer, the connection fails with: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.

In this scenario, theRServer is basically “Server2” as depicted in the below screenshot. From the perspective of your client workstation, it is the 2nd hop away from you.

enter image description here

To make this work, you’ll need to configure Kerberos delegation on Server1 for it to be able to pass any identity token to RServer so the connections will succeed. Note that this identity token will not be a username or password, but instead a Kerberos ticket. You configure configure Kerberos delegation on the account running the process that will be initiating the connection from Server1 to RServer. That account will need to have an . Read through the steps in this article to get a understanding of this problem and how to configure an SPN: Understanding Kerberos Double Hop

Further reference:

SQL Server returns error “Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.” in Windows application

Web App getting Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’

Permissions for PSExec run from SQL job

We are here to answer your question about How to run an R script (which has database connection using integrated windows authentication) on a remote machine under local username? - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji