Microsoft SQL Server Error 18456 Login Failed for User
18456 Error Problem
So I was connecting to Microsoft SQL Server and trying to use the SQL Authentication method and I received this event ID error 18456 login failed for user you provided. See below example.
I did some research online and found out that this usually means that your connection request was successfully received by the server name you specified [so why didn’t it work], but the server is not able to grant you access for a number of reasons and throws error: 18456.
Video shows you the steps 🙂
Below is a list of reasons and some brief explanation what to do:
SQL Authentication not enabled: If you use SQL Login for the first time on SQL Server instance than very often error 18456 occurs because Windows Authentication (only) is set in Server properties (security section).
To Access Server Properties, Open SQL Server Management Studio, go to Object Explorer pane (use view if you can’t see it). Use the connect button to connect to database engine for your server. Once connected you will see it in object explorer. Right click server and click properties. Server Properties window will appear.
See below screenshot that might be causing SQL login to fail
You should set Server Authentication to SQL Server Windows Authentication
Invalid userID: SQL Server is not able to find the specified UserID on the server you are trying to get. The most common cause is that this userID hasn’t been granted access on the server but this could be also a simple typo or you accidentally are trying to connect to different server (Typical if you use more than one server)
Invalid password: Wrong password or just a typo. Remember that this username can have different passwords on different servers.
less common errors: The userID might be disabled on the server. Windows login was provided for SQL Authentication (change to Windows Authentication. If you use SSMS you might have to run as different user to use this option). Password might have expired and probably several other reasons…. If you know of any other ones let me know.
18456 state 1 explanations: Usually Microsoft SQL Server will give you error state 1 which actually does not mean anything apart from that you have 18456 error. State 1 is used to hide actual state in order to protect the system, which to me makes sense. Below is a list with all different states and for more information about retrieving accurate states visit Understanding “login failed” (Error 18456) error messages in SQL Server 2005
ERROR STATE | ERROR DESCRIPTION |
State 2 and State 5 | Invalid userid |
State 6 | Attempt to use a Windows login name with SQL Authentication |
State 7 | Login disabled and password mismatch |
State 8 | Password mismatch |
State 9 | Invalid password |
State 11 and State 12 | Valid login but server access failure |
State 13 | SQL Server service paused |
State 18 | Change password required |
If this post helped you, PLEASE take the time to +1 it.
TITLE: Connect to Server
——————————
Cannot connect to LAPTOP-P08KTN7B.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘LAPTOP-P08KTN7B\shanndyyy’. (Microsoft SQL Server, Error: 18456)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error
——————————
BUTTONS:
OK
——————————
Server Name: LAPTOP-P08KTN7B
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536
this is what mine says, what do i do?
TITLE: Connect to Server
——————————
Cannot connect to localhost.
——————————
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476
——————————
BUTTONS:
OK
——————————
What does this error message mean How can I fix this so it will work? Thank you
Looks like a firewall issue. Try disable the firewall and test it out again. Also, you might have to configure TCP/IP within SQL configuration.
very helpful …
You are genius… I was struggling with this error quite a long time and your solution did it in no time….
hi. everyone . im going to login from my software that i built… when i goto login form in my software. it gives me the message unable to login to ‘username-pc\username’ what is is the problem
I m still facing the problem as i cannot login using windows authentication as well
I also facing sa admin problem.But I also follow above these information.above information was not permanently.we can setting again and again.
I hope that anyone my problem solved recently.
Thanks
Marut Kumar tiwari
helped me a lot….THANKS big time
you’re welcome!
I was locked out and got the SQL error 18456. Eventually I managed to reset my lost SA password using SQL Server Password Changer.
Read this article http://www.sqlrecoverysoftware.net/blog/sql-login-error-18546.html and resolve this SQL error 18456 with simplicity
gracias, estuve viendo mas de 8 videos y cada uno variaba en cierta cosas, pero eran semejante, pero con su información resolví, el problema, gracias
Excellent, this helped a lot. Also, don’t forget to restart as the video says. I got stuck there for a couple of minutes. Thanks!
very good, very useful , simple to understand
Thanks a lot
this was helpful
i am not able to login using the SA account
please help me
Are you able to log in using the account that you installed SQL?
i can not thank you enough 😀
excellent article.. resolved my issue too..
Hi,I was working by using my login.I dont know what I did,not able to login to SSMS.All time throwing the error message that login failed for my account .what could be the problem?
Are you able to login using the SA account?
Thnaks… It helped me… Good.
Hi,
I am still facing the same problem, i need how to change the user (sa) settings on the system, cause the authentication to that particular user still windows session based.
Thnks in advance.
1.
Start a command prompt as NT AUTHORITY\NETWORK SERVICE
1. Open a command prompt.
2. Type the following command to create a new service that launches Task Manager (note the spaces after the equals signs): sc create MyService binPath= %WINDIR%\system32\taskmgr.exe type= interact type= own
3. Right click on Computer and go to Manage
4. Open up services and find MyService, right click on it and start it. (If you are in server 2008 it will ask you to view an interactive service message, click show me the message)
5. When Task Manager appears, select File->New Task (Run…) and type cmd. You now have a cmd promt running as NT Authority/Network Services
2.
Enable the SA Account (If not active)
1. In the command prompt type:
osql -S TheNameOfYourSQLServer -E
2. Type this where it displays 1>
ALTER LOGIN sa ENABLE
3. type this where it displays 2>
GO
3.
Reset the SA account’s password (if this is the first time it has been used, it may be blank already)
1. In the command prompt, if you didn’t do this in the last step, type:
osql -S TheNameOfYourSQLServer -E
2. Type this where it displays 1>
sp_password NULL,’NewPassword’,’sa’
3. Type this where it displays 2>
GO
How do you open a command prompt using NT AUTHORITY\NETWORK SERVICE when the password is unknown?
Thanks lot to give such a details explanation. U solved my Problem, good work!
i am still facing that problem
Holy shit, after HOURS of messing around this fixed my problem. The server was set to Windows Authentication only for some reason.
Thank you so much! I googled a lot, and this was the ONLY post that really got deep into the issue. Clear and with screen shots, thanks!!!
You are an absolute hero!
i was struggling with this 18456 error for a couple hours and your post helped me to solve it about 30seconds. Thanks!