4

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"

ppau2004
  • 193
  • 2
  • 3
  • 16
  • I don't understand that connection string. You should have something like `server=tcp:hostname` somewhere in there. `hostname` should be the same as your SQL server's Computer Name, also same as what you define as host in your Hybrid Connection setup in the Azure Portal. The "Connection PC" acts as a reverse proxy with HCM installed. – evilSnobu Oct 20 '17 at 10:43
  • Making that a bit more clear: `Connection string server == SQL Server Computer Name == hostname definition in HC setup in the portal`. Are you using a named instance by any chance? That's gonna be a different port, and you'll have to add it in the connection string, also make is static in the SQL Server configuration (it's dynamic by default and picked when the server starts up). – evilSnobu Oct 20 '17 at 10:52
  • Hi evilSnobu, you are correct. it is named instance. so I changed dynamic port to static port in SQL server configuration manager. But it still does not work. – ppau2004 Oct 23 '17 at 03:46
  • Did you also use that port in the HC setup in the portal and in the connection string? – evilSnobu Oct 23 '17 at 04:36
  • other things to check: (1) the endpoint host of the hybrid connection in Azure should be the hostname of the SQL machine (i.e. not the Connection PC hostname), (2) mention the SQL port in the connection string (e.g. `"Data Source=SQLHOST\InstanceName,1433..."`) – CyberDude Nov 17 '17 at 14:02

2 Answers2

2

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.

Tayyab
  • 1,207
  • 8
  • 29
0

Since you have a hybrid connection, your connection string should have the endpoint name of your hybrid connection as the data source. Try replacing it.

Renzo Robles
  • 684
  • 6
  • 10