search

ohdsi-studies / PioneerWatchfulWaiting

This study is part of the joint PIONEER - EHDEN - OHDSI studyathon in March 2021, and aims to advance understanding of clinical management and outcomes of watchful waiting in prostate cancer.
Apache License 2.0
7 stars 18 forks source link

Errors when connecting to the SQL server with DatabaseConnector #35

Closed velvinnn closed 3 years ago

velvinnn commented 3 years ago

Hi there, I have an error when trying to connect to the MS sql server with the following code:

dbms = Sys.getenv("DBMS")

I also tried with dll with 32-bit system, but the error still occurs.

Sys.setenv("PATH_TO_AUTH_DLL" = "C:/Users/xxx/Documents/JDBC/sqljdbc_6.0/enu/auth/x64") server = Sys.getenv("DB_SERVER") pathToDriver <- if (Sys.getenv("PATH_TO_DRIVER") == "") NULL else Sys.getenv("PATH_TO_DRIVER") library(DatabaseConnector) connectionDetails <- createConnectionDetails(dbms = dbms,                                                                 server = server,                                                                 pathToDriver = pathToDriver) conn <- connect(connectionDetails)

I got the following error message and it traces back like this:

> conn <- connect(connection details) Connecting using SQL Server driver using Windows integrated security Looking for authentication DLL in path specified in PATH_TO_AUTH_DLL: C:/Users/xxx/Documents/JDBC/sqljdbc_6.0/enu/auth/x64 Error in rJava::.jcall(jdbcDriver, "Ljava/sql/Connection;", "connect", : com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:xxx > traceback() 6: stop(structure(list(message = "com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:xxx", call = rJava::.jcall(jdbcDriver, "Ljava/sql/Connection;", "connect", as.character(url), p), jobj = new("jobjRef", jobj = , jclass = "com/microsoft/sqlserver/jdbc/SQLServerException")), class = c("SQLServerException", "SQLException", "Exception", "Throwable", "Object", "error", "condition"))) 5: .jcheck(silent = FALSE) 4: rJava::.jcall(jdbcDriver, "Ljava/sql/Connection;", "connect", as.character(url), p) 3: connectUsingJdbcDriver(driver, connectionString, dbms = dbms) 2: connect(dbms = connectionDetails$dbms, user = connectionDetails$user(), password = connectionDetails$password(), server = connectionDetails$server(), port = connectionDetails$port(), extraSettings = connectionDetails$extraSettings, oracleDriver = connectionDetails$oracleDriver, connectionString = connectionDetails$connectionString(), pathToDriver = connectionDetails$pathToDriver) 1: connect(connectionDetails)

Thanks in advance for your help!

scossin commented 3 years ago

According to this stackoverflow post, the error "this driver is not configured for integrated authentication" is caused by a missing sqljdbc_auth.dll in the system path.
Stackoverflow user Pavan suggests downloading this file from Microsoft site (you may already have it in "C:/Users/xxx/Documents/JDBC/sqljdbc_6.0/enu/auth/x64") and put it in the Java/jre7/bin and Java/jre7/lib folder for rJava to find it.