Fixed: Microsoft SQL Server Login Failed Error 18456 for User SA
How to Fix Microsoft SQL Server SA Login Failed Error 18456?
There may be two reasons why Microsoft SQL Server SA login is failed for Error 18456. On one hand, disable the SQL server authentication may let the user SA login failed for error 18456. On the other hand, the incorrect SA password may cause login failure too. So the solutions to the issue that Microsoft SQL server user SA login failed for error 18456 are to reset new passwords for the login accounts and enable the SQL server authentication mode.
- Content:
- Part-1: Reset Microsoft SQL server user SA Password
- Part-2: Enable SQL Server Authentication Mode to Get Rid of Login Failed Error 18456
Part-1: Reset Microsoft SQL server user SA Password
Way-1: Login with Windows Authentication
Using Windows local authentication to connect to the SQL server does not require password authentication, which can avoid login failed error 18456 for user SA.
1. Run the Microsoft SQL Server Management Studio.
2. Select the Windows Authentication from the draw-down list.
3. Then click the "Connect" button to log in to the SQL Server.
4. Then expand the folder of Security from the Object Explorer. Then head to the folder of Logins.
5. Double click at use sa. And then reset the password in sa Properties.
Way-2: Reset the SA password with Cocosenor SQL Password Tuner
SQL Password Tuner is designed to reset the SQL Server system admin (SA) password. As long as you forgot the SA password or run into the login failed error 18456 of the user SA, you can use this tool to quickly reset the SA password. If Way-1 and Way-2 can't fix the issues, go to the Part-2 to check the SQL server authentication configurations.
1. Download the SQL Password Tuner and install it on your computer.
2. Launch the SQL Password Tuner and click the "Open File" button from the user interface.
3. Open and add the SQL server master.mdf file. Then select the SA account from the list. The default path of the SQL Server database file is C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\master.mdf.
4. Then select the SA account from the list. Click the "Reset" button and then enter a new password you want to set for the SQL SA account.
Part-2: Enable SQL Server Authentication Mode to Get Rid of Login Failed Error 18456
To get rid of the SA login failure error 18456, we are trying to enable the SQL server authentication mode.
1. Launch the Microsoft SQL Server Management Studio on you PC. Connect the SQL Server with Windows Authentication.
2. Then right click at the SQL server instance. Then click Properties.
3. In the Server Properties, click Security tab, and then select the second option "SQL Server and Windows Authentication mode". Click OK to finish. This mode allows users to login with the SA account and won't cause the login failed error 18456.
4. Restart the SQL Server. You can go to Windows Service to restart it (or go to the SQL server configuration Manager to restart the SQL Server).
Related Articles
- Solved: How to Reset SA Password in MS SQL Server
- How to Change SA Password in SQL Server Management Studio
- Recover SA Password via SQL Server Password Recovery Tool
- How to Reset MS Access Database Password
- Set and remove password protection for Excel 2013
- How to Open a Password Protected Excel File if Forgot Password
