Same error in my case

Same error in my case

Syed,
We've switched to "AAD auth with Service Principal" - works fine and much better to configure (from systems integration point of view).

@beloblotskiy Thanks. How did your val config look like in case of AAD with Service Principal?

@beloblotskiy Thanks. How did your val config look like in case of AAD with Service Principal?

We're using Databricks "Copy Data" now, it supports it out of the box.

we get the same exception

@allenwux @jiayuehu @CarlRabeler @kpanwar @zeqicui

A colleague of mine and I are also getting the same issue.

Cluster Information

• Databricks Runtime Version: 5.2 (Apache Spark 2.4.0, Scala 2.11)
• [Installed Library] com.microsoft.azure:adal4j:1.6.3
• [Installed Library] com.microsoft.azure:azure-sqldb-spark:1.0.2

Code Snippet

import com.microsoft.azure.sqldb.spark.config.Config
import com.microsoft.azure.sqldb.spark.connect._

val config = Config(Map(
  "url"                    -> "<sqlsvrname>.database.windows.net",
  "databaseName"           -> "<sqldbname>",
  "dbTable"                -> "dbo.Bitcoin",
  "user"                   -> "<username>",
  "password"               -> "<password>",
  "authentication"         -> "ActiveDirectoryPassword",
  "encrypt"                -> "true",
  "ServerCertificate"      -> "false",
  "hostNameInCertificate"  -> "*.database.windows.net"
))

val collection = sqlContext.read.sqlDB(config)
collection.show()

Error Message
java.lang.NoClassDefFoundError: com/microsoft/aad/adal4j/AuthenticationException

Any updates on this? We are running into similar issue - pretty much the same config code like in previous post #28 (comment)

@beloblotskiy - can you share config how it worked with service principal? I have tried with both user name/password and service principal - I get the same error in either case. I have verified SPN by using SSMS to ensure connection works and same with azure data factory as well(both of them work and allow me to preview data).

Thanks in advance,
Guruprasad

We are getting this same error using
com.microsoft.azure:adal4j:1.6.3
com.microsoft.sqlserver:mssql-jdbc:6.4.0.jre8

This issue seems to not be specific to azure-sqldb-spark

Is this an incompatibility between adal4j and mssql-jdbc?

Is there a specific authentication exception that is causing this issue or is it a catch all problem for AAD auth exceptions?

@Mike-Ubezzi-MSFT please assign to @VanMSFT

Following up with @GithubMirek and @allenwux

Referenced article: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-spark-connector

We have a workaround. It is lengthy and a bit complex, but it is verified. Hopefully it would unblock some of you.

Root cause

For AAD auth to work correctly, some extra JARs are needed. In addition, since the Azure Databricks runtime already ships an older version the MSSQL JDBC driver, we need to replace that with a newer version first.

High-level steps to fix

Conceptually, the steps are:

• Use a Databricks level init script to delete the default mssql JDBC driver that ships with Databricks runtime
• Use Maven to get a matching set of JARs for a given JDBC driver version. To do this, you downloaded the POM file for the 6.4 release of the driver (extract from the source here), install Apache Maven and OpenJDK if you don’t already have those and then execute mvn dependency:copy-dependencies to get the JARs under the target\dependency sub-folder. This step is also detailed in the MSSQL JDBC driver wiki.
• Then use the appropriate minimum set of JARs required for the MSSQL JDBC 6.4.0 JAR within Databricks. To do this, there are 2 phases. You need the Databricks CLI to do this efficiently. I think the UI might work as well but I just prefer to use the CLI.

Updated repro script

Before I proceed, I’d also like to share my minimal repro script, as this has nothing to do with the Spark connector for SQL. It is JDBC/ ADAL specific. So use the repro below to test:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

val ds = new SQLServerDataSource();
ds.setServerName("somesql.database.windows.net"); // Replace with your server name
ds.setDatabaseName("somedb"); // Replace with your database
ds.setUser("user@contoso.com"); // Replace with your user name
ds.setPassword(dbutils.secrets.get(scope = "akvscope", key = "password")); // Replace with your password
ds.setAuthentication("ActiveDirectoryPassword");
ds.setHostNameInCertificate("*.database.windows.net");

val connection = ds.getConnection();
val stmt = connection.createStatement();

val rs = stmt.executeQuery("SELECT SUSER_SNAME()");
if (rs.next()) {
System.out.println("You have successfully logged on as: " + rs.getString(1));
}

Detailed steps

Step 1 is to use an init script to delete the default MSSQL JDBC driver that ships with the Databricks runtime. I put the below into a shell script (call it deletemssqljdbc.sh):

