Saturday, December 30, 2017

Azure Storage Firewalls and Azure SQL Audit

In a previous post, I started to take a look at the use of Azure Storage firewalls.  We noted that the firewalls seemed to work quite well for protecting against IP address access.  Further, it was noted that you can whitelist certain Azure Trusted Services, which was a fairly limited list.

Okay, so lets test this with Azure SQL Auditing.  The test for this is quite simple.

Steps:
  • Enable Azure SQL Auditing to a target storage account
  • Do some logins
  • Use the audit viewer in Azure SQL to review the audit
  • Enable Azure Storage Firewall
  • Do some more logins
  • See if these logins appear in the audit
 It turns out that when the firewall is enabled, Azure SQL audit cannot write to its audit file.  This is super apparent as the blade itself stops responding.  Here is what it looks like:






When you allow the firewall access from all networks, you can then see the log again.  You will also notice that the logins during the time when the firewall was enabled are not showing up (or, in other words, were never written).  You can see this via the missing timestamps.




It seems weird services such as Azure SQL (at least the audit part) would not be part of the trusted services.  IMHO, it makes some of the use cases for storage firewalls not possible.  Storage accounts that host things like audit and ASC are not ones that you want to be public and a firewall would make sense.


Sunday, December 17, 2017

Fooling around with Azure Storage Firewalls

At the end of September, the Azure storage team announced support for virtual network connected storage.  In the days before managed disk, this was actually quite a big deal.  A lot of my customers got tripped up on the fact that VHDs are stored in Azure storage, and that the container itself could be accessed from anywhere provided someone had the correct name and key.

Azure storage makes use of Azure storage firewalls to allow customers to limit access to a storage account either by subnet/vnet or by any publically addressable IP address (or range).  The goal of this post is to explore this feature in a little more detail.

When you first create an Azure storage account, the default operation is allow access from all vnets/subnets.  In fact, firewall setup is not currently part of the storage account creation process in the portal.  Here is what the firewall tab looks like out of the gate:




Okay, cool.  I can open up storage explorer and add a file no problem.  Lets try setting the "default rule" to deny.  What this will do, essentially, is limit access to my storage account from anywhere.

Here is the powershell command I ran:

Update-AzureRmStorageAccountNetworkRuleset -ResourceGroupName  -Name  -DefaultAction Deny

Here is the result in the portal.


Lets try uploading something in Storage Explorer.  Please note that I did not "log out and log back in" in storage explorer.  Oops! I got an error.

{
  "name": "StorageError",
  "message": "Forbidden",
  "stack": "Error\n    at Function.StorageServiceClient._normalizeError (C:\\Program Files (x86)\\Microsoft Azure Storage Explorer\\resources\\app\\node_modules\\azure-storage\\lib\\common\\services\\storageserviceclient.js:1189:23)\n    at Object.StorageServiceClient._processResponse (C:\\Program Files (x86)\\Microsoft Azure Storage Explorer\\resources\\app\\node_modules\\azure-storage\\lib\\common\\services\\storageserviceclient.js:736:50)\n    at Request.processResponseCallback [as _callback] (C:\\Program Files (x86)\\Microsoft Azure Storage Explorer\\resources\\app\\node_modules\\azure-storage\\lib\\common\\services\\storageserviceclient.js:311:37)\n    at Request.self.callback (C:\\Program Files (x86)\\Microsoft Azure Storage Explorer\\resources\\app\\node_modules\\azure-storage\\node_modules\\request\\request.js:188:22)\n    at emitTwo (events.js:106:13)\n    at Request.emit (events.js:194:7)\n    at Request.<anonymous> (C:\\Program Files (x86)\\Microsoft Azure Storage Explorer\\resources\\app\\node_modules\\azure-storage\\node_modules\\request\\request.js:1171:10)\n    at emitOne (events.js:96:13)\n    at Request.emit (events.js:191:7)\n    at IncomingMessage.<anonymous> (C:\\Program Files (x86)\\Microsoft Azure Storage Explorer\\resources\\app\\node_modules\\azure-storage\\node_modules\\request\\request.js:1091:12)",
  "code": "Forbidden",
  "statusCode": 403,
  "requestId": "xxxxxxxx"
}

Awesome, a pretty standard forbidden error message.  Okay, lets try adding my ip address so I can communicate. You can do this by running the following command:


