How to get JDBC driver Azure function to connect via Microsoft Entra?

Question

How to get JDBC driver Azure function to connect via Microsoft Entra?

Sagar Kapadia 0

I am having issues posting code to my sample azure database. I am working on an azure function that inserts some data that starts from a POST http trigger. I am running into an issue where my connection string can be grabbed from local.settings.json BUT I keep getting errors in two ways. When my connection string uses JDBC (Microsoft Entra integrated authentication), I get this error in my console which doesnt make sense because I should not need to use this java.lang.IllegalArgumentException: KrbException: Cannot locate default realm.

If I use the JDBC (Microsoft Entra password authentication) with authentication=ActiveDirectoryPassword, the error is different and says "Failed to authenticate the user gmail.onmicrosoft.com in Active Directory (Authentication=ActiveDirectoryPassword)." I am not sure if this is caused because I am using a gmail domain or not. I can provide more code if its helpful

Sagar Kapadia 0 Reputation points

2023-10-03T05:09:46.1666667+00:00

I am also using Azure sql free one month trial and free one month trial for the function app

2 answers

Your answer

Sagar Kapadia 0 Reputation points

2023-10-03T05:09:46.1666667+00:00

I am also using Azure sql free one month trial and free one month trial for the function app

Answer 1

MuthuKumaranMurugaachari-MSFT 22,441 Moderator

Sagar Kapadia Thanks for posting your question in Microsoft Q&A. Based on the description, you are looking to connect to Azure SQL database from Azure Functions using Microsoft Entra.

If you have deployed your function code to Azure, then I suggest using Managed Identity and doc: https://learn.microsoft.com/en-us/azure/azure-functions/functions-identity-access-azure-sql-with-managed-identity provides detailed steps to enable system-assigned Managed Identity in Azure Function, grant access to the database and configure connection string with Active Directory Managed Identity authentication.

However, if you are testing in your local environment, you might have to use ActiveDirectoryDefault authentication option and it follows the chain such as Environment, Managed Identity, IntelliJ/VS code/VS etc. for authentication. Check https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-ver16#connect-using-activedirectorydefault-authentication-mode doc for different authentication options and info.

From the exception above, it seems that you are trying to authenticate via Gmail, but only Azure AD (or Microsoft) accounts are supported with the authentication. So, I suggest you follow the above steps for using different authentication and let us know if any issues you face.

I hope this helps with your questions.

Sagar Kapadia 0 Reputation points

2023-10-03T14:55:46.78+00:00

Okay thanks for now I am using local code. I am using visual studio code as my IDE. Do I need to change to intellij to make local setup to work?
Sagar Kapadia 0 Reputation points

2023-10-03T14:57:36.3933333+00:00

I will try the above approach for local and report back if I have any issues. Thanks again
MuthuKumaranMurugaachari-MSFT 22,441 Reputation points Moderator

2023-10-03T15:29:00.5766667+00:00

No, using VS Code follows the similar chain like IntelliJ and here is the reference: https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16#using-active-directory-default-authentication.

Also, make sure that SQL Server Firewall is configured to allow connections from the Azure Function app (IP address or subnet).
Sagar Kapadia 0 Reputation points

2023-10-04T20:47:15.7366667+00:00

I dont think I will be able to make it work as I tried and kept getting this error ClassNotFoundException: com.microsoft.aad.msal4j.AppTokenProviderParameters with azure cli. According to this page, JDBC only accepts the ways that the connection string are given

https://razorsql.com/articles/sql_azure_active_directory.html
MuthuKumaranMurugaachari-MSFT 22,441 Reputation points Moderator

2023-10-04T20:57:09.19+00:00

Sagar Kapadia Can you share details such as code snippet, JSON settings, Connection string (removing sensitive info), runtime version, full exception including stack trace etc.? That would help in understanding the issue better.

Sagar Kapadia 0

package com.function;

import com.microsoft.azure.functions.ExecutionContext;
import com.microsoft.azure.functions.HttpMethod;
import com.microsoft.azure.functions.HttpRequestMessage;
import com.microsoft.azure.functions.HttpResponseMessage;
import com.microsoft.azure.functions.HttpStatus;
import com.microsoft.azure.functions.annotation.AuthorizationLevel;
import com.microsoft.azure.functions.annotation.FunctionName;
import com.microsoft.azure.functions.annotation.HttpTrigger;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Optional;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.*;
import com.fasterxml.jackson.databind.node.*;

