Many times you have applications that need to have a SQL Server login instead of one that uses Windows authentication. So you create one, and then you try to login using the credentials and you get the dreaded: Login failed for user Error: 18456 error. Below I am going to show you one way that fixes the issue.
You know you created the account correctly, but you get this error message when trying to connect.
First thing you should do is check to make sure your instance is set correctly. Right click your instance and choose properties.
Look to see what Server authentication is set to. The most secure method is windows authentication, but you have an old app that still needs SQL Server authentication. Select the item that says: SQL Server and Windows Authentication mode.
It will then prompt you to restart your instance.
Open up SQL Server configuration manager and right click the instance and restart. You can do it other ways, but this is the recommended way per Microsoft to restart your instance.
After trying again, you can see the connection now succeeds and I am able to connect as user ETL.
Jim Salasek's SQL Server Blog 