rm /databricks/jars/*mssql*

deletemssqljdbc.sh is then copied to dbfs using Databricks CLI:

dbfs cp ./deletemssqljdbc.sh dbfs:/

Step 2 is to copy the appropriate JARs from a local machine to the Databricks workspace. I create a new folder under DBFS to copy these to:

dbfs mkdirs dbfs:/FileStore/adaljars

then copy:

dbfs cp dbfs:/FileStore/adaljars/

Step 3 is to install these as libraries and attach to cluster. Important: these versions are specific to the 6.4.0 release of the MSSQL JDBC driver. For higher versions, the versions of these dependency JARs will most likely be different. As mentioned previously, follow the steps at the JDBC driver Wiki if you intend to use a different JDBC driver version other than 6.4.0, in order to ensure that the dependencies are correct.

databricks libraries install --cluster-id --jar dbfs:/FileStore/adaljars/mssql-jdbc-6.4.0.jre8.jar
databricks libraries install --cluster-id --jar dbfs:/FileStore/adaljars/adal4j-1.4.0.jar
databricks libraries install --cluster-id --jar dbfs:/FileStore/adaljars/nimbus-jose-jwt-6.7.jar
databricks libraries install --cluster-id --jar dbfs:/FileStore/adaljars/oauth2-oidc-sdk-5.24.1.jar
databricks libraries install --cluster-id --jar dbfs:/FileStore/adaljars/json-smart-1.3.1.jar
databricks libraries install --cluster-id --jar dbfs:/FileStore/adaljars/gson-2.2.4.jar

Restart the cluster, and your AAD authentication code will now work.

Click here to download a working notebook.

Create a Databricks Cluster

Known working configuration - Databricks Runtime 5.2 (includes Apache Spark 2.4.0, Scala 2.11)

Install the Spark Connector for Microsoft Azure SQL Database and SQL Server

1. Navigate to Cluster > Library > Install New > Maven > Search Packages
2. Switch to Maven Central
3. Search for azure-sqldb-spark (com.microsoft.azure:azure-sqldb-spark)
4. Click Select
5. Click Install

Known working version - com.microsoft.azure:azure-sqldb-spark:1.0.2

Update Variables

Update variable values (custerName, server, database, table, username, password)

Run the Initialisation command (ONCE ONLY)

This will do the following:

1. Create a folder called init under dbfs:/databricks/init/
2. Creta a sub-folder with the name of the Databricks cluster
3. Create a bash script per dependency

Bash Script Commands:

• wget: Retrieve content from a web server
• --quit: Turns off wget's output
• -O: Output

Dependencies:

• http://central.maven.org/maven2/com/microsoft/azure/adal4j/1.6.0/adal4j-1.6.0.jar
• http://central.maven.org/maven2/com/nimbusds/oauth2-oidc-sdk/5.24.1/oauth2-oidc-sdk-5.24.1.jar
• http://central.maven.org/maven2/net/minidev/json-smart/1.1.1/json-smart-1.1.1.jar
• http://central.maven.org/maven2/com/nimbusds/nimbus-jose-jwt/7.0.1/nimbus-jose-jwt-7.0.1.jar

Restart the Databricks Cluster

This is needed to execute the init script.

Run the last cell in this Notebook

This will test the ability to connect to an Azure SQL Database via Active Directory authentication.

Init Command

// Initialisation
// This code block only needs to be run once to create the init script for the cluster (file remains on restart)

// Get the cluster name
var clusterName = dbutils.widgets.get("cluster")

// Create dbfs:/databricks/init/ if it doesn’t exist.
dbutils.fs.mkdirs("dbfs:/databricks/init/")

// Create a directory named (clusterName) using Databricks File System - DBFS.
dbutils.fs.mkdirs(s"dbfs:/databricks/init/$clusterName/")

// Create the adal4j script.
dbutils.fs.put(s"/databricks/init/$clusterName/adal4j-install.sh","""
#!/bin/bash
wget --quiet -O /mnt/driver-daemon/jars/adal4j-1.6.0.jar http://central.maven.org/maven2/com/microsoft/azure/adal4j/1.6.0/adal4j-1.6.0.jar
wget --quiet -O /mnt/jars/driver-daemon/adal4j-1.6.0.jar http://central.maven.org/maven2/com/microsoft/azure/adal4j/1.6.0/adal4j-1.6.0.jar""", true)

// Create the oauth2 script.
dbutils.fs.put(s"/databricks/init/$clusterName/oauth2-install.sh","""
#!/bin/bash
wget --quiet -O /mnt/driver-daemon/jars/oauth2-oidc-sdk-5.24.1.jar http://central.maven.org/maven2/com/nimbusds/oauth2-oidc-sdk/5.24.1/oauth2-oidc-sdk-5.24.1.jar
wget --quiet -O /mnt/jars/driver-daemon/oauth2-oidc-sdk-5.24.1.jar http://central.maven.org/maven2/com/nimbusds/oauth2-oidc-sdk/5.24.1/oauth2-oidc-sdk-5.24.1.jar""", true)

// Create the json script.
dbutils.fs.put(s"/databricks/init/$clusterName/json-smart-install.sh","""
#!/bin/bash
wget --quiet -O /mnt/driver-daemon/jars/json-smart-1.1.1.jar http://central.maven.org/maven2/net/minidev/json-smart/1.1.1/json-smart-1.1.1.jar
wget --quiet -O /mnt/jars/driver-daemon/json-smart-1.1.1.jar http://central.maven.org/maven2/net/minidev/json-smart/1.1.1/json-smart-1.1.1.jar""", true)

// Create the jwt script.
dbutils.fs.put(s"/databricks/init/$clusterName/jwt-install.sh","""
#!/bin/bash
wget --quiet -O /mnt/driver-daemon/jars/nimbus-jose-jwt-7.0.1.jar http://central.maven.org/maven2/com/nimbusds/nimbus-jose-jwt/7.0.1/nimbus-jose-jwt-7.0.1.jar
wget --quiet -O /mnt/jars/driver-daemon/nimbus-jose-jwt-7.0.1.jar http://central.maven.org/maven2/com/nimbusds/nimbus-jose-jwt/7.0.1/nimbus-jose-jwt-7.0.1.jar""", true)

// Check that the cluster-specific init script exists.
display(dbutils.fs.ls(s"dbfs:/databricks/init/$clusterName/"))

Test Command

// Connect to Azure SQL Database via Active Directory Password Authentication
import com.microsoft.azure.sqldb.spark.config.Config
import com.microsoft.azure.sqldb.spark.connect._

// Get Widget Values
var server = dbutils.widgets.get("server")
var database = dbutils.widgets.get("database")
var table = dbutils.widgets.get("table")
var username = dbutils.widgets.get("user")
var password = dbutils.widgets.get("password")

val config = Config(Map(
  "url"                    -> s"$server.database.windows.net",
  "databaseName"           -> s"$database",
  "dbTable"                -> s"$table",
  "user"                   -> s"$username",
  "password"               -> s"$password",
  "authentication"         -> "ActiveDirectoryPassword",
  "encrypt"                -> "true",
  "ServerCertificate"      -> "false",
  "hostNameInCertificate"  -> "*.database.windows.net"
))

val collection = sqlContext.read.sqlDB(config)
collection.show()

Connection reset ClientConnectionId:7e49a51f-fb35-48b9-88f3-ac6031f81e02

Getting this issue on the above code.

This guy pulled it off using a service principal. Just tried it and it works.

https://thedataguy.blog/connect-azure-databricks-to-sql-database-azure-sql-data-warehouse-using-a-service-principal/

How do we fix this? Also is AAD Password Auth supported for SQL on IaaS domain joined VM?

Below INIT script + Installing sqldb-spark maven library on the cluster worked on Databricks

%sh

cat <<EOT >/dbfs/databricks/init/scripts/deletemssqljdbcv3.sh
#!/bin/bash
rm /databricks/jars/*mssql*
sleep 10s
wget http://central.maven.org/maven2/com/microsoft/azure/adal4j/1.6.4/adal4j-1.6.4.jar -O /databricks/jars/adal4j-1.6.4.jar
wget http://central.maven.org/maven2/com/nimbusds/oauth2-oidc-sdk/6.5/oauth2-oidc-sdk-6.5.jar -O /databricks/jars/oauth2-oidc-sdk-6.5.jar
wget https://repo1.maven.org/maven2/com/google/code/gson/gson/2.8.0/gson-2.8.0.jar -O /databricks/jars/gson-2.8.0.jar
wget http://central.maven.org/maven2/net/minidev/json-smart/1.3.1/json-smart-1.3.1.jar -O /databricks/jars/json-smart-1.3.1.jar
wget http://central.maven.org/maven2/com/nimbusds/nimbus-jose-jwt/8.2.1/nimbus-jose-jwt-8.2.1.jar -O /databricks/jars/nimbus-jose-jwt-8.2.1.jar
wget http://central.maven.org/maven2/org/slf4j/slf4j-api/1.7.21/slf4j-api-1.7.21.jar -O /databricks/jars/slf4j-api-1.7.21.jar
wget https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/6.4.0.jre8/mssql-jdbc-6.4.0.jre8.jar -O /databricks/jars/mssql-jdbc-6.4.0.jre8.jar
EOT

Due to recent maven repo changes- https://central.sonatype.org/articles/2019/Apr/30/http-access-to-repo1mavenorg-and-repomavenapacheorg-is-being-deprecated/ , the init script would need to be changed to use "https://repo1.maven.org/" instead of "http://central.maven.org".

Script update for repo URLs
dbutils.fs.put("/databricks/init/scripts/sqlaadauth.sh",
"""#!/bin/bash
rm /databricks/jars/*mssql*
sleep 10s
wget https://repo1.maven.org/maven2/com/microsoft/azure/adal4j/1.6.4/adal4j-1.6.4.jar -O /databricks/jars/adal4j-1.6.4.jar
wget https://repo1.maven.org/maven2/com/nimbusds/oauth2-oidc-sdk/6.5/oauth2-oidc-sdk-6.5.jar -O /databricks/jars/oauth2-oidc-sdk-6.5.jar
wget https://repo1.maven.org/maven2/com/google/code/gson/gson/2.8.0/gson-2.8.0.jar -O /databricks/jars/gson-2.8.0.jar
wget https://repo1.maven.org/maven2/net/minidev/json-smart/1.3.1/json-smart-1.3.1.jar -O /databricks/jars/json-smart-1.3.1.jar
wget https://repo1.maven.org/maven2/com/nimbusds/nimbus-jose-jwt/8.2.1/nimbus-jose-jwt-8.2.1.jar -O /databricks/jars/nimbus-jose-jwt-8.2.1.jar
wget https://repo1.maven.org/maven2/org/slf4j/slf4j-api/1.7.21/slf4j-api-1.7.21.jar -O /databricks/jars/slf4j-api-1.7.21.jar
wget https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/6.4.0.jre8/mssql-jdbc-6.4.0.jre8.jar -O /databricks/jars/mssql-jdbc-6.4.0.jre8.jar""")

So I just was able to get all this working except that my org uses MFA and i get this error...
Caused by: java.util.concurrent.ExecutionException: com.microsoft.aad.adal4j.AuthenticationException: {"error_description":"AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access.

I can use Service Principles for automation, but what about ad-hoc user queries?

I'm having the same issue, with a Python notebook. Is there an easy workaround that doesn't involve installing by hand lots of jars?

Actually using the same INIT script works with Python using JDBC. Unfortunately MFA isn't supported yet. Please provide feedback here

jdbcHostname = "aravishexternalserver.database.windows.net"
jdbcPort = 1433
jdbcDatabase  = "aravishexternaldb"
jdbcUrl = "jdbc:sqlserver://{0}:{1};database={2};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30; Authentication=ActiveDirectoryPassword".format(jdbcHostname,jdbcPort,jdbcDatabase)
connectionProperties = {"user" : "aravishdm@arvindravishgmail.onmicrosoft.com", "password" : "PWD", "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"}

pushdown_query = "(select * from SalesLT.ProductCategory) sales_alias"
df = spark.read.jdbc(url=jdbcUrl, table=pushdown_query, properties=connectionProperties)
df.show(10, False)

Actually using the same INIT script works with Python using JDBC. Unfortunately MFA isn't supported yet. Please provide feedback here

jdbcHostname = "aravishexternalserver.database.windows.net"
jdbcPort = 1433
jdbcDatabase  = "aravishexternaldb"
jdbcUrl = "jdbc:sqlserver://{0}:{1};database={2};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30; Authentication=ActiveDirectoryPassword".format(jdbcHostname,jdbcPort,jdbcDatabase)
connectionProperties = {"user" : "aravishdm@arvindravishgmail.onmicrosoft.com", "password" : "PWD", "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"}

pushdown_query = "(select * from SalesLT.ProductCategory) sales_alias"
df = spark.read.jdbc(url=jdbcUrl, table=pushdown_query, properties=connectionProperties)
df.show(10, False)

We were able to get this working in our development environment. Unfortunately, deploying the same code in a UAT environment, it is failing with the error below. We triple checked the credentials and confirmed all access levels are working as expected. Cannot figure out why we are getting this error. Any ideas?

An error occurred while calling o1606.save.
: com.databricks.spark.sqldw.SqlDWSideException: SQL DW failed to execute the JDBC query produced by the connector.
Underlying SQLException(s):

• com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user [REDACTED] in Active Directory (Authentication=ActiveDirectoryPassword). [ErrorCode = 0] [SQLState = null]

  at com.databricks.spark.sqldw.Utils$.wrapExceptions(Utils.scala:313)
  at com.databricks.spark.sqldw.DefaultSource.createRelation(DefaultSource.scala:86)
  at org.apache.spark.sql.execution.datasources.SaveIntoDataSourceCommand.run(SaveIntoDataSourceCommand.scala:45)
  at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult$lzycompute(commands.scala:72)
  at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult(commands.scala:70)
  at org.apache.spark.sql.execution.command.ExecutedCommandExec.doExecute(commands.scala:88)
  at org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:146)
  at org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:134)
  at org.apache.spark.sql.execution.SparkPlan$$anonfun$executeQuery$5.apply(SparkPlan.scala:187)
  at org.apache.spark.rdd.RDDOperationScope$.withScope(RDDOperationScope.scala:151)
  at org.apache.spark.sql.execution.SparkPlan.executeQuery(SparkPlan.scala:183)
  at org.apache.spark.sql.execution.SparkPlan.execute(SparkPlan.scala:134)
  at org.apache.spark.sql.execution.QueryExecution.toRdd$lzycompute(QueryExecution.scala:116)
  at org.apache.spark.sql.execution.QueryExecution.toRdd(QueryExecution.scala:116)
  at org.apache.spark.sql.DataFrameWriter$$anonfun$runCommand$1.apply(DataFrameWriter.scala:710)
  at org.apache.spark.sql.DataFrameWriter$$anonfun$runCommand$1.apply(DataFrameWriter.scala:710)
  at org.apache.spark.sql.execution.SQLExecution$$anonfun$withCustomExecutionEnv$1.apply(SQLExecution.scala:111)
  at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:240)
  at org.apache.spark.sql.execution.SQLExecution$.withCustomExecutionEnv(SQLExecution.scala:97)
  at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:170)
  at org.apache.spark.sql.DataFrameWriter.runCommand(DataFrameWriter.scala:710)
  at org.apache.spark.sql.DataFrameWriter.saveToV1Source(DataFrameWriter.scala:306)
  at org.apache.spark.sql.DataFrameWriter.save(DataFrameWriter.scala:292)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:498)
  at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
  at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:380)
  at py4j.Gateway.invoke(Gateway.java:295)
  at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
  at py4j.commands.CallCommand.execute(CallCommand.java:79)
  at py4j.GatewayConnection.run(GatewayConnection.java:251)
  at java.lang.Thread.run(Thread.java:748)
  Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user [REDACTED] in Active Directory (Authentication=ActiveDirectoryPassword).
  at com.microsoft.sqlserver.jdbc.SQLServerADAL4JUtils.getSqlFedAuthToken(SQLServerADAL4JUtils.java:57)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.getFedAuthToken(SQLServerConnection.java:3856)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.onFedAuthInfo(SQLServerConnection.java:3832)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.processFedAuthInfo(SQLServerConnection.java:3800)
  at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onFedAuthInfo(tdsparser.java:261)
  at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:103)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4548)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3409)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:85)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3373)
  at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7344)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2713)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2261)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1921)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1762)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1077)
  at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:623)
  at com.databricks.spark.sqldw.JDBCWrapper.getConnector(SqlDWJDBCWrapper.scala:256)
  at com.databricks.spark.sqldw.JDBCWrapper.withConnection(SqlDWJDBCWrapper.scala:281)
  at com.databricks.spark.sqldw.DefaultSource.validateJdbcConnection(DefaultSource.scala:138)
  at com.databricks.spark.sqldw.DefaultSource$$anonfun$createRelation$3.apply(DefaultSource.scala:88)
  at com.databricks.spark.sqldw.DefaultSource$$anonfun$createRelation$3.apply(DefaultSource.scala:86)
  at com.databricks.spark.sqldw.Utils$.wrapExceptions(Utils.scala:283)
  ... 33 more
  Caused by: java.util.concurrent.ExecutionException: com.microsoft.aad.adal4j.AuthenticationException: Server returned error in RSTR - ErrorCode: FailedAuthentication : FaultMessage: MSIS7068: Access denied.
  at com.microsoft.sqlserver.jdbc.SQLServerADAL4JUtils.getSqlFedAuthToken(SQLServerADAL4JUtils.java:55)
  ... 55 more
  Caused by: com.microsoft.aad.adal4j.AuthenticationException: Server returned error in RSTR - ErrorCode: FailedAuthentication : FaultMessage: MSIS7068: Access denied.
  at com.microsoft.sqlserver.jdbc.SQLServerADAL4JUtils.getSqlFedAuthToken(SQLServerADAL4JUtils.java:50)
  ... 55 more

Script update for repo URLs
dbutils.fs.put("/databricks/init/scripts/sqlaadauth.sh",
"""#!/bin/bash
rm /databricks/jars/mssql
sleep 10s
wget https://repo1.maven.org/maven2/com/microsoft/azure/adal4j/1.6.4/adal4j-1.6.4.jar -O /databricks/jars/adal4j-1.6.4.jar
wget https://repo1.maven.org/maven2/com/nimbusds/oauth2-oidc-sdk/6.5/oauth2-oidc-sdk-6.5.jar -O /databricks/jars/oauth2-oidc-sdk-6.5.jar
wget https://repo1.maven.org/maven2/com/google/code/gson/gson/2.8.0/gson-2.8.0.jar -O /databricks/jars/gson-2.8.0.jar
wget https://repo1.maven.org/maven2/net/minidev/json-smart/1.3.1/json-smart-1.3.1.jar -O /databricks/jars/json-smart-1.3.1.jar
wget https://repo1.maven.org/maven2/com/nimbusds/nimbus-jose-jwt/8.2.1/nimbus-jose-jwt-8.2.1.jar -O /databricks/jars/nimbus-jose-jwt-8.2.1.jar
wget https://repo1.maven.org/maven2/org/slf4j/slf4j-api/1.7.21/slf4j-api-1.7.21.jar -O /databricks/jars/slf4j-api-1.7.21.jar
wget https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/6.4.0.jre8/mssql-jdbc-6.4.0.jre8.jar -O /databricks/jars/mssql-jdbc-6.4.0.jre8.jar""")

@arvindshmicrosoft
I tried the same in Azure databricks with scala. I installed the library "com.microsoft.azure:azure-sqldb-spark:1.0.2" in the cluster. Then I ran the script you provided and restarted the cluster. Then when I tried the below code, I keep getting "
java.lang.NoClassDefFoundError: com/microsoft/aad/adal4j/AuthenticationException"

Not sure what am I missing! Thanks in advance

my databricks run time version is 6.5 (includes Apache Spark 2.4.5, Scala 2.11)

%scala
import com.microsoft.azure.sqldb.spark.config.Config
import com.microsoft.azure.sqldb.spark.connect._

val config = Config(Map(
"url" -> ".database.windows.net",
"databaseName" -> "",
"dbTable" -> "

val collection = spark.read.sqlDB(config)
collection.show()

dbutils.fs.put("/databricks/init/scripts/sqlaadauth.sh", """#!/bin/bash rm /databricks/jars/mssql sleep 10s wget https://repo1.maven.org/maven2/com/microsoft/azure/adal4j/1.6.4/adal4j-1.6.4.jar -O /databricks/jars/adal4j-1.6.4.jar wget https://repo1.maven.org/maven2/com/nimbusds/oauth2-oidc-sdk/6.5/oauth2-oidc-sdk-6.5.jar -O /databricks/jars/oauth2-oidc-sdk-6.5.jar wget https://repo1.maven.org/maven2/com/google/code/gson/gson/2.8.0/gson-2.8.0.jar -O /databricks/jars/gson-2.8.0.jar wget https://repo1.maven.org/maven2/net/minidev/json-smart/1.3.1/json-smart-1.3.1.jar -O /databricks/jars/json-smart-1.3.1.jar wget https://repo1.maven.org/maven2/com/nimbusds/nimbus-jose-jwt/8.2.1/nimbus-jose-jwt-8.2.1.jar -O /databricks/jars/nimbus-jose-jwt-8.2.1.jar wget https://repo1.maven.org/maven2/org/slf4j/slf4j-api/1.7.21/slf4j-api-1.7.21.jar -O /databricks/jars/slf4j-api-1.7.21.jar wget https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/6.4.0.jre8/mssql-jdbc-6.4.0.jre8.jar -O /databricks/jars/mssql-jdbc-6.4.0.jre8.jar""")

I missed to point this script in the init script of the cluster. Good catch!

I think this works, however I am having another error now which should be related to my permission
and not related to these jars anymore!! If I have appropriate permissions, I should be successful. Thanks!

By any chance, @aravish do you have an idea on the below exception which I get..

com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user emailid@domain.com in Active Directory (Authentication=ActiveDirectoryPassword).

Caused by: java.util.concurrent.ExecutionException: com.microsoft.aad.adal4j.AuthenticationException: Server returned error in RSTR - ErrorCode: FailedAuthentication : FaultMessage: MSIS7068: Access denied.

This is the same error which I get when I try to log in using SSMS (ActiveDirectoryPassword), despite my email id being the active directory administrator for the data warehouse

I suspect me being outside my company VPN is the issue which is not able to authenticate via ActiveDirectory Password method..

By any chance, @aravish do you have an idea on the below exception which I get..

com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user emailid@domain.com in Active Directory (Authentication=ActiveDirectoryPassword).

Caused by: java.util.concurrent.ExecutionException: com.microsoft.aad.adal4j.AuthenticationException: Server returned error in RSTR - ErrorCode: FailedAuthentication : FaultMessage: MSIS7068: Access denied.

I am having the exact same issue. Any solution to this would be greatly appreciated.

Hello, Do you know if MFA is enabled in the AD Tenant? Could you please check with your Azure admin? Arvind Ravish From: Kamalesh54 notifications@github.com Sent: Monday, June 8, 2020 3:02 PM To: Azure/azure-sqldb-spark azure-sqldb-spark@noreply.github.com Cc: Arvind Ravish Arvind.Ravish@microsoft.com; Mention mention@noreply.github.com Subject: Re: [Azure/azure-sqldb-spark] AAD authentication with password throws java.lang.NoClassDefFoundError: com/microsoft/aad/adal4j/AuthenticationException (#28) dbutils.fs.put("/databricks/init/scripts/sqlaadauth.sh", """#!/bin/bash rm /databricks/jars/mssql sleep 10s wget https://repo1.maven.org/maven2/com/microsoft/azure/adal4j/1.6.4/adal4j-1.6.4.jarhttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Frepo1.maven.org%2Fmaven2%2Fcom%2Fmicrosoft%2Fazure%2Fadal4j%2F1.6.4%2Fadal4j-1.6.4.jar&data=02%7C01%7CArvind.Ravish%40microsoft.com%7C32481b3e0dad4cea07c508d80be6d616%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637272433399291553&sdata=5BIOiBKia6FV2SeZJVMf8LQHXTk2UuC5c9kbudvjLFU%3D&reserved=0 -O /databricks/jars/adal4j-1.6.4.jar wget https://repo1.maven.org/maven2/com/nimbusds/oauth2-oidc-sdk/6.5/oauth2-oidc-sdk-6.5.jarhttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Frepo1.maven.org%2Fmaven2%2Fcom%2Fnimbusds%2Foauth2-oidc-sdk%2F6.5%2Foauth2-oidc-sdk-6.5.jar&data=02%7C01%7CArvind.Ravish%40microsoft.com%7C32481b3e0dad4cea07c508d80be6d616%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637272433399301506&sdata=AzE84pu%2BAph6s6jUWEIMeWicnYx%2B%2FUN5MSrzY%2FEceq8%3D&reserved=0 -O /databricks/jars/oauth2-oidc-sdk-6.5.jar wget https://repo1.maven.org/maven2/com/google/code/gson/gson/2.8.0/gson-2.8.0.jarhttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Frepo1.maven.org%2Fmaven2%2Fcom%2Fgoogle%2Fcode%2Fgson%2Fgson%2F2.8.0%2Fgson-2.8.0.jar&data=02%7C01%7CArvind.Ravish%40microsoft.com%7C32481b3e0dad4cea07c508d80be6d616%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637272433399301506&sdata=vmcZxovNX0EPJRgEIjDfojV3S47eLP8kRyeevc1C1H4%3D&reserved=0 -O /databricks/jars/gson-2.8.0.jar wget https://repo1.maven.org/maven2/net/minidev/json-smart/1.3.1/json-smart-1.3.1.jarhttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Frepo1.maven.org%2Fmaven2%2Fnet%2Fminidev%2Fjson-smart%2F1.3.1%2Fjson-smart-1.3.1.jar&data=02%7C01%7CArvind.Ravish%40microsoft.com%7C32481b3e0dad4cea07c508d80be6d616%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637272433399301506&sdata=uQuvxJ0fHLR%2Fj83JQ6CI6tceSOxO97su0a3Avh8j6sU%3D&reserved=0 -O /databricks/jars/json-smart-1.3.1.jar wget https://repo1.maven.org/maven2/com/nimbusds/nimbus-jose-jwt/8.2.1/nimbus-jose-jwt-8.2.1.jarhttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Frepo1.maven.org%2Fmaven2%2Fcom%2Fnimbusds%2Fnimbus-jose-jwt%2F8.2.1%2Fnimbus-jose-jwt-8.2.1.jar&data=02%7C01%7CArvind.Ravish%40microsoft.com%7C32481b3e0dad4cea07c508d80be6d616%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637272433399311463&sdata=ZnWXXFeLGfRh8GGYSpHK4ZZ%2FZkaG22oKOJcysxS7Q%2Fs%3D&reserved=0 -O /databricks/jars/nimbus-jose-jwt-8.2.1.jar wget https://repo1.maven.org/maven2/org/slf4j/slf4j-api/1.7.21/slf4j-api-1.7.21.jarhttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Frepo1.maven.org%2Fmaven2%2Forg%2Fslf4j%2Fslf4j-api%2F1.7.21%2Fslf4j-api-1.7.21.jar&data=02%7C01%7CArvind.Ravish%40microsoft.com%7C32481b3e0dad4cea07c508d80be6d616%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637272433399311463&sdata=GFg%2F1HOec3ifiJY%2FkKtjje1iYIrWicLWliZbpIRAgZ0%3D&reserved=0 -O /databricks/jars/slf4j-api-1.7.21.jar wget https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/6.4.0.jre8/mssql-jdbc-6.4.0.jre8.jarhttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Frepo1.maven.org%2Fmaven2%2Fcom%2Fmicrosoft%2Fsqlserver%2Fmssql-jdbc%2F6.4.0.jre8%2Fmssql-jdbc-6.4.0.jre8.jar&data=02%7C01%7CArvind.Ravish%40microsoft.com%7C32481b3e0dad4cea07c508d80be6d616%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637272433399321418&sdata=4benTtccKXM6UDfFXQp1q8GqkYnqwWkjXn9wP4Bs4bQ%3D&reserved=0 -O /databricks/jars/mssql-jdbc-6.4.0.jre8.jar""") I missed to point this script in the init script of the cluster. Good catch! I think this works, however I am having another error now which should be related to my permission and not related to these jars anymore!! If I have appropriate permissions, I should be successful. Thanks! By any chance, @aravishhttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Faravish&data=02%7C01%7CArvind.Ravish%40microsoft.com%7C32481b3e0dad4cea07c508d80be6d616%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637272433399321418&sdata=yiuCSiI02LU%2BBl8pIBU7JLJGKsX5C48e5MnrOvw3%2FrQ%3D&reserved=0 do you have an idea on the below exception which I get.. com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user emailid@domain.commailto:emailid@domain.com in Active Directory (Authentication=ActiveDirectoryPassword). Caused by: java.util.concurrent.ExecutionException: com.microsoft.aad.adal4j.AuthenticationException: Server returned error in RSTR - ErrorCode: FailedAuthentication : FaultMessage: MSIS7068: Access denied. This is the same error which I get when I try to log in using SSMS (ActiveDirectoryPassword), despite my email id being the active directory administrator for the data warehouse — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FAzure%2Fazure-sqldb-spark%2Fissues%2F28%23issuecomment-640855998&data=02%7C01%7CArvind.Ravish%40microsoft.com%7C32481b3e0dad4cea07c508d80be6d616%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637272433399331378&sdata=tTIiPwfunoT3ik7eN9KWdEvlqsElZsmY9hi1IFobEXM%3D&reserved=0, or unsubscribehttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAKPNRK5Q2VCZWLV32M54BT3RVU7ULANCNFSM4GQIOFMQ&data=02%7C01%7CArvind.Ravish%40microsoft.com%7C32481b3e0dad4cea07c508d80be6d616%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637272433399331378&sdata=EeRxSlsrhLfO1Swxez0LqBzxg5WrOCue3An7E0gKgcw%3D&reserved=0.

Hey @aravish , indeed i think MFA is enabled because when I login to the azure sql dw using "Active directory universal with mfa support", I am successful. It is only when I use just the "Active directory Password" i get this error.

But sure, I will confirm with the azure admin. Do you see a reason due to it? Thanks

Hello, Do you know if MFA is enabled in the AD Tenant? Could you please check with your Azure admin?

Hey @aravish - no, MFA is not enabled. We are able to get this working in the US East region under the same tenant/active directory. When we are trying on the UNC region, it is failing.

@ramziharb,
What do you mean by UNC region?

@aravish - I meant US North Central region.

Kamalesh, Unfortunately MFA isn’t supported yet as mentioned on the github bug previously. Please provide feedback below https://feedback.azure.com/

@aravish Thanks, but shouldn't I still be able to login using active directory password instead of mfa? Is it either this or that?

Because in SSMS if I am inside the company vpn I can login using just password. Only when I am outside company network I get the same error like what I get using databricks.

For anyone happening upon this thread, let me save you some trouble. The solution above of using the cluster init scripts to remove the jars is a correct approach. But then do yourself a favor, load the latest mssql driver and adal4j packages and specify the driver class in the connection properties.

Works as expected :)

@Kamalesh54 is your Azure SQL connected to a vnet and you're getting prompts you don't expect? If so, even if it's connected to a vnet whose egress is exempted in your conditional access policy(ies) or is white listed at the MFA service: the SQL instance' requests will never appear to be coming from the egress IP(s) you expect. Connections then fail because the user either needs to enroll in MFA or needs to use MFA.

If you exempt any accounts you need to connect without MFA from your MFA policy(ies), it will work as expected.

fro

Hey @thereverand Thanks for your response. My issue as you stated is because I have the MFA enabled in the account and couldn't exempt it due to organizational policies. So once I can get MFA exempted, this approach should work.

We finally solved the issue we were having which turned out to be a firewall issue. Now, we are facing this issue on a sporadic basis. It fails on some tries and works on others. Rerunning the same notebooks sometimes works and sometimes not. Checking to see if anyone else is facing similar issues?

I am not running on DataBricks but experienced the same issue:
java.lang.NoClassDefFoundError: com/microsoft/aad/adal4j/AuthenticationException

In my case it was due to hadoop including a very old version of the mssql driver. To fix it:

rm $HADOOP_HOME/share/hadoop/yarn/lib/mssql-jdbc-6.2.1.jre7.jar

This is probably the same jar DataBricks includes by default which is why rm /databricks/jars/mssql fixes it.

This issue has collected enough anecdotal evidence and guidance over time. Since it is not specific to the connector, but instead dependent on the associated compute runtime environment, I am closing this issue as there is no action expected or planned around this.