JDBC-to-ODBC bridge gone. How to connect to SQL Server now in case of Windows Authentication?

11

3

Connecting to our corporate SQL servers using JDBC never worked for me, perhaps related to the Windows Authentication method that is necessary in our network. However, I found out that configuring the connection using the ODBC tool on Windows and using the JDBC-to-ODBC bridge together with a configuration in DatabaseExplorer worked for me.

I'm currently moving my stuff to a newer 64 bit VDI (old one was 32 bit) and changing Mathematica version from 10.3 to 11.01 and found that the ODBC route doesn't work for me anymore. I get an error: JDBC::classnotfound sun.jdbc.odbc.jdbcOdbcDriver.

Some googling learns me that in Java 8 the whole JDBC-to-ODBC bridge was removed and it looks like Mathematica 11 runs on Java 8, so that might explain it.

Anyone got a solution to this problem? JDBC on its own (that is, without the bridge to ODBC) still does not work for me either.

Sjoerd C. de Vries

Posted 2016-10-19T21:46:40.200

Reputation: 63 549

1

Which (MS?) SQL server version are you using? The jtds-1.3.1.jar file distributed with DataBaseLink seems dated. Why don't you try to use the official MS one: https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774

– Rolf Mertig – 2016-10-19T22:08:46.517

@RolfMertig Thanks, I'll try that tomorrow. – Sjoerd C. de Vries – 2016-10-19T23:06:54.253

@SjoerdC.deVries not sure if my below answer works in your case as you have asked to connect specifically to SQLServer using ODBC-JDBC bridge. As mentioned in M documentation jTDS driver is strongly recommended to use over ODBC. – dsingh – 2016-10-19T23:34:52.557

@SjoerdC.deVries dsingh's answer was the workaround (straight from Wolfram) as of v11. I wasn't aware that this was included in 11.0.1. I've pushed in the past for WRI to develop a native database connection framework. Nearly all my work I do involves pushing/pulling info to DBs, and the java framework is a mess IMO. – kale – 2016-10-20T00:36:08.023

@RolfMertig The versions vary by server. I believe anything between 2010-2014. – Sjoerd C. de Vries – 2016-10-20T05:36:42.210

2@RolfMertig I followed your tip and got it to work. Thanks! Took me about 6 hours, though. I'll write an answer tonight. – Sjoerd C. de Vries – 2016-10-20T13:45:39.587

@SjoerdC.deVries I got the Microsoft Driver working this morning. Looks like that is by far your best bet. – kale – 2016-10-20T16:25:42.470

@kale I described the process that I followed in my answer below. I'm curious how it compares to yours. – Sjoerd C. de Vries – 2016-10-20T21:46:09.733

@RolfMertig As promised: the answer below. And thanks again for the tip! – Sjoerd C. de Vries – 2016-10-20T21:46:58.977

FWIW, V11.0.1/Win7/x64 was the first version for which jTDS using Windows authentication worked for me out of the box, using: OpenSQLConnection[ JDBC["net.sourceforge.jtds.jdbc.Driver", "jdbc:jtds:sqlserver:/myserver/mydb;instance=myinstance"]]. In previous versions, I had to manually download and install the matching ntlmauth.dll. – WReach – 2016-10-21T16:51:49.357

Answers

8

Following Rolf Mertig's tip I examined the merits of Microsoft's latest JDBC driver (not included in Mathematica 11). It worked, but it was quite a trip. The itenary can be found below. Note that you'll need to have evaluated Needs["DatabaseLink`"] first for a few functions below to work.

First, I downloaded Microsofts newest JDCB driver (not the jTDS one) from this download page.

Download the Microsoft JDBC Driver 6.0, 4.2, 4.1, or 4.0 for SQL Server, a Type 4 JDBC driver that provides database connectivity through the standard JDBC application program interfaces (APIs) available in Java Platform, Enterprise Editions.

We need enu\sqljdbc_6.0.7728.100_enu.exe from the download selections.

This zipped package contains the following drivers:

  • Sqljdbc.jar requires a JRE of 5 and supports the JDBC 3.0 API

  • Sqljdbc4.jar requires a JRE of 6 and supports the JDBC 4.0 API

  • Sqljdbc41.jar requires a JRE of 7 and supports the JDBC 4.1 API

  • Sqljdbc42.jar requires a JRE of 8 and supports the JDBC 4.2 API

