How to Fix Login Failed Microsoft SQL Server Error: 18456

You may encounter the SQL Server Error 18456 if the server could not authenticate the connection and this can be caused by the non-availability of the administrator rights to the SQL server or if the TCP/IP protocol is disabled in the SQL server settings.

The issue arises when the user tries to connect to the SQL server (local or remote) but encounters the error 18456 (with different states).

You can fix the SQL server error 18456 by trying the solutions below but before that, check if restarting the server, client computer, and networking computers solves the issue. Moreover, make sure you are typing the correct username and password (not copy-pasting the address).

Also, check if you are entering the correct database name (no typo in it) and make sure you have updated the configuration file accordingly. Furthermore, check if unlocking the account (by using the query ALTER LOGIN WITH PASSWORD= UNLOCK) solves the issue. If you are seeing the errors in the SQL errors log, then make sure your SQL server is not under attack. Last but not least, make sure the server’s clock and client computer clock is correctly set.

Launch the SQL Server as Administrator and Disable UAC on the Server

You may encounter the error 18456 if the SQL server does not have the elevated permissions to execute its operation and launching it as administrator (or disabling the UAC controls on the server) may solve the problem.

Open the SQL Server as Administrator

1. Click Windows and type SQL Server Management Studio.
2. Now right-click on SMSS and select Run as Administrator.

   

3. Then click Yes (if UAC prompt received) and check if the SQL server is clear of the error 18456.
4. If not, then check if disabling UAC on the server machine solves the issue.

Launch the SQL Server in a Single User Mode

1. Click Windows, type, and open the SQL Server Configuration Manager.
2. Now right-click on the SQL Server service (in the SQL Server Services tab) and select Properties.

   

3. Then head to the Startup Parameters tab and in the Specify a Startup Parameter box, type:

   -m

4. Now click on Add and apply the changes.

   

5. Then right-click on the SQL Server service and select Restart.

   

6. Now click Windows, type: SQL Server Management Studio, right-click on SMSS, and select Run as Administrator.
7. Now check if you can connect to the SQL Server as administrator.
8. If so, then add the domain account to the SQL server and assign it the SysAdmin role.
9. Now go back to the SQL Server Configuration Manager window and remove the -m parameter in the Startup Parameters tab.
10. Then restart the SQL Server service (step 3) and check if the SQL server is working fine.

If the issue persists, check if the startup parameters or path details are properly configured. If the issue is still there, make sure your user account does have the required permissions to the database/ reporting services, and then check if the issue is resolved.

Enable the TCP/IP Protocol in the Server Configuration Manager

The error code 18456 in the SQL server means that the server could not authenticate the connection and this can happen if the TCP/IP protocol required to access the database on a network is disabled in the Server Configuration Manager. In this context, enabling the TCP/IP in the SQL Server Configuration Manager may solve the problem.

1. Click Windows and expand Microsoft SQL Server with a year name like 2008 (you may need to scroll a bit to find the option).
2. Now open SQL Server Configuration Manager and click Yes (if UAC prompt received).
3. Then, expand SQL Server Network Configuration and select Protocols for (the server/database name) in the left pane.
4. Now, in the right pane, double-click on TCP/IP and select Yes in the dropdown of Enabled.

   

5. Then apply your changes and click Windows.

   

6. Now type Services, right-click on the result of Services, and select Run as Administrator.

   

7. Then right-click on the SQL Server (with the server’s name) and select Restart.

   

8. Now check if the SQL server is clear of the error 18456.

If that did not do the trick, then make sure you are connecting to the right port of the SQL server (especially if you are using the server in a multi-server environment).

Change the Authentication Mode of the SQL Server

The SQL server might show the error 18456 if the authentication method of the SQL server is not properly configured (e.g: you are trying to login using SQL server authentication whereas the server is configured to use the Windows authentication). In this case, changing the authentication method of the SQL server may solve the problem. Before moving on make sure the status login for the present user (for example SA) is enabled.

1. In the Object Explorer of Microsoft SQL Server Management Studio, right-click on your server and select Properties.
2. Now, in the left pane, select Security, and in the right pane, select SQL Server and Windows Authentication (or vice versa).

   

3. Then apply your changes and in the Object Explorer, right-click on the server.
4. Now choose Restart and once restarted, check if you can connect to the database without error 18456.

If you cannot log into SQL, then you may install MS Power Tools and run the following in an elevated command:

psexec.exe -i -s ssms.exe

Afterward, you may use the installation account of SQL to make the changes and also make sure the SA account is not disabled:

Enable the SA Account and Reset the Account Password

If you cannot connect to the SQL Server, then enabling the SA account of the SQL server and resetting its password may solve the problem.

1. Launch Microsoft SQL Server Management Studio (you may have to use the domain admin account) and expand Security.
2. Then double-click on Logins and open SA.

   

3. Now enter a new password and confirm the password (make sure you use a strong password).
4. Then head to the Server Roles tab and make sure the following roles are selected:

   Public
   
   Sysadmin

   

5. Now head to the Status tab and in the right pane, select Enabled (under Login).

   

6. Then apply your changes and click the Windows button.
7. Now type Services and right-click on it.
8. Then select Run as Administrator and steer to the SQL Server service.
9. Now right-click on it and select Restart.
10. Once the service is restarted, check if the error 18456 of the SQL server is cleared.

Create a New Login and Restart the Reporting Services

If you cannot use any account to connect to the database, then creating a new login and restarting the reporting services may solve the problem.

1. Launch the Microsoft SQL Server Management Studio and expand its Security tab.
2. Then expand Logins and right-click on it.
3. Now select New Login and enter the credentials (in the login name select the computer account) if using the SQL Server Authentication.

   

4. Then make sure to uncheck “User Must Change Password at Next Login” and select the database.
5. Now head to the Server Roles tab and select the Public role.
6. Then, in the User Mapping tab, make sure to choose the database and select db_owner.

   

7. Now apply your changes and click Windows.
8. Then type Services and right-click on the result of Services. Then select Run as Administrator.
9. Now right-click on the SQL Server Reporting Service and select Restart.

   

10. Then reconnect to the database and check if the SQL server is clear of the error 18456.

If so, make sure that you have created a user in BUILTIN\administrators, and then you can use that user to manage the SQL Server. If you have restored the database from a backup, it will be better to remove and re-add the users to clear any old user entries. If you want to run the SQL server as a different user, then type Microsoft SQL Server in the Windows Search, Shift+Right-click on the SQL Server, and select Run as a Different User.  Last but not least, check if using Azure Data Studio with the SQL server sorts out the issue.