Lesson Learned #328: Login failed for user 'xyz'
Published Feb 13 2023 12:29 AM 1,718 Views

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

 

Jose_Manuel_Jurado_1-1676276447958.png

 

You will have the list of the current audit logs 

 

 

Jose_Manuel_Jurado_0-1676276325151.png

 

And finally, 

 

Jose_Manuel_Jurado_2-1676276642213.png

 

With the state number 8 you could see a wrong password - MSSQLSERVER_18456 - SQL Server | Microsoft Learn

 

Enjoy!

Version history
Last update:
‎Feb 13 2023 12:33 AM
Updated by: