Today one of my colleague came with an error when loading the cube which used the Managed Instance database to load the data. It was failing with the below error.
I have seen this multiple time that whenever someone face any issue when connecting to a SQL Server the first and only thing they try to fix is the permission, it is true that majority of connectivity issues with SQL Server is because of permission only but also you can also see it easily using the error. Whenever you see “A network-related or instance specific….” it is never because of permission at SQL Server end.
This time the issue was that they were trying to connect to the Managed Instance which was available to public connections but they were not using the correct connection string.
Once the connection string was corrected the load worked flawlessly.
The connection string with open public endpoint is the only way to connect to a Managed Instance which is outside the VN.