AAD authentication error MFA

[vishalwadhia]

Hello Team

I am currently trying to connect my Databricks workspace to SQL server using the connector. I am trying to use the AAD user name and password method

So far I keep getting this error -
com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user @.com - in Active Directory (Authentication=ActiveDirectoryPassword).

the @.com is my microsoft username

So currently trying to recreate this for a support ticket I am working on. The JDBC url was taken from the SQL database connection string. (ADO.NET (Active Directory password authentication)

I have been using the code snippet provided on github

I have also made myself an active directory admin within the SQL server setting.

this is the full error i am getting

Py4JJavaError: An error occurred while calling o485.load.
: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user "I have taken out my username " in Active Directory (Authentication=ActiveDirectoryPassword).
at com.microsoft.sqlserver.jdbc.SQLServerADAL4JUtils.getSqlFedAuthToken(SQLServerADAL4JUtils.java:62)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.getFedAuthToken(SQLServerConnection.java:4264)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.onFedAuthInfo(SQLServerConnection.java:4237)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.processFedAuthInfo(SQLServerConnection.java:4202)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onFedAuthInfo(tdsparser.java:289)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:125)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:37)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:5173)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3810)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:94)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3754)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7225)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3053)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2562)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2216)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2067)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1204)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:825)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$.$anonfun$createConnectionFactory$1(JdbcUtils.scala:64)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:56)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.getSchema(JDBCRelation.scala:226)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:35)
at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:370)
at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:384)
at org.apache.spark.sql.DataFrameReader.$anonfun$load$2(DataFrameReader.scala:373)
at scala.Option.getOrElse(Option.scala:189)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:373)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:258)
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: java.util.concurrent.ExecutionException: mssql_shaded.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 '022907d3-0f1b-48f7-badc-1ba6abab6d66'.
Trace ID: 1123399b-6832-49f7-8a60-3a38675f0801
Correlation ID: 05cb7dde-133e-427b-b118-194f90860d55
Timestamp: 2021-08-18 19:43:14Z","error":"interaction_required","error_uri":"https://login.windows.net/error?code=50076"}
at com.microsoft.sqlserver.jdbc.SQLServerADAL4JUtils.getSqlFedAuthToken(SQLServerADAL4JUtils.java:60)
... 38 more
Caused by: mssql_shaded.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 '022907d3-0f1b-48f7-badc-1ba6abab6d66'.
Trace ID: 1123399b-6832-49f7-8a60-3a38675f0801
Correlation ID: 05cb7dde-133e-427b-b118-194f90860d55
Timestamp: 2021-08-18 19:43:14Z","error":"interaction_required","error_uri":"https://login.windows.net/error?code=50076"}
at com.microsoft.sqlserver.jdbc.SQLServerADAL4JUtils.getSqlFedAuthToken(SQLServerADAL4JUtils.java:53)
... 38 more

Any help on this would be great

Thank you

[vishalwadhia]

gone through the thread in #26 but still no avail, also started it from scratch but didn't work.

[abij]

If you look at the bottom of the exception:

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 '022907d3-0f1b-48f7-badc-1ba6abab6d66'

So you are required to have an MFA-challenge, but driver does not support this. If you connect using SQL Server Management Studio, using authentication: Azure Active Directory - Universal with MFA, there will be a browser pop-up to login + MFA.

I'm also struggling with this issue at the moment...

[vishalwadhia]

If you look at the bottom of the exception:

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 '022907d3-0f1b-48f7-badc-1ba6abab6d66'

So you are required to have an MFA-challenge, but driver does not support this. If you connect using SQL Server Management Studio, using authentication: Azure Active Directory - Universal with MFA, there will be a browser pop-up to login + MFA.

I'm also struggling with this issue at the moment...

Hi @abij (Alex)

I have managed to sort this out, you either can disable MFA or the workarounds below

I am adding it to this tread in case future users have this error. 2 ways around use the 1) Service Principle or 2)change policy

1. Use a Service Principal instead of a user to perform the sign-in as instructed in the Spark Connector documentation, since Service Principals are not subject to CA policies enforcement while using the Password authentication flow.
   a. How to use Service Principal for authentication: https://docs.microsoft.com/en-us/sql/connect/spark/connector?view=sql-server-ver15#python-example-with-service-principal
   b. How to create a service principal with a secret and assign it access to resources: https://docs.microsoft.com/en-us/azure/active-directory/develop/howto-create-service-principal-portal#register-an-application-with-azure-ad-and-create-a-service-principal
2. Change the CA policy in a way to allow the authentication to work. The way you change the CA policy is up to you or your IT security team. Possible solutions that can be applied here are:
   a. Add the IP address where the Apache Spark connector is running to the trusted locations list of the CA policy: https://docs.microsoft.com/en-us/azure/active-directory/conditional-access/location-condition
   b. Exclude your user from the CA policy: https://docs.microsoft.com/en-us/azure/active-directory/conditional-access/concept-conditional-access-users-groups#exclude-users
   c. Change the grant type to something else beside MFA: https://docs.microsoft.com/en-us/azure/active-directory/conditional-access/concept-conditional-access-grant
   For more information about CA policies and how to configure them, please follow this link: https://docs.microsoft.com/en-us/azure/active-directory/conditional-access/concept-conditional-access-policies

[abij]

This works for me to at least connect, it's not a durable solution (yet) since access-tokens expire after 1H by default.

1. Use the Azure CLI to Authenticate with MFA, for the account you want to use for the database-connection.
2. Generate an access_token: az account get-access-token https://database.windows.net --query accessToken -o tsv remove query /output to get some insights about this token / lifetime etc..
3. Connect like this:

jdbc_df = (spark.read.format("com.microsoft.sqlserver.jdbc.spark")
              .option("url", url)
              .option("dbtable", db_table)
              .option("accessToken", access_token)
              .option("encrypt", encrypt)
              .option("databaseName", database_name)
              .option("hostNameInCertificate", host_name_in_certificate)
              .load())

Like the samples/Databricks-AzureSQL/DatabricksNotebooks/SQL Spark Connector - Python AAD Auth.py

[vishalwadhia]

Hi @abij

Have you tried to use the refresh token instead of the normal access token?

[abij]

refreshToken does not work.

The refreshToken (valid for many days) can be used to get a new accessToken (1H valid and refresh token) without the MFA requirement.

There is a nice mechanism using MSAL (python) to renew AccessToken with local file cache, silent refresh.
https://msal-python.readthedocs.io/