Azure Active Directory Integration not working with JDBC driver , working fine with SSMS
shanu2007 opened this issue · 5 comments
Driver version
12.4.2.0
SQL Server version
Microsoft SQL Azure (RTM) - 12.0.2000.8 Aug 28 2023 13:51:45 Copyright (C) 2022 Microsoft Corporation
Client Operating System
Microsoft datacentre 2019 10.0.17763
JAVA/JVM version
jre 11
Running command
java -cp C:\Users\azure\Downloads\sqljdbc_12.4.2.0_enu\sqljdbc_12.4\enu\jars\mssql-jdbc-12.4.2.jre11.jar ConnectURL.java
Problem description
- able to make Azure Integrated Connection using Sql server management studio
- not able to connect using by jdbc code
`import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ConnectURL {
public static void main(String[] args) {
String connectionUrl = "jdbc:sqlserver://*********.8e80c02b3c79.database.windows.net:3342;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.8e80c02b3c79.database.windows.net;loginTimeout=30;Authentication=ActiveDirectoryIntegrated;";
try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement();) {
String SQL = "SELECT TOP 10 * FROM Person.Contact";
ResultSet rs = stmt.executeQuery(SQL);
// Iterate through the data in the result set and display it.
while (rs.next()) {
System.out.println(rs.getString("FirstName") + " " + rs.getString("LastName"));
}
}
// Handle any errors that may have occurred.
catch (SQLException e) {
e.printStackTrace();
}
}
}`
Expected behavior
should be able to connect and show results
Actual behavior/ STACK TRACE
com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user in Active Directory (Authentication=ActiveDirectoryIntegrated). at com.microsoft.sqlserver.jdbc.SQLServerConnection.getFedAuthToken(SQLServerConnection.java:5853) at com.microsoft.sqlserver.jdbc.SQLServerConnection.onFedAuthInfo(SQLServerConnection.java:5715) at com.microsoft.sqlserver.jdbc.SQLServerConnection.processFedAuthInfo(SQLServerConnection.java:5549) at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onFedAuthInfo(tdsparser.java:310) at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:130) at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:42) at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:6612) at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:5154) at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:5087) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7675) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4137) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3583) at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:3172) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:3014) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1836) at com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnectionInternal(SQLServerDataSource.java:1489) at com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnection(SQLServerDataSource.java:99) at AzureActiveDirectoryAuthentication.main(AzureActiveDirectoryAuthentication.java:91) at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) at java.base/java.lang.reflect.Method.invoke(Method.java:580) at jdk.compiler/com.sun.tools.javac.launcher.Main.execute(Main.java:484) at jdk.compiler/com.sun.tools.javac.launcher.Main.run(Main.java:208) at jdk.compiler/com.sun.tools.javac.launcher.Main.main(Main.java:135) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Error code 0xCAA90014; state 10. at com.microsoft.sqlserver.jdbc.SQLServerConnection.getFedAuthToken(SQLServerConnection.java:5848) ... 22 more Caused by: com.microsoft.sqlserver.jdbc.DLLException: <?xml version="1.0" encoding="utf-8"?><S:Envelope xmlns:wsa="http://www.w3.org/2005/08/addressing" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" xmlns:wsp="http://schemas.xmlsoap.org/ws/2004/09/policy" xmlns:wst="http://schemas.xmlsoap.org/ws/2005/02/trust" xmlns:S="http://www.w3.org/2003/05/soap-envelope"><S:Header><wsa:Action S:mustUnderstand="1" wsu:Id="Action">http://schemas.xmlsoap.org/ws/2005/02/trust/RSTR/Issue</wsa:Action><wsa:To S:mustUnderstand="1" wsu:Id="To">http://schemas.xmlsoap.org/ws/2004/08/addressing/role/anonymous</wsa:To><wsse:Security S:mustUnderstand="1"><wsu:Timestamp wsu:Id="TS" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"><wsu:Created>2024-01-31T18:47:03.1388288Z</wsu:Created><wsu:Expires>2024-01-31T18:52:03.1388288Z</wsu:Expires></wsu:Timestamp></wsse:Security></S:Header><S:Body xmlns:S="http://www.w3.org/2003/05/soap-envelope"><wst:RequestSecurityTokenResponse xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" xmlns:wsp="http://schemas.xmlsoap.org/ws/2004/09/policy" xmlns:wst="http://schemas.xmlsoap.org/ws/2005/02/trust"><wsp:AppliesTo><wsa:EndpointReference xmlns:wsa="http://www.w3.org/2005/08/addressing"></wsa:EndpointReference></wsp:AppliesTo><psf:pp xmlns:psf="http://schemas.microsoft.com/Passport/SoapServices/SOAPFault"><psf:reqstatus>0x8004882c</psf:reqstatus><psf:errorstatus>0x80045b00</psf:errorstatus></psf:pp></wst:RequestSecurityTokenResponse></S:Body></S:Envelope> at com.microsoft.sqlserver.jdbc.AuthenticationJNI.ADALGetAccessTokenForWindowsIntegrated(Native Method) at com.microsoft.sqlserver.jdbc.AuthenticationJNI.getAccessTokenForWindowsIntegrated(AuthenticationJNI.java:85) at com.microsoft.sqlserver.jdbc.SQLServerConnection.getFedAuthToken(SQLServerConnection.java:5806) ... 22 more
Hi @shanu2007,
Is ActiveDirectoryIntegrated set up correctly on your machine? As per the authentication connection property:
Important: If authentication is set to ActiveDirectoryIntegrated, the following two libraries must be installed: mssql-jdbc_auth--.dll (available in the JDBC driver package) and Microsoft Authentication Library for SQL Server (ADAL.DLL). Microsoft Authentication Library can be installed from Microsoft ODBC Driver for SQL Server or Microsoft OLE DB Driver for SQL Server. The JDBC driver only supports version 1.0.2028.318 and higher for ADAL.DLL.
As well, please see the instructions for ActiveDirectoryIntegrated for more information.
Let us know if this resolves your issue.
Hi @shanu2007,
Since this a replica of an existing email issue, this will be closed. All discussion will take place in the email thread.
Yes Microsoft team has verified all dll are present and i am able to connect to instance through azure ad authentication using sql server mangement studio
Hi @shanu2007,
This should have been relayed by support, but I will copy-paste what I sent them:
Thank you. As far as what the driver requires for ActiveDirectoryIntegrated authentication, this looks correct. I’ve tried looking into the SQL error code presented in error2.txt attached below, error code 0xCAA90014. Unfortunately, I could not find anything that would explain why this is working fine with SSMS, but not with a, correctly set up, JDBC driver. For next steps, here are my suggestions:
- Is it possible to use an alternate form of authentication? For example, Windows integrated authentication in the place of Azure SQL with integrated authentication?
- You may also want to reach out to the Active Directory team for further information passing along that the error received is:
- ERROR_ADAL_WSTRUST_REQUEST_SECURITYTOKEN_FAILED (0xcaa90014/-894894060) - The Server WS-Trust response reported a fault exception, and it failed to get assertion.
Our ActiveDirectoryIntegrated environment unfortunately means that aside from verifying the correct DLLs are present, and the correct connection string options set, there is nothing else we can do from the driver’s side, for this authentication scheme.