Resetting the Sql Server Forgotten “SA” Password with 4 Different Methods

Bunlar; Windows Authentication, Single User Mode, Command Line pass reset ve Dbatools powershell yöntemleridir. Hemen bu yöntemler üzerinden adım adım şifre değiştirmeyi nasıl gerçekleştireceğimize bakalım.

These; Windows Authentication, Single User Mode, Command Line pass reset and Dbatools are powershell methods. Let's take a look at how to change the password step by step through these methods.

1: Windows Authentication

- Open Sql Server Management Studio, from the login window that appears, select the Windows Authentication Option as below and say Connect

- After the connection is established, follow the steps below, and you will change the (SA) password. To briefly explain the steps of the screenshot below, we open the Server breakdown by clicking the + sign. We open the Security breakdown, then Login and double-click the sa user here, as seen in Step 4. Press the OK button and the process is complete.

2: Single User Mode - Tek Kullanıcı Modu

If you have not activated Windows Authentication, you can also try this method. This method will be executed via Command Prompt. Run cmd(Command Line) tool as Administrator. Then follow the steps below in order.

$net stop MSSQLSERVER
$net start MSSQLSERVER -m”SQLCMD”

#Thus, we have switched to the sqlcmd tool. Run the command below while in sqlcmd, but replace the parts that say name and password with the username-password you want and run it, then type "GO" and run it.

$CREATE LOGIN name WITH PASSWORD=’password’
$SP_ADDSRVROLEMEMBER name,’SYSADMIN’
$exit
$net stop MSSQLSERVER && net start MSSQLSERVER

#Now open Sql Server Management Studio and select the Windows Authentication option in step 1 as Sql Server Authentication and log in by entering your newly created username and password.

3: Command Line pass reset - Komut Satırı şifre reset

Again, we open the command line in "Administrator" mode and run the command below.

$osql -L

#Then run the command below, but write your own server name in the section that says server. for example my sql server name is DESKTOP-HYJS45

$OSQL -S DESKTOP-HYJS45 -E
$EXEC sp_password NULL, ‘password’, ’sa’
$exit

#If you run the above command directly (sa) your password will be "password".

4: Dbatools in powershell

Dbatools is an open source powershell project. With this project, you can do your daily work in seconds. We will use the Reset-DbaAdmin function to solve this problem.

First of all, you should add this project to your own computer as a module. If you have an internet connection, things are very easy, you can install this module with a single command. However, if you do not have an internet connection, you will first need to learn the location of your powershell modules from the environment path. You should copy and import the file you downloaded to one of those paths.

$Invoke-Expression (Invoke-WebRequest https://dbatools.io/in)
#OR
$Install-Module dbatools

IF YOU DO NOT HAVE AN INTERNET CONNECTION:
https://github.com/sqlcollaborative/dbatools/archive/development.zip 
download the file here!

$env:PSMODULEPATH
#Run this command in powershell and open the zip you downloaded to one of those folders. I used this folder. (C:\ProgramFiles\WindowsPowershell\Module)

$Import-Module dbatools
$Reset-DbaAdmin -SqlInstance . –Login guleruser

#It will ask you for a password. You must provide a very strong password. Otherwise, you cannot connect to the system because the password criteria do not meet.
#It will automatically restart the service. You can connect to the system and see that the user "guler-user" has been created. Problem solved.

Umarım faydalı olmuştur. - I hope it was useful.