/ DB

SQL Error Fix - "Login failed for user" error message when you log on to SQL Server First time after fresh install

Issue

I faced this issue when I installed SQL 2012 and SQL 2014 in an Active Directory configured machine mostly this will be the case for a typical developer machine where you configure all the services in one machine. While installing I did configure the mixed mode authentication with SQL + Windows. Provided the sa account that's created in the AD. I did add the local administrator as SQL administrator. Installation gave some warning about AD configured on the server as a typical developer, I just ignored and installed, now the installation is complete. I got an error instance didn't start in a timely manner.After fixing the service issue I tried to connect to the server and there comes the new issue of login failed. I tried with the administrator account and sa that we used while installing no luck and stuck with a service that no one is allowed to use. 8tlgp

Fix

I have mentioned the steps that helped resolved both the issues.

Service issue

To start the service we I have to set the service account.
  • Open the SQL server configuration manager
1
  • Select the instance that you installed and right click try to start the instance
  • If it works great else set the service account and that should resolve it
2

Login issue

  • The login that we are trying was not added or provided access when it installed.
  • We have to start the SQL in single user mode that will allow the local administrator group users to log in to the server, then add the user and provide access.
3
  • Stop the service if its running and add a startup parameter -m or -f to the startup parameter. (Microsoft article link)
  • Start the service try connecting with the local administrator or users from the administrator group
  • Go add the administrator and the sa account grant access. Follow the step by step instruction. (link)
  • Stop the service and remove the startup parameter. Start it again and try login with the sa account voila it works !!
Note: Dont forget to remove the startup param after fixing the issue.
Related errors:

http://dba.stackexchange.com/questions/87390/sql-server-authentication-login-failed

http://blog.sqlauthority.com/2014/01/17/sql-server-fix-login-failed-for-user-username-the-user-is-not-associated-with-a-trusted-sql-server-connection-microsoft-sql-server-error-18452/