Unexplained Failed Logins on SQL Server
Today I was running some log analytics queries against an Azure SQL database. I thought I’d have a look for any failed logins using this query:
AzureDiagnostics | project TimeGenerated, server_principal_name_s, database_name_s, additional_information_s, application_name_s, host_name_s,client_ip_s,Category,action_id_s | where TimeGenerated >= ago(1d) | where Category == 'SQLSecurityAuditEvents' | where action_id_s == 'DBAF' | order by TimeGenerated desc |
And I found that there were hundreds of failed logins from my Azure Virtual Desktop (AVD) from dbatools.
This was slightly confusing because I haven’t run any PowerShell commands today.
Who or what was running dbatools commands that failed login from my AVD?
Well, it turns out it was me, without realising it.
The explanation is this.
Yesterday, I ran a query using dbatools using Connect-DbaInstance and Invoke-DbaQuery.
And I’d used the Get-AzAccessToken command for authentication.
But I hadn’t closed the connection when I’d finished.
Looking at the logs, exactly an hour after I run the last successful query the failed logins started appearing. And they appeared every 2 or 3 minutes thereafter.
It seems that the Access token expires after 60 minutes then the connection starts to fail.
Who knew that a SQL connection continues to send messages to the database even when you aren’t querying it.
Of course, every developer will tell you that you should close a connection when you have finished with it.
Running Get-DbaConnectedInstance | Disconnect-DbaInstance fixed the issue.
The lesson for today, if you open a connection to a database, remember to close it when you’ve finished.
Useful Links
If you need help managing your Microsoft Data Platform, including SQL Server, please do contact us.
Getting started with Azure: Creating an Azure Virtual Machine
0 Comments