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

Question

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 Files\R\R-3.4.3\bin\Rscript.exe" "D:\RTest\DB.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 AUTHORITY\ANONYMOUS 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.

Vankirk

Are all the machines involved joined to Active Directory? Is Kerberos the authentication protocol? – Simba

All the servers are part of the same active directory. – Vankirk

That's what I thought. This means Kerberos is the default authentication protocol. Answer posted below. – Simba

If we've answered your question please mark it as such which will verify it to others in the community; otherwise please let us know if any. – Simba

Answer 1

The problem is not your code. You're seeing the classic kerberos "double-hop" problem. While Server1 knows your identity when you're logged onto your workstation using integrated Windows authentication also known as iwa, 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 AUTHORITY\ANONYMOUS 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.

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 spn. 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 AUTHORITY\ANONYMOUS LOGON'.” in Windows application

Web App getting Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Permissions for PSExec run from SQL job

Simba