RJDBC SQL queries: "This driver is not configured for integrated authentication"

Question

In order to use the R library RJDBC for running SQL queries, I installed the SQL JDBC driver on my computer.

As I've done previously on other machines, I've copied the mssql-jcb_auth-8.4.1.x64.dll to the Java/jdk-.../bin directory prior to installing the RJDBC package in R.

When I attempt to connect to a SQL server from R, i.e.

dbhandle = dbConnect(drv, "jdbc:sqlserver://dw_sqlXX_YY; databaseName=my_db; integratedSecurity=true;")

I get the following error message:

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:83a3242e-097f-4a69-9eed-d1ccbecadc7b

I looked up the "driver not configured for integrated authentication" and found a related stackoverflow post,

JDBC SQLServerException: "This driver is not configured for integrated authentication."

where the user was instructed be make sure that the .dll is in both the driver directory and in the bin directory for Java. I have already satisfied these requirements, as well as the 64-bit compatibility issue.

Is there anything else that I may be missing that I can try? Could there be issues with incompatibilities between driver versions (I have 8.4) and the most recent versions of JDK Java or the RJDBC package in R? It seems as though I've exhausted most other alternatives.

Answer 1

Put the DLL in a directory, and add that directory to the PATH environment variable. That is the recommend approach (putting it in the bin folder of your JDK is not guaranteed to work, and it might be the 'wrong' JDK). See also Connecting with integrated authentication On Windows.

Answer 2

I also had a lot of problems at the beginning. Try this:

drv <- JDBC(driverClass = "class", classPath = "path")
dbhandle = dbConnect(drv, "jdbc:sqlserver://dw_sqlXX_YY; databaseName=my_db; integratedSecurity=true;", username, password)

If it doesn't work, check this:

1. class is ok.
2. path is ok.
3. username and password are ok.
4. You have Java installed.
5. You have rJava and DBI packages installed.

However, I advise you to use the RODBC package. RODBC is more efficient, no size limit and you don't need high technical knowledge. Here is a good tutorial.