/**
 * Azure Functions with HTTP Trigger.
 */
public class Function {
    /**
     * This function listens at endpoint "/api/HttpExample". Two ways to invoke it using "curl" command in bash:
     * 1. curl -d "HTTP Body" {your host}/api/HttpExample
     * 2. curl "{your host}/api/HttpExample?name=HTTP%20Query"
     */
    @FunctionName("HttpExample")
    public HttpResponseMessage run(
            @HttpTrigger(
                name = "req",
                methods = {HttpMethod.POST},
                authLevel = AuthorizationLevel.ANONYMOUS)
                HttpRequestMessage<Optional<String>> request,
            final ExecutionContext context) {
        context.getLogger().info("Java HTTP trigger processed a request.");
        
        if (!request.getBody().isPresent()) {
            return request.createResponseBuilder(HttpStatus.BAD_REQUEST)
                    .body("Request body is empty")
                    .build();
        }
                String requestBody = request.getBody().get();
                String connectionString = System.getenv("SQLConnectionString");
                context.getLogger().info("cval IS " + connectionString);
                ObjectMapper mapper = new ObjectMapper();
                JsonNode jsonData;

    try {



            jsonData = mapper.readTree(requestBody);
                        if (!jsonData.has("payloadId") || !jsonData.has("cardId")) {
                return request.createResponseBuilder(HttpStatus.BAD_REQUEST)
                        .body("Missing required keys: payloadId, cardid, cartItems")
                        .build();
            }
            context.getLogger().info("Received JSON: " + requestBody);

        } catch (IOException e) {
            context.getLogger().severe("Error processing JSON: " + e.getMessage());
            return request.createResponseBuilder(HttpStatus.BAD_REQUEST)
                    .body("Invalid JSON format")
                    .build();
        } catch (Exception e) {
            context.getLogger().severe("Error processing JSON: " + e.getMessage());
            return request.createResponseBuilder(HttpStatus.INTERNAL_SERVER_ERROR)
                    .body("Error processing JSON")
                    .build();
        }

        try {
            
            Connection connection = DriverManager.getConnection(connectionString);
            context.getLogger().info("hereNOWWWWW" + connection);
            String insertQuery = "INSERT INTO AdAdaptedUsers (PayloadID, CatalogID, CardID, CartItemBPN, CartItemQTY, CartItemName, CartItemCategoryName) VALUES (?, ?, ?, ?, ?, ?, ?)";
            
             PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
                preparedStatement.setString(1, jsonData.get("payloadId").asText());
                preparedStatement.setString(2, jsonData.get("catalogId").asText());
                preparedStatement.setString(3, jsonData.get("cardId").asText());
                
                JsonNode cartItem = jsonData.get("cartItems").get(0);

                preparedStatement.setString(4, cartItem.get("bpn").asText());
                preparedStatement.setString(5, cartItem.get("qty").asText());
                preparedStatement.setString(6, cartItem.get("name").asText());
                preparedStatement.setString(7, cartItem.get("categoryName").asText());

                preparedStatement.executeUpdate();

                connection.close();
                
                return request.createResponseBuilder(HttpStatus.OK)
                    .body("Data inserted successfully into the SQL database")
                    .build();

        } catch (SQLException e) {
                context.getLogger().severe("SQL error: " + e.getMessage());
                e.printStackTrace();

                // Build an error response
                return request.createResponseBuilder(HttpStatus.INTERNAL_SERVER_ERROR)
                    .body("Error inserting data")
                    .build();
    } catch (Exception e) {
        e.printStackTrace();
        return request.createResponseBuilder(HttpStatus.BAD_REQUEST)
                .body("Invalid JSON format in the request body. At the end")
                .build();
    }
}
}

Sagar Kapadia 0

I am using java version 17 and connection string is like this

    "SQLConnectionString": "jdbc:sqlserver://{server:port};database={db};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;Authentication=ActiveDirectoryDefault"

  <dependencies>
            <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>12.2.0.jre11</version>
        </dependency>
        <dependency>
<groupId>com.microsoft.azure</groupId> 
<artifactId>msal4j</artifactId> 
<version>1.11.3</version> 
</dependency>
<dependency>
    <groupId>com.azure</groupId>
    <artifactId>azure-identity</artifactId>
    <version>1.7.0</version>
