Cannot Login to SQL Server using administrator account


After installing SQL server on a machine, it happens that you connect or disconnect that machine to domain. WHen you do this, the administrator account can no longer access the database engine. The below steps allow you to regain access to the SQL server...

  1. Stop SQL Service: on the command line type: net stop MSSQLServer
  2. Start the SQL Server in Management mode: on the command line type: net start MSSQLServer /m
  3. Open the SQL Server management studio, cancel the login dialog
  4. Open new sql server engine query window: from the menu, Click file->new->Database engine query
  5. Enable SA account if not enabled: in the query window type: Alter login sa enable
  6.  Set the password of the sa account: alter login sa with password='my password'
  7. Stop the SQL server from the command line: net stop MSSQlServer
  8. Start SQL Service from the command line: net start mssqlserver
  9. Start the SQL Management studio and connect to the server using sa account
  10. Add you domain administrator as sysadmin
  11. Disable the sa account when you finish

 

Comments (24)

  1. ZAHID says:

    ms sql server 2008 R2, It sows me login window on execution of Alter query, Can you me- ZAHID

  2. Soli says:

    Stupid! doesn't work on SQL Server 2008 +

  3. Syed says:

    Its working on SQL Server 2008 R2

    Small update on step 1 and 3

    Run as administrator both command line and SQL Server Management Studio

  4. Ebonweaver says:

    Bogus information, you can’t do anything in the management studio without authenticating first.

  5. just me1 says:

    its work fine on 2012 , thnax alot πŸ˜€

  6. Velja says:

    it doest work on any server

  7. George says:

    This worked for me, just make sure you’re starting SQL Server Management Studio as Administrator, and then authenticate using the domain admin account once in management mode.

  8. Sohail says:

    Thanks for sharing! Helpful post!

  9. MTR says:

    Thanks, it has worked for me. cheers.

  10. guest says:

    Does work on 2012 box. Run as admin after and connect to local host using windows auth.

  11. Pee (Thailand) says:

    Oh! Great i already find a solution for this problem for several days, but this solution is worked. Thank you very much.
    I am deploy SQL 2012 on Windows 2012 R2 with AD.

  12. Anonymous says:

    Tonight, I was preparing a Hyper-V image running Windows Server 2012 R2, SQL Server 2014 and Dynamics CRM 2015 to investigate a problem we ran into the other day. The problem we were facing was described…

  13. Anonymous says:

    Tonight, I was preparing a Hyper-V image running Windows Server 2012 R2, SQL Server 2014 and Dynamics

  14. jun says:

    Windows 2012 R2 server
    SQL 2008

    still cant login as as, checked logfile: "Authentication mode is WINDOWS-ONLY."

  15. jun says:

    cant even browse my SQL server πŸ™

  16. hwelat says:

    it has worked for me, in Windows 2008 R2 server
    SQL 2008 , Thank you

  17. Anonymous says:

    Recent Releases and Announcements

    SQL 2016 CTP 2.3 has been released

    http://www.microsoft

  18. Sheeraz says:

    Thanks.. it has worked for me, for SQL server 2012 in Windows 2012 R2 server

  19. Anjani says:

    Im unable to access my sql as an admin login,please find me some solution,tried the above steps but showing error as incorrect syntax when I tried step no 2

  20. NiMa says:

    Thanks , worked for me πŸ™‚

  21. NiMa says:

    Thanks , worked for me πŸ™‚

  22. markus says:

    For me worked:
    1. and 2.
    3. Open the SQL Server management studio (run as administrator) and dont cancel login dialog! Press login and use windows authentication (domainadministrator)
    4. Add user to sysadmin

  23. Cloud-Ras says:

    IΒ΄m for sure gonna remember this tip.
    Some times where itΒ΄s not a posibillity to login as Admin, and SA is not enabled.
    Thanks πŸ™‚

  24. Kiran says:

    Osm explanation. thansk it works

Skip to main content