Cannot connect to on-premise SQL Server from Azure role VM

Question

I've followed the tutorial here http://msdn.microsoft.com/en-us/hh328536 but I get the following error when connecting from Powershell to the SQL server:

PS D:\Users\Administrator> $sql.Open()
Exception calling "Open" with "0" argument(s): "Timeout expired.  The timeout period elapsed prior to completion of the
 operation or the server is not responding."
At line:1 char:10
+ $sql.Open <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

 

I can successfully ping the server by its network name from the Azure role. I have also completely disabled the Windows firewall on the SQL machine to eliminate any interference. There's only one instance of SQL server running on that machine on the default port, 1433. I've enabled TCP/IP and Named Pipes everywhere in the SQL Configuration MMC. I don't know what else to try. Any suggestions?

Thanks,

Clement

Answer 1

Ok, I've just noticed that adding "Connect timeout=150" to the connection string fixes the problem. The question is: why is there such a big delay considering that the default timeout is 15 seconds?

Answer 2

Thanks to Microsoft Support I was able to fix my problem using these instructions: http://blogs.msdn.com/b/windows_azure_connect_team_blog/archive/2011/06/03/speeding-up-sql-server-connections.aspx.