SQL Login Failures and Azure SQL
We ran into an issue recently with a large number of SQL Login failures triggering an alert for our sysadmins. This was in one of our Azure SQL databases so the usual tools to troubleshoot alerts don’t always apply.
We did have auditing turned on at the “server” level for Azure SQL. That helped some as I was able to see that there was a login failure in those audit logs using the online viewer. When I tried to troubleshoot the errors using the default extended event trace in Azure SQL, all I could see was a crazy username that didn’t make sense. Even when I deliberately entered a bad password for a known account, I still wasn’t able to see the user name.
After quite a bit of poking around, I decided to try examining the audit log in Azure with the query tool. That opened up a pretty straightforward query with a handful of columns pre-populated in the SELECT statement and using the sys.fn_get_audit_file function to open up the XEL file stored in Azure storage. Once I saw that and knew the name of the file(s), I could open that up in SSMS and examine the data more fully.
I initially saw the description showing an error code of 18456 with error states of 8 for my bad password and 132 for the vast majority of the other issues. After several searches, I came across an older article from Aaron Bertrand that gave several helpful descriptions for the error states, but not 132. More searching led me to a more official MS page that strangely listed just SQL 2017 in the “applies to” list, but error state 132 led to an Azure Active Directory error. I still couldn’t see the AAD logs, but when our sysadmins looked, they weren’t able to see anything correlating to those times. At that point, I decided to just show all of the columns from the audit and was able to see the app and client names. That pointed to one user who had, indeed, tried to open SSMS using Azure Active Directory to connect to the DB. There was a failure that one time, which was resolved, but something behind the scenes apparently kept on trying to connect to that database and failing.
I’ve included a code snippet below to use as a starting point in case you have any issues. You can look in the Azure portal for the file name from your audit logs by opening those logs in a query editor online, though there might be an easier way to get those in a more generic fashion.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
—replace the URL in the function with the appropriate URL for your file | |
SELECT TOP 1000 * | |
FROM sys.fn_get_audit_file('https://subscription.blob.core.windows.net/paths/auditfile.xel', default, default) | |
WHERE (event_time <= '2019-02-20T21:56:36.631Z') | |
/* additional WHERE clause conditions/filters can be added here */ | |
and action_id IN ('DBAF') —look specifically for auth failures | |
ORDER BY event_time DESC |