Read: 367
Anything can happen and everything does not happen, but it is better to have a study for the future so as not to rush about later, with the question - How? So now I will analyze the question that has long worried me, 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 to connect to the management snap-in named as SQL Management Studio... Of course, it all works for the time being, but this time always falls on such a paradox:
Everything usually falls on Friday, and on Monday, which does not fall on Friday.
I avoid this in every possible way and constantly work out the scenes and steps to restore and backup if something goes wrong.
What can you learn from the expanded?
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 on behalf of the system account. It's good. Now, at any time when you have scheduled maintenance work, we do this, redefine 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:
Launch Command Prompt with administrator rights and create users and give him local administrator rights:
C: \\ Windows \\ system32\u003e net user ekzorchik [email protected] / add
C: \\ Windows \\ system32\u003e net localgroup Administrators ekzorchik / add
The command completed successfully.
C: \\ Windows \\ system32\u003e exit
Now in the tab Log On service properties SQL Server we change on behalf of whom the service will start as shown in the screenshot below:
This account:. \\ Ekzorchik
Password: [email protected]
Confirm password: [email protected]
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 do the same 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\u003e net stop SQLSERVERAGENT
The SQL Server Agent (MSSQLSERVER) service is stopping ..
The SQL Server Agent (MSSQLSERVER) service was stopped successfully.
C: \\ Windows \\ system32\u003e net stop MSSQLSERVER
C: \\ Windows \\ system32\u003e 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 to the system under the already created local account ekzorchik... Opening the snap S 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 an error:
I conclude that such a trick will not work, remembering what else can be done, here in Ubuntu to the superuser account root in mysql I go into single-user mode, I wonder if there is such a thing.
I return everything as it was, i.e. Starting the service on behalf of the system
Reading the documentation on the official website Microsoft led me here to such actions to reset the password on the account SA:
I run the snap SQL Server Configuration Manager:
Start - All Programs - Microsoft SQL Server 2008 R2 - Configuration Tools - SQL Server Configuration Managerthen stop the service SQL Server (MSSQLSERVER)
After that, I open the stopped service by right-clicking its properties ( Properties), here 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 ... As a result, you should get 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 proceed 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\u003e sqlcmd
1\u003e create login recovery with password \u003d " [email protected]"
2\u003e go
1\u003e sp_addsrvrolemember "recovery", "sysadmin"
2\u003e go
1\u003e quit
Stop the service again SQL Server (MSSQLServer), run properties, go to the tab Advanced and remove the added parameter at startup and start the service again.
I press Connect
Now what? And just change the password sql account sa as planned, and the current one will remain just in case:
(local) (SQL Server 10.50.1600 - recovery) - Security - Logins - highlight the account sa and through the right click Properties I enter a new password:
Password: [email protected]@
Confirm password: [email protected]@
And I press OK... I log out and check that I can connect under the account SQL:
File - Disconnect Object Explorer
File - Connect Object Explorer ...
I press Connect and voila everything worked out as it was intended.
On a note: But everything can be done from the command line:
C: \\ Windows \\ system32\u003e net stop mssqlserver
The SQL Server (MSSQLSERVER) service is stopping.
The SQL Server (MSSQLSERVER) service was stopped successfully.
C: \\ Windows \\ system32\u003e net start mssqlserver / mSQLCMD
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service was started successfully.
C: \\ Windows \\ system32\u003e sqlcmd
1> <я_подключился>
Now I know even more by SQL Server 2008 R2 just in case of emergency, suddenly someone in our department will take it and deliberately change everything without notifying anyone. The task set at the very beginning of this note is completed. On this I say goodbye, respectfully the author of the blog - Ollo Alexander aka ekzorchik.
Post navigation
will appear more often :)
ICD card: 4432-7300-2472-8059
Yandex money: 41001520055047
Hello everyone, today I'll tell you how to change or reset sa password sql server. Let me remind you that sa is a username in MS SQL, which by default has the highest privileges, the account itself is local, often even disabled, but nevertheless often used. It may turn out that you forgot the password from 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. The password does not meet Windows policy requirements because it is too short. Then set a stronger password.
All then you will reset the sa password in sql.
Another use case for 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 \u003d "new_password"
it will replace the password with new_password
Using Asunsoft SQL Password Geeker
There is a utility Asunsoft SQL Password Geeker, unfortunately paid, but capable of performing the task. Run 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 also a fourth way to change the sa password, and it consists in running MS SQL in single-user mode. What do we need.
First, we stop MS SQL Server, it can be done through services, or 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 local Administrators group of the computer to connect to the instance of SQL Server as sysadmin, but you need to tell SQL to do so. You can view the operating mode in the properties of the service.
On the command line, write
cd C: \\ Program Files \\ Microsoft SQL Server \\ 110 \\ Tools \\ Binn
sqlcmd.exe: EXEC sp_addsrvrolemember "server name \\ username", "sysadmin"
We restart the service and enjoy life, do not forget to remove the -m parameter in the registry later. It's so easy to reset the sa password in sql, both by built-in methods and by third-party ones.
"Sa" is a username 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.
The default sa password
Let me remind you the default sa password, oddly enough sa
The only requirement is that you must be a local administrator everywhere
Change sa password in sql via GUI
Let's start, open Start and follow the path All Programs\u003e Microsoft SQL Server 2012 R2\u003e SQL Server Management Studio
Or you can open command prompt and type ssms there.
This will open up SQL Server Management Studio.
The default is Windows Authentication, which means that you can only log in with a local or domain Windows account, as long as you have rights.
The sa account is disabled by default, but that won't prevent you from changing its password.
ms sql allows you to reset sa password through its properties by right-clicking and choosing 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 password policy checkbox is checked, 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 number in the password
If you uncheck the box, you can set a new password and save. The password for the sa user in sql has been changed.
The only thing is, if you want to use the sa account, then you need to enable it, for this go to the status item and specify the Login name as 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 login with the sa user to sql.
If, when you try to log in, Management Studio displays error 233 that the connection to the server was successfully established, but then an error occurred when logging in, then do the following.
Open Start\u003e Control Panel\u003e Administrative Tools\u003e 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. The password does not meet Windows policy requirements because it is too short. Then set a stronger password.
All then you will reset the sa password in sql.
Another use case for 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 \u003d ‘new_password’
it will replace the password with new_password
Using Asunsoft SQL Password Geeker
There is a utility Asunsoft SQL Password Geeker, it is paid, but capable of performing the task. Run 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 also a fourth way to change the sa password, and it consists in running MS SQL in single-user mode.
First, we stop MS SQL Server, it can be done through services, or 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 local Administrators group of the computer to connect to the instance of SQL Server as sysadmin, but you need to tell SQL to do so. You can view the operating mode in the properties of the service.
On the command line, write
cd C: \\ Program Files \\ Microsoft SQL Server \\ 110 \\ Tools \\ Binnsqlcmd.exe: EXEC sp_addsrvrolemember "server name \\ username", "sysadmin"
Restart the service, do not forget to remove the -m parameter in the registry later. Password reset to user sa in sql.
