Post reply

SA Password Recovery

  • February 3, 2011 at 10:17 pm

    #144578

    Hi,

    I want to recover the password for SA. As the DBA is not inplace and I don't have sysadmin rights for any other database users with which I can go ahead and change the password for SA. How can I can recover the password for SA.

  • February 4, 2011 at 3:02 am

    #1282330

    I know of no way of way of recovering the sa password.

    Was the dba a member of a windows active directory group which you could be added to?

    Other options could be to shut down the SQL services and copy the raw data and log file and attach them to another instance. You would need to keep the original instance for items such as encryption keys etc. This would require a fair amount of work and administration.

    edit

    ----

    Another thought, has the BUILTIN\Administrators group been removed/added? Could you be added into local admins?

  • February 4, 2011 at 3:10 am

    #1282336

    if your sql instance is mixed mode, then you must have some windows account which have the SA permission. Login with that account and reset the password for sa login.

    ----------
    Ashish

  • February 4, 2011 at 3:20 am

    #1282339

    Hi,

    Yes I am also one of the member of windows active directory group. It's not logging in with BUiLT/Administaration rights too.

  • February 4, 2011 at 3:32 am

    #1282343

    Yes I am also one of the member of windows active directory group. It's not logging in with BUiLT/Administaration rights too.

    It should not be the case. Can you please paste your complete error msg here when you trying to connect with windows account which have the SA permission.

    ----------
    Ashish

  • February 4, 2011 at 3:57 am

    #1282347

    Hi,

    The error message is below

    Cannot connect to DBSQL.

    Additional Information:

    Login falied for user 'jp\1451'. (Microsoft SQL Server, Error:18456)

  • February 4, 2011 at 4:23 am

    #1282351

    Does the previous dba's windows account still exist? Can you have the password reset and login using this account?

  • February 4, 2011 at 4:59 am

    #1282363

    you must have one localadmin account in your system and it could be domain admin account.

    Or you can try to add that account in proper windows group which have the sql login permission as SA.

    Then login to your system with local admin account and connect SQL. Then try to reset the password.

    ----------
    Ashish

  • April 13, 2011 at 2:28 pm

    #1311326

    You might want to check out the 'Advanced SQL Password Recovery' product by Elcomsoft. I don't have experience with this tool, but Denny Cherry mentioned it in his book (Securing SQL Server) as a way to retrieve the passwords of SQL-authenticated accounts (including sa). Note that this will require shutting down SQL Server in order to make a copy of the master databases mdf file.

    Brian Kukowski

  • April 13, 2011 at 2:33 pm

    #1311329

    Actually, it doesn't look like SQL Server needs to be shut down. The tool just reads from master.mdf directly

    Brian Kukowski (4/13/2011)

    You might want to check out the 'Advanced SQL Password Recovery' product by Elcomsoft. I don't have experience with this tool, but Denny Cherry mentioned it in his book (Securing SQL Server) as a way to retrieve the passwords of SQL-authenticated accounts (including sa). Note that this will require shutting down SQL Server in order to make a copy of the master databases mdf file.

    Brian Kukowski

  • April 14, 2011 at 2:05 am

    #1311464

    anarayanamoorthy

    try this: http://www.eggheadcafe.com/software/aspnet/34150451/enable-the-sa-account-or-set-back-sysadmin-permission-an-account.aspx

    The other day there was a programmer who had the SA account inactive and did not know the password.

    I followed these instructions and managed to activate and change the password for the SA account.

    Hope it helps! 🙂

    Cláudio Silva

  • April 14, 2011 at 3:12 pm

    #1311880

    You can change the sa password following the steps outlined here -

    http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

    I have had to do this once and it worked great.

    Here is a synopsis of the steps in the blog post

    Set single user mode

    Configuration Manager

    Stop SQL Server Instance

    Adv. Tab > Properties text box > add ";-m" to end of list of startup paramaters

    OK > restart Server Instance

    Add a login to sysadmin role

    login to SQL Server with server acct

    Exec sp_addsrvrolemember '<domain\login>', 'sysadmin';

    Remove single user mode

    Login with server acct (now sysadmin role) > change 'sa' password

    remove sysadmin role from server acct if needed

  • April 14, 2011 at 3:14 pm

    #1311882

    Note when you start in single user mode and log into the SQL Server instance, you need to use a Windows Administrator account and you need a single connection application. So in SSMS, DO NOT use Object Explorer, only connect a query window. Or connect with SQLCMD.

  • April 15, 2011 at 11:32 am

    #1312280

    Try this article, it worked for me

    http://deepakrangarajan.blogspot.com/2008/01/forgot-sa-password-in-sql-server-2005.html

  • April 21, 2011 at 7:42 am

    #1314510

    SQL Server running in single user mode accepts local windows admin account as sysadmin logins (even if group 'BUILT-IN\Administrators' is remvoed from sysadmin role).

    So you need to have a local window admin account and start the SQL Server service in single user mode and then do all the recovery work by issuing T-SQL command through SQLCMD or Query Analyzer which I found is very light-weight while SSMS in this case might be rejected for connection.

    Good luck!

    Bazinga!

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply