Azure hybrid connection not working with SQL Server Named Instance

3
votes

I have tried to create a hybrid connection for my on premises SQL Server running on Windows Server 2012 with a named instance. I did provided my server address as the endpoint along with the 1433 port. I have also configured the SQL Server to static ports and also allowed outbound connections to these ports (9350 – 9354, 5671, 80, 443) as per documentation. However, when I try to access the database from my azure web application I get the "A connection was successfully established with the server, but then an error occurred during the pre-login handshake." (Full stack trace mentioned in comments).

However, when I try to create the hybrid connection to another on-premises SQL Server with default instance, with the same settings as above, it works fine.

Could not find a strong enough explanation for this behavior so far.

2
sql-serverazuretcpazure-web-app-servicewindows-server-2012
Full Stack Trace:"A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)....." - Tayyab
Do these SQL Servers both run on windows server 2012? - Wayne Yang
Yes each do run on a separate Windows Server 2012. - Tayyab
Are you sure your named instance runs on 1433/TCP? It usually picks up a dynamic port, which you can change to a static. I've also seen that stack trace when i was calling the SQL server by a DNS alias, not by its Computer Name (e.g. SQLSRV004). - evilSnobu
I have tried the both of the solutions you mentioned but yet no success - Tayyab

2 Answers

1
votes

After quite some research and experimentation finally I was able to work out the solution for the problem. Actually one need to enable TLS 1.0 (from windows registry) for both your client and server.

Following are the correct value for registry to have your named instance work out:

  • [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0]
  • [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client] "DisabledByDefault"=dword:00000000 "Enabled"=dword:00000001
  • [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server] "DisabledByDefault"=dword:00000000 "Enabled"=dword:00000001

Also note that Hybrid connection communicates via ports so be careful to see that if you have multiple instance of your SQL Server make sure each is listening at a different static TCP port.

1
votes

For anyone else having this issue who does not want to mess with Windows registry keys, here is another answer based on @evilSnobu's comment.

I was working with SQL Server 2017 and a named database instance. I had to go into SQL Server Configuration Manager, go to "SQL Server Network Configuration" > "Protocols for [instance of db]" > right-click on TCP/IP > Properties. Go to the bottom of the "IP Addresses" tab, remove the "TCP Dynamic Ports" value and fill in "TCP Port" with 1433.

Lastly, restart the SQL Server instance. And also worth mentioning is that, if you're using a connection string, make sure the instance name is specified.

SQL Server Network Configuration TCP/IP Properties