Failed to authenticate the user (Active Directory Password) on remote (docker run on Azure DevOps)

Question

i am unable to authenticate the user within connect to azure sql db on remote (docker run on Azure DevOps). It is running without an issue locally from Katalon on my computer or via MSSMS with the same user.

This is the keyword i am using:

@Keyword
def connect() {

    SQLServerDataSource ds = new SQLServerDataSource();
    ds.setServerName('****-mssql.database.windows.net');
    ds.setPortNumber(1433)
    ds.setDatabaseName('*****');
    ds.setUser('TEC*****');
    ds.setPassword('******');
    ds.setEncrypt(true);
    ds.setTrustServerCertificate(false);
    ds.setHostNameInCertificate('*.database.windows.net');
    ds.setLoginTimeout(30)
    ds.setAuthentication('ActiveDirectoryPassword');

    try {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver")
    } catch (ClassNotFoundException e) {
        e.printStackTrace()
        return
    }
    try {
        connection = ds.getConnection()
    } catch (Exception e) {
        e.printStackTrace()
        return
    }
    if (connection != null) {
    } else {
        println("Failed to make connection!")
    }
    return connection
}

Docker run is standard katalon docker image call within yml on Azure DevOps pipeline. Before i was able to authenticate and perform tests on this sql db with sql server authentication method. Unfortunately we had to get rid of this and have only AAD authentication possible.

This is the error i am receiving:

2022-10-19T14:06:19.7727391Z com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user TEC******  in Active Directory (Authentication=ActiveDirectoryPassword).
2022-10-19T14:06:19.7729164Z    at com.microsoft.sqlserver.jdbc.SQLServerADAL4JUtils.getSqlFedAuthToken(SQLServerADAL4JUtils.java:49)
2022-10-19T14:06:19.7731772Z    at com.microsoft.sqlserver.jdbc.SQLServerConnection.getFedAuthToken(SQLServerConnection.java:3609)
2022-10-19T14:06:19.7733140Z    at com.microsoft.sqlserver.jdbc.SQLServerConnection.onFedAuthInfo(SQLServerConnection.java:3580)
2022-10-19T14:06:19.7734324Z    at com.microsoft.sqlserver.jdbc.SQLServerConnection.processFedAuthInfo(SQLServerConnection.java:3548)
2022-10-19T14:06:19.7735580Z    at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onFedAuthInfo(tdsparser.java:261)
2022-10-19T14:06:19.7736640Z    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:103)
2022-10-19T14:06:19.7737691Z    at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4290)
2022-10-19T14:06:19.7738824Z    at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3157)
2022-10-19T14:06:19.7739940Z    at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:82)
2022-10-19T14:06:19.7741103Z    at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3121)
2022-10-19T14:06:19.7742344Z    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
2022-10-19T14:06:19.7744999Z    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
2022-10-19T14:06:19.7747724Z    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2026)
2022-10-19T14:06:19.7750596Z    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1687)
2022-10-19T14:06:19.7752937Z    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1528)
2022-10-19T14:06:19.7754834Z    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:866)
2022-10-19T14:06:19.7755846Z    at com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnectionInternal(SQLServerDataSource.java:968)
2022-10-19T14:06:19.7756726Z    at com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnection(SQLServerDataSource.java:69)
2022-10-19T14:06:19.7757305Z    at javax.sql.DataSource$getConnection.call(Unknown Source)
2022-10-19T14:06:19.7757863Z    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:47)
2022-10-19T14:06:19.7758486Z    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116)
2022-10-19T14:06:19.7759217Z    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:120)

score 0 · Answer 1 · answered Nov 04 '22 at 17:38

If the user is otherwise authenticating normally, this could be due to a known issue with older version of the ODBC Driver for SQL Server. If this is the case, updating the driver to the latest version should resolve the issue.

Another possibility is that the connection properties are not correct and the JDBC URL is not being used. If this is the case, you need to update the connection properties:

User: myuser
Password: AD password
Host: jdbc:sqlserver://<HOST_NAME>:1433;databaseName=xxx;IntegratedSecurity=false;authentication=ActiveDirectoryPassword;
Port:1443
Database: xxx

Failed to authenticate the user (Active Directory Password) on remote (docker run on Azure DevOps)

1 Answers1