Azure SQL DB audit stops working.

Question

Greetings. I have a very simple Azure SQL DB audit that works exactly as expected... then stops overnight for some reason.

So I can define my audit and confirm that it's set correctly:

Set-AzSqlServerAudit -ResourceGroupName "myRG" -ServerName "myServer" -AuditActionGroup "FAILED_DATABASE_AUTHENTICATION_GROUP"

 Get-AzSqlServerAudit -ResourceGroupName "myRG" -ServerName "myServer" 

AuditActionGroup                    : {FAILED_DATABASE_AUTHENTICATION_GROUP}
PredicateExpression                 : 
StorageKeyType                      : Primary
RetentionInDays                     : 7
ResourceGroupName                   : myRG
ServerName                          : myServer
BlobStorageTargetState              : Enabled
StorageAccountResourceId            : /subscriptions/myResourceID/resourceGroups/myRG/providers/Microsoft.Storage/storageAccounts/myStorageAcct
EventHubTargetState                 : Disabled
EventHubName                        : 
EventHubAuthorizationRuleResourceId : 
LogAnalyticsTargetState             : Disabled
WorkspaceResourceId                 : 

I then do some testing by intentionally failing some login attempts, and see them in the azure portal database/ auditing/ view audit logs tab as I would expect.

All is well, until I come in the next day and intentionally fail some login attempts, and no new entries make it into the log. I have confirmed this twice now.

I can jump start this thing to get it going again by simply rerunning the "Set-AzSqlServerAudit" command, but obviously that shouldn't be required.

Why does this guy stop working overnight?

Answer 1

Hi Chris,

My first thought was: Which service tier have you deployed the database with?

If it is a serverless database it might happen, if the database switches to "standby" the audit also switches to standby and won't start again... just a thought, and to be honest... actually, I don't know how to resolve that issue (if it correct)

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 2

Hi Chris

In which service tier have you deployed that Azure SQL Database?

Is it a serverless database? Maybe if the database goes into standby mode, the audit also moves to that state and fails to start up again?

Just a guess, to be honest, actually I don't have a clue how to resolve that...

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 3

I'm in the Standard S2 tier. The DB is located in a "SQL Server", but not one that I can RDP in to like a VM or anything -- it's simply houses an individual DB. It's also definitely not a managed instance.

Eidt:

I do not believe this considered "serverless", but could be wrong?

Answer 4

I worked with Microsoft on this. It turned out to be that the storage account for this process was being shared w another process, and apparently each process needs to have it's own to avoid issues.

Once this was assigned it's own storage account the issue went away. Just FYI.