Database Blob Auditing Policy using .NET C#


We have a good example on how to use C# to create an Azure SQL database with the Microsoft Azure SQL Management Library for .NET at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-get-started-csharp

Let extend this example and see how to set database blob auditing policy:

// Modify the blob auditing policy properties, send and receive and see it its still ok
IList<string> auditActionsAndGroups = new List<string> {
  "BATCH_COMPLETED_GROUP"
  ,"SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP"
  ,"FAILED_DATABASE_AUTHENTICATION_GROUP"
  //,string.Format("INSERT on database::{0} by public", databaseName)
  //,string.Format("UPDATE on database::{0} by public", databaseName)
};

DatabaseBlobAuditingPolicy updatedDatabasePolicy = new DatabaseBlobAuditingPolicy
{
  State = BlobAuditingPolicyState.Enabled,
  RetentionDays = 180,
  StorageAccountAccessKey = "{removed}",
  StorageEndpoint = "https://{removed}.blob.core.windows.net/",
  AuditActionsAndGroups = auditActionsAndGroups,
  StorageAccountSubscriptionId = _subscriptionId,
  IsStorageSecondaryKeyInUse = false
};

sqlMgmtClient.Databases.CreateOrUpdateBlobAuditingPolicy(resourceGroupName, serverName, 
  databaseName, updatedDatabasePolicy);

var getDatabaseBlobAuditingPolicy = sqlMgmtClient.Databases.GetBlobAuditingPolicy(resourceGroupName, 
   serverName, databaseName);

Please replace the {removed} with your blob storage account parameters.

The recommended set of action groups to use is the following combination - this will audit all the queries and stored procedures executed against the database, as well as successful and failed logins:
    “BATCH_COMPLETED_GROUP“,
    “SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP“,
    “FAILED_DATABASE_AUTHENTICATION_GROUP“

This above combination is also the set that is configured by default when enabling auditing from the Azure portal. You can review the supported groups and actions using the REST API documentation page.

 

REST API - Database Blob Auditing Policy
https://msdn.microsoft.com/library/azure/mt695939.aspx

PowerShell cmdlets - Database Blob Auditing Policy
https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqldatabaseauditingpolicy?view=azurermps-4.1.0

 

SQL Threat Detection

It's also possible to configure SQL Threat Detection. SQL Threat Detection continuously monitors databases for potentially harmful attempts to access sensitive data. SQL threat detection provides a new layer of security, which enables customers to detect and respond to potential threats as they occur by providing security alerts on anomalous activities. Users will receive an alert upon suspicious database activities, potential vulnerabilities, and SQL injection attacks, and anomalous database access patterns. SQL threat detection alerts provide details of suspicious activity and recommend action on how to investigate and mitigate the threat. Users can explore the suspicious events using SQL Database Auditing to determine if they result from an attempt to access, breach, or exploit data in the database. Threat detection makes it simple to address potential threats to the database without the need to be a security expert or manage advanced security monitoring systems.

// Modify the threat detection policy properties, send and receive and see it its still ok

DatabaseSecurityAlertPolicy securityAlertPolicy = new DatabaseSecurityAlertPolicy
{
  State = SecurityAlertPolicyState.Enabled,
  EmailAccountAdmins = SecurityAlertPolicyEmailAccountAdmins.Enabled,
  EmailAddresses= "admin01@contoso.com;ellen@contoso.com",
  RetentionDays = 180,
  StorageAccountAccessKey = "{removed}",
  StorageEndpoint = "https://{removed}.blob.core.windows.net/"
};

sqlMgmtClient.Databases.CreateOrUpdateThreatDetectionPolicy(resourceGroupName, serverName, 
   databaseName, securityAlertPolicy);

var getDbSecurityAlertPolicy = sqlMgmtClient.Databases.GetThreatDetectionPolicy(resourceGroupName,
   serverName, databaseName);

Please replace the {removed} with your blob storage account parameters.

 

REST API - Threat Detection
https://msdn.microsoft.com/en-us/library/azure/mt652113.aspx

PowerShell cmdlets - Threat Detection
https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqlserverthreatdetectionpolicy?view=azurermps-4.1.0

 

(updated at 2017/06/14)

Comments (0)

Skip to main content