Windows Azure Client with IP address 'XXX.XXX.XXX.XX' is not allowed to access the server

with new version of windows azure only go to firewall settings and click on add client IP and you will be okey. see picture bellow

If you want to manage SQL Azure from the Azure Portal you have to check the "Allow other Windows Azure Services to connect to this server". That is also required later for your Web/Worker roles deployed to Azure:

If you want to connect from your home/work PC, you have to keep the firewall up-to-date with your public IP address! Your public IP Address is in the little popup windows, which pops out when you want to add new firewall rule:

As Azure keeps changing, so do the ways to get to the firewall.

Click your SQL server (not the database, but the server) -> click firewall -> add the IP range you want to allow.

Dont forget to click save!