Performing an Azure SQL Security Audit with AzSDK

I have really been enjoying being an MVP.  One of the great perks about this is access to mailing lists where all the MVPs can discuss relevant topics to the different Microsoft areas in scope.  One email thread got me to look at the Azure AzSDK project on github.  This project seems to have a bunch of Microsoft contributors, and is focused on building scripts to both report and remediate on security baselines in Azure.  The goal of this post is to show how to apply this project against your Azure SQL resources.

The first thing to cover here is that the AzSDK is focused on the Azure side of any given resource.  That is to say, it will investigate (via the APIs) how your resource is configured in Azure against recommended best practices.  In the case of SQL, for example, it will not "log in" to the server to do any checks at the sql level.

Step 1 in the process is obviously to install the module.  You can find detailed instructions in their posted Installation Guide.  What I like about the guide is it's focus on not having to use elevated permissions in powershell to get the project up and going. One key note here is to ensure you have the correct version of the AzureRM modules installed.

After installation, you can simply run the built-in set of command-lets against your Azure resources.  As always, I recommend reading the code before running it to get an idea of what it is doing.  There is a lot of bootstrapping code in the modules, which eventually targets JSON files that have all the rules to apply defined.

Here is an example of one of the SQL rules:

   {
      "ControlID": "Azure_SQLDatabase_Audit_Enable_Logging_and_Monitoring_DB",
      "Description": "Enable SQL Database audit with selected event types and retention period of minimum $($this.ControlSettings.SqlServer.AuditRetentionPeriod_Min) days",
      "Id": "SQLDatabase140",
      "ControlSeverity": "Medium",
      "Automated": "Yes",
      "MethodName": "CheckSqlDatabaseAuditing",
      "Rationale": "Auditing enables log collection of important system events pertinent to security. Regular monitoring of audit logs can help to detect suspicious and malicious activities early enough.",
      "Recommendation": "Run command  Set-AzureRmSqlDatabaseAuditingPolicy -ResourceGroupName '{ResourceGroupName}' -ServerName '{ServerName}' -DatabaseName '{DatabaseName}' -StorageAccountName '{StorageAccountName}' -EventType 'All' -AuditType 'Blob' -RetentionInDays $($this.ControlSettings.SqlServer.AuditRetentionPeriod_Min). Refer: https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqldatabaseauditingpolicy",
      "Tags": [
         "SDL",
         "TCP",
         "Automated",
         "Audit",
         "SOX",
         "SqlDatabase"
      ],
      "Enabled": true,
      "FixControl": {
         "FixMethodName": "EnableDatabaseAuditingPolicy",
         "FixControlImpact": "Low",
         "Parameters": {
            "StorageAccountName": ""
         }
      }
   },

I like that it is extensible, and allows you to potentially add your own rules if required.

Running the scan is pretty easy.  You can follow the instructions here and simply target the resource group you would like to evaluate.  Here is the output from the command on one of my resources in production.

Oh my, that is a lot of failures.  You can find detailed reports in a CSV that is stored on disk (see the last line of the screenshot above).  Upon review, it seems like all of my failures were due to auditing not being turned on at the database level.  Of course, auditing is turned on at the server level, and, as per the docs, this covers all databases.  Seems like this test could use some improvement.

In any event, I really like where this project is going and will be following it closely. I can see adding this type of automated check in a build/release pipeline to ensure at least the baselines are covered.  There is a LOT more that this project can do, so be sure to check it out if you are interested.