Retrieve the password of sa account

  • I forgot the password of sa account, therefore I don't be able to login to sql server 2008

    I have another account, but with no especial privileges

    I know that exists tools that retrieve SQL Server password based on hash algoritm and the value of the column called "password" on the table sysxlogins in SQL Server 2000. (I downloaded a project in Visual Basic from this site called SQLPasswordRetireve that doing this.)

    But, in SQL Server 2008, there is no table sysxlogins. I assume that the equivalent table is sys.sql_logins, but here the value for the column "password" displays NULL.

    How could I retrieve the password of sa account ?

    Thanks in advance

    PD

    Due to there is no another account with sysadmin server rol, I can not reset the password.

  • There might be a way to hack it, not sure. I know you could stop the SQL service, save your database and log files, unistall SQL and reinstall it. That would allow you to reset it, but it's a bit of overkill, and eliminates anything you have saved in msdb (jobs, SSIS packages, etc.), model, etc.

    I use Windows authentication on my servers. That eliminates the need for sa, and I usually don't like to allow mixed-mode authentication. If I do, I put a seriously strong password on sa, record that securely, and then make sure nobody ever uses it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • If you have forgotten it, you might have to simply reset it. One workaround (I haven't tried this) is to try and transfer the old one. In sp_helprevlogin, comment out the "AND p.name <> 'sa'" and see if you can get this to transfer over. It won't give you the password, but it could keep things moving. sp_helprevlogin comments this to make everyone reset the thing on the new installation anyway.

  • Here's what I did when somebody installed the server themselves and didn't remember what they put for the password...

    I used the Server Management thingy to log in using windows authentication. Then I could go to the sa account and set the password to something known.

    [font="Arial"]Halfbubble ------------
    You just can't do this stuff unless you're at least half a bubble off center.[/font]

  • Video (works in SQL Server 2008 as well):

    How to recover from being locked out of a SQL Server 2005 database

    Basically you stop SQL Server. You restart it in single user mode. Then you connect locally (on the server) with an account that is a local administrator on the server. SQL Server, when running in single user mode, will automatically treat that user as a member of the sysadmin fixed server role.

    K. Brian Kelley
    @kbriankelley

  • Add user to windows group named "SQLServer2005MSSQLUser$ServerName$SQLServerInstanceName" and login to machine with that account and login to sql server and try to change the password for "sa" account. Hope this helps!

    Manu

  • MANU (3/1/2009)


    Add user to windows group named "SQLServer2005MSSQLUser$ServerName$SQLServerInstanceName" and login to machine with that account and login to sql server and try to change the password for "sa" account. Hope this helps!

    Manu

    Unless you can help it, you should not use these groups. Especially since on a clustered server they aren't local to the server, they are actually at the domain level.

    K. Brian Kelley
    @kbriankelley

  • Dear Brian:

    Thanks for you reply

    I watch the video:

    "How to recover from being locked out of a SQL Server 2005 database"

    but I could not log on the SQL Server

    I have a named instance of SQL Server called "SQLSERVER2008"

    I started in single mode with:

    sqlservr.exe -m -s SQLSERVER2008

    then

    When I run:

    sqlcmd -E -S WIN005\SQLSERVER2008

    where WIN005 is the server and SQLSERVER2008 is the named instance

    it show:

    Login failed for user 'DOMAIN1\user1'. Reason: The account is disabled.

    where DOMAIN1 is the windows domain and user1 is my account in that domain.

    I thought that the connection with sqlcmd is local, with no domain user.

    Have I start windows session in the server with local administrator instead of domain user ?

  • You need to use an account that is a local administrator.

  • Dear Brian:

    Thank you very much for your help.

    The video is very demonstrative

    In my case, it works with few adjustment due to named instance. It works in SQL Server 2008

    I added the option -s to command sqlservr.exe . Also, I added -S to command sqlcmd

    Finally, I not used [BUILTIN\administrator], but [SERVERxx\administrator]

    Best regards

    Roger

  • How to retrieve Password in SQL 2005 witout creating/logging windows authentication?

    Also how to identify passwords using TSQL or through VB programme

  • If you have locked yourself out of SQL Server, look at single-user mode. As far as retrieving passwords? Can't be done. SQL Server stores a hash of the password, not the actual password. You can use a tool like NGS SQuirreL for SQL Server, which will brute force the password, but it's not actually a retrieval.

    K. Brian Kelley
    @kbriankelley

  • Hi Brian,

    How do I get your solution to work on a server that is the DC?!

    I'm running SQL Server 2008 on Windows Server 2008, and this server is the AD DC, and so there are no local accounts.

    I stupidly removed the Administrator login in SQL Server Management Studio, and I don't know the sa password!

    Regards,

    Barry

  • Apologies Brian, please ignore my previous post. I was able to carry out your procedure using the domain admin account.

    It failed the first time because I hadn't closed ALL the SQL Server services and so it was erroring saying there was another user logged in.

    I've now back in and all is well with the world!

    Many thanks,

    Barry

  • Change SA Password in SQL Server Using SQL Server Management Studio

    1.Connect to SQL Server Using SQL Server Management Studio 2. Expand Security -> Logins -> Right click sa to open up Login Properties in SQl management studio, Login section.

    SSMS Expert

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

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