Jan 25 2019
- last edited on
Apr 07 2022
My customer wants to write custom Log Search Queries (in Log Analytics) for Azure SQL for the following Scenarios:
• Log failures, manual logging shut down and attempts to purge
• Attempts to access OS functionality via the database
• Known attack profiles, such as Buffer overflow, Denial of Service, SQL inject
• Use of the Application ID (ApplID) from a source other than the defined owner Application location (based on host name or IP address of App / Reporting Server)
Please Note: I know Advanced Threat Protection covers some of the scenarios mentioned here e.g. detecting SQL Injections, etc… But the customer wants custom queries for all of these scenarios.
I have the following Questions:
• Which AUDIT GROUPS should I enable to capture more Logs(apart from the 3 that are enabled by default) so that I can write queries for the above use cases using KQL on the logs collected ?
• If we keep ATP aside and assume that SQL Server is running on a VM in Azure, how would we achieve the above use cases based on the logs collected via the MMA agent installed on the VM ?
• The customer is using these custom queries to get appropriate result set and in turn to create PowerBI Dashboards which they want to share with their customers, how can I get ATP data/ recommendation outside the Azure Portal so that customer can create visualizations on top it and share with it’s customers.
• Please Note: I have seen Azure Security Centre REST API Documentation and I know I can pull Recommendations and Tasks using these APIs, but that’s not what the customer is looking for. Customer wants the underlying data and a custom query on top it which detects the security incident. I know these incidents are generated by complex ML algorithm running under the hood, but I hope I was able to put across the customer’s expectation clearly.
Please let me know your inputs on what’s possible and pointers on how to achieve it.