Can't connect to MS SQL using Active Directory Integrated Services via DatabaseLink

Question

We have a data lake in here that uses MS SQL Server 12.02 and connects using the active directory integrated services authentication.

The standard driver would not work

<< "DatabaseLink`"
sqlStandard = 
  SQLConnection[
   JDBC["Microsoft SQL Server(jTDS)", 
    "jdbc:sqlserver://myserver;DatabaseName=db;authentication=ActiveDirectoryIntegrated"], 
   "Name" -> "DataLake", "Catalog" -> Automatic,"Description" -> None, "ReadOnly" -> Automatic, 
   "RelativePath" -> False, "TransactionIsolationLevel" -> Automatic,"UseConnectionPool" -> Automatic];
connStandard = OpenSQLConnection[sqlStandard]
(JDBC::error: The syntax of the connection URL 'jdbc:jtds:sqlserver://myserver;DatabaseName=dw-scv-prod;authentication=ActiveDirectoryIntegrated' is invalid.)

score 5 · Accepted Answer · answered Apr 14 '21 at 14:35

I downloaded the MS SQL driver from Microsoft

https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15

and installed it under C:\Users\myuser\AppData\Roaming\Mathematica\Applications\Oracle\Java

also placed the following dll driver in

mssql-jdbc_auth-9.2.1.x64.dll

in the same directory.

sqlMS = SQLConnection[
   JDBC["com.microsoft.sqlserver.jdbc.SQLServerDriver", "jdbc:sqlserver://myserver;DatabaseName=dbprod;authentication=ActiveDirectoryIntegrated"], 
   "Name" -> "DataLake", "Catalog" -> Automatic, 
   "Description" -> None, "ReadOnly" -> Automatic, 
   "RelativePath" -> False, "TransactionIsolationLevel" -> Automatic, 
   "UseConnectionPool" -> Automatic];
connMS = OpenSQLConnection[sqlMS]
(Success)

Can't connect to MS SQL using Active Directory Integrated Services via DatabaseLink

1 Answers1