Post reply

Forgot SA password

  • March 25, 2015 at 2:52 am

    #302511

    Hi Experts,

    I installed SQL server and i dont remember what password i gave. Is there any way to recover / Change the password?

    Thanks in Advance.

    Regards,

    Vijay

  • March 25, 2015 at 3:14 am

    #1786204

    Vijay

    Yes, log on to the server as an administrator, start SQL Server in single user mode, connect locally using Windows authentication, change the sa password, and restart SQL Server.

    John

  • March 25, 2015 at 3:18 am

    #1786206

    Hi John,

    Thanks for the reply. Problem is, i am unable to log in via Windows authentication as well.

    Regards,

    Vijay

  • March 25, 2015 at 3:20 am

    #1786207

    Why?

  • March 25, 2015 at 3:51 am

    #1786213

    John,

    I dont know what is the exact problem.

    Regards,

    Vijay

  • March 25, 2015 at 4:29 am

    #1786217

    If you can't connect as a windows logon, it's gone. I sure hope this isn't your production server.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 25, 2015 at 5:27 am

    #1786225

    Grant

    Would the discussion I had with a different user HERE be of use?

    I didn't hear anything back from them in the end, but the technique is supposed to work.

    I've not had to use it myself ever, but would like to read your point of view.

  • March 25, 2015 at 6:32 am

    #1786257

    You could shut down the service (although if you can do that, you ought to be able to connect to it) and the copy the database files to a safe location. Reinstall SQL Server and get the security right and then try attaching the databases. Assuming stopping the service didn't leave any of them in an uncrecoverable state, that could work.

    So, yeah. I think you're on to the best path for recovery. But, wow, talk about an outage.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 25, 2015 at 6:35 am

    #1786261

    Yes, or he could tell us why he is "unable to log in via Windows authentication". I'd be surprised if he's tried exactly what I suggested in my first post.

    John

  • March 25, 2015 at 6:35 am

    #1786263

    Yeah it's nasty.

    Pretty much the first step in any installation document I write (if the organisation doesn't have one, or amend if they do) is to create a Windows group that is the SQL admin and then disable sa (and preferably SQL logins completely) from logging in.

    Heck of an outage as you say - hopefully there's some out-of-hours overtime to be had! 😉

  • March 25, 2015 at 6:46 am

    #1786266

    EMarkM (3/25/2015)

    Yeah it's nasty.

    Pretty much the first step in any installation document I write (if the organisation doesn't have one, or amend if they do) is to create a Windows group that is the SQL admin and then disable sa (and preferably SQL logins completely) from logging in.

    Heck of an outage as you say - hopefully there's some out-of-hours overtime to be had! 😉

    Gotta love a consultant!

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 25, 2015 at 6:51 am

    #1786269

    If you've already granted access to other accounts, and the issue is that you can't remember your own the SA login password, then you can can login under a different account and attempt to crack the SA password using the below script.

    By querying against the sys.sql_logins table and leveraging the pwdcompare() function, you can quickly try out an unlimited number of passwords without going through the normal login process. Just load up your passwords into the @PW table, run the script, and it will return both sql account name and password for any hits.

    declare @PW table (pwtext varchar(180) not null primary key);

    insert into @PW (pwtext) values

    ('sa'),('dev'),('prod'),('admin'),('admin1'),('administrator')

    ,(''),('password'),('123456'),('12345678'),('1234')

    ,('qwerty'),('12345');

    select @@servername servername, name, pw.pwtext

    , type_desc, create_date, modify_date

    , is_disabled, is_policy_checked, is_expiration_checked

    from sys.sql_logins l

    join @PW pw on pwdcompare(pw.pwtext, l.password_hash) = 1;

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 12 posts - 1 through 11 (of 11 total)

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