1

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.

Max
  • 487
  • 5
  • 19
  • As you specify your Java path with `/`, is this a Linux or MacOS install? If so, the Windows .dll is not going to work for you. – Mark Rotteveel Nov 20 '20 at 20:21
  • No, the / rather than \ was an oversight on my part. This is a Windows install – Max Nov 20 '20 at 22:06
  • Have you tried putting the DLL in a directory, and add that directory to the PATH environment variable? That is actually 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](https://learn.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver15#Connectingintegrated) – Mark Rotteveel Nov 21 '20 at 08:34
  • Creating a directory with the DLL and a path to it seems to have worked. Thanks for the suggestion. Oddly enough, just moving it to the /bin directory worked fine previously. – Max Nov 23 '20 at 22:12

2 Answers2

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
0

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.

Marcos Pérez
  • 1,260
  • 2
  • 7
  • For whatever reason, I found RJDBC queries to run much faster than RODBC queries. I'm not sure why getting RJDBC to work on this machine is such a struggle, it was fairly straightforward on my old computer and I'm following the same procedures, albeit with newer packages. class, path, username, and password are fine, and I have everything installed. I'm thinking it might be a versions compatibility problem at this point. – Max Nov 20 '20 at 22:09
  • Given the error is produced by the Microsoft SQL Server JDBC driver, you can be sure the driver is loaded and used, so Java, and rJava and DBI are also installed. – Mark Rotteveel Nov 21 '20 at 08:41