JDBC connection from on-prem App to Azure SQL DB using ActiveDirectoryIntegrated Auth not working

Question

I am trying to connect an on-prem application (SonarQube) with an Azure managed SQL DB. The connection uses JDBC, and I am using JDBC v12.2.0. This works using a SQL User and password, but does not work with Authentication=ActiveDirectoryIntegrated. The error is "Failed to authenticate the user in Active Directory (Authentication=ActiveDirectoryIntegrated)". It also references "Could not discover endpoint for Integrate Windows Authentication. Check your ADFS settings"; but from what I can tell that refers to browser based connections. The application making the connection runs as a Windows Service using a service account. The service account in AD is synced with AAD. The service account is a user in the DB in db_datareader, db_datawriter, db_ddladmin, db_securityadmin roles. The app UI is only used on-prem. Only the DB Connection is made to Azure. I have tried many variations on the connection string including leaving some sections out. My non-working connection string is: sonar.jdbc.url=jdbc:sqlserver://.database.windows.net:1433;database=;encrypt=true;trustServerCertificate=true;hostNameInCertificate=.database.windows.net;loginTimeout=30;Authentication=ActiveDirectoryIntegrated Working connection string is: sonar.jdbc.username=XXXXXXXXXXXXXX sonar.jdbc.password=YYYYYYYYYYYYYY sonar.jdbc.url=jdbc:sqlserver://.database.windows.net:1433;database=***;encrypt=true;trustServerCertificate=true;hostNameInCertificate=.database.windows.net;loginTimeout=30; Any ideas or help would be appreciated. Thanks!

Answer 1

Hi @Victor Coupez , It seems that you are trying to connect an on-prem application (SonarQube) with an Azure managed SQL DB using JDBC v12.2.0. You mentioned that the connection works using a SQL User and password, but does not work with Authentication=ActiveDirectoryIntegrated. The error message "Failed to authenticate the user in Active Directory (Authentication=ActiveDirectoryIntegrated)" indicates that the authentication failed. According to the documentation, the Active Directory Integrated authentication requires that the client and server are in the same domain or in trusted domains. If the client and server are not in the same domain or in trusted domains, you can use Active Directory Password authentication or Active Directory Universal authentication. You mentioned that the application making the connection runs as a Windows Service using a service account. The service account in AD is synced with AAD. The service account is a user in the DB in db_datareader, db_datawriter, db_ddladmin, db_securityadmin roles. Based on the information you provided, it seems that you are using a service account to connect to the Azure SQL DB. In this case, you can use Active Directory Password authentication. You can create an Azure AD user and assign it to the db_datareader, db_datawriter, db_ddladmin, db_securityadmin roles in the Azure SQL DB. Then, you can use the Azure AD user's credentials to connect to the Azure SQL DB. Here is an example of a connection string that uses Active Directory Password authentication:

jdbc:sqlserver://<server-name>.database.windows.net:1433;database=<database-name>;user=<user-name>@<domain-name>.onmicrosoft.com;password=<password>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

Please replace the <server-name>, <database-name>, <user-name>, <domain-name>, and <password> placeholders with the appropriate values. I hope this helps! Let me know if you have any further questions. If this answer helped you please mark it as "Verified" so other users can reference it. Thank you, James

Answer 2

I have the same problem, .NET applications can connect but java applications cannot connect

az login --tenant "********-****-****-****-*********"

import java.sql.Connection;
import java.sql.Statement;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
public class Main {
    public static void main(String[] args) {

        String serverName = "******.database.windows.net";
        String portNumber = "1433";
        String databaseName = "******";
        String authentication =  "ActiveDirectoryIntegrated";

        try {
            SQLServerDataSource ds = new SQLServerDataSource();
            ds.setServerName(serverName);
            ds.setPortNumber(Integer.parseInt(portNumber));
            ds.setDatabaseName(databaseName);
            ds.setAuthentication(authentication);

            try (Connection con = ds.getConnection(); Statement stmt = con.createStatement();) {
                System.out.println("Connection established successfully.");
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user in Active Directory (Authentication=ActiveDirectoryIntegrated).

in my case using user and password based authentication is not an option