Connecting to an SQL Azure Database from SSMS - IP Restriction

Question

I am trying to connect to a new SQL Azure Database via SQL Server Management Studio. The client box running SSMS is hosted on a production DB so we restrict access on 1433 to just a handful of IPs using IP Sec. The client IP is configured on the Azure Portal for access to the Azure DB. I can only get access to the Azure DB from the client by opening 1433 publicly, which I'm not keen to do on account of their being a production DB on there.

I appreciate the cloud does not provide us with the certainty in terms of stable IPs. But we're only using this connection to perform some various admin tasks and we're happy to change the IP from time-to-time, as required.

But how can I know what IP Azure is responding on? When I ping {myserver}.database.windows.net I get an IP but when I add this to the IP Sec rule it still doesn't work. Is Azure responding on a different IP? If so how can I come to know what this IP address is as SSMS only throws a generic network error?

Additional Information:

The box running SSMS is not hosted within Azure. We are currently running this from another datacenter

Answer 1

You don't want to connect to Windows Azure SQL Database by using an IP address, you will want to use the logical servername you are assigned (something like fooxxxbar.database.windows.net).

Also, WASD only uses port 1433, you can't configure that (not today, anyway).

The firewall rules for WASD are for the client connections. When you first create your SQL database you will be prompted to create firewall rules and your current IP address will be listed as an example of an IP to allow.

HTH