SQL Server connection timeout - routing destination

Question

SQL Server connection timeout - routing destination

Jon Perkins 0

I have a Rest API service running on Azure North Europe, and a SQL Server database running at the same place. My clients have a Win UI app (C#) which connects to the Rest API service. There are only a handful of clients so far (< 50) but they are all around the world. I am finding that two clients in particular are constantly experiencing problems whereby the API service tries to connect to the SQL service but is thwarted by timeout issues.

The numbers in the exception message indicates that my use of the default 20 timeout is insufficient so I have just raised it to 60, but remembering that the REST API and the SQL are both in Dublin I would not expect to see such a lag. The error message is:

Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. This failure occurred while attempting to connect to the routing destination (my italics). The duration spent while attempting to connect to the original server was - [Pre-Login] initialization=46; handshake=18; [Login] initialization=0; authentication=0; [Post-Login] complete=1; The duration spent while attempting to connect to this server was - [Pre-Login] initialization=9; handshake=26; [Login] initialization=0; authentication=0; [Post-Login] complete=14057;

Does anybody have any observations on what I am seeing here?

My clients in Canada do not seem to have any problems. I have one person in Kent and one person in Essex who particularly have this problem, but other people (e.g. Oxford, Worcester, London) never seem to have the problem. I guess these could be red herrings but I mention them anyway.

Azure support: if you are reading this I have a session trace id of 66C5F868-D577-4576-AE51-C3A5C19793E7 for ya :-)

Many thanks all!

Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2024-12-03T01:14:57.07+00:00

Hi @jon perkins

Thanks for the question and using MS Q&A platform.

Based on the error message and the context provided, it appears that the connection timeouts are likely due to network routing or latency issues, especially during the post-login phase when your API service attempts to connect to the SQL Server.

Considering that both your SQL Server and REST API are hosted in the same Azure region (North Europe/Dublin), and the issue is specific to certain clients (Kent and Essex), here are some potential reasons and actions to consider:

Network Routing and Latency:

The error message you encountered, referencing the “routing destination,” indicates a potential issue with Azure's internal network routing between the client’s location and the Azure region. Network congestion or inefficient routing could be contributing to connection delays.

Action: Utilize Azure Network Watcher to monitor and diagnose network latency or routing issues. This tool can help identify network paths and detect any bottlenecks or inefficient routing path.

DNS Resolution Issues:

A possible cause could be slow DNS resolution. Since only certain clients are experiencing timeouts, it's worth checking if DNS is resolving slowly for those clients.

Action: Ask the clients in Kent and Essex to test DNS resolution times using tools like nslookup to see if there are any significant delays.

Check for Network Congestion:

If the issue is happening at specific times of day, it might be due to network congestion in the affected regions. Try to identify if the issue correlates with peak hours.

Action: Check with the affected clients about the times they experience issues to see if there's a pattern of network congestion or routing delays.

If none of these steps resolve the issue, contacting Azure Support with the session trace ID you provided may offer further insights, particularly if the problem is related to Azure’s internal infrastructure.

Please go through this documentation that might help you: Timeout expired messages when connecting to SQL Server

I hope this information helps, please do let us know if you have any Queries.
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2024-12-03T15:55:49.1566667+00:00

Hi @Jon Perkins

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Jon Perkins 0 Reputation points

2024-12-03T16:38:29.53+00:00

My apologies Sai, I did not get a notification of your earlier reply and I have been in meetings all day so I didn't get around to checking the status of the post yet.

Since I wrote that I have made some more discoveries and, I think, have altered the parameters of the investigation a bit.

On reflection I don't think the geographical location of the specific customers I had in mind is an issue. There is nothing outside of the Dublin Azure centre that connects directly to SQL Server. The websites hosted on the Azure App Services make the SQL login connection, and some external programs that connect in do so via a REST service which also resides in the same location. The connection errors are reported by the website app of the REST API service respectively i.e. the only actual connections made are from other components internal to the data centre. The person who I notice is affected the most is when she uses her system (which I supplied) at about 10pm, so that could be explained by some specific load issue of some other process within Azure.

I was particularly drawn to the error message comment about This failure occurred while attempting to connect to the routing destination but this too could be a distraction. I will investigate further on the Network Watcher.

The SQL connection pool looks healthy. I ran sp_who and there were many sleeping connections which I assume are the waiting connections within the pool, so there was no exhaustion there.

I haven't made any changes within the Firewall settings of the server for a long time but maybe I should investigate that some more? The problem is only intermittent but I am rolling out a new commercial system and I want to have confidence that there isn't a problem lurking, in due course there should be a lot more users...

Many thanks,

Jon
Jon Perkins 0 Reputation points

2024-12-03T17:04:34.1633333+00:00

Hello Sai,

Apologies for not responding to your earlier message, I did not receive a notification and have been busy so had not got around to looking for a reply yet. I did get a popup of your second message and did reply, but that seemed to disappear when I posted it so I'm trying again.

I have been investigating this further and have clarified my thinking somewhat. Basically there is nothing that connects to the SQL Server external to the Azure data centre. The same problem is being raised from several of my website apps, and also from a central REST API service app. All of them have a connection string to gain access, and most of the time all works well. So I think the geographical location of specific customers is not an issue, I believe the problem to be internal to Dublin Azure. The lady in Essex who I most often see have a problem logs in at about 10pm so there may be some particular timing factor at work here which affects the routing issue.

