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

Windows authentication

windows authentication failing

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

Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

Thanks in advance

Full screen grab of services

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

  • 1
    Is the sql browser service running?
    – Tom V
    Commented May 27, 2015 at 6:44
  • I have 1 sql server browser. Not sure if its for 2008 or 2014. Ill post image above
    – Crudler
    Commented May 27, 2015 at 6:47
  • Try to run your MSSQLSERVER2014 instance in single-user mode and check if your user is sysadmin. mssqlfun.com/2014/09/04/…
    – Eric Ly
    Commented May 27, 2015 at 6:57
  • Ok, now in single user mode. how do i check if user is sysadmin?
    – Crudler
    Commented May 27, 2015 at 7:04
  • Connect with management studio using a local admin (on windows level) account and check the security inside SSMS
    – Tom V
    Commented May 27, 2015 at 7:30

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.