Azure SQL database: Error 18456 State 122 causes failed connections to database alert trigger

Question

we've recently set up alerts for failed connections to database using Azure Monitor. We started getting a bunch of failed connection alerts from all of our databases.

After some investigation in system log using etc query

SELECT *
FROM sys.event_log
WHERE event_type = 'connection_failed'
ORDER BY start_time DESC

I can see that there are lost of 'Login failed for user.' and 'Login failed for user '%.*ls'.%.*ls%.*ls' messages.

Now I was able to find that this seems to be specifically Error 18456 with State 122 https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-ver15 which according to documentation is 'Failure due to empty user name or password.'

All of our applications seem to work in a correct way but the error occurs on all 4 databases including 'master' almost all the time. I'm not sure how to debug exactly what's causing this. I've looked at some potential reasons but nothing seems to be the case.

Edit: I just talked with some developers. They mentioned that sometimes when they login to SQL Azure Db from their local PC's using SQl management studio they have network issues after some time and they are logged out. I'm just not sure whether this is the real reason for these since logs don't tell that much.

Regards.

maybe simply brute force attacks from the outside? Are you having firewall restrictions in place? — silent
Yes there are FireWall restrictions. Plus like I mentioned according to Microsoft state 122 means 'Failure due to empty user name or password.' I can't imagine that would be brute force attack. I added small edit to the post. — Mateusz Migała

Alberto Morillo Alberto Morillo · Accepted Answer · 2019-10-30T19:41:15

Please enable Auditing on your Azure SQL Database to further investigate. After that you can click "View Audit logs" and search for Event type "Login" and action not successful.

Once you filter login events, make a click on any event, scroll the window that shows the detail of the event you just clicked on, and you will see important information like IP address of the host.

If you use the dashboard available on the "View Audit Logs" you can have details by type, by IP address and by principal. Just click on the type you would like to see details, and you will get all related events, each one will all details.

Make a click on the IP addresses that you don't recognize (left side on below image), make a click on the failed login attempts for each principal (right side on below image, where you see the Pie chart).

Azure SQL database: Error 18456 State 122 causes failed connections to database alert trigger

1 Answers