1

We have following config of Availability Group On-Premise:

server1.domain.com - primary
server2.domain.com - sync secondary
server3.domain.com - async secondary

sql-listener.domain.com - availability group listener DNS name

Recently we have setup Read Only Routing (ROR) Read Only Routing URLs:

server1 - TCP://server1.domain.com:1433
server2 - TCP://server2.domain.com:1433
server3 - TCP://server3.domain.com:1433

Read Only Routing Lists:

server1: server2, server3, server1
server2: server1, server3, server2
server3: server2, server1, server3

Issue/Question:

One of our Azure App Services (Application), is using hybrid connection to connect to on-premise AG Listener (sql-listener.domain.com) Hybrid connection settings:

Endpoint host: sql-listener
Entpoint port: 1433

Application is using connection string:

Server=sql-listener,1433;Initial Catalog=MyDatabase;User Id=SQLAuthLogin;    
Password=SQLPassword;App=MyApp;MultiSubnetFailover=True

And without "ApplicationIntent=ReadOnly" part, application CAN connect to on-prem DB just fine
When I am adding "ApplicationIntent=ReadOnly" part to connection string, app can't connect to on-prem db and throws error:

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: TCP Provider, error: 0 - No such host is known.)

Additional Info: Application is not integrated to any VNET
Application CANNOT connect when I replace "Server=sql-listener,1433" part by "Server=192.168.xxx.xxx" (AG Listener's IP Address)

After googling similar topics, I found that when app connects to AG Listener with "ApplicationIntent=ReadOnly", listener responds and provides readable secondary database server's address to Application (I assume it is "server2.domain.com" DNS address, not IP address ?)

So I created extra hybrid connections in Azure, that have endpoint hosts and port as "server1" "server2" "server3" and 1433, and made sure these hybrid connections are added to Application and connected to hybrid connection manager at on-prem server

I hoped this way "server1" "server2" "server3" DNS names would "become visible" to Application and when AG listener responds to Application (with conn string "ApplicationIntent=ReadOnly") option and directs it to "server2" (server2.domain.com), Application would be able to connect

But Application still throws error "A network-related or instance-specific error..."

What I am missing ?
How to make Read Only Routing work with Azure App Service that connects over Hybrid Connection ?

  • What is the output of "nslookup server1.domain.com" (additionally for server2 and 3 as well) both using the FQDN and shortname? – Sean Gallardy Aug 22 '19 at 18:57
  • Note: This should be run from the problematic client machine. – Sean Gallardy Aug 23 '19 at 2:24
  • @SeanGallardy I am not sure if this can be provided, since Azure App Service is Software as a Service and I do not have access to host that hosts the Application. Application accesses on-prem DB over Hybrid Connection (that creates sort of TCP tunnel). So no access to "problematic client machine"... – Aleksey Vitsko Aug 23 '19 at 6:55
0

Microsoft Support engineer replied below on my case:

I (Support engineer) have reached my technical leads to have an urgent update on this and I was able to get it.
Unfortunately, the Product Group confirms my initial suspicions about the setup you want to achieve not being possible due to Hybrid Connections limitations therefore not supporting this. It’s a by-design behavior and currently Hybrid Connections does not support this scenario. The Product Group engineers confirmed me that this feature is being considered however they have no ETA to share.

Hybrid Connections do not support AG read-only routing - as simple as that

I have created feature request on Azure website, please login and up-vote it if you can https://feedback.azure.com/forums/169385-web-apps/suggestions/38917648-enable-hybrid-connections-to-work-with-read-only-r

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.