SQL Database as a Service is a very cool feature of Windows Azure Pack.
Because the SQL servers are probably internet facing you want to deploy them as secure as possible, maybe even in a different VLAN/Subnet than your WAP servers.
“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)”
You receive this error in Windows Azure Pack because your WAP servers and SQL servers are in other broadcast domains.
When you add your SQL Availablity Group in WAP it first makes a connection over TCP/IP.
When it reads the members of the Availablity Group, it tries to resolve the hostnames over NETBIOS/Named Pipes, this fails because there servers are not in the same broadcast domain.
I did some packet sniffing with WireShark and did see the following:
When servers are in the same broadcast domain:
You can there is a packet going out to the broadcast IP (.255) for the name “SQL12”, and a response back from my SQL server called “SQL12”.
When a try the same with a SQL server that is not in the same broadcast domain:
It keeps trying to send broadcast packets, but never receives a response back.
And then the connection from the WAP admin portal fails.
I’m communication with someone from the WAP team to get this fixed, although I’m not sure this is something that needs to be fixed in WAP.
Put your SQL Server node names in the hostfile of your WAP Admin servers.
If you have any questions, leave a comment!