I am setting up a virtual server network on Azure.
1) Active Dir - Windows Server 2012 Datacenter
2) SQL Server - Windows Server 2012 Datacenter
3) App Server - Windows Server 2008 R2 Datacenter
4) App Server - Windows Server 2012 Datacenter
All servers visible in Active Directory & All servers have IP's
SQL Server firewall setup in/out rules for sql default port 1433
App Servers can ping the SQL Server using machine name and IP
I have confirmed SQL Server is listening on port 1433 using Port Query utility.
To test SQL Connectivity I create a connection.udl file on each app server and double click it to open the UI.
I then configure it to connect to the SQL server using machine name with the MS OLE DB Provider for SQL Server and click the "Test the Connection" button which returns error:
---------------------------
Microsoft Data Link Error
---------------------------
Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found.
---------------------------
OK
---------------------------
I then change the configuration to use the IP address of the SQL Server instead of the machine name and the connection succeeds.
What settings should be altered on any or all of these machines to be sure the connection succeeds using the machine name? This is very odd considering the app servers can ping the sql server using IP or machine name.