OHDSI Home | Forums | Wiki | Github

Setup for OHDSI R packages to work with Windows authentication from a non-Windows machine

Implementers
#1

For a long time we’ve struggled to get the OHDSI packages to run on a non-Windows machine. I finally got it to work, so I thought I’d share my test setup.

Environment
client - MacOS running BigSur and where OHDSI R packages will run
database - MS SqlServer 2019 (15.0.2)
AD - Active Directory 2008 R2

Setup
Background: Kerberos is used by Active Directory, you need to have the appropriate packages installed on your client machine. For MacOS, the Kerberos packages are usually already installed. On Linux, you’ll have to install krb5-user. Most of this setup comes from [5].

  1. On the non-windows client machine, create or update /etc/krb5.conf so that it points to your AD server. Here’s an example of krb5.conf:
    [libdefaults]
default_realm = DOMAIN.COMPANY.COM
 
[realms]
DOMAIN.COMPANY.COM = {
  kdc = dc-33.domain.company.com
}

  1. Run kinit <username>@DOMAIN.COMPANY.COM to get a ticket granting ticket from the kerberos domain controller (KDC). (NOTE: you want to make sure your KDC is accessible from your machine. This was an issue for me when I VPN’ed.)

  2. Download the latest MSSql JDBC driver. I used v8.2.2.

  3. Try to connect to the database with the following code in RStudio:

    library(devtools)
library(DatabaseConnector)
connectionDetails <- createConnectionDetails(dbms="sql server", pathToDriver =  , server=, extraSettings="authenticationScheme=JavaKerberos")
c <- connect(connectionDetails = connectionDetails)

    In RStudio, you should see that the variable c has a value and the new connection in the connections tab as well.

Hopefully, this will help other sites that are using non-Windows environments to run OHDSI packages and their institutions do not allow sql server local accounts.

References
[1] Connect Azure Data Studio to SQL Server using Windows authentication - Kerberos
[2] Using Kerberos integrated authentication to connect to SQL Server
[3] Overview of Service Principal Name and Kerberos authentication in SQL Server
[4] Active Directory Authentication: How does Kerberos and NTLM work
[5] How to enable Integrated Authentication on macOS and Linux using Kerberos

2 Likes
Failure to connect to server - Is it SQL vs Windows Authentication?
#2

If you are getting the below error on Mac:
Error in rJava::.jcall(jdbcDriver, "Ljava/sql/Connection;", "connect", : com.microsoft.sqlserver.jdbc.SQLServerException: Kerberos Login failed: Integrated authentication failed. ClientConnectionId:13fb0d4e-4822-4de2-a125-8408334cb3ed due to javax.security.auth.login.LoginException (Cannot get any of properties: [user, USER] from con properties not available to garner authentication information from the user)

For Mac (I am sure Monterey and Ventura has this, unsure for lower MacOS), instead of kinit you can also try:

  1. /System/Library/CoreServices and find Ticket Viewer
  2. Click “Add Identity”, then enter UNI@DBMI.COLUMBIA.EDU for identity.
  3. After you click “Continue”, a Kerberos ticket should have been generated.
t