Add-AzureRmStorageAccountNetworkRule -ResourceGroupName  -Name  -IPAddressOrRange ""

Now when I try and upload, everything works.  I was simply able to hit retry and it worked.  I now have a "secured" storage account!

One interesting thing to note is that even when the default action is set to deny, the checkbox for "Allow trusted Microsoft services to access this storage account" is enabled.  Based on the documentation there is only about 5 services that are granted access.  In a future post, I hope to see how this checkbox handles diagnostic/audit storage for things like ASC, Azure SQL, etc.

  
In this post, we covered some basics of using Azure Storage firewalls to secure your storage accounts.




Saturday, December 9, 2017

Azure ARM Template for Service Health

There have been a few changes to how Azure is surfacing health data for the platform itself.  For more information on a new service in preview, please see this link.  Monitoring the platform for health issues is an important part of any overall monitoring strategy.

In the examples, they provide a walk through of how to enable this service using the portal.  This post expands on that by providing an ARM template to do the same thing (in this case an email alert).

You can apply this to any of your subs.

Enjoy!

{
    "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "actionGroups_name": {
            "defaultValue": "SubHealth",
            "type": "String"
        },
        "activityLogAlerts_name": {
            "defaultValue": "ServiceHealthActivityLogAlert",
            "type": "String"
        },
        "emailAddress":{
            "type":"string"
        }
    },
    "variables": {
        "alertScope":"[concat('/','subscriptions','/',subscription().subscriptionId)]"
    },
    "resources": [
        {
            "comments": "Action Group",
            "type": "microsoft.insights/actionGroups",
            "name": "[parameters('actionGroups_name')]",
            "apiVersion": "2017-04-01",
            "location": "Global",
            "tags": {},
            "scale": null,
            "properties": {
                "groupShortName": "[parameters('actionGroups_name')]",
                "enabled": true,
                "emailReceivers": [
                    {
                        "name": "[parameters('actionGroups_name')]",
                        "emailAddress": "[parameters('emailAddress')]"
                    }
                ],
                "smsReceivers": [],
                "webhookReceivers": []
            },
            "dependsOn": []
        },
        {
            "comments": "Service Health Activity Log Alert",
            "type": "microsoft.insights/activityLogAlerts",
            "name": "[parameters('activityLogAlerts_name')]",
            "apiVersion": "2017-04-01",
            "location": "Global",
            "tags": {},
            "scale": null,
            "properties": {
                "scopes": [
                    "[variables('alertScope')]"
                ],
                "condition": {
                    "allOf": [
                        {
                            "field": "category",
                            "equals": "ServiceHealth"
                        },
                        {
                            "field": "properties.incidentType",
                            "equals": "Incident"
                        }
                    ]
                },
                "actions": {
                    "actionGroups": [
                        {
                            "actionGroupId": "[resourceId('microsoft.insights/actionGroups', parameters('actionGroups_name'))]",
                            "webhookProperties": {}
                        }
                    ]
                },
                "enabled": true,
                "description": ""
            },
            "dependsOn": [
                "[resourceId('microsoft.insights/actionGroups', parameters('actionGroups_name'))]"
            ]
        }
    ]
}


Tuesday, December 5, 2017

Azure SQL Database Firewall Rules

Today I had a use case where I wanted to grant external access to one of my Azure SQL databases that was hosted in an elastic pool along with other client databases. 

If you are unaware, in Azure SQL, there are two types of firewalls.  The first is at the server level, and applies to all databases hosted on a particular server.  The second is a "database" firewall, where rules are configured within a given database an only applies to that database.  The second method is particularly useful when using Azure SQL and opting for a "database-contained" approach. 

You can read more about the different database firewall options and the order of operations here.

In any event, database contained firewall rules fits my need as I want to grant access to only a single database on a particular server.

To view the firewall rules currently in place:
select * from sys.database_firewall_rules

To add a new rule:
execute sp_set_firewall_rule @name = 'fwrule' @start_ip_address = '<ip>', @end_ip_address  = '<ip>'

To remove a rule:
execute sp_delete_database_firewall_rule @name = 'fwrule'

One key thing to note is that database firewall rules do not appear on the Azure portal, and are probably not reviewed in either Azure Security Center, Threat Detection, or otherwise.  As far as I can tell, these events are also not part of SQL diagnostics.  The only service that might capture them is the audit, but those would need to be reviewed manually.

Remember to actively review your databases for these types of changes and limit who can make them.