First published on MSDN on Jun 12, 2017
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:
[code language="csharp"]
// 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);
[/code]
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-azurermsqldatabaseauditingpol...
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.
[code language="csharp"]
// 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);
[/code]
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-azurermsqlserverthreatdetecti...
(updated at 2017/06/14)
