Today, we have a service request that our customer needs more information about a login failed ocurred in their databases. Following, I would like to share a recommendation about it.
If your application returned a login failed, for example, as below using SQL Server Management Studio:
Cannot connect to servername.database.windows.net.
===================================
Login failed for user 'XXX'. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&Evtsrc=MSSQLServer&EvtID=18456&Link...
------------------------------
Server Name: servername.database.windows.net
Error Number: 18456
Basically, if you enable SQL Auditing, you could find out what is the error code and the state that represents among other information what is the main cause.
For example, using Home->SQL Databases->MyDB | Auditing in the portal I could obtain the audit logs selecting the option View Audit Logs
You will have the list of the current audit logs
And finally,
With the state number 8 you could see a wrong password - MSSQLSERVER_18456 - SQL Server | Microsoft Learn
Enjoy!