You may have an Azure SQL misconfigured allowing any address to connect, this is a bad idea and needs to be fixed. The problem comes when you need to limit your firewall and don’t know what addresses normally connects to your database. If you miss an undocumented address you will face connectivity problems.

So, before we do this we need to make and investigation to see which clients are connecting to the database.

First, we need to enable auditing. Remember to enable auditing on database server-level rather than database-level because the firewall is set on the database server.

In this example, we are using Table storage as our auditing log destination. For this to work we need to save the logs to a storage account. If you have not already an suitable storage account create one.

If you set a retention on the logs, the table name will be in a date format. My script is made to work with this type of naming. Note the table name and then go ahead and save the settings.

Now we wait. Wait long enough to ensure any type of normal activity will occur.

Download the script from my github.

https://github.com/Altitude365/Altitude-Public/blob/master/Tools/Azure%20SQL%20Resize/Azure%20SQL%20Resize.ps1

Open you copy of the script and edit the fist lines of the code.

$StorageAccountName = "StorageAccountName"
$StorageAccountKey = "StorageAccountKey"
$Tablename = "SQLDBAuditLogsxxxxx" #This is only the first two parts of the table name, not the date part
[string[]]$dates = @( #A string array of all the dates you want to analyze
"20161115"
"20161116"
)

Run the script to analyze the logs. The script will return two Out-GridView’s. Ine is a summarization of all the different client (see picture below) and the other one in a complete table of all the logs. Examen and validate each address and add the known (normal) once to the database server firewall.

Warning! If you have a lot of traffic to your database, you may need to filter the logs to make the script run faster.

This is just to give you an Idea of that’s going on. After your investigation is done you can safely configure the firewall without causing interruptions.

Another good thing is to enable threat detection on you Azure SQL DB Server. Then you will receive e-mail alerts like this one when something suspicious is occurring.

Finally put a lock on your database to protect it from accidental deletion. J

Stay safe and follow me on twitter for more awesome tips @MeapaX