Search code examples
azureconnectionhybrid

Azure hybrid connection Manager does not work

Requirement

I have an On-Premise SQL Server and an Azure webApp Service that needs to talk each other. I installed Hybrid Connection Manager on another computer which is in the same LAN as the SQL server. Let's refer this computer as the Connection PC.

The working part

  • Installation and configuration was straight forward, status of the hybrid connection showing as connected.
  • Tested the WebApp on localhost, it worked fine. Which means nothing wrong with the asp.net code
  • SSMS on the connection PC can access and query SQL DB
  • I installed a test SQL DB on the connection PC itself and use the WebApp to talk to the test DB instead, It worked fine.

The Problem

After tested ok on localhost, I published ASP.Net WebApp to Azure, it gave following error:

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)

troubleshoot attempt

  • Does that mean Hybrid Connection manager has to be installed on the same computer that runs SQL Server?
  • I did attempt to install HCM on the SQL Server, but it says it cannot work with Win2008R2. The connection PC has win2012 on it. So it can have HCM. I just do not have time to migrate the SQL Server to another server. So I hope HCM does not have to be on SQL server.

Connection String

In my webapp, the connection string is as follows.

connectionString="metadata=res:///ScaleDataModel.csdl|res:///ScaleDataModel.ssdl|res://*/ScaleDataModel.msl;provider=System.Data.SqlClient;provider connection string="data source=WIN2008R2\DataPlus;initial catalog=DataPlus;persist security info=True;user id=test;password=testpass;multipleactiveresultsets=True;application name=EntityFramework"" providerName="System.Data.EntityClient"

Solution

  • Hybrid Connection communicates via TCP ports thus you need to specify the listening static port in your connection string like:

    Data source=ServerName:Port;

    Also here is an actually detailed article about the all problems you may encounter while working with Hybrid Connections.

    Hope this helped you.