2

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.

John R Smith
  • 704
  • 4
  • 16
Ray
  • 25
  • 1
  • 5
  • Are all the machines involved joined to Active Directory? Is Kerberos the authentication protocol? – T-Heron Dec 19 '17 at 12:07
  • All the servers are part of the same active directory. – Ray Dec 19 '17 at 22:16
  • That's what I thought. This means Kerberos is the default authentication protocol. Answer posted below. – T-Heron Dec 19 '17 at 22:53
  • 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. – T-Heron Dec 21 '17 at 02:32

1 Answers1

5

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

T-Heron
  • 4,892
  • 7
  • 22
  • 44