Hi, everyone, today I will tell you how to change or reset the sa sql server password. Let me remind you that sa is a user’s login in MS SQL, which by default has the highest privileges, the accounting itself is local, often even off, but still frequently used. It may happen that you have forgotten the password and thus lost access to the databases, today you will learn how to bypass it and restore access to your database.
And so you have MS SQL, how to install sql, I already told you who do not know the look. You have a task to change the password for the user sa sql.
The default password is sa
Let me remind you of the default password of sa, strange as it may seem, sa
The only requirement is that you must be a local administrator
Change password sa to sql via graphical interface
Start, open the start and follow the path All programs > Microsoft SQL Server 2012 R2 > SQL Server Management Studio Environment
Alternatively, you can open a command line and type ssms there.
It doesn’t matter which way you choose to have the same effect, you’ll get SQL Server Management Studio. But the second method is clearly faster.
By default, Windows Authentication is worth it, which means that you will only be able to log on to your local Windows account or domain, as long as you have permissions.
As you can see, the sa account is disabled by default, but this will not prevent you from changing its password.
ms sql allows you to reset the password sa through its properties by right-clicking and selecting properties from the context menu.
On the General tab, you will see a new password entry field, the only thing to keep in mind is that if you click Require the use of a password policy, you will have to come up with a strong password that meets security requirements, namely
- There must be a capital letter in the password
- There must be a small letter in the password
- There must be a special character or a number in the password
If you remove the checkmark, you can set anything, I’ve set, for example, from 1 to 6. As you can see, changing the password sa in sql is easier than changing the turnip.
The only thing you want to do is to enable the sa account, go to the status item and specify the login name Enabled.
Also nuance, you remember that you have Windows authentication, which means that this is not the case for sa. Right-click on the server name at the top of the hierarchy and select the properties.
On the Security tab, select the SQL Server and Windows Authentication option. You can now login to sql with the sa user.
If Management Studio tries to login and gives an error of 233 that the connection to the server has been successfully established, but then an error occurs when logging in, then do the following.
Open Start > Control Panel > Administration > Services and restart the SQL Server service.
As you can see, I am now successfully logged on.
Change the password sa to sql via the command line
To reset the sa password at the command line in sql, use these commands.
osql -L
With this command, you will see all available MS SQL servers of their SPN
Next, enter the command
osql -S server name-E
write down below.
sp_password NULL, <paste_new_password_tut>, ‘sa’
GO
If the Password validation failed message appears. The password does not meet Windows policy requirements because it is too short. Set a stricter password.
After that, you will reset the sa password to sql.
Another way to use osql is to use it like this
cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn
then we try to connect under a trusted operating system account
osql.exe” -S (local)\ your server name is -E
And the last frontier
ALTER LOGIN SA WITH PASSWORD=’new_password’
it will replace the password with a new_password
Using Asunsoft SQL Password Geeker
There is a utility Asunsoft SQL Password Geeker, unfortunately paid, but able to perform the task. Start it by clicking Browse and then follow the path C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA and open master.mdf
Now, to reset the sa password to sql, select it and click Reset.
Change the sa password in monopoly mode
There is also a fourth way to change the password from sa, and it is to run MS SQL in single-user mode. What we need.
The first is to stop MS SQL Server, it is possible through services, and it is possible from the command line
net stop MSSQLSERVER
Next, open the Windows Registry and navigate to the
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\ MSSQLSERVER
Now you need to set the parameter on the ImagePath -m line to speak of single-user mode. This is how I did it
“C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe” -m -s MSSQLSERVER
Now run MS SQL command
net start MSSQLSERVER
Now SQL is run in single-user mode and allows any member of the local group of computer administrators to connect to an instance of SQL Server with sysadmin rights, but you need to tell SQL about it. You can see the mode of operation in the properties of the service.
On the command line, we write
cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn
sqlcmd.exe: EXEC sp_addsrvrolemember ‘user name сервера\имя’, ‘sysadmin’
GO
Restart the service and enjoy life, don’t forget to remove the -m parameter from the registry. That’s how easy it is to reset the sa password into sql, both by built-in methods and by third parties.