3

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.

services

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

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 - Team Monica May 27 '15 at 6:44
  • I have 1 sql server browser. Not sure if its for 2008 or 2014. Ill post image above – Crudler May 27 '15 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/… – DeadEye May 27 '15 at 6:57
  • Ok, now in single user mode. how do i check if user is sysadmin? – Crudler May 27 '15 at 7:04
  • Connect with management studio using a local admin (on windows level) account and check the security inside SSMS – Tom V - Team Monica May 27 '15 at 7:30
5

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, privacy policy and cookie policy

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