Error on SharePoint portal if TFS is configured on a custom port for SQL Server

Recently, in one of my support incident I faced an issue with SharePoint portal where it shows a SQL connectivity error at the bottom of the page.

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Unable to determine when the data was last updated. More...”

During troubleshooting this issue, we verified the connectivity using the UDL file from SharePoint server to warehouse database and SharePoint server to Analysis services with SharePoint Application pool account and everything is fine. We didn’t find any useful information from SQL logs as well. Also, firewall was disabled.

Resolution:

We identified that the SQL instance for Team foundation server is on a custom port (not 1433) and Analysis services on a default port. When SharePoint tries to load the data, it connects to the Tfs_Warehouse database and Tfs_Analysis cube. You can see that the SQL instance for warehouse database like this

To resolve this issue, I have created a SQL alias on TFS Application tier server and gave the alias name in Reporting configuration in the Team Foundation Administration console as shown in the below screen.

Content Created by – Chandra Sekhar Viswanadha
Content Reviewed by – Romit Gulati