SQL Server Issue: Login failed for user 'username'

Check if the SQL Server is running SQL Server Authentication. In Microsoft SQL Server Management Studio, right click the database server, choose Properties and see the Server authentication section in the Security page.

This blog post could help finding what is causing the error:

Understanding "login failed" (Error 18456) error messages in SQL Server 2005

To determine the true reason for the failure, the administrator can look in the server's error log where a corresponding entry will be written.

Look at the error state:

ERROR STATE   ERROR DESCRIPTION
2 and 5       Invalid userid
6             Attempt to use a Windows login name with SQL Authentication
7             Login disabled and password mismatch
8             Password mismatch
9             Invalid password
11 and 12     Valid login but server access failure
13            SQL Server service paused
18            Change password required