SQL Server Error 18456

SQL Server Error 18456

You can watch the video on SQL Server Error 18456, or read the article below.

The ?Login Failed? error 18456 can occur due to a variety of reasons. The 18456 error is displayed when the server name you entered was correct, but the connection cannot be granted due to one of a number of reasons.? This error is common for all SQL versions regardless of the version or type; i.e. SQL Server Enterprise or SQL Server Express.

The following demonstration shows you how to pinpoint the causes and resolve them.? In our examples we?re using Microsoft SQL Server 2008 R2 on Microsoft Windows Server 2008 R2.

The 18456 error message does not provide much information on the cause login failure.? This is because it is intended to hide details from unauthorized users, in case someone tries to enter the SQL server by guessing information.? If you are the administrator of the SQL hosting server ( Windows Server 2008 R2 in our example), you can check the Event Viewer or more details regarding Error 18456.

What are the Main Causes of SQL Server Error 18456?

(1)??? Error 18456 commonly occurs when mixed mode authentication is enabled. You may be trying to log in using Windows authentication with a user that the SQL server does not recognize as a SQL user login.? This can be a result of the first-time SQL server configuration.? This error can occur while you are using Windows 7 or Windows Vista with User Account Control. If you are using one of these operating systems, you should try using the ?Run as Admin? option.

(2)??? Error 18456 will occur If you are using SQL server authentication and the login or password is incorrect.

(3)??? This error can also occur in the event of disabling or locking the username in the server, and also in the case of password expiration.

How can a ?SQL Server Error 18456 be Resolved?

Before proceeding with troubleshooting, take the time to verify that your credentials are correct.

If you are using Windows 7 or Windows Vista, you should try opening the SQL Server using the ?Run as Admin? option and see whether that resolves the problem.

If you are trying to log in using Windows Authentication and you receive Error 18456, you should login using SQL SA credentials and go to Security and verify that your Active Directory Windows account is associated with your SQL login.? If not, you can add your Active Directory Windows account. However, to add a user to the SQL database you must at least have Server Administrator access and a SA password.

If you try to log in using SQL server authentication, you should log in using SQL SA credentials, go to Security and check to ensure that the user name passwords are not expired or locked.

Go to Logins, select the user you want to check, and go to properties.

You can check whether password expiration is enabled by navigating to the ?General? tab.

Next, go to the Status tab and check whether your account is locked or not.? Here in this illustration, the ?Login is locked out? option is not activated and it is grayed out which means the account is not locked.

The other possible reason for Error 18456 is that Windows Authentication or Mix Mode Authentication is not enabled on the database itself.? You can check this by navigating to the properties of the database.? Select the database instance and go to Properties.

Click on the Security tab and go to the server authentication area to check this.

If you change Server authentication to ?SQL Server and Windows Authentication? mode or vice versa, you must restart the SQL Server service.? You can use the Microsoft Service console to restart the SQL Server if needed – just right-click and choose the Restart option.

Usually, if Error 18456 occurs, it will have an error state number that can help determine the cause. Obtaining information on the error state can make troubleshooting significantly easier.? Windows Event Viewer holds details of the error.? In the Event Viewer, this error is associated with the event ID 18456.

The Error state descriptions are as follows:

2 and 5 indicate that an Invalid USERID was used to log in.
6 indicates that user has attempted to use a Windows login name with SQL Authentication.
7 indicates that login is disabled and password could be mismatched.
8 indicates that password mismatch has occurred.
9 indicates an invalid password.
11 and 12 indicate that a valid login was used but server access failure has occurred.
13 indicates that the SQL Server service is paused.
18 indicates that a change of password is required.

Watch the Video