Trying to make a connection but facing SQL Server Error? Well, a major of individuals have already complained about the error that occurred but is unable to deal with it effectively. Therefore, we have dug deeper and brought to you the best solutions to ease the problem.
There are many causes that lead to the trouble, be it an incorrect password, invalid username, inappropriate connection, or anything in between. Here’s the comprehensive guide covering all aspects related to the error. Scroll down the page to the bottom and fix the issue in no time.
What is SQL Server Error 18456?
Before jumping into the solutions to fix the SQL Server Error 18456, let’s first understand what this error is all about. A message is displayed on the screen when an attempt to connect is rejected due to an authentication failure involving a poor password or user name: “User ‘user name>’ was unable to log in. (Error: 18456 in Microsoft SQL Server) “.
The following is additional information returned to the client:
“Login failed for user ‘<user_name>’. (.Net SqlClient Data Provider)”
This specific thing is known as SQL error. The nature of the authentication problem is intentionally hidden in the error message sent to the client to increase security. However, an error in the SQL Server error log provides an error state that corresponds to an authentication failure condition.
Steps of SQL Server Error 18456
This SQL Error is further divided into many states, and each of them indicates a certain condition. Let’s find more about these states here.
Stage 1: No further information is given at this stage, which indicates that as a SQL Administrator, you do not have the authority to receive error details. (Attempt to connect to SQL after logging in as an Administrator on the server.)
Stage 2-5: The errors at these stages indicate that the username you’ve entered is incorrect. However, there’s a chance the user doesn’t exist or is misspelled.
Stage 6: At this stage, a Windows username was utilized in order to carry out SQL Authentication. If you’re using Windows usernames, utilize Windows Authentication.
Stage 7: The login for the username is disabled, and the password is wrong. As a SQL Administrator, activate the user’s login and change the password.
Stage 8-9: The password for the user is wrong. Confirm that the password is accurate, or log in as a SQL Administrator and reset the password.
Stage 11-12: Although the login is genuine, access to the server was denied at this stage of the SQL Error. To resolve this, you can run your program as an administrator, then give the Windows using a specific login and permissions on the SQL server.
Stage 18: The password for the user must be changed, leading to the error. You can log in as a SQL Administrator and change the user’s password to fix the trouble.
What are the causes of SQL Server Error 18456
There are plenty of causes that triggers the SQL Server Error 18456. It is essential to know each cause to find the best solution to handle the problem.
- During server login, if either the username or password is invalid, an error will be generated.
- When users first log on to a SQL Server instance, they may see SQL Server error 18456. “Windows Authentication” is enabled in the security area, and SQL Server cannot detect the user.
- Your login password may have run out of validity. That can also happen if the username on the server is disabled.
How to Fix The Error?
Since now you are well aware of everything about the SQL Error, it’s time to unwrap the solutions to fix the problem. Here’re mentioned some of the practical methods to carry out.
- Enable TCP/ IP protocol
The SQL Server error number 18456 indicates that the server does not authenticate the connection. That can occur if the TCP/IP protocol necessary to connect to the database over a network is disabled in the Server Configuration Manager.
Enabling TCP/IP in SQL Server Configuration Manager may solve the problem in this case. Follow the steps to root out the trouble.
- Expand Microsoft SQL Server with a year name like 2008 under Windows (you may need to scroll a bit to find the option).
- Now click Yes in SQL Server Configuration Manager.
- Then, launch SQL Server Network Configuration available in the left panel and select Protocols for the server/database name.
- Double-click TCP/IP in the right pane and choose Yes from the Enabled menu.
- After that, click Windows to apply your changes.
- Now type Services into the search box, right-click the result, and choose Run as Administrator.
- Then, on the SQL Server with the server’s name, right-click and select Restart.
- Examine the SQL server to see if the error 18456 has been resolved.
If it doesn’t work, double-check that you’re using the correct SQL server port. This is valid if you use the server in a multi-server environment.
Restore Repair can examine the repositories and repair faulty or missing files if the problem is with your computer, laptop, or notebook. When the problem is caused by system corruption, this typically works.
- Reset Microsoft SQL Server User Password
Using Windows local authentication to connect to the SQL server avoids the login failed problem 18456 for user SA because it does not require password authentication. Here’s how you can do it.
- Start the Microsoft SQL Server Management Studio application.
- From the drop-down menu, choose Windows Authentication.
- Then, to connect to the SQL Server, click the “Connect” button.
- Then, using the Object Explorer, expand the Security folder. Then go to the Logins folder.
- At usage “Sa”, double-click. Navigate to the Properties and reset the password.
- Create a new login
If you do not access the database using an account, try creating a new login and restarting the reporting services.
- Expand the Security tab in the Microsoft SQL Server Management Studio.
- Right-click on Logins after expanding it.
- If using SQL Server Authentication, click New Login and enter the credentials (in the login name, select the computer account).
- Then choose the database and uncheck “User Must Change Password at Next Login.”
- Now pick the Public role from the Server Roles tab.
- Make sure to choose the database and DB owner in the User Mapping tab.
- Now click Windows and apply your changes.
- After that, type Services and right-click on the result. After that, choose Run as Administrator.
- Restart the SQL Server Reporting Service by right-clicking on it.
- Reconnect to the database and verify that the SQL server is free of the 18456 error.
If that’s the case, make sure you’ve created a user in BUILTINadministrators so you can control the SQL Server. If you’ve restored the database from a backup, you should remove and re-add users to any previous user entries.
To run the SQL server as a different user, type Microsoft SQL Server into the Windows Search box, then Shift+Right-click on the SQL Server and select Run as a Different User. Last but not least, see if the issue is resolved by using Azure Data Studio with the SQL server.
- Launch SQL server as administrator
If the SQL server does not have elevated permissions to do its job, you may get the error 18456, and starting it as administrator (or disabling the UAC protections on the server) may fix the problem.
- Search for SQL Server Management Studio under Windows.
- Right-click SMSS and choose Run as Administrator from the context menu.
- Then, if a UAC prompt appears, click Yes to confirm that the SQL server is free of error 18456.
- Whether not, see if turning off UAC on the server machine fixes the problem.
Conclusion
That’s all about it. In this guide, you have learned different stages of the error and some easy ways to fix them. SQL Server Error 18456 occurs when the server does not authenticate the connection.
It can be caused by a lack of administrator permissions on the SQL server or the TCP/IP protocol being deactivated in the SQL server settings. Try the techniques mentioned above to resolve SQL server error 18456.
