If you are trying to login to a SQL server and you are receiving just the generic 18456 error, here is how to figure out what is going on.
- Open up the Microsoft SQL Server Management Studio tool.
- Login with an account that has administrative privileges.
- Click on Security and make sure under “Login Auditing” you have at least Failed logins only checked or higher.
- Click OK.
- Try to login with your account on another server to trigger the 18456 Error
- Go back to the SQL Server and open up Event Viewer (Start->Administrative Tools->Event Viewer)
- Click on Windows Logs->Application
- You should see Event ID 18456 inside of your logs (It won’t be Critical or Error level, just Information).
- Double click on your event, and it should give you some information on why the account is unable to login.
For example, in my case, I was attempting to use SQL authentication on the server, and the log told me the server was only setup for Windows Authentication (sure enough, it was! :P).
Hope this helps!