Timeout errors on MSOLEDBSQL or MSOLEDBSQL19 connection to Azure database

We have an application that connects to an Azure database. When it establishes a connection the connection string would look like this:

Provider=MSOLEDBSQL;Server=<servernamehere>.database.windows.net;Database=<databasehere>;Uid=<whateverthisshouldbe>;Pwd=<whateverthatshouldbe>;MARS Connection=True;Application Name=<ourapplicationhere>;

We have also recently started using MSOLEDBSQL19, so if that is installed the connection string could be

Provider=MSOLEDBSQL19;Server=<servernamehere>.database.windows.net;Database=<databasehere>;Uid=<whateverthisshouldbe>;Pwd=<whateverthatshouldbe>;MARS Connection=True;Application Name=<ourapplicationhere>;

A few points for consideration:

When I run the same application from my own machine to connect to the same database it works flawlessly, EVERY time.
We have several clients on several different Azure databases. This client is a relatively small client with 8 users. We have clients with up to 100 users who never experience this issue when they connect to their respective Azure database.
When we try to run the software on this client's PCs it would connect just fine some times and then, seconds later, it would fail, only to connect fine again a short time later.

This is the error that's raised when the connection fails:

Invalid connection string - no connection for connection string: (see above)

Login Timeout expired

A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books online.

Named Pipes Provider: Could not open a connection to SQL Server [53]

Because we have so many other clients who never have these problems connecting to their Azure databases that have identical configurations to this one, I don't think the problem is on the Server side, despite what this message says and what I am finding online.

What I tried

I did a speed check on their internet and found it had a very respectable 100mbps down and nearly as much up. For a site of this size that should be plenty.
Thinking the issue was DNS related I configured one user's Ethernet connection to use the CloudFlare DNS servers. The problem persisted
Again in the DNS realm, I put the IP address to our Azure server in the Hosts file. The problem persisted

So I'm stuck. Any suggestions on what we could try next?

One thing that strikes me as odd is the mention of "Named Pipes Provider" in the inner exception. I would have thought it uses TCP/IP but what do I know? Or is there some kind of configuration change required here?

EDIT:

For troubeshooting, I installed SSMS on a user's machine and I tried to connect to tcp:.database.windows.net. Here's what I found:

Click Connect. If it takes anything over a second, I cancel. Click Connect again.
This happened like 4 or 5 times.
Click Connect and wouldn't you know it.... STRAIGHT IN. Now what is going on!?

I also ran powershell -Command "Test-NetConnection -ComputerName '<servernamehere>.database.windows.net' -Port 1433" from a Windows command prompt to test database network connectivity and got a peculiar result. The first time it succeeds:

ComputerName     : <servernamehere>.database.windows.net                                                              
RemoteAddress    : NN.NN.NNN.NNN
RemotePort       : 1433
InterfaceAlias   : MyInterface
SourceAddress    : xxx.xxx.xxx.xxx
TcpTestSucceeded : True

But when I repeat the same command, it fails. What's really strange is that it resolves the SAME ComputerName to DIFFERENT IP addresses on different occasions:

ComputerName     : <servernamehere>.database.windows.net                                                              
RemoteAddress    : MM.MMM.MMM.M
RemotePort       : 1433
InterfaceAlias   : MyInterface
SourceAddress    : xxx.xxx.xxx.xxx
TcpTestSucceeded : False

It sometimes resolves to one IP address, sometimes the other. One IP address ALWAYS fails, the other ALWAYS succeeds.

CodePudding user response：

One thing that strikes me as odd is the mention of "Named Pipes Provider" in the inner exception. I would have thought it uses TCP/IP but what do I know? Or is there some kind of configuration change required here?

This is expected after a TCP/IP connection failure. The client driver will try Named Pipes if the TCP connection fails. The reference to named pipes is unrelated to your issue.

What's really strange is that it resolves the SAME ComputerName to DIFFERENT IP addresses on different occasions

Cient connections from outside Azure connect to the Azure SQL Database Gateway as described in the SQL Database Connectivity architecture. Since a gateway may have multiple IP addresses (which vary by region), you may get different IP addresses for the same DNS name.

The symptom that one gateway IP address connection succeeds but the other fails suggests that your client has blocked (or not whitelisted) the problem IP/subnet or TCP port 1433 traffic to that IP/subnet. Ask your client to whitelist gateway IP/subnets for the region(s) hosting your SQL Database. The Gateway IP addresses and subnets are listed in the Gateway IP address section of the documentation.