Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Recover sa password [closed]

I have a computer which was used by another employee.

SQL Server 2008 R2 was installed but I don't know the 'sa' password.

When I try to alter the login, it gives below error.

Cannot alter the login 'sa', because it does not exist or you do not have permission.

When I try to restore a database, it gives a different permission error.

(When I enter the Security --> Logins --> sa --> Properties windows authentication is disabled.)

Can I change it?

P.S: Password is not "password" :)

like image 570
Stack User Avatar asked Jul 13 '12 18:07

Stack User


People also ask

How do I recover my SA password?

If you do not know the “sa” login password, create a SQL server login using the below T-SQL script. CREATE LOGIN NewSA WITH PASSWORD = 'Password@1234'; Add the login NewSA to the server level role sysadmin. Please use the following T-SQL script to add the SQL Server login to the server level role sysadmin.

Where is SA password stored?

The sa user is a SQL Server login and its password is encrypted and stored in the DMV sys. sql_logins (Database Management View) in the master database.

Why is SA account locked out?

When password policy is checked for a SQL Server login account, your account will get locked out after too many failed login attempts. In this article we'll show you 2 methods to unlock SQL Server SA account when it is locked out or disabled.


1 Answers

The best way is to simply reset the password by connecting with a domain/local admin (so you may need help from your system administrators), but this only works if SQL Server was set up to allow local admins (these are now left off the default admin group during setup).

If you can't use this or other existing methods to recover / reset the SA password, some of which are explained here:

  • Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005
  • Is there a way I can retrieve sa password in sql server 2005
  • How to recover SA password on Microsoft SQL Server 2008 R2

Then you could always backup your important databases, uninstall SQL Server, and install a fresh instance.

You can also search for less scrupulous ways to do it (e.g. there are password crackers that I am not enthusiastic about sharing).

As an aside, the login properties for sa would never say Windows Authentication. This is by design as this is a SQL Authentication account. This does not mean that Windows Authentication is disabled at the instance level (in fact it is not possible to do so), it just doesn't apply for a SQL auth account.

I wrote a tip on using PSExec to connect to an instance using the NT AUTHORITY\SYSTEM account (which works < SQL Server 2012), and a follow-up that shows how to hack the SqlWriter service (which can work on more modern versions):

  • Recover access to a SQL Server instance
  • More on Recovering Access to a SQL Server Instance

And some other resources:

  • Connect to SQL Server When System Administrators Are Locked Out

  • Leveraging Service SIDs to Logon to SQL Server Instances with Sysadmin Privileges

like image 108
Aaron Bertrand Avatar answered Sep 27 '22 00:09

Aaron Bertrand