Audit/Activity logs of Azure SQL database RRS feed

  • Question

  • Is there any way by which I can have an alert triggered whenever some user tries to access any table in Azure SQL database for which he/she doesnt have any access and gets the below error:

    The SELECT permission was denied on the object 'People', database 'POCDB', schema 'HighlyConfidential'.

    Can these query logs be captured?

    Tuesday, June 4, 2019 1:32 PM

All replies

  • Hi Nandan Hegde,

    AFAIK it's possible by following the below steps.

    1. Create (or use already existing) log analytics workspace as explained here.
    2. Enable SQL database auditing as explained here. Make sure you select logs destination as Log Analytics and provide your log analytics workspace name.
    3. Try your requirement i.e., try to access any table of your Azure SQL database for which a user doesn't have any access.
    4. Go to your log analytics workspace, route to Logs tile and run query something like shown below to fetch related logs. Note that xxxxxxxxxxxxxxxxxxxx should be replaced with the content of your error i.e., something like "the select permission was denied on the object", etc.

    AzureDiagnostics| where Category == 'SQLSecurityAuditEvents'| where additional_information_s contains "xxxxxxxxxxxxxxxxxxxx" | summarize AggregatedValue= any(additional_information_s) by Computer, bin(TimeGenerated, 30s) 

    5. Check the output of the above query to make sure the error is captured in the logs.
    6. Create a log analytics alert as explained here here

    Hope this helps!!

    Thursday, June 6, 2019 11:18 AM
  • Hello Krishna,

    I am getting the below error:

    'where' operator: Failed to resolve table or column expression named 'AzureDiagnostics' Support id: 2afb24c4-b0f5-4760-9319-eae3c79eb75e

    Can you please guide on this issue

    Thursday, June 6, 2019 1:18 PM