[sql-server] 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?

This question is related to sql-server connection error-code

The answer is


For me, it was wrong login and password.


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


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.


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


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.


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


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.


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.


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.


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.

enter image description here


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:

screenshot of TCP/IP configuration

Double clicking the TCP/IP gave me this:

TCP/IP properties, showing wrong IP address

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.


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.


I have faced the same issue. In case you see it, run this query:

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

then it shows a 18456 error code.

Do one thing again run same query after reconnecting SQL server. It will work fine.


  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

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.

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


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 ....


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


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


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 gray color row

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

Now try to log in, I hope You can.

Thanks


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.


Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to connection

Apache Server (xampp) doesn't run on Windows 10 (Port 80) "Proxy server connection failed" in google chrome Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user 'root'@'localhost'(using password:YES) "The underlying connection was closed: An unexpected error occurred on a send." With SSL Certificate Login to Microsoft SQL Server Error: 18456 How do I start Mongo DB from Windows? java.rmi.ConnectException: Connection refused to host: 127.0.1.1; mySQL Error 1040: Too Many Connection org.apache.http.conn.HttpHostConnectException: Connection to http://localhost refused in android What is the functionality of setSoTimeout and how it works?

Examples related to error-code

REST API error code 500 handling Login to Microsoft SQL Server Error: 18456 Error Code: 1062. Duplicate entry '1' for key 'PRIMARY'