Since Mathematica 11 uses Java 8 we need Sqljdbc42.jar.

Double clicking the installer unpacks the files to a folder. The location is unimportant as we will just be copying two files from it.

Locate sqljdbc42.jar in subfolder sqljdbc_6.0\enu. Also pick up sqljdbc_auth.dll from sqljdbc_6.0\enu\auth\x64\sqljdbc_auth.dll for 64 bits PCs (which I tried) or sqljdbc_6.0\enu\auth\x86 for 32 bits PCs (which I haven't tried).

The first file should be copied to one of the following three folders:

  • $InstallationDirectory/SystemFiles/Links/DatabaseLink/Java
  • $UserBaseDirectory/Applications/Oracle/Java
  • $BaseDirectory/Applications/Oracle/Java

where the $names are Mathematica variables indicating a base directory. Replace them with the result of evaluating that variable. I used the second one.

sqljdbc_auth.dll can be copied to the same directory or to C:\Windows\System32. Both locations seem to work.

Next a driver resource file is necessary. This file provides some details on the driver, among which the class name (I needed quite some googling before I found the correct one) and the exact calling protocol (this differs from the jTDS one).

JDBCDriver[
 "Name" -> "Microsoft SQL Server (MS6 driver)", 
 "Driver" -> "com.microsoft.sqlserver.jdbc.SQLServerDriver", 
 "Protocol" -> "jdbc:sqlserver://", 
 "Description" -> "Microsoft SQL Server using Microsoft's jdbc 6.0 driver", 
 "Location" -> "C:\\Users\\vriessc\\AppData\\Roaming\\Mathematica\\DatabaseResources\\sqljdbc6.m"
]

This file should be located in one of the folders you get upon executing:

DatabaseResourcesPath[]

{
  {"C:\\Program Files\\Wolfram Research\\Mathematica\\11.0\\SystemFiles\\Links\\DatabaseLink\\DatabaseResources", "DatabaseLink"}, 
  {"C:\\Users\\vriessc\\AppData\\Roaming\\Mathematica\\DatabaseResources", None}, 
  {"C:\\ProgramData\\Mathematica\\DatabaseResources", None}
}

The first result contains similar (standard) resource files, but it is chained to the version 11 directory which I don't like. I choose the second one, which holds my own predefined connection defintions.

The resource file has extension ".m" but it should not be edited with Mathematica because it adds a header to the file which is invisible in Mathematica itself and causes the resource to be skipped by DatabaseLink. Use a plain tekst editor for this. Note that the file contains a link to its own location, so make sure to make that it matches its actual location.

With this file in place, and with the kernel restarted, a connection can now be made using:

conn = 
  OpenSQLConnection[
    JDBC["Microsoft SQL Server (MS6 driver)", "ServerName"],
   "Properties" -> {"integratedSecurity" -> "true"},
   "Catalog" -> "DatabaseName"
  ]

The "integratedSecurity" -> "true" part is very important. It makes Windows Authentication work for me (note the lack of credentials in the call?). This didn't work with the old jTDS drivers, so this is really why the new JDCB drivers are necessary.

A last, optional step, would be to make a resource file that makes the above a named connection. Previously, I had been using DatabaseExplorer for that goal, but for some reason I got weird results in this case. So I just copied an existing connection file and modified it appropriately in a text editor.

SQLConnection[
  JDBC["Microsoft SQL Server (MS6 driver)", "ServerName"], 
  "Location" -> "C:\\Users\\vriessc\\AppData\\Roaming\\Mathematica\\DatabaseResources\\MyArbitraryConnectionName.m", 
 "Name" -> "MyArbitraryConnectionName", 
 "Catalog" -> "DatabaseName",
 "Description" -> "My descripion", 
 "Password" -> "", 
 "Properties" -> {"integratedSecurity" -> "true"}, 
 "ReadOnly" -> Automatic, 
 "RelativePath" -> False, 
 "TransactionIsolationLevel" -> Automatic, 
 "UseConnectionPool" -> Automatic, 
 "Username" -> ""
]

The connection can now be opened using the simple:

conn = OpenSQLConnection["MyArbitraryConnectionName"]

Sjoerd C. de Vries

Posted 2016-10-19T21:46:40.200

Reputation: 63 549

I took a little bit of a shortcut compared to yours. I dropped the applicable .jar and .dll in $BaseDirectory/Applications/Oracle/Java. Then I did OpenSQLConnection[JDBC["com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://sqlservername;databaseName=DBname;integratedSecurity=true"]]. This worked well. Your solution is definitely more thorough. – kale – 2016-10-21T02:18:42.130

(Sorry for the ugly comment above). Also, connecting to a Microsoft Access file directly also works now with the UCanAccess protocol. However, I've learned that you need to set memory=False for large databases (or increase Java heap space) to get it to work quickly. – kale – 2016-10-21T02:26:26.280

1@kale My goal was not having to change my existing programs, which was achieved this way. Anyway, how does Access access helps accessing an SQL server? That just isn't the same thing, or is it? – Sjoerd C. de Vries – 2016-10-21T06:17:50.067

1@SjoerdC.deVries Well done! Now please send an email to Wolfram Support demanding that this new driver be part of Mathematica 11.1 or whatever the next version is called. – Rolf Mertig – 2016-10-21T08:53:43.060

@SjoerdC.deVries Yep, not related, just an FYI since that has also been another headache for me in the past. Great job putting this together, too. – kale – 2016-10-21T12:23:38.983

5

Yes you are right, JDBC-to-ODBC support is gone as Mathematica uses java8. However 11.0.1 mathematica comes with a support for using open source JDBC driver which can be used to connect Microsoft Access DB without relying on JDBC-to-ODBC bridge. e.g.

In[32]:= << DatabaseLink`

In[34]:= $Version

Out[34]= "11.0.1 for Mac OS X x86 (64-bit) (September 21, 2016)"

In[36]:= JDBCDrivers["Microsoft Access(UCanAccess)"]

Out[36]:JDBCDriver["Name" -> "Microsoft Access(UCanAccess)", 
"Driver" -> "net.ucanaccess.jdbc.UcanaccessDriver", 
 "Protocol" -> "jdbc:ucanaccess://", "Version" -> 3.1, 
 "Description" -> "Open-source Java JDBC driver implementation that allows Java \developers and JDBC client programs to read/write Microsoft Access databases.", 
 "Location" ->"/Applications/Mathematica11.0.1.app/Contents/SystemFiles/Links/DatabaseLink/DatabaseResources/accessjdbc.m"]

In[37]:= conn = 
 OpenSQLConnection[
 JDBC["Microsoft Access(UCanAccess)", 
  "/Users/damanjits/databaselink/Tests/AccessDB.accdb"]];

In[38]:= SQLCreateTable[conn, SQLTable["TEST"], 
 SQLColumn["id", "DataTypeName" -> "integer", "PrimaryKey" -> True]]

Out[38]= 0

In[39]:= SQLTables[conn]

Out[39]= {SQLTable["TEST", "TableType" -> "TABLE"]}

There is a small section in documentation which describes about this driver at DatabaseLink/tutorial/DatabaseReference.

dsingh

Posted 2016-10-19T21:46:40.200

Reputation: 609

I noticed the addition of this driver, but its description lists Access as target whereas I need MS SQL Server. I didn't have the feeling that it would work for that application, or does it? – Sjoerd C. de Vries – 2016-10-20T05:27:34.627

1

I am not sure this will be helpful, as I am running Mathematica 9, but maybe the issue is the same, as move from 32 bit to 64 bit.

I use this code as I move from Windows 32 bit to Windows 64 bit, working with Microsoft SQL-Server:

Needs["DatabaseLink`"];
openConn[]:=Block[
  {},
  If[$System==="Microsoft Windows (64-bit)",
    Return[OpenSQLConnection[JDBC["Microsoft SQL Server(jTDS)",
      "SERVERNAME/DATABASENAME"],
      "Username"->"USERNAME",
      "Password"->"PASSWORD"]]];
  If[$System==="Microsoft Windows (32-bit)",
    Return[OpenSQLConnection[JDBC["ODBC(DSN)",
      "DRIVER={SQL Server};"<>
        "SERVER=SERVERNAME;"<>
        "DATABASE=DATABASENAME"],
      "Username"->"USERNAME",
      "Password"->"PASSWORD"]]];
  Null
]

Manuel --Moe-- G

Posted 2016-10-19T21:46:40.200

Reputation: 957

1Thanks for the effort, but it doesn't work for me. Given that you use a username and password it looks like your network doesn't use Windows Authentication. In my case, with the network using WA, my Windows credentials are automatically passed to the server without the need to enter them. – Sjoerd C. de Vries – 2016-10-20T20:10:43.973