无法登录到SQL Server + SQL Server身份验证+错误:18456

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

我已经在本地主机\sql2008服务器(例如)上创建了登录帐户。User123)

Mapped to Database (default)

映射到数据库(默认)

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

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

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

但是，登录到SQL Server失败，并出现以下消息(对于User123)

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

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

Error Details:

错误的细节:

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

用户“User123”(Net)登录失败。SqlClient数据提供商)

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

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

any help on this please.

请帮帮忙。

5 个解决方案

#1

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.

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

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:

虽然如果该用户在数据库和服务器上有相关的授权，如果服务器遇到登录的凭据问题，那么它将阻止将认证授予SQL server，客户端将得到以下错误消息:

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:

现在，通过查看错误消息，您会觉得这不是理解级别和状态的描述性信息。默认情况下，操作系统错误将显示“State”为1，而不考虑验证登录的问题的性质。因此，为了进一步研究，您需要查看相关的SQL Server实例错误日志，以获得关于此错误的严重性和状态的更多信息。您可以在日志中查看相应的条目:

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.

正如上面定义的，严重程度和状态列上的错误是找到问题根源的准确反映的关键。在上面的错误8中，状态表示由于密码不匹配而导致的身份验证失败。Books online引用:默认情况下，级别低于19的用户定义消息在出现时不会发送到Microsoft Windows应用程序日志。因此，级别低于19的用户定义消息不会触发SQL Server代理警报。

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:

SQL Server protocol (Dev.team)的程序经理Sung Lee概述了关于错误状态描述的进一步信息:常见的错误状态及其描述如下表所示:

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

您可以看到，从SQL Server实例的错误日志中没有定义严重性或状态级别。因此，下一个故障排除选项是查看事件查看器的安全日志[编辑，因为屏幕快照不见了，但您得到了。

idea, look in the event log for interesting events].

想法，在事件日志中寻找有趣的事件]。

#2

You need to enable SQL Server Authentication:

您需要启用SQL Server身份验证:

1. In the Object Explorer, right click on the server and click on "Properties"
2. 在对象资源管理器中，右键单击服务器并单击“属性”

2. In the "Server Properties" window click on "Security" in the list of pages on the left. Under "Server Authentication" choose the "SQL Server and Windows Authentication mode" radio option.
3. 在“服务器属性”窗口中，单击左侧页面列表中的“Security”。在“服务器身份验证”下，选择“SQL Server和Windows身份验证模式”单选选项。

3. Restart the SQLEXPRESS service.
4. 重新启动SQLEXPRESS服务。

#3

I had this same problem, however mine was because I hadn't set the Server authentication to "SQL Server and Windows Authentication mode" (which you had) I just wanted to mention it here in case someone missed it in your question.

我遇到了同样的问题，但是我的问题是，我没有将服务器身份验证设置为“SQL Server和Windows身份验证模式”(您有)，我只是想在这里提一下，以防有人在您的问题中遗漏了它。

You can access this by

你可以通过

• Right click on instance (IE SQLServer2008)
• 右键单击实例(IE SQLServer2008)
• Select "Properties"
• 选择“属性”
• Select "Security" option
• 选择“安全”选项
• Change "Server authentication" to "SQL Server and Windows Authentication mode"
• 将“服务器认证”改为“SQL Server and Windows authentication mode”
• Restart the SQLServer service
  • Right click on instance
  • 右键单击实例
  • Click "Restart"
  • 点击“重新启动”
• 重新启动SQLServer服务右键单击实例单击“重新启动”

#4

You can access this by

你可以通过

Right click on instance (IE SQLServer2008)
Select "Properties"
Select "Security" option
Change "Server authentication" to "SQL Server and Windows Authentication mode"
Restart the SQLServer service
    Right click on instance
    Click "Restart"

Just for anyone else reading this: This worked for me on 2012 SQL Server too. Thanks

对于阅读本文的任何人来说:这在2012年的SQL Server上也适用。谢谢

#5

The correct solution to the problem is to make sure SQL server authentication is turned on for your SQL Server.

正确的解决方案是确保SQL服务器的SQL server身份验证被打开。