Unable to see SQLSecurityAuditEvents logs for ResourceType MANAGEDINSTANCES

Shinde, Balaji 116

Hi All,

We have enabled Diagnostic setting on our SQL Managed instances using Azure policy. But we are not receiving the SQLSecurityAuditEvents for ResourceType MANAGEDINSTANCES. We are getting logs for ResourceType SERVERS/DATABASES, but not for MANAGEDINSTANCES.

attached is the capture.jpg screenshot of one sql maanged instance DiagnosticSetting Page:

So the question is, are the SQLSecurityAuditEvents for ResourceType MANAGEDINSTANCES available in log analytic workspace, if yes, how do we see them?

Below is the query we are using, we get no results.

AzureDiagnostics
| where ResourceProvider =~ "Microsoft.Sql" and Category =~ "SQLSecurityAuditEvents" and ResourceType =~ "MANAGEDINSTANCES"

GeethaThatipatri-MSFT 27,007 Reputation points Microsoft Employee

2023-03-30T14:47:47.1633333+00:00

@Shinde, Balaji We haven't heard back from you. Just wanted to check if you had the chance to check my response.
Shinde, Balaji 116 Reputation points

2023-04-01T09:21:29.6833333+00:00

Hi @GeethaThatipatri-MSFT Thanks for your help. During "To create the Server Audit specification" step, I received an error "Cannot find the object 'sp_addserver', because it does not exist or you do not have permission." I still went ahead with next command, now I am able to see only one log for it in LAW. I created a user in MI and re-logged to it, also executed some simple SQL queries to see if the logs are generated or not. But there is only one log I see which is generated after I enabled SQL auditing on MI. So not sure, if this is working or not.
Shinde, Balaji 116 Reputation points

2023-04-03T04:54:54.3266667+00:00

Hi @GeethaThatipatri-MSFT It seems to be working, I am seeing more logs for managed instance. Is there a way we can automate this so that any managed instances deployed in future in our tenant has auditing enabled?

1 answer

GeethaThatipatri-MSFT 27,007 Reputation points Microsoft Employee

2023-04-03T14:30:29.0966667+00:00
Hi, @Shinde, Balaji Thanks for posting your question in the Microsoft Q&A forum.

As I understand you have enabled Diagnostic settings on SQL MI but you are not receiving SQLSecurityAuditEvents.

You Might need additional configuration wherein you can follow this Github link

Diagnostics enabled

Configuration on Specific SQL MI within SQL itself.

Regards

Geetha
Please sign in to rate this answer.
GeethaThatipatri-MSFT 27,007 Reputation points Microsoft Employee

2023-04-04T22:28:44.25+00:00

Hi, @Shinde, Balaji I suspect there may be some SQL Automation / SQL Jobs / Scheduled Tasks, etc. that can be done. Something like this maybe is an option https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance?view=azuresql

If you think your question has been answered, click "Mark as Accept Answer",This can be beneficial to other community members reading this forum thread.

Regards Geetha

Shinde, Balaji 116 Reputation points

2023-04-05T06:59:14.1633333+00:00

Seems there are more auditing options needs to be enabled for managed instance https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql, After doing this I am getting all kinds of security and audit logs in workspace. https://github.com/raghavender7/Prevent-Data-Exfiltration-in-Azure-SQL-Managed-Instance/blob/master/SQLMIauditingforLinkedServer.md#step-2-to-configure-auditing-at-azure-sql-managed-instance-level link provides information on enabling Auditing on Linked server.
Sign in to comment