How to run an R script (which has a database connection using integrated Windows authentication) on a remote computer with a local username?

Question

Consider the following scenario:

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

We have the 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 removes R Script remotely on server R 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 can use any alternative method of replacing PsExec.

Answer 1

The problem is not in your code. You see the classic kerberos "double -hop". Although Server1 knows your identity when you logged in to your workstation using integrated Windows authentication, also known as iwa , RServer does not know your identity, because what is transmitted to it from Server1 is not your authentication token, but an account credential Server1 computer records (Local System). Since anonymous access is probably not allowed on RServer, the connection is not made: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' .

In this scenario, the server server is basically “Server2,” as shown in the following screenshot. From the point of view of your client workstation, this is the 2nd jump from you.

To do this, you need to configure Kerberos delegation on Server1 so that it can transfer any authentication token to RServer for the connections to succeed. Please note that this identity token will not be a username or password, but a Kerberos ticket. You configure Kerberos delegation of authority in the account that starts the process that will initiate the connection from Server1 to RServer. This account will need to have spn . Check out the steps in this article to understand this issue and how to configure SPN: Kerberos Double Hop Overview

Further link:

SQL Server returns the error "Login failed for user" NT AUTHORITY \ ANONYMOUS LOGON. " in windows application

Error logging into web application for user "NT AUTHORITY \ ANONYMOUS LOGON"

Permissions to Run PSExec from an SQL Job