</dependency>

Sagar Kapadia 0 Reputation points

2023-10-04T21:21:35.14+00:00

2023-10-04T20:37:20.740Z] Executed 'Functions.HttpExample' (Failed, Id=90856c74-120e-44fb-8c20-3233bdc1562c, Duration=932ms)

[2023-10-04T20:37:20.740Z] System.Private.CoreLib: Exception while executing function: Functions.HttpExample. System.Private.CoreLib: Result: Failure

[2023-10-04T20:37:20.740Z] Exception: ClassNotFoundException: com.microsoft.aad.msal4j.AppTokenProviderParameters

[2023-10-04T20:37:20.740Z] Stack: java.lang.reflect.InvocationTargetException

[2023-10-04T20:37:20.740Z] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

[2023-10-04T20:37:20.740Z] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)

[2023-10-04T20:37:20.740Z] at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

[2023-10-04T20:37:20.740Z] at java.base/java.lang.reflect.Method.invoke(Method.java:568)

[2023-10-04T20:37:20.740Z] at com.microsoft.azure.functions.worker.broker.JavaMethodInvokeInfo.invoke(JavaMethodInvokeInfo.java:22)

[2023-10-04T20:37:20.740Z] at com.microsoft.azure.functions.worker.broker.EnhancedJavaMethodExecutorImpl.execute(EnhancedJavaMethodExecutorImpl.java:22)

[2023-10-04T20:37:20.740Z] at com.microsoft.azure.functions.worker.chain.FunctionExecutionMiddleware.invoke(FunctionExecutionMiddleware.java:19)

[2023-10-04T20:37:20.740Z] at com.microsoft.azure.functions.worker.chain.InvocationChain.doNext(InvocationChain.java:21)

[2023-10-04T20:37:20.740Z] at com.microsoft.azure.functions.worker.broker.JavaFunctionBroker.invokeMethod(JavaFunctionBroker.java:125)

[2023-10-04T20:37:20.740Z] at com.microsoft.azure.functions.worker.handler.InvocationRequestHandler.execute(InvocationRequestHandler.java:34)

[2023-10-04T20:37:20.740Z] at com.microsoft.azure.functions.worker.handler.InvocationRequestHandler.execute(InvocationRequestHandler.java:10)

[2023-10-04T20:37:20.740Z] at com.microsoft.azure.functions.worker.handler.MessageHandler.handle(MessageHandler.java:44)

[2023-10-04T20:37:20.740Z] at com.microsoft.azure.functions.worker.JavaWorkerClient$StreamingMessagePeer.lambda$onNext$0(JavaWorkerClient.java:94)

[2023-10-04T20:37:20.740Z] at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)

[2023-10-04T20:37:20.740Z] at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)

[2023-10-04T20:37:20.740Z] at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)

[2023-10-04T20:37:20.740Z] at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)

[2023-10-04T20:37:20.740Z] at java.base/java.lang.Thread.run(Thread.java:833)

[2023-10-04T20:37:20.740Z] Caused by: java.lang.NoClassDefFoundError: com/microsoft/aad/msal4j/AppTokenProviderParameters

[2023-10-04T20:37:20.740Z] at com.azure.identity.implementation.IdentityClientBase.getManagedIdentityConfidentialClient(IdentityClientBase.java:316)

[2023-10-04T20:37:20.741Z] at com.azure.identity.implementation.IdentityClient.lambda$getManagedIdentityConfidentialClientApplication$6(IdentityClient.java:129)

[2023-10-04T20:37:20.741Z] at reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:45)

[2023-10-04T20:37:20.741Z] at reactor.core.publisher.MonoCacheTime.subscribeOrReturn(MonoCacheTime.java:143)

[2023-10-04T20:37:20.741Z] at reactor.core.publisher.Mono.subscribe(Mono.java:4475)

[2023-10-04T20:37:20.741Z] at reactor.core.publisher.FluxFlatMap$FlatMapMain.onNext(FluxFlatMap.java:427)

[2023-10-04T20:37:20.741Z] at reactor.core.publisher.FluxIterable$IterableSubscription.slowPath(FluxIterable.java:335)

[2023-10-04T20:37:20.741Z] at reactor.core.publisher.FluxIterable$IterableSubscription.request(FluxIterable.java:294)

[2023-10-04T20:37:20.741Z] at reactor.core.publisher.FluxFlatMap$FlatMapMain.onSubscribe(FluxFlatMap.java:371)

[2023-10-04T20:37:20.741Z] at reactor.core.publisher.FluxIterable.subscribe(FluxIterable.java:201)

[2023-10-04T20:37:20.741Z] at reactor.core.publisher.FluxIterable.subscribe(FluxIterable.java:83)

[2023-10-04T20:37:20.741Z] at reactor.core.publisher.Mono.subscribe(Mono.java:4490)

[2023-10-04T20:37:20.741Z] at reactor.core.publisher.Mono.blockOptional(Mono.java:1786)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.SQLServerSecurityUtility.getDefaultAzureCredAuthToken(SQLServerSecurityUtility.java:405)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.SQLServerConnection.getFedAuthToken(SQLServerConnection.java:5791)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.SQLServerConnection.onFedAuthInfo(SQLServerConnection.java:5618)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.SQLServerConnection.processFedAuthInfo(SQLServerConnection.java:5463)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onFedAuthInfo(tdsparser.java:311)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:131)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:42)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:6490)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:5068)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:5002)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7685)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4048)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3487)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:3077)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2919)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1787)

[2023-10-04T20:37:20.741Z] at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1229)

[2023-10-04T20:37:20.741Z] at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:681)

[2023-10-04T20:37:20.741Z] at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:252)

[2023-10-04T20:37:20.741Z] at com.function.Function.run(Function.java:79)

[2023-10-04T20:37:20.741Z] ... 18 more

[2023-10-04T20:37:20.741Z] Caused by: java.lang.ClassNotFoundException: com.microsoft.aad.msal4j.AppTokenProviderParameters

[2023-10-04T20:37:20.741Z] at java.base/java.net.URLClassLoader.findClass(URLClassLoader.java:445)

[2023-10-04T20:37:20.741Z] at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:587)

[2023-10-04T20:37:20.741Z] at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:520)

[2023-10-04T20:37:20.741Z] ... 51 more
MuthuKumaranMurugaachari-MSFT 22,441 Reputation points Moderator

2023-10-05T17:57:06.3966667+00:00
Sagar Kapadia Thanks for sharing the details. I observed the below exception from the stack trace:

[2023-10-04T20:37:20.741Z] Caused by: java.lang.ClassNotFoundException: com.microsoft.aad.msal4j.AppTokenProviderParameters

You are using version 1.11.3 for package msal4j (v.1.11.3) but AppTokenProviderParameters was introduced as part of 1.13.0 (https://github.com/AzureAD/microsoft-authentication-library-for-java/blob/v1.13.0/src/main/java/com/microsoft/aad/msal4j/AppTokenProviderParameters.java). Can you upgrade this to the latest version? Check out microsoft-authentication-library-for-java repo and releases for reference.
Sagar Kapadia 0 Reputation points

2023-10-06T16:19:29.2366667+00:00

If I make an outlook domain would it work? Which account types can use the default way such as sql authenthication or microsoft entra way? I just need to do a POC. My company has its own domain in email just waiting for access rights before then doing this poc
Sagar Kapadia 0 Reputation points

2023-10-06T16:20:41.1433333+00:00

you mentioned "From the exception above, it seems that you are trying to authenticate via Gmail, but only Azure AD (or Microsoft) accounts are supported with the authentication. So, I suggest you follow the above steps for using different authentication and let us know if any issues you face."
MuthuKumaranMurugaachari-MSFT 22,441 Reputation points Moderator

2023-10-09T18:36:39.44+00:00

Sagar Kapadia SQL Server authentication is username and password credentials stored in SQL Server (Connecting through SQL Server Authentication doc) and this doesn't involve Microsoft Entra.

Can you confirm if Outlook domain is integrated with Azure AD? I will add SQL Server team to comment on that.

Answer 2

Sagar Kapadia 0

Best thing to do for anyone who has this issue is just use sql authentication. Click on reset password in the azure portal for the database and use sql authentication if you dont know your password which is what I did. From there, it was straight forward especially from a quick connection on local.

MuthuKumaranMurugaachari-MSFT 22,441 Reputation points Moderator

2023-10-17T14:23:47.07+00:00

Sagar Kapadia Thanks for sharing it with the community. Glad, you found the solution :)

Share via

How to get JDBC driver Azure function to connect via Microsoft Entra?

2 answers

Your answer