Changing the sa password in sql. Changing the sa password in MS SQL Using Asunsoft SQL Password Geeker
Read: 366
Anything can happen and anything can’t happen, but it’s better to have a plan for the future so as not to rush around later with the question - How? So now I will look at the question that has been bothering me for a long time, what to do if on the database server MS SQL Server 2008 R2 which I inherited from the previous system administrator did not leave at all no passwords for connecting to the management snap-in called SQL Management Studio. Of course, it all works for the time being, but this time always falls on the following paradox:
Everything usually falls on Friday, and on Monday what didn't fall on Friday.
I avoid this in every possible way and constantly work out scenarios and steps for recovery and backup if something goes wrong.
What can be learned from what is unfolded?
Start - Control Panel - Administrative Tools - Services, service related to service MSSQL - SQL Server (MSSQLSERVER) when opening its properties ( Properties) and go to the tab Log On runs under the system account. This is good. Now, at any time when you have scheduled maintenance work, we do this, we override the launch of this service on behalf of an account with (required) administrator rights on this system, but first, of course, we create a user:
Let's launch Command Prompt with administrator rights and create users and give him local administrator rights:
C:\Windows\system32>net user ekzorchik 712mbddr@ /add
C:\Windows\system32>net localgroup Administrators ekzorchik /add
The command completed successfully.
C:\Windows\system32>exit
Now in the tab Log On service properties SQL Server change on behalf of whom the service will be launched as shown in the screenshot below:
This account: .\ekzorchik
Password: 712mbddr@
Confirm password: 712mbddr@
This will be followed by a window with the text:
The account .\ekzorchik has been granted the Log On As a Service right.
The new logon name will not take effect until you stop and restart the service
We follow the same principle for the service. SQL Server Agent On behalf of whom to run this service.
Well, all the changes will be applied correctly when the service is stopped and then started.
C:\Windows\system32>net stop SQLSERVERAGENT
The SQL Server Agent (MSSQLSERVER) service is stopping..
The SQL Server Agent (MSSQLSERVER) service was stopped successfully.
C:\Windows\system32>net stop MSSQLSERVER
C:\Windows\system32>net start SQLSERVERAGENT
The SQL Server Agent (MSSQLSERVER) service is starting.
The SQL Server Agent (MSSQLSERVER) service was started successfully.
Then I do Logoff, and then Logon into the system under an already created local account ekzorchik. I open the S snap-in QL Management Studio:
Start - All Programs - Microsoft SQL Server 2008 R2 - SQL Server Management Studio and log in using Windows Authentication
I press Connect, but in response, instead of successfully logging in, I get the following error:
I conclude that such a trick will not work, remembering what else can be done, here in Ubuntu to a superuser account root V mysql I go into single-user mode, I wonder if there is such a thing here.
I return everything as it was, i.e. Starting the service on behalf of the system
Reading documentation on the official website Microsoft led me to these steps to reset your account password SA:
I launch the equipment SQL Server Configuration Manager:
Start - All Programs - Microsoft SQL Server 2008 R2 - Configuration Tools - SQL Server Configuration Manager, then I stop the service SQL Server (MSSQLSERVER)
After that, I open the stopped service by right-clicking its properties ( Properties), then I switch to the tab Advanced and in the line:
Startup Parameters after:-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files \Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
I indicate the key -mSQLCMD . The result should be the following:
Now I start the service to apply the settings. If by service SQL everything is fine in the management snap-in, the service has started, then we move on to the next stage.
I open the command line console with administrator rights:
Start - All Programs - Accessories- launch Command Prompt with administrator rights
C:\Windows\system32>sqlcmd
1> create login recovery with password="712mbddr@"
2> go
1> sp_addsrvrolemember "recovery","sysadmin"
2> go
1> quit
Stopping the service again SQL Server (MSSQLServer), launch properties, go to the tab Advanced and I remove the added parameter in startup and start the service again.
I press Connect
What now? Let's just change the password sql account sa as planned, but the current one will remain just in case:
(local) (SQL Server 10.50.1600 - recovery) - Security - Logins- I select an account sa and via right click Properties I enter a new password:
Password: 712mbddr@@
Confirm password: 712mbddr@@
And I press OK. I log out and check that I can connect to my account. SQL:
File - Disconnect Object Explorer
File - Connect Object Explorer…
I press Connect and voila everything worked out as planned.
Note: But everything can be done from the command line:
C:\Windows\system32>net stop mssqlserver
The SQL Server (MSSQLSERVER) service is stopping.
The SQL Server (MSSQLSERVER) service was stopped successfully.
C:\Windows\system32>net start mssqlserver /mSQLCMD
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service was started successfully.
C:\Windows\system32>sqlcmd
1> <я_подключился>
Now I know even more SQL Server 2008 R2 just in case someone in our department takes it and deliberately changes everything without warning anyone. The task set at the very beginning of this note has been completed. With this I say goodbye, with respect, the author of the blog - Ollo Alexander aka ekzorchik.
Post navigation
will appear more often :)
ICD card: 4432-7300-2472-8059
Yandex-money: 41001520055047
“sa” is a user login in MS SQL, which by default has the highest privileges; the account itself is local, often even disabled, but nevertheless often used. If you have lost or forgotten the password for this “sa” account, then you will not have access to manage your databases.
Default password sa
Let me remind you that the default password is sa, oddly enough sa
The only requirement is that you must be a local administrator everywhere
Change sa password in sql via GUI
Let's get started, open Start and go to All Programs > Microsoft SQL Server 2012 R2 > SQL Server Management Studio
Or you can open a command prompt and type ssms there.
SQL Server Management Studio will open.
By default, Windows authentication is set, which means that you can only log in with a local Windows account or a domain account, as long as you have rights.
The sa account is disabled by default, but this will not prevent you from changing its password.
ms sql allows you to reset the sa password through its properties, to do this, right-click and select properties from the context menu.
On the general tab you will see a field for entering a new password, the only thing to keep in mind is that if the Require the use of a password policy checkbox is checked, you will have to come up with a strong password that meets security requirements, namely
- The password must have a capital letter
- Password must have a lowercase letter
- There must be a special character or number in the password
If you uncheck the box, you can set a new password and save it. The password for the sa user in sql has been changed.
The only thing is that if you want to use the sa account, then you need to enable it; to do this, go to the status item and specify Login Name Enabled.
Another nuance, you remember that you have Windows authentication, which means that this is not suitable for us for sa. Right-click on the server name at the top of the hierarchy and select properties.
On the Security tab, select the SQL Server and Windows Authentication option. Now you can log in using the sa user in sql.
If, when you try to log in, Management Studio gives an error 233 that the connection to the server was successfully established, but then there was an error when logging in, then do the following.
Go to Start > Control Panel > Administrative Tools > Services and restart the SQL Server service.
Then the connection is successful and without errors.
Change sa password in sql via command line
To reset the sa password in sql via the command line, use the commands.
With this command you will see all available MS SQL servers and their SPN
sp_password NULL,<вставьте_новый_пароль_тут>, 'sa'
If the message Password validation failed appears. The password does not meet Windows policy requirements because it is too short. Then set a stronger password.
All after this you will reset sa password in sql.
Another option for using osql is like this
cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn then we try to connect under a trusted OS account
osql.exe" -S (local)\your server name -E
And the last frontier
ALTER LOGIN SA WITH PASSWORD= ‘new_password’
it will replace the password with new_password
Using Asunsoft SQL Password Geeker
There is a utility called Asunsoft SQL Password Geeker, it is paid, but it can perform the task. Launch it, click Browse, then follow the path C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA and open master.mdf
now to reset sa password in sql, select it and click Reset.
Change sa password in exclusive mode
There is a fourth way to change the password for sa, and it is to run MS SQL in single-user mode.
The first thing is to stop MS SQL Server, you can do it through services, or you can do it from the command line
net stop MSSQLSERVER
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MSSQLSERVER
Now you need to set the parameter in the line ImagePath-m will just talk about single-user mode. I got it like this
"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -m -s MSSQLSERVER
Now start MS SQL with the command
net start MSSQLSERVER
SQL is now running in single-user mode and allows any member of the computer's local Administrators group to connect to the instance of SQL Server with sysadmin rights, but you need to tell SQL this. You can view the operating mode in the service properties.
On the command line we write
cd C:\Program Files\Microsoft SQL Server\110\Tools\Binnsqlcmd.exe: EXEC sp_addsrvrolemember "server name\user name", "sysadmin"
We restart the service, do not forget to remove the -m parameter in the registry. Password reset for user sa in sql.
Hello everyone, today I’ll tell you how to change or reset password sa sql server. Let me remind you that sa is a user login in MS SQL, which by default has the highest privileges; the account itself is local, often even disabled, but nevertheless often used. A situation may arise that you forgot the password for it and thereby lost access to the databases; today you will learn how to bypass this and restore access to your databases.
sp_password NULL,<вставьте_новый_пароль_тут>, 'sa'
If the message Password validation failed appears. The password does not meet Windows policy requirements because it is too short. Then set a stronger password.
All after this you will reset sa password in sql.
Another option for using osql is like this
then we try to connect under a trusted OS account
osql.exe" -S (local)\your server name -E
And the last frontier
ALTER LOGIN SA WITH PASSWORD= "new_password"
it will replace the password with new_password
Using Asunsoft SQL Password Geeker
There is a utility called Asunsoft SQL Password Geeker, which unfortunately is paid, but is capable of completing the task. Launch it, click Browse, then follow the path C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA and open master.mdf
now to reset sa password in sql, select it and click Reset.
Change sa password in exclusive mode
There is a fourth way to change the password for sa, and it is to run MS SQL in single-user mode. What do we need?
The first thing is to stop MS SQL Server, you can do it through services, or you can do it from the command line
net stop MSSQLSERVER
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MSSQLSERVER
Now you need to set the parameter in the line ImagePath-m will just talk about single-user mode. I got it like this
"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -m -s MSSQLSERVER
Now start MS SQL with the command
net start MSSQLSERVER
SQL is now running in single-user mode and allows any member of the computer's local Administrators group to connect to the instance of SQL Server with sysadmin rights, but you need to tell SQL this. You can view the operating mode in the service properties.
On the command line we write
cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn
sqlcmd.exe: EXEC sp_addsrvrolemember "server name\user name", "sysadmin"
Restart the service and enjoy life, do not forget to remove the -m parameter in the registry later. This is how you can simply reset the sa password in sql, using both built-in and third-party methods.