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 user@domain.com 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
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 user@domain.com 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