Intermittent Authentication Failure using ActiveDirectoryPassword

[melshami]

I have a situation in which I receive intermittent authentication failure when using ActiveDirectoryPassword, To troubleshoot I setup a loop to connect to our Synapse Sql Database using python. The random error we receive is:

This is the spark read configuration:
df = spark.read
.format("com.microsoft.sqlserver.jdbc.spark")
.option("url", connDict['url'])
.option(typeString, typeArg)
.option("authentication", connDict['authentication'])
.option("user", connDict['username'])
.option("password", connDict['password'])
.load()

JARs being used
content-type-2.2.jar |  
oauth2-oidc-sdk-11.9.1.jar |  
adal4j-1.6.7.jar |  
spark-mssql-connector_2.12-1.3.0-BETA.jar

**Then I loop through 20 times. **

from datetime import datetime
makeAttemptsJDBC = 20
delayBetweenJDBCAttempts = 2

errors = {}
successes = 0
for i in range(0, makeAttemptsJDBC):
if i > 0:
time.sleep(delayBetweenJDBCAttempts)
try:
df = readJDBCFromDb(config, 'query', 'SELECT TOP 1 * FROM sys.tables')
successes += 1
now = datetime.now()
print('Read ' + str(i) + ' was successful' + ' Time - ' + str(now))
except Exception as e:
now = datetime.now()
print('Read ' + str(i) + ' failed' + ' Time - ' + str(now))
strE = str(e)[:300]
if strE not in errors:
errors[strE] = 1
else:
errors[strE] += 1

print('Out of ' + str(makeAttemptsJDBC) + ', ' + str(successes) + ' were successful.')
if len(errors) == 0:
print('No failures')
else:
for key,val in errors.items():
print(val, 'failed with error:', key)

RESULTS:
Read 0 was successful Time - 2024-01-23 01:54:12.250192
Read 1 was successful Time - 2024-01-23 01:54:18.196843
Read 2 was successful Time - 2024-01-23 01:54:24.128849
Read 3 was successful Time - 2024-01-23 01:54:30.311090
Read 4 was successful Time - 2024-01-23 01:54:36.197872
Read 5 was successful Time - 2024-01-23 01:54:42.051919
Read 6 failed Time - 2024-01-23 01:54:51.428730
Read 7 was successful Time - 2024-01-23 01:54:57.679738
Read 8 was successful Time - 2024-01-23 01:55:03.704308
Read 9 was successful Time - 2024-01-23 01:55:09.719151
Read 10 was successful Time - 2024-01-23 01:55:15.801442
Read 11 was successful Time - 2024-01-23 01:55:22.040815
Read 12 was successful Time - 2024-01-23 01:55:27.857129
Read 13 was successful Time - 2024-01-23 01:55:33.743849
Read 14 was successful Time - 2024-01-23 01:55:39.879636
Read 15 was successful Time - 2024-01-23 01:55:46.353185
Read 16 failed Time - 2024-01-23 01:55:55.789135
Read 17 was successful Time - 2024-01-23 01:56:02.831716
Read 18 was successful Time - 2024-01-23 01:56:09.351897
Read 19 was successful Time - 2024-01-23 01:56:15.728788

Out of 20, 18 were successful.
: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user [email protected] in Active Directory (Authentication=ActiveDirectoryPassword).
at com.microsoft.sqlserver.jdbc.SQLServerADAL4JUtils.getSqlFedAuthToken(SQLServerADAL4JUtils.java:62)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.getFedAuthToken(SQLServerConnection.java:4442)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.onFedAuthInfo(SQLServerConnection.java:4415)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.processFedAuthInfo(SQLServerConnection.java:4380)
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:5233)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3988)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:85)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3932)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7375)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3206)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2713)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2362)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2213)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1276)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:861)
at org.apache.spark.sql.execution.datasources.jdbc.connection.BasicConnectionProvider.getConnection(BasicConnectionProvider.scala:49)
at org.apache.spark.sql.execution.datasources.jdbc.connection.ConnectionProvider$.create(ConnectionProvider.scala:77)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$.$anonfun$createConnectionFactory$1(JdbcUtils.scala:64)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.getQueryOutputSchema(JDBCRDD.scala:62)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:57)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.getSchema(JDBCRelation.scala:239)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:36)
at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:350)
at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:281)
at org.apache.spark.sql.DataFrameReader.$anonfun$load$3(DataFrameReader.scala:253)
at scala.Option.getOrElse(Option.scala:189)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:253)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:176)
at sun.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
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:357)
at py4j.Gateway.invoke(Gateway.java:282)
at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
at py4j.commands.CallCommand.execute(CallCommand.java:79)
at py4j.GatewayConnection.run(GatewayConnection.java:238)
at java.lang.Thread.run(Thread.java:750)
Caused by: java.util.concurrent.ExecutionException: com.microsoft.aad.adal4j.AuthenticationException: Connection reset
at com.microsoft.sqlserver.jdbc.SQLServerADAL4JUtils.getSqlFedAuthToken(SQLServerADAL4JUtils.java:60)
... 40 more
Caused by: com.microsoft.aad.adal4j.AuthenticationException: Connection reset
at com.microsoft.sqlserver.jdbc.SQLServerADAL4JUtils.getSqlFedAuthToken(SQLServerADAL4JUtils.java:53)
... 40 more