Applications started failing to login to SQL Server 2005 after SP3 Upgrade reporting error as “"Login Failed for user ' ', the user is not associated with a trusted SQL Server connection”

This is from my experience of today. While a SQL 2005 EE (64 Bit) on Win 2008 EE Failover Cluster is upgraded to SP3, all the SQL Accounts failed to logon to SQL Server reporting below error:

"Login Failed for user ' ', the user is not associated with a trusted SQL Server connection".

Root cause of this issue was SP3 setup on cluster changed the SQL Server Authentication mode from Mixed to Windows and hence all SQL Logins started to fail. Hence I simply changed SQL Server authentication mode to SQL Server and Windows (mixed mode) and everything started working. (Please note that this instance is supposed to be on SQL and Windows Mixed mode authentication)

Moral of the story: If you experience similar error a production server after a major change, make sure you are using correct authentication mode before diving into advanced troubleshooting.