Unable to connect to Oracle database from Azure Function in VS Code

[Mounesh1881]

I am trying to connect to an on-premises Oracle database from an Azure Function using Visual Studio Code. I have installed the necessary packages (Oracle.ManagedDataAccess, System.Configuration.ConfigurationManager, System.Security.Permissions) and configured my connection string as follows:
Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXXXX)(PORT=1648))(CONNECT_DATA=(SERVICE_NAME=XXXXXX)));User ID=XXXXX;Password=XXXXX;

However, I am encountering a connection timeout error (ORA-50000: Connection request timed out). I have verified that the host is up and running, and I can connect to the database using the Oracle SQL Developer extension in VS Code.

Here is the code I am using:

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Extensions.Logging;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using System.Threading.Tasks;

namespace Company.Function
{
public class HttpTrigger1
{
private readonly ILogger _logger;

    public HttpTrigger1(ILogger<HttpTrigger1> logger)
    {
        _logger = logger;
    }

    [Function("HttpTrigger1")]
    public async Task<IActionResult> Run([HttpTrigger(AuthorizationLevel.Function, "get", "post")] HttpRequest req)
    {
        _logger.LogInformation("C# HTTP trigger function processed a request.");

        string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXXX)(PORT=1648))(CONNECT_DATA=(SERVICE_NAME=XXXX)));User ID=XXXX;Password=XXXX;Validate Connection=true;";

        OracleConnection conn = new OracleConnection(connectionString);
        try
        {
            await conn.OpenAsync();
            _logger.LogInformation("Connected to Oracle database.");

            OracleCommand cmd = new OracleCommand("SELECT * FROM dual", conn);
            OracleDataReader reader = await cmd.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                string result = reader.GetString(0);
                _logger.LogInformation($"Query Result: {result}");
            }
        }
        catch (Exception ex)
        {
            _logger.LogError($"Error connecting to Oracle database: {ex.Message}");
            return new StatusCodeResult(StatusCodes.Status500InternalServerError);
        }
        finally
        {
            await conn.CloseAsync();
        }

        return new OkObjectResult("Oracle database query executed successfully.");
    }
}

}

Error :
[2024-12-24T16:45:45.711Z] Error connecting to Oracle database: ORA-50000: Connection request timed out

[alexkeh]

Increase your Connection Timeout value to something large, such as 300. That will give some more time for the actual connection exception to manifest itself since it's taking longer than the default timeout. You'll see the real error then.

If the Azure Function is not running locally (i.e., on a cloud instance), make sure there's network connectivity between the cloud Azure Function instance and the DB. As a security precaution, cloud firewalls block most ports by default.

[Mounesh1881]

I am running the Azure Function App locally and have not yet deployed it to the cloud.
I increased the connection timeout, but I'm still encountering the timeout issue[Connection Timeout=300].

I connected to the GlobalProtect (organization VPN) and tried pinging the host. I was able to see the packet transmission. I also verified the credentials and successfully logged into the database using the SQL Developer extension in the same VS Code environment. I was able to establish a connection and query the database, but when we run the Function App, we are getting a timeout issue.

Error :
[2024-12-25T14:12:25.169Z] Error connecting to Oracle database: ORA-50000: Connection request timed out
[2024-12-25T14:12:25.204Z] Executing StatusCodeResult, setting HTTP status code 500
[2024-12-25T14:12:25.271Z] Executed 'Functions.HttpTrigger1' (Failed, Id=0d4c188e-d3a2-4dba-99da-1c58a0eef40f, Duration=328532ms)
[2024-12-25T14:12:25.276Z] System.Private.CoreLib: Exception while executing function: Functions.HttpTrigger1. Microsoft.Azure.WebJobs.Script.Grpc: Failed to proxy request with ForwarderError: RequestTimedOut. System.Net.Http: The operation was canceled. System.Net.Sockets: Unable to read data from the transport connection: The I/O operation has been aborted because of either a thread exit or an application request.. The I/O operation has been aborted because of either a thread exit or an application request.

[alexkeh]

Can you turn on ODP.NET tracing and share the trace? Set the trace level to 7. That will tell us which internal operation ODP.NET is stuck on and provide indications of the root cause.

[Mounesh1881]

It is resolved now. thank you