Based on real life experience. Tried to setup a local MSSQL server on the local PC. Then the error came, Error Number: 18456 Severity : 14 State : 1. Initially the setup was smooth as I was login to the DB using the Windows Authentication. Why on earth is there a ‘MSSQL Login failed’ showing?
Note: If you are looking for the solution that I tried in the end, you may proceed to the end of the article and get the solution. If this has helped you, perhaps consider donating via PayPal to help us maintain this website for more helpful articles.
The Journey
Let’s call my DB Server connection SQLEXPRESS01.
Everything was good. The only hiccup was granting permission as this server was installed using a local administrator ID. Perhaps though this part we can leave this for another time.
Anyway, I have created a new Login Credential, let’s call it svcABC (pertaining that svc meant service, as in a service account) which is to be authenticated with SQL Server authentication.
The Beginning of the Error Number: 18456
Now let’s try to login using the created service account svcABC with the passsword.
Click Connect.
Login failed for user ‘svcABC’. (Microsoft SQL Server, Error: 18456) is shown. So off to some digging.
Have dug through an article in StackOverflow regarding this error. It was mentioned that one needs to restart the service using SQL Server Configuration Manager in order for this to take effect.
Thus, I opened the SQL server Configuration Manager and located the Service and chose Restart. This usually should resolve the issue. Though in my case, the login still failed. Then off to the logs. Depending on your location of your Microsoft SQL Server install location, locate the logs as such. Example the below is the location of my MSSQL.
C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS01\MSSQL\Log
View the ERRORLOG as to why this MSSQL login failed using a word editor (ex: Notepad, VS Code).
2022-12-09 13:35:01.72 Logon Login failed for user 'svcABC'. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT: <local machine>]
Oh no, it seems that it still did not work even after the restart.
MSSQL Login failed: What does ‘Integrated authentication only’ Meant?
Digging once again, and this brought me to another article which mentioned that all one needed to do is to restart the MSSQL service (this was already done previously). Though to double confirm if this happened, open the Query and typed this to verify.
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly');
The result returned was 1. What does this mean? It looks like the restart did not take into effect even after countless restart is being done.
It looks like another error to look at. This meant that the server authentication was not configured to authenticate using SQL Server and Windows Authentication mode.
Proceed to right click the server in Microsoft SQL Server Management Studio and click Security and click on the radio button for SQL Server and Windows Authentication mode.
Restart the service once again and check if this resolved your issue. If yes, hooray, it not read on (like what I had)
What to do next for this Error Number: 18456?
Out of ideas for this MSSQL login failed scenario, it hit me to restart it directly using the Windows Services tab.
Services located. Click the restart button or right click and click Restart. If this does not work for you, perhaps you need to login as an Administrator (this happened to me).
Now then, to confirm if the connection is now working, let us try to run the SQL query again. This time it returns 0. When it returns 0, it means that SQL Server can now take authentication from SQL Login IDs. Now let us try to login to the SQL Server.
Conclusion For this MSSQL Error
Error Number: 18456 is just one of those errors usually is related to the login account unable to authenticate. I believe the issue I had was more due to permission setting without the privilege to restart the services. So always make sure that your ID has sufficient privilege to stop and start the SQL Server services.
Till our next article. Donation is always welcome if this article is useful to you.
