SQL Connectivity Issue From Cloud app to on-prem server

Santhi Dhanuskodi 325

hi,

I have deployed a .net core MVC app in azure app service as a webapp.

I have to connect to a on-prem sql server to retrieve data. I have setup Azure relay/hybrid connection for the same. Also I have installed hybrid connection manager in on-prem server.

I get SQL connectivity issues intermittently. Either of these 3 errors. All these exceptions are occuring inconsistently. I have enabled TCP/IP already in the server.

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=121; handshake=59896;

Login failed for user '<userid>'.

We cannt see any logs from SQL server which are useful for this.

I suspect its more of Relay/hybrid connection issue. how do i troubleshoot this? What could be the causes? Where can we see additional logs for hybrid connection?

Grmacjon-MSFT 18,641 Reputation points

2024-05-07T22:46:12.8266667+00:00
@Santhi Dhanuskodi we are sorry to hear you're facing these issues. You're right, the intermittent SQL connectivity issues with your .NET Core MVC app could be a problem with the Azure Relay Hybrid Connection.

Here's how you can troubleshoot and identify the root cause:

1. Azure Relay Hybrid Connection Logs:

Event Viewer: On your on-premises server where the Hybrid Connection Manager is installed, open the Event Viewer. Navigate to "Applications and Services Logs" > "Microsoft" > "Service Bus" > "Client."

These logs might provide insights into errors or warnings related to the Hybrid Connection establishment or communication with Azure Relay.

2. Azure Portal Logs:

Go to the Azure portal and navigate to your Azure Relay resource.

Under "Monitor," you'll find "Namespace logs" and "Connection logs."

Namespace logs might reveal issues with the overall Azure Relay service health. Connection logs, if enabled, could offer details specific to your hybrid connection and communication attempts.

3. Network Connectivity Checks:

Verify that there's no firewall rule blocking outbound connections to Azure Relay ports (TCP 443) on your on-premises server.

Use tools like ping or tracert to confirm network connectivity between your on-premises server and the Azure Relay service endpoint.

Consider temporary measures like disabling security software firewalls on the on-premises server to isolate if these are causing intermittent connectivity issues. (Re-enable them after testing)

4. Examine .NET Application Logs:

Within your deployed .NET Core MVC app on Azure App Service, check the application logs for any exceptions or errors related to database connection attempts.

These logs might provide more details about the specific SQL connection failure you're encountering (e.g., timeout, login issues).

5. Review Azure App Service Settings:

Double-check your Azure App Service configuration for the connection string to your on-premises SQL server. Ensure it uses the correct hostname or IP address, port number, and credentials.

Hope that helps.

-Grace
Olaf Helper 45,286 Reputation points

2024-05-08T05:05:00.6333333+00:00

We cannt see any logs from SQL server which are useful for this.

Because the client app can't connect/reach SQL Server of which it isn't aware, so what should it log?

You have to check the complete network connection, e.g. FireWall could block the connection.
Santhi Dhanuskodi 325 Reputation points

2024-05-08T08:09:12.42+00:00
We dont see logs as said in this section. 'Namespace Logs' or 'Connection logs' not present in azure portal

2. Azure Portal Logs:

Go to the Azure portal and navigate to your Azure Relay resource.

Under "Monitor," you'll find "Namespace logs" and "Connection logs."

Namespace logs might reveal issues with the overall Azure Relay service health. Connection logs, if enabled, could offer details specific to your hybrid connection and communication attempts.
MikeyQiaoMSFT-0444 3,190 Reputation points

2024-05-27T06:03:44.02+00:00

Hi，Santhi Dhanuskodi

Hello, may I ask how you are progressing with this issue? If you are satisfied with anyone's answer, please don't forget to "Accept it." This will make a significant contribution to the community and will assist others who have similar issues as you.

2 answers

Bruce (SqlWork.com) 67,916 Reputation points

2024-05-08T16:12:29.83+00:00

because you are using a proxy, the logs are not going to tell you much. aside from the timeout, there are no errors as far as the proxy is concerned.

you will probably need to run a network trace between the on-prem client and the Sqlserver. an outgoing trace from the on-prem client to the azure proxy will tell you if you are getting connection / traffic issues.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
MikeyQiaoMSFT-0444 3,190 Reputation points

2024-05-14T09:25:52.63+00:00

Hi,Santhi Dhanuskodi

We haven't received a response from you for some time, and I would like to know the status of your issue. The issue may be caused by TLS. Please refer to the following documentation.

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/tls-exist-connection-closed
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SQL Connectivity Issue From Cloud app to on-prem server

2 answers

Your answer