Skip to content

May 26, 2011

97

How to recover SA password on Microsoft SQL Server 2008 R2

When you are using MS SQL Server in mixed mode, it is very important that you know your SA password.

There can be different reasons you lost the password

  • Person who installed the SQL Server knows the password but has left the building.
  • You did not write down the password in your password file
  • Password file is lost

Steps to recover the SA password

  • Start SQL Server Configuration Manager

  • Stop the SQL services

  • Edit the properties of the SQL Service

  • Change the startup parameters of the SQL service by adding a –m; in front of the existing parameters

  • Start the SQL services. These are now running in Single User Mode.
  • Start CMD on tthe SQL server
  • Start the SQLCMD command. Now you will see following screen

  • Now we create a new user. Enter following commands
    • CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′ (Remember SQL server has default strong password policy
    • Go
      • Now this user is created
  • Now we grant the user a SYSADMIN roles using the same SQLCMD window.
    • sp_addsrvrolemember ‘recovery’, ‘sysadmin’
    • go
  • Stop the SQL service again
  • Change the SQL service properties back to the default settings

  • Start the SQL service again and use the new created login (recovery in my example)
  • Go via the security panel to the properties and change the password of the SA account.

  • Now write down the new SA password.
Share and Enjoy:
  • LinkedIn
  • Facebook
  • del.icio.us
  • Yahoo! Buzz
  • Twitter
  • Google Bookmarks
Read more from General, Security, SQL
97 Comments Post a comment
  1. bahare
    Sep 19 2011

    very Well It works!thank you for sharing your information in such a good way!!!

    Reply
  2. Craig Martin
    Feb 10 2012

    This was EXACTLY what I needed. Thanks much!

    Reply
  3. Stephen Miller
    Mar 1 2012

    Thank you thank you thank you

    Reply
  4. Sancho
    Mar 4 2012

    I also find other 3 methods to reset forgotten SA password, learn more details at: http://www.top-password.com/knowledge/change-sql-server-password.html

    Reply
  5. Mar 10 2012

    Nice job !!! Thank you !!! *****

    Reply
  6. Fredrik
    Mar 15 2012

    Super! You made my day!

    Reply
  7. Mar 28 2012

    great how to, Thanks a lot

    Reply
  8. Gaurav
    Mar 29 2012

    Thanks a lot .. it worked for me .. saved my lot of time for reinstallation

    Reply
  9. Harish
    Mar 30 2012

    Very very useful,, this resolves my issue,,,
    I was very much happy,,,,,,,

    Thanks a lot for the guys who have found this…

    Reply
  10. JRH
    Apr 5 2012

    This works (as you knew it would) Thank you.

    Reply
  11. Apr 11 2012

    Thank you the above article is very helpfull

    Reply
  12. Apr 13 2012

    This article saved a lot of time for me. Thank you

    Reply
  13. T
    Apr 14 2012

    Thanks for the step by step details, this helped a lot with the default instance. Would you know how to make it do it for a named instance? I have searched and searched by no one seems to be plagued by a named instance. So maybe I’m missing something really obvious?

    Reply
    • Diego R.
      Apr 25 2012

      Hey,

      I was having the same problem but finally found a solution, now I just can’t login, i’ve been able to create the user but somehow I keep getting a Login failed for user ‘recovery’ (Even when copying user and password straight from the console statement)

      sqlcmd-S .\
      or
      sqlcmd -S \

      e.g.
      sqlcmd-S HOMEPC\SQLSERVER2

      More about it at
      http://msdn.microsoft.com/en-us/library/ms188247.aspx

      Reply
  14. Apr 21 2012

    It’s not Working properly……It’s getting Occur
    Sqlcmd: Error:Sqlserver Native Client 10.0 login Timeout Expaired…

    How to solve it…

    Reply
    • gertvangorp
      Apr 24 2012

      Hi,

      Can you send me the steps you followed and where it went wrong??

      Thanks

      Gert

      Reply
    • Dez
      Nov 14 2012

      Had to use:

      SQLCMD -E -S ‘server_name\db_name’

      to solve “Sqlcmd: Error:Sqlserver Native Client 10.0 login Timeout Expired” error. Worked terrific after that, thank you!

      Reply
  15. Kaz
    Apr 23 2012

    Thank you – just what I was looking for and very easy to follow.

    Reply
  16. Dennis
    May 15 2012

    Wanted to say BIG THANK YOU!!! for super easy instruction that work well. Unlike other site they lure people using techniques that not going to work and giving us option to purchased software ect.

    Reply
  17. Sanjay
    May 26 2012

    thanks a lot for the info. it save lots my time as I was planning to do reinstall itself :)

    Reply
  18. Paul
    Jun 11 2012

    These instructions are the best. Thank you.

    Reply
  19. Heby Joseph
    Jun 14 2012

    yesss .i can solve my issue. Thanks very very much

    Reply
  20. tristan
    Jun 22 2012

    Bless your cotton socks!

    THANK YOU!!!!

    Reply
  21. Vali
    Jun 27 2012

    Merci, merci, merci, merci, merci, merci !

    Reply
  22. Jul 30 2012

    Beautiful work,definitely we can try for this tool to recovery the password.Many thanks for well information and share.

    Reply
  23. MonsterCable
    Aug 8 2012

    Thank you so much! After searching and searching online, this was exactly what I was looking for. I guess most people don’t understand what it means to lose the SQL SA password.

    Thanks again!

    Reply
  24. Jeep
    Aug 28 2012

    Great – Thanks!

    Reply
  25. modtanoy
    Sep 5 2012

    Thank you very much.

    Reply
  26. Sep 11 2012

    Nice job !!! Thank you !!!

    Reply
  27. Ashok Tewatia
    Sep 19 2012

    This works for me . Thanks

    Reply
  28. Cornel
    Sep 28 2012

    Thank you!
    Had to use:
    sqlcmd -S
    othewise it would not connect to any instance. Also if you cut and paste from this web page, re-type the single quotes to avoid syntax errors.

    Reply
  29. Pedro
    Sep 29 2012

    You are great!!!! Very Well It works!thank you for sharing your information in such a good way!!!

    Reply
  30. Fabien
    Oct 4 2012

    Thank you so much !!!!
    All the best to you in your life man !!

    Reply
  31. XiaoYu
    Oct 5 2012

    This is great, well written step by step instruction. THANK YOU. One more unrelated question, how did you make the screen shot looks like a broken paper edge? Thank you again.

    Reply
  32. Roberto
    Oct 12 2012

    Thanks, you saved my day!

    Reply
  33. Lothar Geisinger
    Oct 22 2012

    Respect! Thanks a lot!

    Reply
  34. Sam
    Oct 26 2012

    Perfect! You made my day! Thanks!

    Reply
  35. Hacked
    Oct 29 2012

    Worked perfectly and saved the day! Thank you!

    Reply
  36. Nov 2 2012

    Thanks for the awesome solution!

    Just a few tips:

    Since you’re in single-user mode, you may receive the following error:

    “Login failed for user ‘[user]‘. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461).

    To resolve this, make sure you stop SSAS and SSRS and SQL Server Agent for the instance that you’re working with. You can then get in.

    Also, if you have direct access to the server, you’ll be able to login to SQL Management Studio (if installed) and then do your management through the GUI.

    For my case, I needed to get my network account to authenticate on my local PC, which was being denied because someone else installed SQL on my machine. I was part of the Administrators group, so it let me take it over.

    Happy coding!

    Reply
    • Teezee
      Aug 27 2013

      Thanks a million times Derek. This one worked so well for me as I had the same problem.

      Cheers

      Reply
  37. AldoArmisi
    Nov 6 2012

    Problem solved, thank You very much!

    Reply
  38. Matt
    Nov 7 2012

    Worked amazingly. Cheers!

    Reply
  39. Nov 14 2012

    You need to run command prompt with administrative rights.

    Reply
    • Jimmy
      Sep 10 2013

      @Rafik Berzi
      Thank you so much, I was getting authentication failures even though I was logged in with admin credentials. This got me in.

      Reply
  40. Sunan
    Nov 15 2012

    Thanks a lot brow. It’s worked..Great Tutorial :)

    Reply
  41. Nov 26 2012

    Nice post mate, helped me out just now (inherited a developer machine with no idea what the local password was!)

    Reply
  42. Jerry
    Nov 28 2012

    It works.

    a few advise

    1. this password recovery is for single login mode, not mixed mode(as mentioned at very begining). if you SQL setup in mixed mode, you can reset your sa password using OS admin account.

    2.Derek’s tip very useful. I had same situation.

    Reply
  43. tskeggs
    Dec 14 2012

    Brillant, that was a life saver !

    Reply
  44. Rajesh
    Dec 15 2012

    It’s working fine.Thanks..

    Reply
  45. SQLn00b
    Dec 17 2012

    After adding -m; the “SQLCMD” command worked for me when I started only the “SQL Server (MSSQLSERVER)” service.

    Reply
  46. Ali
    Jan 9 2013

    Hi,
    It was so complete and useful for me.

    Thanks a lot

    Reply
  47. Suday Kumar Ghosh
    Jan 20 2013

    Superb!!!!!!!! Thanks from my heart…………..

    Reply
  48. Saskia
    Jan 21 2013

    This solution also works for SQL Server 2012. Thanks a lot!

    Reply
  49. Kumaresan
    Feb 11 2013

    Thank you man. Its great.

    Reply
  50. Shane
    Feb 27 2013

    Looks like this does not work for 2012 :( bummer – or I am missing something?

    Reply
    • gertvangorp
      Aug 16 2013

      not sure, not yet tested on SQL 2012

      Gert

      Reply
  51. Mar 13 2013

    I’m really enjoying the design and layout of your site. It’s a very easy on the eyes which makes it much more enjoyable for me to come
    here and visit more often. Did you hire out a developer to create your theme?
    Fantastic work!

    Reply
    • gertvangorp
      Aug 16 2013

      I am using Traction PRO as theme

      Gert

      Reply
  52. Amin Reza
    Mar 17 2013

    tnx a lot

    Reply
  53. Jortx
    Mar 21 2013

    Awesome. Cheers!

    Reply
  54. kannan.g
    Mar 26 2013

    thanks a lot!!!!!!!!!!

    Reply
  55. Stevek
    Apr 3 2013

    Thank you, very effective with easy to follw instructions!

    Reply
  56. Andy
    Apr 24 2013

    Nearly 2 years later and it’s still helping people! Thanks.

    Reply
  57. Rakesh Kumar Singh
    May 22 2013

    Thank you very Much

    Reply
  58. pele
    May 29 2013

    In the line sp_addsrvrolemember ‘recovery’, ‘sysadmin’
    I had to write sp_addsrvrolemember “recovery”, “sysadmin”

    even so it works very well

    Reply
    • Dec 17 2013

      sp_addsrvrolemember ‘recovery’, ‘sysadmin’ also threw an error, but the following worked for me:
      sp_addsrvrolemember recovery, sysadmin

      Thanks again to the writer!

      Reply
  59. Ali Azsari
    Jun 19 2013

    Thanks!

    Reply
  60. paresh
    Jun 27 2013

    Great, it’s really helpful thanks

    Reply
  61. Kake
    Jul 8 2013

    Saved my ass…

    Reply
  62. Sheila
    Jul 17 2013

    Wow! This could not have been written any better! Thanks so much!

    Reply
  63. Jul 26 2013

    Thanks a million, this fixed things right up.

    Reply
  64. Christiane
    Aug 1 2013

    You’re my hero !
    Thank you so much !
    And it is very very clear. Very good job.

    Reply
  65. Hi excellent website! Does running a blog similar to this take a great
    deal of work? I’ve virtually no knowledge of programming but I had been hoping to start my own blog in the near future. Anyway, if you have any ideas or techniques for new blog owners please share. I know this is off topic however I just wanted to ask. Many thanks!

    Reply
  66. Perry
    Aug 8 2013

    It Workzz !! Cheers :-)

    Reply
  67. Florin Chirilas
    Aug 14 2013

    Worked great Thank you !

    Reply
  68. Aug 20 2013

    This saved me a lot of trouble!!!

    Thanks for sharing.

    Reply
  69. Juan
    Aug 20 2013

    Excellent, thank you

    Reply
  70. Teezee
    Aug 27 2013

    thanx a lot. ;)

    Reply
  71. Remez
    Aug 29 2013

    it’s work , Thank so much

    Reply
  72. masi
    Aug 31 2013

    Thanks,
    You’re my hero !

    Reply
  73. MB
    Sep 19 2013

    With SQL Server 2008 R2 Express I had to use:

    sqlcmd -S [servername]\[instance]

    This was even for the default instance sqlexpress.

    Reply
  74. Mirek
    Sep 26 2013

    Good job

    Reply
  75. kemal kayalı
    Sep 28 2013

    YOU ARE THE MAN!!! saved me

    Reply
  76. Ronaldo Bicca
    Oct 4 2013

    Great Job. Thank you!

    Reply
  77. Rajaonary
    Oct 9 2013

    Thanks a lot, you save me

    Reply
  78. shahp
    Oct 15 2013

    Thanks a lot!!!!!

    Reply
  79. Oct 17 2013

    This didn’t work for me.

    It tells me “Alter failed for Login ‘sa’

    An exception occurred while executing a Transact-SQL statement or batch.

    Cannot set a credential for principal ‘sa’ (Microsoft SQL Server, Error:15535)

    Reply
    • Oct 17 2013

      found a solution, had to do the following:

      select the “Map To Credential” check box on the General tab of the Login Properties – sa dialog box.

      that got rid of my error

      Reply
  80. Oct 23 2013

    Thanks a lot, very clearly explained :)

    Reply
  81. fipsinator
    Oct 30 2013

    thank you!
    still helps in 2013 :-)

    Reply
  82. Ross
    Oct 31 2013

    Worked but only after doing something else, not mentioned in the article above…

    Starting SQLCMD without any other parameters only starts it in the default instance of SQL, if you are trying to create an SA account in a named instance (e.g. sqlServer\sql2008), you need to connect SQLCMD to that named instance. Mine for example, is \SQL2008 so to connect SQLCMD to that, I had to open the cmd prompt and write:
    SQLCMD -S .\SQL2008

    Reply
  83. Peter
    Nov 1 2013

    Thanks! Very, very helpful!

    Reply
  84. Khalid
    Nov 21 2013

    The instructions are very clear and straight forward. Thanks!

    Reply
  85. Andrew
    Dec 11 2013

    Excellent article. Really got me out of a bind. thank you.

    Reply
  86. Rudy
    Dec 19 2013

    thanks, it works like a charm

    Reply
  87. Jan 17 2014

    this process is very complicated i have another solution to recover the SQL Server Password which is highly reliable and robust the SQL Password recovery software. You can use its demo version which ensured you how it perform riskfree password recovery process.

    http://www.sqlmdfviewer.org/sql-password-recovery-freeware.html

    Reply

Trackbacks & Pingbacks

  1. Lost SA Password for SQL 2008 R2 | Squishnet

Leave a comment

required
required

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments