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.
I have mentioned the steps that helped resolved both the issues.
To start the service we I have to set the service account.
- Open the SQL server configuration manager
- 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
- 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.
- 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.