CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

Don’t Rely On a Static IP Address for Your SQL Database

Don’t Rely On a Static IP Address for Your SQL Database

Rate This
  • Comments 2

I’ve seen a number of customers open support incidents because they couldn’t connect to their SQL Database server which was ultimately due to the incorrect assumption that the server’s IP address is static. In fact, the IP address of your logical server is not static and is subject to change at any time. All connections should be made using the fully qualified DNS name (FQDN) rather than the IP address.

The following picture from the Windows Azure SQL Database Connection Management Technet article shows the network topology for a SQL Database cluster.

image

Your logical server (e.g., with a FQDN of xyz.database.windows.net) resides on a SQL Database cluster in one of the backend SQL Server nodes. Within a given region (e.g., North Central US, South Central US, North Europe, etc) there are generally many SQL Database clusters, as required to meet the aggregate capacity of all customers.  All logical servers within a cluster are accessed through the network load balancer (the single blue block with the note saying “Load balancer forwards ‘sticky’ sessions…” in the diagram) via a virtual IP address.

If you do a reverse name lookup from your server’s IP address you will actually see the name of the cluster load balancer. For example, if I try to ping one of my servers (whose actual server name starts with ljvt in the screenshot below) you will see that the displayed name associated with the IP address is instead data.sn3-1.database.windows.net, where the sn3-1 portion of the name maps to the specific cluster in the region (South Central) hosting this server.

image

Microsoft may do an online migration of your logical server between clusters within a region, load balancing capacity across the clusters within the region. This move is a live operation and there is no loss of availability to your database during the operation. When the migration completes, existing connections to your logical server are terminated and upon reconnecting via fully qualified domain name your app will be directed to the new cluster.  However, if your application caches or connects by IP address instead of FQDN then your connection attempts will fail.

A migration moves all of your settings, including any SQL Database firewall rules that you have.  Consequently there are no Azure-specific changes that are required in order to connect.  However, if your on-premise network infrastructure blocks/filters outgoing TCP/IP traffic to port 1433—the port used for SQL connections—and you had it restricted to a fixed IP address then you may need to adjust your client firewall/router.  The IP address of your SQL Database server will always be a part of the address ranges listed in the Windows Azure Datacenter IP Ranges list.  You should allow outgoing traffic for port 1433 to these address ranges rather than a specific IP address.

Keith Elmore – Principal Escalation Engineer

Leave a Comment
  • Please add 1 and 2 and type the answer here:
  • Post
  • OK then help us out with how to get from inside a corp firewall and proxy out to our SQL Azure database to manage and update it.

    for example our company DBA needs to connect to the server....  and our proxy / firewall wants mapping of IP addresses not DNS names. I think we would understand an added cost for making that happen.

  • Current Azure security is vulnerable to data ex-filtration as I can't limit the egress traffic from my Azure VMs to what is absolutely necessary (DB, Blob, etc.) - I must open towards full Azure IP scope which contains other VMs belonging to anyone (read intruder).

    To me Azure services are not PCI DSS 3.0 complaint and are vulnerable. This limitation is of a high risk to the environment.

    Furthermore, even Network Security Groups (NSG) do not provide means to restrict traffic to specific services only.

    The whole problem could be easily resolved if MS published IP ranges used by each service - then configuration of FW rules would become at least possible (still not easy as new ranges are being added all the time).

Page 1 of 1 (2 items)