The connection strings do not modify the pool size so the default value of 100 is assumed. When running sp_who I can see that there are many waiting pool connections, so it is not exhaustion.

I have not made any changes to the firewall in a long time but I do wonder if there is something occasionally being blocked. The problem has only become apparent for maybe five months but I seem to be seeing it a bit more often. Many connections are made in total per hour and I only get two or three reports per day (from the Exception handlers in the code) so it is a small percentage, but as I say it never used to happen at all. I have checked that the server isn't being overloaded - the performance monitor is fine but I have upgraded from 20DTU to 50 DTU and see no improvement.

I think I need to spend more time analysing specifically which websites and services are experiencing the problem and look for any commonality between them. However if you have any further observations based on this new information then I would be grateful to hear further from you.

I have a new software product starting to roll out to new customers and am a bit nervous of this issue being a problem that will grow and potentially give a bad initial impression to people using my program.

With kind regards,

Jon
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2024-12-03T18:17:16.98+00:00

Hi @Jon Perkins

It appears that the issue could be related to resource contention or network routing within Azure, particularly around certain times of day. Based on your findings, I suggest continuing with Network Watcher for latency analysis, monitoring resource usage on the SQL server, and checking for blocking or long-running queries. Additionally, implementing connection retry logic on the application side will help reduce the impact of intermittent issues.

Keep a close eye on both the network paths and Azure SQL performance during the problematic periods, and don't hesitate to reach out to Azure support for deeper insights using your trace logs.
Jon Perkins 0 Reputation points

2024-12-03T19:55:57.37+00:00

OK thanks, will do Sai.

Many thanks
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-12-03T21:06:19.4733333+00:00

When running sp_who I can see that there are many waiting pool connections, so it is not exhaustion.

No, that sounds suspicious. A connection in the connection pool is kept alive for 60 seconds (by default). You said that you had < 50 customers. You don't say how active they are, but your comment about your customer in Essex suggest that they are not working around the clock. So if you have properly closed and disposed your connections, you should not see very many idle connections.
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2024-12-04T18:50:28.44+00:00

Hi @Jon Perkins

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2024-12-06T09:17:40.8433333+00:00

Hi @Jon Perkins

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Your answer

Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2024-12-03T15:55:49.1566667+00:00

Hi @Jon Perkins

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Jon Perkins 0 Reputation points

2024-12-03T16:38:29.53+00:00

My apologies Sai, I did not get a notification of your earlier reply and I have been in meetings all day so I didn't get around to checking the status of the post yet.

Since I wrote that I have made some more discoveries and, I think, have altered the parameters of the investigation a bit.

On reflection I don't think the geographical location of the specific customers I had in mind is an issue. There is nothing outside of the Dublin Azure centre that connects directly to SQL Server. The websites hosted on the Azure App Services make the SQL login connection, and some external programs that connect in do so via a REST service which also resides in the same location. The connection errors are reported by the website app of the REST API service respectively i.e. the only actual connections made are from other components internal to the data centre. The person who I notice is affected the most is when she uses her system (which I supplied) at about 10pm, so that could be explained by some specific load issue of some other process within Azure.

I was particularly drawn to the error message comment about This failure occurred while attempting to connect to the routing destination but this too could be a distraction. I will investigate further on the Network Watcher.

The SQL connection pool looks healthy. I ran sp_who and there were many sleeping connections which I assume are the waiting connections within the pool, so there was no exhaustion there.

I haven't made any changes within the Firewall settings of the server for a long time but maybe I should investigate that some more? The problem is only intermittent but I am rolling out a new commercial system and I want to have confidence that there isn't a problem lurking, in due course there should be a lot more users...

Many thanks,

Jon
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2024-12-03T18:17:16.98+00:00

Hi @Jon Perkins

It appears that the issue could be related to resource contention or network routing within Azure, particularly around certain times of day. Based on your findings, I suggest continuing with Network Watcher for latency analysis, monitoring resource usage on the SQL server, and checking for blocking or long-running queries. Additionally, implementing connection retry logic on the application side will help reduce the impact of intermittent issues.

Keep a close eye on both the network paths and Azure SQL performance during the problematic periods, and don't hesitate to reach out to Azure support for deeper insights using your trace logs.
Jon Perkins 0 Reputation points

2024-12-03T19:55:57.37+00:00

OK thanks, will do Sai.

Many thanks
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-12-03T21:06:19.4733333+00:00

When running sp_who I can see that there are many waiting pool connections, so it is not exhaustion.

No, that sounds suspicious. A connection in the connection pool is kept alive for 60 seconds (by default). You said that you had < 50 customers. You don't say how active they are, but your comment about your customer in Essex suggest that they are not working around the clock. So if you have properly closed and disposed your connections, you should not see very many idle connections.
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2024-12-04T18:50:28.44+00:00

Hi @Jon Perkins

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2024-12-06T09:17:40.8433333+00:00

Hi @Jon Perkins

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

SQL Server connection timeout - routing destination

Your answer