Azure Question posted in
The official documentation to get you started can be found here.

Azure – Trying to connect to SQL DB – provider: TCP Provider, error: 26 – Error Locating Server/Instance Specified

I have a C# .NET 8 isolated Azure Function App that I am trying to connect to a SQL database, but I am having trouble trying to locate the problem.

The SqlException:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 26 – Error Locating Server/Instance Specified)

We have different teams where I am, I am on the development team, there is a networking team and a SQL team. I am trying to pin point where the issue might lie.

The Function is trying to connect the SQL directly, using both managed identity and straight user name and password, but no luck. From the Function App to the Azure MS SQL server is not exposed and behind vnets and private endpoints. They seems to be setup correctly from a networking side as I can resolve the server address and port.

I have gone down to use simple code to test if I can open the connection:

try
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        _logger.LogInformation("Successfully connected to SQL Server.");
    }

    return new OkObjectResult("Successfully connected to SQL Server.");
}
catch (SqlException ex)
{
    var result = JsonConvert.SerializeObject(new
    {
        Message = $"SqlException: {ex.Message}",
        ex.StackTrace,
        ex.InnerException,
        ex.Source
    });

    _logger.LogError(ex, ex.Message);
    return new ObjectResult(result) { StatusCode = StatusCodes.Status503ServiceUnavailable };
}
catch (Exception ex)
{
    var result = JsonConvert.SerializeObject(new
    {
        Message = $"Exception: {ex.Message}",
        ex.StackTrace,
        ex.InnerException,
        ex.Source
    });

    _logger.LogError(ex, ex.Message);
    return new ObjectResult(result) { StatusCode = StatusCodes.Status503ServiceUnavailable };
}

Is this issue from the database, net working or code?

I have tried tcpping through Kudu on Azure function app and it can resolve the .database.windows.net 1433

I have created a new Azure MS SQL server and database with no vnets and private endpoints, and no permissions or anything like that, but I can connect to that just fine.

I have also tried different connection strings as well.

EDIT:

More context about the function, its using a Linux app plan.

I updated the connection string to use the IP address instead of the server name and ended up with an error like this:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)

After doing some more reading, its point me in the direction of TLS/SSL Issues. So I added the function to a Linux docker container locally and now I get the same error when trying to connect to the one SQL server.
But, when I connect again to the basic one I created, it works. What SQL configuration am I missing and why is it related to Linux image?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    We found this post Unable to connect Azure Function with Azure SQL using private endpoint

    Basically there are two app settings I need to add because we are using a VNET and private endpoint:

    WEBSITE_DNS_SERVER with value 168.63.129.16 
WEBSITE_VNET_ROUTE_ALL with value 1

    From the above mentions post: "These settings will send all of your outbound calls from your app into your VNet in addition to enabling your app to use Azure DNS private zones."


  2. 0

    Is this issue from the database, net working or code?

    Your Newtwork setup is the problem:

    • Check whether any NSG rules are restricting the SQL Server connection.
    • Make sure the firewall settings for SQL Server permit connections from the IP range of your Azure Function App. Alternatively, you can connect SQL Server from your Azure tenancy by selecting the Allow All Azure Services option.
    • Ensure that the private endpoint configuration is correct if you have v net integrated, and DNS resolution is properly set up for the private endpoint.

    A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 – An internal exception was caught)

    To resolve above error, add the Encrypt=false; item to the connection string. as below:

    Server=tcp:servername.database.windows.net,1433;Initial Catalog=databaseName;Persist Security Info=False;User ID=username;Password={your_password};MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=True;Connection Timeout=30;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search