Azure alert when SQL firewall rule added

Question

Is there a way to get an alert when a firewall rule is added to an Azure SQL database? I checked the monitoring blade and didn't see a metric or log event for this.

Answer 1

You can do this using the Activity Log. If you add a new firewall rule and check the Activity Log after a few minutes you'll see a series of entries with the operation name Update SQL server firewall rules. Click on the one with a status of Succeeded. The next page will have a New alert rule button.

You'll need to edit the condition as it sets the initiator to your username. To do this, click the condition and change the Event Initiated by value.

Answer 2

You can use a logic app to achieve that:

1. Create a new logic app using "When a HTTP request is received" template. Use the following JSON as your sample payload. Then you will need to add a condition and an action (email?). In email body you can send more details like caller email, caller ip, etc.

Conditions should be Status=Activated and OperationName="Microsoft.Sql/servers/firewallRules/write". 2. create a new alert in Monitor. The resource will be your SQL Server and the signal will be "All Administrative operations". Create a new action group and set an webhook action. Use the logic app webhook url generate in the previous step.

{
    "headers": {
        "Connection": "Keep-Alive",
        "Expect": "100-continue",
        "Host": "abc.logic.azure.com",
        "User-Agent": "IcMBroadcaster/1.0",
        "X-CorrelationContext": "abc",
        "Content-Length": "1350",
        "Content-Type": "application/json; charset=utf-8"
    },
    "body": {
        "schemaId": "Microsoft.Insights/activityLogs",
        "data": {
            "status": "Activated",
            "context": {
                "activityLog": {
                    "channels": "Operation",
                    "eventSource": "Administrative",
                    "eventTimestamp": "2019-03-09T10:00:36.549+00:00",
                    "eventDataId": "eventid",
                    "level": "Informational",
                    "operationName": "Microsoft.Sql/servers/firewallRules/write",
                    "properties": {
                        "originalEventTimestamp": "03/09/2019 10:00:17",
                        "correlationId": "correlationId",
                        "eventId": "eventId",
                        "eventName": "OverwriteFirewallRules",
                        "operationName": "Microsoft.Sql/servers/firewallRules/write",
                        "status": "Succeeded",
                        "description_scrubbed": "description",
                        "caller": "useremail@email.com",
                        "callerCredentialType": "LiveId",
                        "eventChannel": "OperationLogStore",
                        "ipAddress": "useripaddress",
                        "eventSource": "SQL Databases Event Supplier"
                    },
                    "resourceId": "resourceId",
                    "resourceGroupName": "groupname",
                    "resourceProviderName": "MICROSOFT.SQL",
                    "status": "Succeeded",
                    "subscriptionId": "subscriptionId",
                    "submissionTimestamp": "2019-03-09T10:00:36.549+00:00",
                    "resourceType": "microsoft.sql/servers"
                }
            },
            "properties": {}
        }
    }
}