It appears there is a probable bug in the factory that handles SQL Connections to Microsoft Azure.Today while publishing a code first migration project to SQL Server I kept running into this problem every time I used Visual Studio’s publish method to “Test Connection” to my SQL Server or if I tried running update-database command from the NuGet package manager window
Cannot open server 'xxx' requested by the login. Client with IP address 'zzz.yyy.1yu.57' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.
Interestingly I was able to connect to the same server via SQL Server Management Studio. Quick search around the internet always pointed towards using the Azure Portal to set Firewall Rules. Now I was quite sure that my firewall rules were setup properly since I was after-all able to access the same database using the same client machine but using SQL Server Management Studio. In desperation I even tweeted AzureSupport who were kind enough to look into this and point me to some (rudimentary, yawn!) documentation.
Well long story short it turns out that there was a typo in the connection string I was using from within Visual Studio to connect to Azure DB. Definitely my bad! However if Azure had returned back a sensible error message and told me to go and check the credentials I am throwing its way instead of asking me to muck about with the Firewall settings this would have saved me a couple of hours of cursing my laptop and Azure.
So people if you are running into weird issues with connection requests declined follow the good old basics and check your connection strings. Don’t rely on Azure to tell you why it is angry with you. Just like with real life this is something you have to figure out yourself….now what did I do wrong this time, why won’t she talk to me!
As mentioned on twitter we have created a forum on http://aka.ms/FC1040467 to investigate further. Thanks
As mentioned on Twitter, we have created a forum post on http://aka.ms/FC1040467 to investigate this further. Thanks
Thank you very much.