Login to Microsoft SQL Server Error: 18456

问题:

I am getting this error while trying to connect to the SQL Server.

Microsoft SQL Server Error: 18456

Can anybody tell me what the error code means?

回答1:

If you're trying to connect using "SQL Server Authentication" then you may want to modify your server authentication:

Within the Microsoft SQL Server Management Studio in the object explorer:

1. Right click on the server and click Properties

2. Go to the Security page

3. Under Server authentication choose the SQL Server and Windows Authentication mode radio button

4. Click OK

5. Restart SQL Services

回答2:

Check out this blog article from the data platform team.

http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

You really need to look at the state part of the error message to find the root cause of the issue.

2, 5 = Invalid userid
6 = Attempt to use a Windows login name with SQL Authentication
7 = Login disabled and password mismatch
8 = Password mismatch
9 = Invalid password
11, 12 = Valid login but server access failure
13 = SQL Server service paused
18 = Change password required

Afterwards, Google how to fix the issue.

回答3:

Before opening, right-click and choose 'Run as Administrator'. This solved the problem for me.

回答4:

first see the details of the error if "state" is "1" Ensure the database is set for both SQL and Windows authentication under SQL server / Properties / Security.

for other state see the above answers ....

回答5:

1. Check whether mixed mode authentication is enabled in you server->properties
2. Then create a login in the server->security
3. create an user for that login in your database
4. Then restart your server by right clicking the instance and select restart

回答6:

I have faced this issue.

Please look at the attached image,

Step 1: Go to server property

Step 2: Go to Security

Step 3: Change server authentication as SQL server and WindowsAuthenication mode

and restart your Sql server.

回答7:

Just happened to me, and turned out to be different than all other cases listed here.

I happen to have two virtual servers hosted in the same cluster, each with it own IP address. The host configured one of the servers to be the SQL Server, and the other to be the Web server. However, SQL Server is installed and running on both. The host forgot to mention which of the servers is the SQL and which is the Web, so I just assumed the first is Web, second is SQL.

When I connected to the (what I thought is) SQL Server and tried to connect via SSMS, choosing Windows Authentication, I got the error mentioned in this question. After pulling lots of hairs, I went over all the setting, including SQL Server Network Configuration, Protocols for MSSQLSERVER:

Double clicking the TCP/IP gave me this:

The IP address was of the other virtual server! This finally made me realize I simply confused between the servers, and all worked well on the second server.

回答8:

18456 Error State List

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

Potential causes 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 server might be set in Windows Authentication mode (only).

How to fix? Check this SQL Server and Windows Authentication Mode page.

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

Hope that helps

回答9:

Right Click the User, go to properties, change the default database to master This is the screen print of the image which shows what you have to check if you have the error 19456. Sometimes it default to a database which the user doesn't have permission

回答10:

First go to start bar then search local services Then click on "view local services" Then it will open service window then go to SQL Server(MSSQLSERVER) right click on it and click on stop and then again right click on it and click start. Now you can able to login and put your user name as 'sa' and password is your won password.

回答11:

If you change a login user credential or add new login user then after you need to log in then you will have to restart the SQL Server Service. for that

GO to--> Services

Then go to SQL Server(MSSQLSERVER) and stop and start again

Now try to log in, I hope You can.

Thanks

回答12:

Please check to see if you are connected to the network if this is a domain member PC. Also, make sure you are not on a dual home PC as your routes may be incorrect due to network metrics. I had this issue when I could not connect to the domain the SQL windows authentication switched to the local PC account but registered it as a SQL authentication. Once I disabled my wireless adapter and rebooted, the Windows integration switched back to the domain account and authenticated fine. I had already set up Mixed mode as you had already done as well so the previous posts do not apply.

回答13:

For me, it was wrong login and password.

回答14:

Also you can just login with windows authentication and run the following query to enable it:

ALTER LOGIN sa ENABLE ;  
GO  
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;  
GO

Source: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode

回答15:

I believe this can happen if you are trying to log in with a user that is defined in Active Directory, but attempt using "SQL Server Authentication" in the login screen. I do not know how to specify a different user with NTLM/Windows Authentication: When I click the Windows Authentication dropdown, the username and password is blanked out, and I can only log in as myself.

回答16:

In my case multiple wrong attempts locked the account.To do that I had tried running the below query and it worked: ALTER LOGIN WITH PASSWORD= UNLOCK And make sure to set the option "Enforce Password Security" option for specific user to be unchecked by right click on Sql Server -> Properties.

回答17:

SQL Server connection troubleshoot

In case you are not able to connect with SQL Authentication and you've tried the other solutions.

You may try the following:

Check connectivity

• Disable Firewall.
• Run PortQry on 1434 and check the answer.

Check the state

• Try to connect with SSMS or sqlcmd and check the message.
• State 1 is rarely documented but it just mean you don't have the right to know the true state.
• Look at the log file in the directory of SQL server to know what is the state.

The State 5

What ? my login doesn't exist ? it's right there, I can see it in SSMS. How can it be ?

The most likely explanation is the most likely to be the right one.

The state of the login

• Destroy, recreate it, enable it.
• reset the password.

Or...

"You don't look at the right place" or "what you see is not what you think".

The Local DB and SQLEXPRESS conflict

If you connect with SSMS with Windows authentication, and your instance is named SQLEXPRESS, you are probably looking at the LocalDb and not the right server. So you just created your login on LocalDb.

When you connect through SQL Server authentication with SSMS, it will try to connect to SQLEXPRESS real server where your beloved login doesn't exist yet.

Additional note: Check in the connection parameters tab if you've not forgotten some strange connection string there.

回答18:

I have faced the same issue. You need to enable the Mixed Mode Authentication first. For more details How to enable Mixed Mode Authentication

回答19:

Another worked solution for me. serever->security->logins->new logins->General->create your user name as login name,Click sql server authentication add passwords

uncheck the password verification three checkboxes . This will work.

Remeber to change the server properties ->Security from Server authentication to SQL Server and Windows Authentication mode

回答20:

you can do in linux for mssql change password for sa account

sudo /opt/mssql/bin/mssql-conf setup

The license terms for this product can be downloaded from
 http://go.microsoft.com/fwlink/?LinkId=746388 Jump Jump
and found in /usr/share/doc/mssql-server/LICENSE.TXT.
Do you accept the license terms? [Yes/No]:yes
  Setting up Microsoft SQL Server
Enter the new SQL Server system administrator password:  --Enter strong password
Confirm the new SQL Server system administrator password: --Enter strong password
 starting Microsoft SQL Server...
 Enabling Microsoft SQL Server to run at boot...
Setup completed successfully.

回答21:

I got this error after creating a new sysadmin user under my SQL instance. My admin user was created under a specific domain MyOrganization/useradmin

Using useradmin on a disconnected environment allows you to create other users using SQL Server authentication, but as soon as you try to login you get

Microsoft SQL Server Error: 18456

To fix the problem, try to connect again to your Organization network and create the user while you are connected and then you can get disconnected and will work.

回答22:

I have tried this solution and worked for me 1-Enable SQL and windows mixed authentication mode from SQL server properties. 2-Restart the server from SQL server configuration manager * make sure your password is the correct one.

https://www.liquidweb.com/kb/troubleshooting-microsoft-sql-server-error-18456-login-failed-user/