none
为什么在命令行下使用单用户模式启动sql,但是连接不上 RRS feed

  • 问题

  • 我的步骤如下

    1、在cmd下输入net stop mssqlserver

    2、在cmd下运行下面语句 :net start mssqlserver /m

    3、然后再输入sqlcmd /E /S joe

    消息 18461,级别 14,状态 1,服务器 JOE,第 1 行
    用户 'JOE\Administrator' 登录失败。原因: 服务器处于单用户模式。目前只有一位管理员能够连接。

    刚刚启动sql还没有连接进来,为什麽sql会说“目前只有一位管理员能够连接”

    在SSMS下也是一样报同样的错误

    消息 18461,级别 14,状态 1,服务器 JOE,第 1 行
    用户 'JOE\Administrator' 登录失败。原因: 服务器处于单用户模式。目前只有一位管理员能够连接。


    给我写信: QQ我:点击这里给我发消息

    2012年10月25日 1:54

答案

  • Did you look at the log? Local system tried to connect, what's that?
    2012年10月25日 3:21
  • 使用 cmd 方式之前,有重启以确保之前的连接都断开么?

    如果还是不行的话,先以正式模式启动,启动之后,进去观察一下 master..sysprocess 中 spid > 50 的记录看有那些,以此来确定是否有程序在自动连接

    2012年10月25日 4:13

