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

Implementers
1

Hello All,
I am new to all this (including R) and a disclaimer: I am a health services researcher, not a programmer so forgive me. We are trying to get Achilles up and running.

Our IT Dept DBA has helped me with this problem but we are stumped. He believes this connection is being attempted using SQL Authentication which our SQL server rejects. We cannot find a way to force a Windows Authentication. Tried lots of various ways but here are 3 (below). My code may just be wrong all together.

library(Achilles)
Loading required package: SqlRender
Loading required package: DatabaseConnector
Loading required package: RJDBC
Loading required package: DBI
Loading required package: rJava
Loading required package: rjson

library(RODBC)

ATTEMPT 1:

connectionDetails ← createConnectionDetails(dbms=“sql server”, server=“XXX.XX.X.X”,

  •                                          user="MYNAME", password="MYPASSWORD", schema='OMOP')

achillesResults ← achilles(connectionDetails, cdmDatabaseSchema=“OHDSI.OMOP”,

  •                         resultsDatabaseSchema="results.OMOP", sourceName="Achillesout", 

  •                         cdmVersion = "5", vocabDatabaseSchema="OHDSI.OMOP")

Connecting using SQL Server driver
Error in .jcall(drv@jdrv, “Ljava/sql/Connection;”, “connect”, as.character(url)[1], :
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ‘MYNAME’. ClientConnectionId:9363dff3-4174-439a-a313-421fcbec8583

my note: ERROR MESSAGE HAD THE ACTUAL USERNAME REPORTED, OBVIOUSLY. WE REQUIRE WINDOWS AUTHENTICATION ON OUR SQL SERVER AND ASSUME THAT THIS CODE SETS UP FOR SQL AUTHENTICATION WHICH WILL NOT WORK.

ATTEMPT 2:

connectionDetails ← createConnectionDetails(dbms=“sql server”, server=“XXX.XX.X.X”,

  •                                          domain="OURDOMAIN",

  •                                          user="MYNAME", schema='OMOP')

achillesResults ← achilles(connectionDetails, cdmDatabaseSchema=“OHDSI.OMOP”,

  •                         resultsDatabaseSchema="results.OMOP", sourceName="Achillesout", 

  •                         cdmVersion = "5", vocabDatabaseSchema="OHDSI.OMOP")

Connecting using SQL Server driver
Warning: Using JTDS driver because a domain is specified. This may lead to problems. Try using integrated security instead.
Error in .jcall(drv@jdrv, “Ljava/sql/Connection;”, “connect”, as.character(url)[1], :
java.sql.SQLException: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

my note: Here We added our server domain name and removed the password. Failed.

ATTEMPT 3:

connectionDetails ← createConnectionDetails(dbms=“sql server”, server=“OUR IP ADDRESS”, extraSettings = “trusted_conection=true”)

achillesResults ← achilles(connectionDetails, cdmDatabaseSchema=“OHDSI.OMOP”,

  •                         resultsDatabaseSchema="results.OMOP", sourceName="Achillesout", 

  •                         cdmVersion = "5", vocabDatabaseSchema="OHDSI.OMOP")

Connecting using SQL Server driver using Windows integrated security
Feb 10, 2017 2:29:45 PM com.microsoft.sqlserver.jdbc.AuthenticationJNI
WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path
Error in .jcall(drv@jdrv, “Ljava/sql/Connection;”, “connect”, as.character(url)[1], :
com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:a35faeb5-4926-42f8-bac6-15a53bccdf44

my note: Here drastically simplified the connectiondetails. Failed.

2

Hi @kmurray. Are you connecting from a Windows machine?

1 Try running the client process (e.g. RStudio) as the target Window user (e.g. runas.exe /user: RStudio.exe) and

2 Set the dbms , server and schema properties (i.e. similar to attempt 3 with no extraSettings).

3

To add onto @t_abdul_basser’s questions/points

Based on attempt 3, it does not appear that the driver can find the sqljdbc_auth library. I know for myself, I needed to make sure this library was located in a Windows system folder. Assuming you are on a Windows system and you have downloaded the SQL Server JDBC drivers, here’s one way to resolve this:

  1. Locate the sqljdbc_auth.dll in your JDBC installation. On my machine, this is located under C:\Program Files\Microsoft JDBC Driver 4.2 for SQL Server\sqljdbc_4.2\enu\auth\x64\sqljdbc_auth.dll. If you are running 32-bit Windows, use the x86 folder instead of x64 in the mentioned file path.
  2. Copy the sqljdbc_auth.dll to C:\Windows\system32.

One this is done, you may want to restart your system to be sure that Windows has picked up the change (likely overkill but never hurts on Windows systems).

Here is an example connection that I took from an R script I was running locally where I used SQL Authentication:

# Set parameters
connectionDetails <- createConnectionDetails(dbms="sql server",
                                             server="myservername",
                                             schema = "mydatabasename");

I’ve masked the server and schema names but they should point to your server and database respectively. One other item to note regarding the server: in Windows environments, you may need to provide the fully qualified domain name to your server in the event that you are crossing a domain boundary. To illustrate what I mean, in the example above where I have myservername you may need to use myservername.domain.com for it to carry forward your windows authentication to the server.

Hope this help.

4

Thank you both for your suggestions.

Copying the sqljdbc_auth.dll to C:\Windows\system32 worked. Thanks for the help.

Onward and upward!

5

Does anyone know where to put the sqljdbc_auth.dll on centos6 linux server?

1 Like
6

If anyone else is experiencing issues with this now or in the future:

I had to put the .dll somewhere in my user path variable. If I put it in my system path variable (e.g. Windows/System32 as the DatabaseConnector help page suggests) it would only work if I ran RStudio with administrator rights.

(I am using RStudio in a Windows environment, obviously.)

8

Hi @ke987. Did you ever figure out where to get this working on linux?

9

@Adam_Black or @ke987, I wrote a post on how I configured a test environment to use Windows Authentication for a Mac. Hopefully, it is useful. If you already got this to work in a non-Windows environment, please add comments to my post in places I might have missed. Thanks.