Associate static IP to a Azure SQL server/database

Question

I need to associate a public static IP address to points to an Azure SQL server, the server only has DNS name but doesn’t have IP address. I already tried Azure Load Balancer, Trafic Manager and DNS, and in any of the services there is an option to associate a static IP that point to the Data Base Name, for example the DB name is: testnetmarksrv.database.windows.net Any options…?

Answer 1

This architecture provides the answer Azure SQL Database gateway IP addresses . The table with the Public IP addresses for each region is what you will need to use for your firewall configuration with the Public IP address of the gateway as the destination address on your firewall and also allowing traffic outbound on port 1433 as this is the only port that the gateway will listen on. I performed a couple of test to ensure that mydatabase.windows.net resolves to the IP address displayed in the table and it seem to have worked for me. I dont know if this is the official record Microsoft has for listing the public IP addresses for their IP addresses as I could not see some of the newer data centers on that list. The other thing that you will need to do is to set your Azure SQL Datase connection to Proxy mode. The following powershell will set that.

Get SQL Server ID

$sqlserverid=(Get-AzSqlServer -ServerName sql-server-name -ResourceGroupName sql-server-group).ResourceId

Set URI

$id="$sqlserverid/connectionPolicies/Default"

Get current connection policy

(Get-AzResource -ResourceId $id).Properties.connectionType

Update connection policy

Set-AzResource -ResourceId $id -Properties @{"connectionType" = "Proxy"} -f

I am not sure about why you have a requirement to limit outbound SQL traffic but will urge you to also have a look at restricting inbound connections to your Azure SQL Databases. This should be done on the Azure SQL Server that your Azure SQL Database lives on. Below are some resources to help to configure a Azure SQL Server firewall rule/s and provides a number of methods to set firewall rules including through the protal, through SQL and through the commandline. Azure SQL Database and SQL Data Warehouse IP firewall rules

Hope this helps!

Answer 2

No. You cannot get a static IP address assignment for your Azure SQL Database. Moreover, what you refer (testnetmarksrv.database.windows.net) designates your Azure SQL Database Server, not a single Database. This is a logical server, in which you can put up to 149 Databases (150 with the Master DB).

You have to workaround your requirement for static IP address assingment to work with the DNS Name (testnetmarksrv.database.windows.net).