全部回复

  • 补充一下:

    在第三个步骤里输入:3、然后再输入sqlcmd /E /S joe 或者sqlcmd /E /S joe /A

    都不行

    也是报错

    消息 18461,级别 14,状态 1,服务器 JOE,第 1 行
    用户 'JOE\Administrator' 登录失败。原因: 服务器处于单用户模式。目前只有一位管理员能够连接。


    给我写信: QQ我:点击这里给我发消息

    2012年10月25日 1:56
  • Is sql agent running? You can look at sql log to find out if any process is in it.
    2012年10月25日 1:59
  • sql agent已经停了

    输入net stop mssqlserver 的时候已经把sql agent也停止了

    DAC已经开启


    给我写信: QQ我:点击这里给我发消息

    2012年10月25日 2:20
  • Does DAC work? Can only open query window with DAC by the way.
    2012年10月25日 2:29
  • 看提示还有其它连接已在你之前进行,是否可以尝试修改port做测试呢?
    2012年10月25日 2:45
  • 我看到errorlog里没有连接连进来SQLSERVER

    sql2005  win7   计算机名:joe

    ip地址:192.168.1.166

    这是errorlog日志:

    2012-10-25 10:38:32.11 Server      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
     Nov 24 2008 13:01:59
     Copyright (c) 1988-2005 Microsoft Corporation
     Developer Edition on Windows NT 6.1 (Build 7601: Service Pack 1)

    2012-10-25 10:38:32.11 Server      (c) 2005 Microsoft Corporation.
    2012-10-25 10:38:32.11 Server      All rights reserved.
    2012-10-25 10:38:32.11 Server      Server process ID is 6420.
    2012-10-25 10:38:32.11 Server      Authentication mode is MIXED.
    2012-10-25 10:38:32.11 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
    2012-10-25 10:38:32.11 Server      This instance of SQL Server last reported using a process ID of 9836 at 2012/10/25 10:37:43 (local) 2012/10/25 2:37:43 (UTC). This is an informational message only; no user action is required.
    2012-10-25 10:38:32.11 Server      Registry startup parameters:
    2012-10-25 10:38:32.11 Server        -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
    2012-10-25 10:38:32.11 Server        -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
    2012-10-25 10:38:32.11 Server        -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
    2012-10-25 10:38:32.11 Server      Command Line Startup Parameters:
    2012-10-25 10:38:32.11 Server        /m
    2012-10-25 10:38:32.13 服务器         SQL Server is starting at high priority base (=13). This is an informational message only. No user action is required.
    2012-10-25 10:38:32.13 服务器         Detected 4 CPUs. This is an informational message; no user action is required.
    2012-10-25 10:38:32.23 服务器         Address Windowing Extensions is enabled. This is an informational message only; no user action is required.
    2012-10-25 10:38:32.28 服务器         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.
    2012-10-25 10:38:32.29 服务器         Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
    2012-10-25 10:38:33.35 服务器         Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
    2012-10-25 10:38:33.36 服务器         Database Mirroring Transport is disabled in the endpoint configuration.
    2012-10-25 10:38:33.36 spid5s      Warning ******************
    2012-10-25 10:38:33.36 spid5s      SQL Server started in single-user mode. This an informational message only. No user action is required.
    2012-10-25 10:38:33.36 spid5s      Starting up database 'master'.
    2012-10-25 10:38:33.48 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    2012-10-25 10:38:33.58 spid5s      Starting up database 'mssqlsystemresource'.
    2012-10-25 10:38:33.60 spid5s      The resource database build version is 9.00.4035. This is an informational message only. No user action is required.
    2012-10-25 10:38:33.74 spid5s      Server name is 'JOE'. This is an informational message only. No user action is required.
    2012-10-25 10:38:33.74 spid6s      Starting up database 'model'.
    2012-10-25 10:38:33.92 spid6s      Clearing tempdb database.
    2012-10-25 10:38:34.04 服务器         A self-generated certificate was successfully loaded for encryption.
    2012-10-25 10:38:34.04 服务器         Server is listening on [ 'any' <ipv6> 1433].
    2012-10-25 10:38:34.04 服务器         Server is listening on [ 'any' <ipv4> 1433].
    2012-10-25 10:38:34.05 服务器         Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    2012-10-25 10:38:34.05 服务器         Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2012-10-25 10:38:34.05 服务器         Server is listening on [ 'any' <ipv6> 1434].
    2012-10-25 10:38:34.05 服务器         Server is listening on [ 'any' <ipv4> 1434].
    2012-10-25 10:38:34.05 服务器         Dedicated admin connection support was established for listening remotely on port 1434.
    2012-10-25 10:38:34.19 登录          错误: 17187,严重性: 16,状态: 1。
    2012-10-25 10:38:34.19 登录          SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again.  [客户端: 192.168.1.166]
    2012-10-25 10:38:34.25 spid6s      Starting up database 'tempdb'.
    2012-10-25 10:38:34.30 登录          错误: 17187,严重性: 16,状态: 1。
    2012-10-25 10:38:34.30 登录          SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again.  [客户端: <local machine>]
    2012-10-25 10:38:34.35 服务器         The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
    2012-10-25 10:38:34.35 服务器         SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2012-10-25 10:38:34.37 spid9s      Starting up database 'msdb'.
    2012-10-25 10:38:34.37 spid11s     Starting up database 'ReportServerTempDB'.
    2012-10-25 10:38:34.37 spid12s     Starting up database 'AdventureWorks'.
    2012-10-25 10:38:34.37 spid13s     Starting up database 'dbdreamlink'.
    2012-10-25 10:38:34.37 spid10s     Starting up database 'ReportServer'.
    2012-10-25 10:38:34.43 spid15s     Starting up database 'hengshan'.
    2012-10-25 10:38:34.43 spid19s     Starting up database 'pubs'.
    2012-10-25 10:38:34.43 spid16s     Starting up database 'Northwind'.
    2012-10-25 10:38:34.43 spid18s     Starting up database 'pratice'.
    2012-10-25 10:38:34.43 spid17s     Starting up database 'partionTest'.
    2012-10-25 10:38:34.43 spid14s     Starting up database 'GPOSDB'.
    2012-10-25 10:38:34.44 spid20s     Starting up database 'S_C_SC'.
    2012-10-25 10:38:34.46 spid21s     Starting up database 'TransactionTestDb'.
    2012-10-25 10:38:34.47 spid22s     Starting up database 'YQBlog'.
    2012-10-25 10:38:34.49 spid23s     Starting up database 'test_shrink'.
    2012-10-25 10:38:34.51 spid24s     Starting up database 'PerfAnalysis'.
    2012-10-25 10:38:35.36 spid20s     Bypassing recovery for database 'S_C_SC' because it is marked as an inaccessible database mirroring database. A problem exists with the mirroring session. The session either lacks a quorum or the communications links are broken because of problems with links, endpoint configuration, or permissions (for the server account or security certificate). To gain access to the database, figure out what has changed in the session configuration and undo the change.
    2012-10-25 10:38:35.45 spid21s     Analysis of database 'TransactionTestDb' (16) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
    2012-10-25 10:38:37.31 spid14s     CHECKDB for database 'GPOSDB' finished without errors on 2012-10-20 22:24:47.777 (local time). This is an informational message only; no user action is required.
    2012-10-25 10:38:42.08 spid18s     CHECKDB for database 'pratice' finished without errors on 2012-08-15 22:57:04.880 (local time). This is an informational message only; no user action is required.
    2012-10-25 10:38:42.14 spid5s      Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
    2012-10-25 10:38:42.14 spid5s      Recovery is complete. This is an informational message only. No user action is required.
    2012-10-25 10:44:02.42 登录          错误: 18461,严重性: 14,状态: 1。
    2012-10-25 10:44:02.42 登录          Login failed for user 'JOE\Administrator'. Reason: Server is in single user mode. Only one administrator can connect at this time. [客户端: <local machine>]
    2012-10-25 10:44:09.85 登录          错误: 18461,严重性: 14,状态: 1。
    2012-10-25 10:44:09.85 登录          Login failed for user 'JOE\Administrator'. Reason: Server is in single user mode. Only one administrator can connect at this time. [客户端: <local machine>]
    2012-10-25 10:44:11.24 登录          错误: 18461,严重性: 14,状态: 1。
    2012-10-25 10:44:11.24 登录          Login failed for user 'JOE\Administrator'. Reason: Server is in single user mode. Only one administrator can connect at this time. [客户端: <local machine>]
    2012-10-25 10:44:12.33 登录          错误: 18461,严重性: 14,状态: 1。
    2012-10-25 10:44:12.33 登录          Login failed for user 'JOE\Administrator'. Reason: Server is in single user mode. Only one administrator can connect at this time. [客户端: <local machine>]


    给我写信: QQ我:点击这里给我发消息

    2012年10月25日 2:46
  • Should stop db mirroring first.
    2012年10月25日 2:53
  • 不能使用DAC进行连接,刚才我已经说了

    给我写信: QQ我:点击这里给我发消息

    2012年10月25日 2:55
  • Stop mirror before setting to single user mode.
    2012年10月25日 3:04
  • 我已经按照一下方法删除镜像了

    -------------------------------删除镜像-----------------------------------------------------------
    --查看终端点 
    select * from sys.endpoints
     
    --删除某终端点(终端点不带引号)   
    drop ENDPOINT endpoint_Mirroring
     
    --删除证书  在master | Security | Certificates
    --删除用户  在master | User
    
    --然后可以删除登录名  drop login <login_name>
    
    --修改master key  :  
     alter master key drop encryption by service master key
    -- 删除master key  : 
    drop master key
    
    --删除镜像的命令: 
    ALTER DATABASE S_C_SC set partner OFF
    
    RESTORE DATABASE S_C_SC WITH recovery  --放弃事务,立刻还原
    
    


    给我写信: QQ我:点击这里给我发消息


    2012年10月25日 3:05
  • 但是还是登录失败

    C:\Users\Administrator>sqlcmd /E /S joe
    消息 18461,级别 14,状态 1,服务器 JOE,第 1 行
    用户 'JOE\Administrator' 登录失败。原因: 服务器处于单用户模式。目前只有一位管理员能够连接。

    C:\Users\Administrator>sqlcmd /E /S joe /A
    消息 18461,级别 14,状态 1,服务器 JOE,第 1 行
    用户 'JOE\Administrator' 登录失败。原因: 服务器处于单用户模式。目前只有一位管理员能够连接。

    这是errorlog

    2012-10-25 11:02:57.42 Server      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
    	Nov 24 2008 13:01:59 
    	Copyright (c) 1988-2005 Microsoft Corporation
    	Developer Edition on Windows NT 6.1 (Build 7601: Service Pack 1)
    
    2012-10-25 11:02:57.42 Server      (c) 2005 Microsoft Corporation.
    2012-10-25 11:02:57.42 Server      All rights reserved.
    2012-10-25 11:02:57.42 Server      Server process ID is 11564.
    2012-10-25 11:02:57.42 Server      Authentication mode is MIXED.
    2012-10-25 11:02:57.42 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
    2012-10-25 11:02:57.42 Server      This instance of SQL Server last reported using a process ID of 10524 at 2012/10/25 11:02:50 (local) 2012/10/25 3:02:50 (UTC). This is an informational message only; no user action is required.
    2012-10-25 11:02:57.42 Server      Registry startup parameters:
    2012-10-25 11:02:57.42 Server      	 -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
    2012-10-25 11:02:57.42 Server      	 -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
    2012-10-25 11:02:57.42 Server      	 -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
    2012-10-25 11:02:57.42 服务器         SQL Server is starting at high priority base (=13). This is an informational message only. No user action is required.
    2012-10-25 11:02:57.42 服务器         Detected 4 CPUs. This is an informational message; no user action is required.
    2012-10-25 11:02:57.64 服务器         Address Windowing Extensions is enabled. This is an informational message only; no user action is required.
    2012-10-25 11:02:57.70 服务器         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.
    2012-10-25 11:02:57.71 服务器         Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
    2012-10-25 11:02:58.77 服务器         Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
    2012-10-25 11:02:58.78 服务器         Database mirroring has been enabled on this instance of SQL Server.
    2012-10-25 11:02:58.78 spid5s      Starting up database 'master'.
    2012-10-25 11:02:59.07 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    2012-10-25 11:02:59.18 spid5s      Starting up database 'mssqlsystemresource'.
    2012-10-25 11:02:59.19 spid5s      The resource database build version is 9.00.4035. This is an informational message only. No user action is required.
    2012-10-25 11:02:59.31 spid5s      Server name is 'JOE'. This is an informational message only. No user action is required.
    2012-10-25 11:02:59.31 spid8s      Starting up database 'model'.
    2012-10-25 11:02:59.48 spid8s      Clearing tempdb database.
    2012-10-25 11:02:59.62 服务器         A self-generated certificate was successfully loaded for encryption.
    2012-10-25 11:02:59.62 服务器         Server is listening on [ 'any' <ipv6> 1433].
    2012-10-25 11:02:59.63 服务器         Server is listening on [ 'any' <ipv4> 1433].
    2012-10-25 11:02:59.63 服务器         Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    2012-10-25 11:02:59.63 服务器         Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2012-10-25 11:02:59.63 服务器         Server is listening on [ 'any' <ipv6> 1434].
    2012-10-25 11:02:59.63 服务器         Server is listening on [ 'any' <ipv4> 1434].
    2012-10-25 11:02:59.63 服务器         Dedicated admin connection support was established for listening remotely on port 1434.
    2012-10-25 11:02:59.64 服务器         The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
    2012-10-25 11:02:59.64 服务器         SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2012-10-25 11:02:59.97 spid8s      Starting up database 'tempdb'.
    2012-10-25 11:03:00.00 登录          错误: 18456,严重性: 14,状态: 16。
    2012-10-25 11:03:00.00 登录          Login failed for user 'NT AUTHORITY\SYSTEM'. [客户端: fe80::74c1:db4:1764:6f8c%11]
    2012-10-25 11:03:00.11 spid12s     Starting up database 'msdb'.
    2012-10-25 11:03:00.11 spid13s     Starting up database 'ReportServer'.
    2012-10-25 11:03:00.11 spid14s     Starting up database 'ReportServerTempDB'.
    2012-10-25 11:03:00.11 spid15s     Starting up database 'AdventureWorks'.
    2012-10-25 11:03:00.11 spid16s     Starting up database 'dbdreamlink'.
    2012-10-25 11:03:00.19 spid19s     Starting up database 'Northwind'.
    2012-10-25 11:03:00.19 spid20s     Starting up database 'partionTest'.
    2012-10-25 11:03:00.19 spid21s     Starting up database 'pratice'.
    2012-10-25 11:03:00.19 spid18s     Starting up database 'hengshan'.
    2012-10-25 11:03:00.19 spid17s     Starting up database 'GPOSDB'.
    2012-10-25 11:03:00.20 spid22s     Starting up database 'pubs'.
    2012-10-25 11:03:00.21 spid23s     Starting up database 'S_C_SC'.
    2012-10-25 11:03:00.23 spid24s     Starting up database 'TransactionTestDb'.
    2012-10-25 11:03:00.25 spid25s     Starting up database 'YQBlog'.
    2012-10-25 11:03:00.26 spid26s     Starting up database 'test_shrink'.
    2012-10-25 11:03:00.28 spid27s     Starting up database 'PerfAnalysis'.
    2012-10-25 11:03:01.15 spid28s     The Service Broker protocol transport is disabled or not configured.
    2012-10-25 11:03:01.15 spid28s     Server is listening on [ 'any' <ipv6> 5022].
    2012-10-25 11:03:01.15 spid28s     Server is listening on [ 'any' <ipv4> 5022].
    2012-10-25 11:03:01.15 spid28s     The Database Mirroring protocol transport is now listening for connections.
    2012-10-25 11:03:01.32 spid24s     Analysis of database 'TransactionTestDb' (16) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
    2012-10-25 11:03:02.34 spid17s     CHECKDB for database 'GPOSDB' finished without errors on 2012-10-20 22:24:47.777 (local time). This is an informational message only; no user action is required.
    2012-10-25 11:03:03.12 spid28s     Service Broker manager has started.
    2012-10-25 11:03:07.14 spid21s     CHECKDB for database 'pratice' finished without errors on 2012-08-15 22:57:04.880 (local time). This is an informational message only; no user action is required.
    2012-10-25 11:03:07.26 spid5s      Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
    2012-10-25 11:03:07.26 spid5s      错误: 8355,严重性: 16,状态: 1。
    2012-10-25 11:03:07.26 spid5s      Server-level event notifications can not be delivered. Either Service Broker is disabled in msdb, or msdsb failed to start. Event notifications in other databases could be affected as well. Bring msdb online, or enable Service Broker. 
    2012-10-25 11:03:07.26 spid5s      Recovery is complete. This is an informational message only. No user action is required.
    2012-10-25 11:03:55.62 spid52      The 数据库镜像 protocol transport has stopped listening for connections.
    2012-10-25 11:03:57.62 spid52      The 数据库镜像 protocol transport is disabled or not configured.
    2012-10-25 11:04:16.72 spid54      Using 'xpstar90.dll' version '2005.90.4035' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
    2012-10-25 11:06:26.57 spid28s     Service Broker manager has shut down.
    2012-10-25 11:06:26.72 spid5s      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.
    


    给我写信: QQ我:点击这里给我发消息

    2012年10月25日 3:07
  • 已经删除镜像了


    给我写信: QQ我:点击这里给我发消息

    2012年10月25日 3:11
  • 从提示看,你的启动是成功的

    问题应该在于你是用界面去登录,你直接用 sqlcmd 去登录试试

    2012年10月25日 3:18
  • 用 managent studio 去登录,一般会产生两个连接,一个是对象资源管理器,另一个是新建一个查询窗口

    而单用户模式只允许一个连接,所以通过使用界面去登录会导致失败

    用界面登录,你可以考虑这样的方式,在出现登录窗口的时候,选择取消,等界面完全出现之后,按 Ctrl+N 去新建一个查询窗口,这时候再登录,这个时候因为只产生一个连接,所以如果没有意外的话,是可以成功的

    2012年10月25日 3:21
  • Did you look at the log? Local system tried to connect, what's that?
    2012年10月25日 3:21
  • cmd下跟界面都试过了,都不行

    给我写信: QQ我:点击这里给我发消息

    2012年10月25日 3:53
  • 这个我知道,DAC的连接方式,但是不成功

    给我写信: QQ我:点击这里给我发消息

    2012年10月25日 3:54
  • sql服务的启动帐户是 Local system

    给我写信: QQ我:点击这里给我发消息

    2012年10月25日 3:55
  • 想问一下SQL启动的时候是不是就有进程连接进来,是什么进程


    给我写信: QQ我:点击这里给我发消息

    2012年10月25日 4:01
  • 使用 cmd 方式之前,有重启以确保之前的连接都断开么?

    如果还是不行的话,先以正式模式启动,启动之后,进去观察一下 master..sysprocess 中 spid > 50 的记录看有那些,以此来确定是否有程序在自动连接

    2012年10月25日 4:13
  • 我说了我的步骤了

    我的步骤如下

    1、在cmd下输入net stop mssqlserver

    2、在cmd下运行下面语句 :net start mssqlserver /m

    3、然后再输入sqlcmd /E /S joe


    给我写信: QQ我:点击这里给我发消息

    2012年10月25日 4:54
  • 成功了,原来是SSRS连接进来的,关掉SSRS服务就可以了


    给我写信: QQ我:点击这里给我发消息


    2012年10月25日 5:03