How to Run an R Script (Which Has Database Connection Using Integrated Windows Authentication) on a Remote MAChine Under Local Username

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

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

Connecting to MS SQL Server with Windows Authentication using Python?

You can specify the connection string as one long string that uses semi-colons (;) as the argument separator.

Working example:

import pyodbc
cnxn = pyodbc.connect(r'Driver=SQL Server;Server=.\SQLEXPRESS;Database=myDB;Trusted_Connection=yes;')
cursor = cnxn.cursor()
cursor.execute("SELECT LastName FROM myContacts")
while 1:
    row = cursor.fetchone()
    if not row:
        break
    print(row.LastName)
cnxn.close()

For connection strings with lots of parameters, the following will accomplish the same thing but in a somewhat more readable way:

conn_str = (
    r'Driver=SQL Server;'
    r'Server=.\SQLEXPRESS;'
    r'Database=myDB;'
    r'Trusted_Connection=yes;'
    )
cnxn = pyodbc.connect(conn_str)

(Note that there are no commas between the individual string components.)

Login failed for user 'DOMAIN\MACHINENAME$'

NETWORK SERVICE and LocalSystem will authenticate themselves always as the correpsonding account locally (builtin\network service and builtin\system) but both will authenticate as the machine account remotely.

If you see a failure like Login failed for user 'DOMAIN\MACHINENAME$' it means that a process running as NETWORK SERVICE or as LocalSystem has accessed a remote resource, has authenticated itself as the machine account and was denied authorization.

Typical example would be an ASP application running in an app pool set to use NETWORK SERVICE credential and connecting to a remote SQL Server: the app pool will authenticate as the machine running the app pool, and is this machine account that needs to be granted access.

When access is denied to a machine account, then access must be granted to the machine account. If the server refuses to login 'DOMAIN\MACHINE$', then you must grant login rights to 'DOMAIN\MACHINE$' not to NETWORK SERVICE. Granting access to NETWORK SERVICE would allow a local process running as NETWORK SERVICE to connect, not a remote one, since the remote one will authenticate as, you guessed, DOMAIN\MACHINE$.

If you expect the asp application to connect to the remote SQL Server as a SQL login and you get exceptions about DOMAIN\MACHINE$ it means you use Integrated Security in the connection string. If this is unexpected, it means you screwed up the connection strings you use.

pymssql windows authentication

So, I figured I should answer my own question (it's been a few months) with the method that I ultimately used to solve this problem.

Short answer: I used something else.

Longer answer: For testing windows authentication (other than the currently logged on windows user, which does work) I started using SQLCMD tool from Microsoft, combined with PsExec.

The PsExec I executed with the elevated (-h) and load profile (-e) flags. Using the full user name DOMAIN\USERNAME.

The SQLCMD I executed with the trusted connection -E flag.

The rest is up to you.

SQL Connection String Using a Domain User?

Have a look at connectionstrings.com for every possible variation - a very handy resource I use all the time

Specifically, you want this format:

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

This, of course, only works if the domain account in question is the one opening the connection.

There's no easy way to connect with arbitrary credentials - but you can impersonate the user in question and then connect.

This can be a bit of a pain. An alternative if the users are on the local network (or you control their browser config) is to use Kerberos authentication on your site. The pages will be served with the relevant user's permissions - then you can use the connection string above and IIS will connect to the Db with the appropriate credentials for each user. This is particularly useful from a security perspective as the Db is able to audit on a per-user basis, and permissions can be per-user/row/column instead of only per-app.

SQL Server returns error Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. in Windows application

If your issue is with linked servers, you need to look at a few things.

First, your users need to have delegation enabled and if the only thing that's changed, it'l likely they do. Otherwise you can uncheck the "Account is sensitive and cannot be delegated" checkbox is the user properties in AD.

Second, your service account(s) must be trusted for delegation. Since you recently changed your service account I suspect this is the culprit. (http://technet.microsoft.com/en-us/library/cc739474(v=ws.10).aspx)

You mentioned that you might have some SPN issues, so be sure to set the SPN for both endpoints, otherwise you will not be able to see the delegation tab in AD. Also make sure you're in advanced view in "Active Directory Users and Computers."

If you still do not see the delegation tab, even after correcting your SPN, make sure your domain not in 2000 mode. If it is, you can "raise domain function level."

At this point, you can now mark the account as trusted for delegation:

In the details pane, right-click the user you want to be trusted for
delegation, and click Properties.

Click the Delegation tab, select the Account is trusted for delegation
check box, and then click OK.

Finally you will also need to set all the machines as trusted for delegation.

Once you've done this, reconnect to your sql server and test your liked servers. They should work.

---

Related Topics