无法登录 SQL Server + SQL Server 身份验证 + 错误:18456 [英] Unable to login to SQL Server + SQL Server Authentication + Error: 18456

问题描述

我已经在我的 localhost\sql2008 服务器(例如 User123)上创建了登录帐户

映射到数据库(默认)

SQL Server 上的身份验证模式设置为(Windows 和 SQL)

但是登录到 SQL Server 失败并显示以下消息(对于 User123)

注意:已多次检查用户名/密码是否正确输入

错误详情:

<块引用>

用户User123"登录失败(Net.SqlClient 数据提供程序)

服务器名称:localhost\sql2008 错误编号:18456 严重性:14 状态:1行号:65536

请对此提供任何帮助.

解决方案

默认情况下，登录失败的错误消息只是客户端用户连接由于登录凭据不匹配而被服务器拒绝.您可能要检查的第一项任务是查看该用户是否对该 SQL Server 实例和相关数据库也具有相关权限，这很好.显然，如果没有设置必要的权限，那么您需要通过授予该用户登录的相关权限来解决该问题.

考虑该用户是否在数据库上拥有相关授权 &如果服务器遇到该登录的任何凭据问题，那么它将阻止将身份验证授予 SQL Server，客户端将收到以下错误消息:

Msg 18456, Level 14, State 1, Server , Line 1用户<姓名>"登录失败

好吧，通过查看错误消息，您觉得这对理解 Level & 是非描述性的.状态.默认情况下，无论登录身份验证问题的性质如何，操作系统错误都会将状态"显示为 1.因此，为了进一步调查，您还需要查看相关的 SQL Server 实例错误日志，以获取有关 Severity & 的更多信息.此错误的状态.您可能会查看日志中的相应条目:

2007-05-17 00:12:00.34 登录错误:18456，严重性:14，状态:8.或2007-05-17 00:12:00.34 用户<用户名>"登录失败.

如上定义的严重性和错误上的状态列是找到问题根源的准确反映的关键.上述错误编号 8 表示由于密码不匹配导致身份验证失败.联机丛书指: 默认情况下，严重性低于 19 的用户定义消息在发生时不会发送到 Microsoft Windows 应用程序日志.因此，严重性低于 19 的用户定义消息不会触发 SQL Server 代理警报.

SQL Server 协议 (Dev.team) 的程序经理 Sung Lee 概述了有关错误状态描述的更多信息:下表提供了常见错误状态及其描述:

错误状态错误描述-------------------------------------------------------------------------------2 和 5 无效的用户 ID6 尝试使用 Windows 登录名进行 SQL 身份验证7 登录被禁用和密码不匹配8 密码不匹配9 密码无效11 和 12 登录有效但服务器访问失败13 SQL Server 服务暂停18 需要修改密码好吧，我还没有完成，如果出现错误你会怎么做:2007-05-17 00:12:00.34 用户<用户名>"登录失败.

您可以看到该 SQL Server 实例的错误日志中没有定义严重性或状态级别.因此，下一个故障排除选项是查看事件查看器的安全日志 .

I have created login account on my localhost\sql2008 Server (Eg. User123)

Mapped to Database (default)

Authentication Mode on SQL Server is set to both (Windows and SQL)

But login to SQL Server fails with following message (for User123)

Note: Have checked multiple time that UserName / Password are typed in correctly

Error Details:

Login failed for user 'User123' (Net.SqlClient Data Provider)

Server Name : localhost\sql2008 Error Number: 18456 Severity : 14 State : 1 Line Number : 65536

any help on this please.

解决方案

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

这篇关于无法登录 SQL Server + SQL Server 身份验证 + 错误:18456的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！