Tag: Audit

Query Azure MySQL Audit Logs

The common practice for user management in Azure AD is to create Azure AD Security Groups for users and assign permissions to groups rather than individual users. For example, the common practice for users who need access to Azure MySQL instance would to be add them to an Azure AD Group (say MySqlDb_Users) and then setup this Azure AD Group for access on Azure MySQL Instance.

Pre-requisite

  • Ensure that Audit Logs are enabled on all the production instances for Azure MySql as documented here — https://docs.microsoft.com/en-us/azure/mysql/howto-configure-audit-logs-portal
  • Make sure to enable Auditing on Server Parameters section (at least Connection audit log event type).
  • The logs can be saved to Storage Account, Event Hubs or Azure Log Analytics Workspace. We used Log Analytics Workspace for the runbook because it provides rich querying capabilities.

The following Log Analytics Query and the corresponding screenshot shows that individual user activity is tracked in the Audit Logs in spite of them using the Azure AD Group as username to connect Azure MySQL — external_user_s field logs the exact individual user, connection_id_d and thread_id_d carry over the Connection/ThreadId which can be used to tie the user across all log records.

AzureDiagnostics
| where Category == "MySqlAuditLogs"
| order by TimeGenerated desc
| project TimeGenerated, event_class_s, event_subclass_s, user_s, external_user_s, connection_id_d, thread_id_d, event_time_t, sql_text_s, db_s, table_name_s, error_code_d

Audit the SQL server logins

Here is a trigger that will enable you to log the alter database login activity on a SQL instance. The trigger will insert a record for each alter login activity. It will log which username has changed the login and when. For this to work, Audit table needs to be created before hand.

Script to create Audit table:

USE [Enter DB Name here]
GO

CREATE TABLE [dbo].[Audit](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[QueryText] [varchar](260) NULL,
	[EventDescription] [nvarchar](260) NULL,
	[eventtime] [datetime] NULL,
 CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Script to create the trigger:

Use Master
GO

If Exists (Select * From sys.server_triggers where [name] = 'DDL_Trigger_Login_Changed')
    DROP TRIGGER [DDL_Trigger_Login_Changed] ON ALL SERVER
go

CREATE TRIGGER [DDL_Trigger_Login_Changed]
ON ALL SERVER
AFTER CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
AS
Declare @results varchar(max)
Declare @subjectText varchar(max)
SET @subjectText = '*ALERT* DATABASE LOGIN changed on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() 
SET @results = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))

/* Insert the change event into the Audit table */
INSERT INTO [Enter DB Name here].[dbo].[Audit]
           ([QueryText]
           ,[EventDescription]
           ,[eventtime])
     VALUES
           (@results
           ,@subjectText
           ,CURRENT_TIMESTAMP)

/* Send an email when a login is altered */
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Email profile',
    @recipients = 'Enter Email Address here',
    @body = @results,
    @subject = @subjectText,
    @exclude_query_output = 1 --Suppress 'Mail Queued' message
GO