I’ve heard a few instances of people having difficulty using WA Connect to reach their on-premises SQL Server from a web or worker role, so I thought I’d provide a few tips:
1. Configure your role for access via Remote Desktop, remote into a role instance
2. Confirm that you can ping from web role to SQL server and vice versa.
a. If you can’t ping, check the firewall rules
3. Confirm that you can reach the SQL server on its port 1433
a. From your web role run: telnet <SQL-SERVER-NAME> 1433 – if you get an error:
i. Check the firewall rules on SQL server, ensure tcp 1433 inbound is allowed.
ii. Make sure that SQL server is configured to accept remote connections.
4. Increase the timeout on your SQL connection string. For example:
connectionString = "Data Source=(local);Database=AdventureWorks; Integrated Security=SSPI;Connection Timeout=60”
Also keep in mind that you are traveling a longer distance to reach your SQL Server than you would if it were in your datacenter, so understand that the simple physics of network latency could hamper performance.