How to fix “Connect Timeout” and/or “Cannot open server xxx requested by the login” when connecting to Azure SQL Managed Instance in redirect mode

Here is something that will save you lots of time and headaches when trying to connect to Azure SQL Managed Instances, especially from onprem servers or from other clouds; I had to repeat this multiple times to multiple actors, so I know it will happen to someone else too.

In most cases, “Connect Timeout” and/or “Cannot open server xxx requested by the login; Login failed” errors are caused by the firewall configuration and a lack of understanding the SQLMI networking model, let me explain:

As stated by this article, which you should read, the recommended connection type is redirect:

• Redirect (recommended): Clients establish connections directly to the node hosting the database. To enable connectivity using redirect, you must open firewalls and Network Security Groups (NSG) to allow access on ports 1433, and 11000-11999. Packets go directly to the database, and hence there are latency and throughput performance improvements using redirect over proxy. Impact of planned maintenance events of gateway component is also minimized with redirect connection type compared to proxy since connections, once established, have no dependency on gateway.

As stated in the documentation, your client initially logs to the gateway on port 1433 (as the good ol’ SQL Server), but then in redirect mode the connection is made directly directly to the database container in the backend, through a random port in the range 11000-11999 , opening up this while range is essential, as if you open (or happen to have opened before) anything less, you can find yourself not being able to connect at all to your instance, as the redirect port is not static (it is indeed, ephemeral).
Be careful with your networking configuration!