By default login failed error message is nothing but a client user connection has been refused by the server due to mismatch of login credentials. First task you might check is to see whether that user has relevant privileges on that SQL Server instance and relevant database too, thats good. Obviously if the necessary prvileges are not been set then you need to fix that issue by granting relevant privileges for that user login.
Althought if that user has relevant grants on database & server if the Server encounters any credential issues for that login then it will prevent in granting the authentication back to SQL Server, the client will get the following error message:
Msg 18456, Level 14, State 1, Server <ServerName>, Line 1
Login failed for user '<Name>'
Ok now what, by looking at the error message you feel like this is non-descriptive to understand the Level & state. By default the Operating System error will show ‘State’ as 1 regardless of nature of the issues in authenticating the login. So to investigate further you need to look at relevant SQL Server instance error log too for more information on Severity & state of this error. You might look into a corresponding entry in log as:
2007-05-17 00:12:00.34 Logon Error: 18456, Severity: 14, State: 8.
or
2007-05-17 00:12:00.34 Logon Login failed for user '<user name>'.
As defined above the Severity & State columns on the error are key to find the accurate reflection for the source of the problem. On the above error number 8 for state indicates authentication failure due to password mismatch. Books online refers: By default, user-defined messages of severity lower than 19 are not sent to the Microsoft Windows application log when they occur. User-defined messages of severity lower than 19 therefore do not trigger SQL Server Agent alerts.
Sung Lee, Program Manager in SQL Server Protocols (Dev.team) has outlined further information on Error state description:The common error states and their descriptions are provided in the following table:
ERROR STATE ERROR DESCRIPTION
------------------------------------------------------------------------------
2 and 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 and 12 Valid login but server access failure
13 SQL Server service paused
18 Change password required
Well I'm not finished yet, what would you do in case of error:
2007-05-17 00:12:00.34 Logon Login failed for user '<user name>'.
You can see there is no severity or state level defined from that SQL Server instance’s error log. So the next troubleshooting option is to look at the Event Viewer’s security log [edit because screen shot is missing but you get the
idea, look in the event log for interesting events].
Getting “Login failed for user (Microsoft SQL Server, error 18456)” error is a nightmare for the DBA’s and developers while working on the SQL Server Database. The error code indicates a failed login attempt which may be occurred due to several reasons. Though, the foremost reason for getting such Microsoft SQL Server error code 18456 could be a wrong password entry.
However, in many cases, an error code comes up with a description that helps users to get a clear picture of the error. But as you can see in the screenshot above, it doesn’t define the exact reason for getting the SQL Server error 18456. Though, in order to get the detailed information about the Microsoft SQL Server error code 18456, a Database administrator can check the event log.
Here in this article, I am trying to explain to you all the possible solutions to fix “Login Failed for user (Microsoft SQL Server, error 18456)” error.
Why SQL Server login failed for user error 18456 occurs?
SQL Server login failed for user error can occur due to any of the following reason:
- Invalid credentials when logging into SQL Server
- Enabling wrong authentication mode
- Password expired of the existing user account
- Disable username on the Server
States of SQL Server Error 18456
|
State |
Description |
|
1 |
Authentication failure |
|
2 and 5 |
Incorrect or invalid userid. (Error: 18456, Severity: 14, State: 5.) |
|
6 |
Try to login with Windows but specifying as a SQL Server account. (Error: 18456, Severity: 14, State: 6.) |
|
7 |
Login disabled. (Error: 18456, Severity: 14, State: 7.) |
|
8 |
Password incorrect. (Error: 18456, Severity: 14, State: 8.) |
|
9 |
Invalid password |
|
11 and 12 |
Valid login credentials but server access failed. (Error: 18456, Severity: 14, State: 11.) |
Workaround to resolve Microsoft SQL Server error code 18456
Before going through the procedure, make sure that the credentials used for login the SQL Server should be valid. Follow the below steps to enable “SQL Server Authentication” to log in through it.
- Launch Microsoft SQL Server Management Studio.
- Do right-click on SQL Server under Object Explorer, and go to Properties.
- In the Server Properties dialogue box, click on Security tab.
- Under Server authentication, opt for the SQL Server and Windows Authentication mode.
- Here you need to expand Security -> Logins and right-click on the server name and select Properties.
- Now in the Login properties dialogue box, pass the password details (using SQL Authentication).
- Finally, move to the Status tab, and select Enabled under login and click OK and restart the SQL Server.
Note: If you want Windows Authentication instead of SQL Server Authentication option, it is required to have admin rights to the user to attempt successful login. To do so, follow the below-mentioned steps:
- Launch SQL Server, and log in as an SQL administrator.
- Move to the Server where the user account exists and click to Plus (+) sign to expand the Logins.
- Right-click on the account that you want to log in as Windows Authentication, and click New Login.
- Now in the Login – New dialogue box window, click Search and type username which you have selected in the Logins folder, and click on Check Names button.
- Here in the Login-New window, move to Server Roles and check sysadmin.
- Finally, go to the Securables tab to verify the role addition.
You can perform any of the above-mentioned methods to fix the “Login Failed for user (Microsoft SQL Server, error 18456)”. However, if you still unable to fix it, you can opt for a professional SQL Database Recovery tool.
You can also use any of the below search term to find the solution to resolve Microsoft SQL Server error code 18456:
Microsoft SQL server error 18456 windows authentication
Microsoft SQL server error 18456 SQL server authentication
login failed for user ‘<user_name>’. (Microsoft SQL Server, error: 18456)
SQL server login failed for user 18456
Login failed for user ‘sa’ in SQL server 2008 r2
Related Post
I can’t seem to connect to our local instance of Microsoft SQL Server. I obtained the followinf infrotmation from the error log and I can’t find anything in regards to Severity 14 and state 1. If anyone has any information in regards to this it would be much appreciated. Thanks in advance!
===================================
Cannot connect to 10.1.0.191.
===================================
Login failed for user ‘kbober’. (.Net SqlClient Data Provider)
——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
——————————
Server Name: 10.1.0.191
Error Number: 18456
Severity: 14
State: 1
——————————
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
Ошибка 18456 связана с отключенной SQL аутентификацией. Ее можно решить 2 путями. Либо включить SQL аутентификацию либо создать логин на основе пользователя Windows.
Для первого варианта сделаем следующее:
1) Зайдем под логином. Обратите внимание что бы у нас в поле 1 стоял Windows
2) Зайдем в свойства конфигурации сервера
3) Зайдем на закладку «Безопасность» и включим гибридную ацетификацию Windows и SQL:
После этого мы сможем зайти под SQL логином
Второй вариант — нам нужно создать логин на основе пользователя Windows
Для этого сделаем:
1) Зайдем в папку «Безопасность» — «Логины»:
2) После этого добавляем существующего пользователя Windows для работы в SQL:
Все должно заработать.
…
Теги:
#ms-sql
Приветствую.
Имеется MS Windows 2008r2 + SQL Server 2008 Standart. Буквально каждую минуту выскакивает в логах:
Ошибка: 18456, серьезность: 14, состояние: 38.
Login failed for user ‘NT AUTHORITY\система’. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: <local machine>]
Посоветуйте что можно предпринять.
26.02.2013 22:53 Server Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 26.02.2013 22:53 Server (c) 2005 Microsoft Corporation. 26.02.2013 22:53 Server All rights reserved. 26.02.2013 22:53 Server Server process ID is 3036. 26.02.2013 22:53 Server System Manufacturer: 'Supermicro', System Model: 'X8DTL'. 26.02.2013 22:53 Server Authentication mode is MIXED. 26.02.2013 22:53 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'. 26.02.2013 22:53 Server This instance of SQL Server last reported using a process ID of 2084 at 26.02.2013 22:48:57 (local) 26.02.2013 18:48:57 (UTC). This is an informational message only; no user action is required. 26.02.2013 22:53 Server Registry startup parameters: -d C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf -e C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG -l C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf 26.02.2013 22:53 Сервер SQL Server is starting at high priority base (=13). This is an informational message only. No user action is required. 2013-02-26 22:53:50.030 Сервер Detected 8 CPUs. This is an informational message; no user action is required. 26.02.2013 22:53 Сервер Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required. 26.02.2013 22:53 Сервер Node configuration: node 0: CPU mask: 0x00000000000000f0 Active CPU mask: 0x00000000000000f0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required. 26.02.2013 22:53 Сервер Node configuration: node 1: CPU mask: 0x000000000000000f Active CPU mask: 0x000000000000000f. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required. 26.02.2013 22:53 spid9s Starting up database 'master'. 2013-02-26 22:54:00.060 spid9s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'. 26.02.2013 22:54 spid9s SQL Trace ID 1 was started by login "sa". 26.02.2013 22:54 spid9s Starting up database 'mssqlsystemresource'. 26.02.2013 22:54 spid9s The resource database build version is 10.00.1600. This is an informational message only. No user action is required. 26.02.2013 22:54 spid9s Server name is 'DB-SERVER'. This is an informational message only. No user action is required. 26.02.2013 22:54 spid13s Starting up database 'model'. 26.02.2013 22:54 spid13s Clearing tempdb database. 26.02.2013 22:54 Сервер The certificate [Cert Hash(sha1) "CF7471551E1B99869882FFB6E7278B98631B5F6D"] was successfully loaded for encryption. 2013-02-26 22:54:01.030 Сервер Server is listening on [ 'any' <ipv6> 1433]. 2013-02-26 22:54:01.030 Сервер Server is listening on [ 'any' <ipv4> 1433]. 2013-02-26 22:54:01.040 Сервер Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ]. 2013-02-26 22:54:01.040 Сервер Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ]. 2013-02-26 22:54:01.040 Сервер Server is listening on [ ::1 <ipv6> 1434]. 2013-02-26 22:54:01.040 Сервер Server is listening on [ 127.0.0.1 <ipv4> 1434]. 2013-02-26 22:54:01.040 Сервер Dedicated admin connection support was established for listening locally on port 1434. 26.02.2013 22:54 Сервер The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/db-server.krasnobor-tula.local ] for the SQL Server service. 26.02.2013 22:54 Сервер The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/db-server.krasnobor-tula.local:1433 ] for the SQL Server service. 26.02.2013 22:54 Сервер SQL Server is now ready for client connections. This is an informational message; no user action is required. 26.02.2013 22:54 spid13s Starting up database 'tempdb'. 26.02.2013 22:54 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:54 Вход Login failed for user 'sqladmin'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: fe80::2d9d:3a04:8101:bb5a%13] 26.02.2013 22:54 spid16s The Service Broker protocol transport is disabled or not configured. 26.02.2013 22:54 spid16s The Database Mirroring protocol transport is disabled or not configured. 26.02.2013 22:54 spid16s Service Broker manager has started. 26.02.2013 22:54 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:54 Вход Login failed for user 'NT AUTHORITY\система'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: <local machine>] 26.02.2013 22:54 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:54 Вход Login failed for user 'NT AUTHORITY\система'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: <local machine>] 26.02.2013 22:54 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:54 Вход Login failed for user 'sqladmin'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: 192.168.15.5] 26.02.2013 22:54 spid12s A new instance of the full-text filter daemon host process has been successfully started. 26.02.2013 22:54 spid20s Starting up database 'utka'. 26.02.2013 22:54 spid22s Starting up database 'uat'. 26.02.2013 22:54 spid24s Starting up database 'do'. 26.02.2013 22:54 spid23s Starting up database 'zup'. 26.02.2013 22:54 spid12s Starting up database 'msdb'. 26.02.2013 22:54 spid25s Starting up database 'ptic1'. 26.02.2013 22:54 spid19s Starting up database 'zakazy'. 26.02.2013 22:54 spid27s Starting up database 'do_copy'. 26.02.2013 22:54 spid21s Starting up database 'ptichka'. 26.02.2013 22:54 spid26s Starting up database 'krn'. 26.02.2013 22:54 spid28s Starting up database 'zik'. 26.02.2013 22:54 spid29s Starting up database 'zikn'. 26.02.2013 22:54 spid19s Recovery is writing a checkpoint in database 'zakazy' (6). This is an informational message only. No user action is required. 26.02.2013 22:54 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:54 Вход Login failed for user 'NT AUTHORITY\система'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: <local machine>] 26.02.2013 22:54 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:54 Вход Login failed for user 'sqladmin'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: 192.168.15.5] 26.02.2013 22:54 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:54 Вход Login failed for user 'NT AUTHORITY\система'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: <local machine>] 2013-02-26 22:54:38.090 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 2013-02-26 22:54:38.090 Вход Login failed for user 'sqladmin'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: 192.168.15.5] 26.02.2013 22:54 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:54 Вход Login failed for user 'sqladmin'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: 192.168.15.5] 26.02.2013 22:54 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:54 Вход Login failed for user 'sqladmin'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: 192.168.15.5] 26.02.2013 22:54 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:54 Вход Login failed for user 'sqladmin'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: 192.168.15.5] 26.02.2013 22:54 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:54 Вход Login failed for user 'NT AUTHORITY\система'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: <local machine>] 26.02.2013 22:55 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:55 Вход Login failed for user 'NT AUTHORITY\система'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: <local machine>] 26.02.2013 22:55 spid9s Recovery is complete. This is an informational message only. No user action is required. 26.02.2013 22:55 spid57 Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required. 26.02.2013 22:55 spid57 Using 'xpsqlbot.dll' version '2007.100.1600' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required. 2013-02-26 22:55:59.020 spid57 Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required. 2013-02-26 22:55:59.060 spid57 Using 'xpstar.dll' version '2007.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required. 26.02.2013 22:55 spid57 Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required. 26.02.2013 22:55 spid57 Using 'xplog70.dll' version '2007.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required. 26.02.2013 22:56 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:56 Вход Login failed for user 'NT AUTHORITY\система'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: <local machine>] 26.02.2013 22:57 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:57 Вход Login failed for user 'NT AUTHORITY\система'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: <local machine>] 26.02.2013 22:58 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:58 Вход Login failed for user 'NT AUTHORITY\система'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: <local machine>] 26.02.2013 22:59 Вход Ошибка: 18456, серьезность: 14, состояние: 38. 26.02.2013 22:59 Вход Login failed for user 'NT AUTHORITY\система'. Причина: не удалось открыть явно указанную базу данных. [КЛИЕНТ: <local machine>]