I setup an Azure Hybrid Connection for an Azure Function in order to connect to an on-premises SQL Server database. I added the below as the connection string however; I receive the following error while attempting to connect. Guidance here on how to format the connection string to work with hybrid connection would be appreciated. Error Message, Code, and Connection string that I am using below.
The Azure function is using .NET Framework and I am using SQL Server 2019
A connection was successfully established with the server, but then an error occurred during the login process (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Azure Function Code
using System.Linq
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Azure.WebJobs.Host;
using System.Data.SqlClient;
namespace AzureHybridConnectionTest
{
public static class TestOnPremConnection
{
[FunctionName("TestHybridConnection")]
public static async Task<HttpResponseMessage> Run([HttpTrigger(AuthorizationLevel.Function, "post", Route = null)] HttpRequestMessage req, TraceWriter log)
{
log.Info("Testing Connection to On Premise SQL Database.");
string connectionString = System.Environment.GetEnvironmentVariable("HybridConnectionString");
log.Info(connectionString);
using(SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
log.Info($"SQL Connection open to database {conn.Database}");
}
log.Info("SQL Connection closed");
return req.CreateResponse(HttpStatusCode.OK, "Success!");
}
}
}
Connection String
Server=tcp:[hybrid connection endpoint];Initial Catalog=[db name];Persist Security Info=False;User ID=[user];Password=[password];MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;
Thank you Anand Sowmithiran. Posting your suggestion as an answer so that it will be helpful for other community members who face similar kind of issues.
As per this doc If you are using the on prem sql server there could be port issue like network is not allowing the incomming connections.
Hybrid Connections can connect Azure App Service Web Apps to on-premises resources that use a static TCP port. Supported resources include Microsoft SQL Server, MySQL, HTTP Web APIs, Mobile Services, and most custom Web Services.
For further information check the SO and also check the MSSQL 18456 error log.