Handling IP governence on Azure SQL server

Question

Hi All,

Currently many of our resources have contributor access on our RG's due to the ask by the client and this in turn leads to resources whitelisting multiple IP's on the server without the approval or governance of the DBA.

Is there any way wherein we can create an access policy to restrict users having contributor access in RG's from adding the IP's?

Also, is there any way to send a notification email/alert whenever an IP is added/deleted/updated?

Answer 1

You can create an Azure Policy to specify the list of IP addresses allowed.

{  
  "mode": "All",  
  "policyRule": {  
    "if": {  
      "allOf": [  
        {  
          "field": "Microsoft.Sql/servers/firewallRules/startIpAddress",  
          "notIn": "[parameters('listOfStartIpAddresses')]"  
        },  
        {  
          "field": "Microsoft.Sql/servers/firewallRules/endIpAddress",  
          "notIn": "[parameters('listOfEndIpAddresses')]"  
        }  
      ]  
    },  
    "then": {  
      "effect": "[parameters('effect')]"  
    }  
  },  
  "parameters": {  
    "effect": {  
      "type": "String",  
      "metadata": {  
        "displayName": "Effect",  
        "description": "Enable or disable the execution of the policy"  
      },  
      "allowedValues": [  
        "Audit",  
        "Deny",  
        "Disabled"  
      ],  
      "defaultValue": "Deny"  
    },  
    "listOfStartIpAddresses": {  
      "type": "Array",  
      "metadata": {  
        "displayName": "List of Start IP Addresses for SQL",  
        "description": "List of Start IP Addresses for SQL"  
      }  
    },  
    "listOfEndIpAddresses": {  
      "type": "Array",  
      "metadata": {  
        "displayName": "List of End IP Addresses for SQL",  
        "description": "List of End IP Addresses for SQL"  
      }  
    }  
  }  
}  

About getting notified when an IP is added see here how an Azure Logic App can help you with that.