none
SQL Server 2008R2 群集切换后用户不能登录 RRS feed

 > 

  • 问题

  • Question
    登录进行投票
    0
    登录进行投票

    SQL Server R2 群集从node1切换到node2后，一个用户不能登录，再次切换到node1后，可以登录
    errorlog日志显示如下，请忽略ip地址，[客户端: 10.1.x.x] 为同一ip：

    node1

    ........
    2020-05-15 16:12:05.62 备份          Database backed up. Database: hswinrun2, creation date(time): 2015/09/12(21:58:27), pages dumped: 171222, first LSN: 11949292:1573:277, last LSN: 11949292:1967:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'Z:\backup\hswinrun2_qsq_20200515161200.bak'}). This is an informational message only. No user action is required.
    2020-05-15 16:37:02.61 spid16s     Service Broker manager has shut down.
    2020-05-15 16:37:03.12 登录          错误: 18456，严重性: 14，状态: 23。
    2020-05-15 16:37:03.12 登录          Login failed for user 'htzqtzb'. 原因: 在重新验证连接所使用的登录名时，服务器访问验证失败。 [客户端: 10.1.x.x]
    2020-05-15 16:37:03.12 spid336     错误: 18056，严重性: 20，状态: 23。
    2020-05-15 16:37:03.12 spid336     The client was unable to reuse a session with SPID 336, which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
    2020-05-15 16:37:03.15 登录          错误: 18456，严重性: 14，状态: 23。
    2020-05-15 16:37:03.15 登录          Login failed for user 'htzqtzb'. 原因: 在重新验证连接所使用的登录名时，服务器访问验证失败。 [客户端: 10.1.x.x]
    2020-05-15 16:37:03.15 spid330     错误: 18056，严重性: 20，状态: 23。
    2020-05-15 16:37:03.15 spid330     The client was unable to reuse a session with SPID 330, which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
    2020-05-15 16:37:03.80 登录          错误: 18451，严重性: 14，状态: 1。
    2020-05-15 16:37:03.80 登录          Login failed for user 'htzqtzb'. Only administrators may connect at this time. [客户端: 10.1.x.x]
    2020-05-15 16:37:04.09 spid9s      SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
    2020-05-15 16:37:04.11 spid9s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
    2020-05-15 16:37:04.19 登录          错误: 17188，严重性: 16，状态: 1。
    2020-05-15 16:37:04.19 登录          SQL Server cannot accept new connections, because it is shutting down. The connection has been closed. [客户端: 10.1.35.122]
    2020-05-15 16:37:04.20 登录          错误: 17188，严重性: 16，状态: 1。
    2020-05-15 16:37:04.20 登录          SQL Server cannot accept new connections, because it is shutting down. The connection has been closed. [客户端: 187.187.x.x]
    2020-05-15 16:37:04.35 服务器         The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/QYTZBSQL.qytzb.com ] for the SQL Server service. 
    2020-05-15 16:37:04.35 服务器         The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/QYTZBSQL.qytzb.com:1433 ] for the SQL Server service. 
    2020-05-15 16:37:04.38 登录          错误: 17188，严重性: 16，状态: 1。
    2020-05-15 16:37:04.38 登录          SQL Server cannot accept new connections, because it is shutting down. The connection has been closed. [客户端: 187.187.x.x]
    2020-05-15 16:37:04.38 登录          错误: 17188，严重性: 16，状态: 1。
    2020-05-15 16:37:04.38 登录          SQL Server cannot accept new connections, because it is shutting down. The connection has been closed. [客户端: 187.187.x.x]
                                              
                                              -end-


    node2

    .......
    2020-05-15 16:37:21.61 spid33s     Starting up database 'winsql'.
    2020-05-15 16:37:21.75 spid33s     Recovery is writing a checkpoint in database 'winsql' (9). This is an informational message only. No user action is required.
    2020-05-15 16:37:23.58 登录          错误: 18456，严重性: 14，状态: 8。
    2020-05-15 16:37:23.58 登录          Login failed for user 'htzqtzb'. 原因: 密码与所提供的登录名不匹配。 [客户端: 10.1.x.x]
    2020-05-15 16:37:41.04 登录          错误: 18456，严重性: 14，状态: 8。
    2020-05-15 16:37:41.04 登录          Login failed for user 'htzqtzb'. 原因: 密码与所提供的登录名不匹配。 [客户端: 10.1.x.x]
    2020-05-15 16:39:14.71 spid9s      Recovery is complete. This is an informational message only. No user action is required.
    2020-05-15 16:39:14.99 登录          错误: 18456，严重性: 14，状态: 8。
    2020-05-15 16:39:14.99 登录          Login failed for user 'htzqtzb'. 原因: 密码与所提供的登录名不匹配。 [客户端: 10.1.x.x]
    2020-05-15 16:39:16.01 登录          错误: 18456，严重性: 14，状态: 8。
    2020-05-15 16:39:16.01 登录          Login failed for user 'htzqtzb'. 原因: 密码与所提供的登录名不匹配。 [客户端: 10.1.x.x]
    2020-05-15 17:00:34.44 spid12s     Service Broker manager has shut down.
    2020-05-15 17:00:34.47 登录          错误: 18456，严重性: 14，状态: 8。
    2020-05-15 17:00:34.47 登录          Login failed for user 'htzqtzb'. 原因: 密码与所提供的登录名不匹配。 [客户端: 10.1.x.x]
    2020-05-15 17:00:34.65 spid9s      SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
    2020-05-15 17:00:34.65 spid9s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
    2020-05-15 17:00:34.67 服务器         The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/QYTZBSQL.qytzb.com ] for the SQL Server service. 
    2020-05-15 17:00:34.67 服务器         The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/QYTZBSQL.qytzb.com:1433 ] for the SQL Server service. 

                                                      -end-


    node1

    ......
    2020-05-15 17:00:51.60 spid28s     Starting up database 'winhis'.
    2020-05-15 17:01:12.54 spid9s      Recovery is complete. This is an informational message only. No user action is required.
    2020-05-15 17:02:16.60 spid79      Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
    2020-05-15 17:02:16.66 spid79      Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
    ........




    可以看出是什么原因导致node2上用户无法登录吗？






    2020年5月15日 14:35
    |

答案

全部回复

  • Question
    登录进行投票
    1
    登录进行投票
    Did you copy sql logins to node2? Do they have same sid?
    2020年5月15日 16:04
    |
  • Question
    登录进行投票
    0
    登录进行投票
    检查sid，不是同名login就是一样的用户哦

    想不想时已是想，不如不想都不想。

    2020年5月17日 13:22
    |
    版主
  • Question
    登录进行投票
    0
    登录进行投票

    你好，

    根据node2的报错信息，登录用户'htzqtzb'的密码不正确。

    你可以用数据库管理员账号登录数据库，然后点击安全性，再点击登录用户，找到用户'htzqtzb'：

    然后右击该用户，打开属性，更改密码

    或者将Node2的登录用户'htzqtzb'删了，将Node1的登录信息复制过来

    希望对你有用，如果还有什么问题，请随时反馈，谢谢

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    2020年5月18日 1:34
    |