Blog Post

Azure Database Support Blog
4 MIN READ

Lesson Learned #366: Cannot open server microsoft.com using authentication = ActiveDirectoryPassword

Jose_Manuel_Jurado's avatar
Jun 07, 2023

Today we had a very interesting case as our client encountered the following message: "Cannot open server 'microsoft.com' requested by the login. The login failed," or they had the message "Login failed for user 'username\microsoft.com'.  while using the Azure Active Directory Password authentication. Following, I would like to share with you some lessons learned.

 

In the first case, with this error message "Cannot open server 'microsoft.com' requested by the login. The login failed,", everything indicates that an attempt is being made to connect to the server "microsoft.com" because the username has the format "username@microsoft.com". The "@" symbol has a special meaning when connecting, as it overrides the server name specified in the connection string. Therefore, it suggests that the client is connecting with SQL Authentication.

 

In the second hand "Login failed for user 'username\microsoft.com, similarly, the same situation occurs with the login error, indicating that the ODBC library is connecting with SQL Authentication. 

 

To resolve this issue, we need to be sure that the ODBC driver that we are using supports AAD aunthetication. Once, we have tested this one, it is possible that we might have the following error message: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to load ADAL4J Java library that indicates that there was an issue loading the ADAL4J Java library for Microsoft SQL Server and we need to add it in our project you could see a reference how to do it here: Feature dependencies - JDBC Driver for SQL Server | Microsoft Learn

 

ADAL4J (Azure Active Directory Authentication Library for Java) is a library used for authenticating Java applications with Azure Active Directory (AD). 

 

If you are still facing the error,  com.microsoft.sqlserver.jdbc.SQLServerException: Failed to load ADAL4J Java

 

To resolve this issue, you can try the following steps:

  1. Check library dependencies: Ensure that all required dependencies for ADAL4J, such as other libraries or JAR files, are correctly included in your project's classpath or build configuration.

  2. Verify library version compatibility: Confirm that you are using the correct version of ADAL4J that is compatible with your Java and SQL Server versions. Check the documentation or release notes for the specific versions of ADAL4J and SQL Server that you are working with.

  3. Verify library installation: Ensure that the ADAL4J library is properly installed in your Java environment. You can try reinstalling or updating the library to the latest version to rule out any potential installation issues.

  4. Check for conflicting libraries: Verify that there are no conflicting libraries or versions that might interfere with the loading of ADAL4J. Conflicts can occur when multiple libraries provide similar classes or functionality. Resolving such conflicts may involve excluding or excluding conflicting libraries from your project's dependencies.

  5. Check runtime environment: If you are running your application in a containerized environment, such as Docker, make sure that the ADAL4J library is correctly included in the container or the container image.

  6. Review error logs: Examine any additional error messages or stack traces provided along with the exception. They might provide more specific details about the root cause of the issue. 

 

Finally, if you want to reproduce these errors once you have everything installed, you can make a change. For example, in the connection string, remove the "authentication=ActiveDirectoryPassword" keyword from the Java script.

 

 

package testconnectionms;

import java.sql.*;

/**
 * Simple connection test.
 *
 */
public class SQLTest
{
	static {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (Exception ex) {
			System.err.println("Unable to load JDBC driver");
			ex.printStackTrace();
			System.exit(1);
		}
	}

	public static void main(String[] args)
		throws SQLException
	{

		boolean AAD = true;

		String usernameSQL      = "SQLLogin";
		String passwordSQL      = "SQLPassword";

		String usernameAAD   = "username@microsoft.com";
		String passwordAAD   = "AADPassword";
		String envname    = "env";
		String seconds   = "10";

		String url="";

		Connection connection;
		
		if(AAD==false)			
		{
		  System.out.println("Arguments are: username="+usernameSQL+",envname="+envname+",seconds="+seconds);
		  url = String.format("jdbc:sqlserver://servername.database.windows.net:1433;database=dbname;sslProtocol=TLSv1.2;encrypt=true", envname,envname);
		}
		else
		{
		  System.out.println("Arguments are: username="+usernameAAD+",envname="+envname+",seconds="+seconds);
		  url = String.format("jdbc:sqlserver://servername.database.windows.net:1433;database=dbname;sslProtocol=TLSv1.2;encrypt=true;authentication=ActiveDirectoryPassword",envname,envname);
		}		  

		System.out.println("-- Connecting to " + url);
		
		long start = System.currentTimeMillis();
		
		if(AAD==false)			
		{		
		  connection = DriverManager.getConnection(url,usernameSQL, passwordSQL);
		}
		else
		{
		  connection = DriverManager.getConnection(url,usernameAAD, passwordAAD);			
		}		  

		if (false) {
			
		}
		
		final long time1 = System.currentTimeMillis();
		long time2 = time1;
		long cnt = 0;
		Integer secInt = Integer.valueOf(seconds);

        PreparedStatement st = connection.prepareStatement("SELECT 1");
		st.setQueryTimeout(20);
		while (time1 + (secInt.intValue() * 1000) >= time2)
		{
			cnt++;
			
			
			ResultSet rs = st.executeQuery();
			rs.next();
			rs.close();
			
			
			time2 = System.currentTimeMillis();
		}
		st.close();
		
		Long statementsCount = Long.valueOf(cnt);
		Long statementsPerSecond = Long.valueOf(cnt / ((time2 - time1) / 1000));
		Double timePerStatement = Double.valueOf(1000.0 / (cnt / ((time2 - time1) / 1000.0)));
		
		System.out.println("statementsCount="+statementsCount);
		System.out.println("statementsPerSecond="+statementsPerSecond);
		System.out.println("timePerStatement="+timePerStatement);
	}

}

 

 

Enjoy!

Updated Jun 07, 2023
Version 2.0
No CommentsBe the first to comment