We are authenticating SQL MI users with AAD - Universal with MFA and would like to see a log of all sign ins. Would this be under the AAD/Users/Audit Logs/Sign ins? We'd like to see a few months worth of data.

Thanks

CodePudding user response：

In Azure Ad only 30 days of Sign in logs for user, Managed identities or even Service principals are available. After 30 days all the logs whether audit, Activity or Sign-In are flushed out on Azure’s end, unless you store the logs every month or every seven days in an Azure storage account or send it to Log analytics workspace.

For Azure AD Free Edition Sign in logs are retained only for 7 days. If you upgrade to Azure AD
Premium P1 or Azure AD Premium P2 then the sign in logs of past 30 days are available.

I created one SQL Server and enabled Azure AD User and tried Signing in with Azure AD Universal MFA via SSMS and managed Identity like below :-

Assigned VMs managed identity access to SQL server:-

SignInLogs of SQL server:-

For managed Identity logs, you can visit here :-

After clicking on try it out, New SignIn logs page will appear and you can select managed identity to verify if your managed identity is successfully logged into an Azure SQL server :-

As, Sign in logs are only available for 30 days in Azure AD, You can save the logs in the storage account every month by following the steps below :-

Click on Export Data settings above SignIn Logs page or Diagnostics Settings> Add diagnostics Settings > Select the required logs > Archive to Storage account > Select your storage account from Azure AD page like below :-

OR

Now, all your sign in and managed identity logs will be stored and populate in your storage account like below:-

Reference :-
How long does Azure AD store reporting data? - Microsoft Entra | Microsoft Learn