Today, I worked on an interesting service request where our customer is facing the error message "Connection to database master failed (error 11001, state 0): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)" trying to connect to Azure SQL Managed Instance.
All points to that the issue is related about DNS resolution based on the message "No such host is known". In this situation, we saw that our customer created their own entry in their custom DNS to the .database.windows.net DNS Zone pointing to the name of the Azure SQL Managed Instance, for example, nameofmanagedinstance.virtualcluster.database.windows.net 10.0.1.254.
However, when our customer tries to connect they faced the error message that we mentioned before. Why? Because our customer is using Redirection.
In this redirection the client will connect directly to the node hosting the Managed Instance that has a specific name. Unfortunately, if the name of this node is not in the Custom DNS that our customer has the redirection will not be possible and you are going to have the error message that the node is unknown.
In this situation, we have 2 different alternatives:
Add the name of the node in the Custom DNS. Unfortunately, it is quite complex, because we might have different nodes name and different IPs. For this reason, forward the request to the default Azure DNS will be a solution.
Don't use a Custom DNS for zone resolution .database.windows.net and add as a last Custom DNS server the IP of the default Azure DNS server.
If you want to know the name and the IP of this node you could take a network trace from the client machine. But, it is not a recommend solution.
Change the connection policy to Proxy.
In this situation, the redirection will be not take place and you could connect normally.