Search code examples
rdatabasewindowskerberospsexec

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

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.

Solution

  • 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 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.

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

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

    Permissions for PSExec run from SQL job