sql server 2014 unable to connect with Microsoft account or sa

Question

I am running sql standard 2008 r2 on my box. and its been working great. I just installed sql server 2014 on my machine as a second instance.

The service is running fine, but when I try to log into it with my sa password, I get an error about the login failing.

Assuming I maybe messed up the password on install, I uninstalled and reinstalled. Same issue.

The weird thing is that when I try log in with windows authentication, it also fails

yet I can log into my 2008 R2 instance with windows authentication, no problem

Something is bugging me that its not necessarily the log in, but maybe how the service is logging in, but it is logged in exactly the same way as the 2008r2 service.

Full error message is here

Server Name: CRAIG-PC\MSSQLSERVER2014
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

Thanks in advance

---

Full screen grab of services

browser is running from this path "C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe"

Answer 1

The following steps should allow you to create a new sysadmin account, you can then connect using the new account and update the sa password as required.

1. Log in to Windows as a local administrator (must be local admin, not just domain admin, on the machine hosting SQL Server).
2. Stop the SQL Server Windows service.

3. Open command prompt (cmd.exe) as an administrator and run the SQL Server exe with the -m flag (minimal config). Leave this running. (For named instances use -s {instancename}, see https://technet.microsoft.com/en-us/library/aa178031)

   C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe -m

4. Open another command prompt window as an administrator.

5. Run SqlCmd.exe and connect to the instance.

   sqlcmd -s localhost\sqlexpress

6. Create a user and assign to the sysadmin role.

   create login new_admin with password='new_password'; go

   sp_addsrvrolemember new_admin, 'SYSADMIN'; go

7. Exit sqlcmd using ctrl + c and close the command prompt.

8. On the other command prompt (where you started sqlservr.exe) press ctrl + c and then Y to stop SQL Server. Restart the SQL Server Windows service.

You should be able to log in using the new username and password.

Edit: If you have difficulties connecting once the server is started in minimal config mode you may need to stop other services to prevent anything opening a connection, particularly SSRS/SSIS etc. if they're running as local administrators.