Microsoft SQL DatabaseLink connection using Windows Authentication

13

9

For those who use Mathematica with databases in companies, a common way to securely connect to those is using Windows Authentication (detailed here).

Mathematica doesn't seem to support this authentication method. How can you connect to a database securely using Windows authentication?

kale

Posted 2012-09-07T03:07:30.693

Reputation: 10 290

Answers

15

After much digging, I found this MathGroup archive detailing how to get Mathematica working with SQL servers protected by Windows authentication.

It's repeated here mainly for posterity's sake.

  1. Download the jTDS files from here: http://sourceforge.net/projects/jtds/files/
  2. Unzip and locate the ntlmauth.dll file in the appropriate folder (x64, x86, IA64).
  3. Copy this file to $InstallationDirectory under SystemFiles>Java>(appropriate Windows folder)>bin
  4. Restart Mathematica.

Let's hope Wolfram decides to include this in the next version.

Update for V10

Now in V10 jTDS 1.3.1 is the standard. No need to manual upgrade.

kale

Posted 2012-09-07T03:07:30.693

Reputation: 10 290

I have a 32-bit operating system. When I downloaded the jTDS file and unzipped it I saw two folders, one called x64 and one called x86 both containing the ntlmauth.dll file. There was no folder for x32. Which version of this file should I use? Also, when I went to my Mathematica...\Java folder there are two sub-folders, one called "Windows" and one called "Windows-x86-64". Both of these folders have a "bin" sub-folder. Which of these is the correct folder to use? – lara – 2013-10-16T08:49:49.633

@LaraJordan, x86 is 32bit. Grab the .dll from that folder and place it under Java>Windows not Windows-x86-64... – kale – 2013-10-16T22:13:24.540

Another option is to use ODBC. Just configure a named connection in the Windows ODBC Control panel and use DatabaseExplorer to puck this up. Works always for me. – Sjoerd C. de Vries – 2015-12-26T16:59:05.423

@kale What do you actually mean by "Now in V10 jTDS 1.3.1 is the standard. No need to manual upgrade." You don't have to copy the ntlmauth.dll file? I don't see it in my folders. – Sjoerd C. de Vries – 2016-10-19T21:25:25.243

@SjoerdC.deVries I'm almost positive they included ntlmauth.dll in the v10 releases. I connect to SQL servers all the time with it now using Windows authentication. It's not in the folder I mention in my answer, but I think it's somewhere else (I'll look tomorrow). If, by chance, you're trying to make a SQL connection in v11, it's quite broken right now because Java no longer includes the needed jTDS hooks. (I have a bug report in with WRI) – kale – 2016-10-19T22:44:04.230

@SjoerdC.deVries In v11 on MacOS, this file is located in /Applications/Mathematica.app/Contents/SystemFiles/Links/DatabaseLink/Java/Libraries/Windows-x86-64 by default (I'm guessing that's where it is on the PC side) – kale – 2016-10-19T22:47:11.020

@kale I moved to v11 indeed (see my question of today). Good that you've been working on this already. Did you find a workaround for the time being? – Sjoerd C. de Vries – 2016-10-19T23:04:36.937

Wow!.. this really works. I just implemented it in version 9. Tks – Murta – 2012-12-08T17:49:08.910

@Murta, I really hoped they implemented it in v9. It's crucial to a lot of us in enterprises. – kale – 2012-12-08T18:45:44.387

Unfortunately the answer is no. Now I have another challenge that is similar to your post in this question.

– Murta – 2012-12-08T18:49:58.783