积极答复者
为什么在命令行下使用单用户模式启动sql,但是连接不上

问题
-
我的步骤如下
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' 登录失败。原因: 服务器处于单用户模式。目前只有一位管理员能够连接。
答案
全部回复
-
我看到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>] -
我已经按照一下方法删除镜像了
-------------------------------删除镜像----------------------------------------------------------- --查看终端点 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 --放弃事务,立刻还原
- 已编辑 Steven.桦仔 2012年10月25日 3:16
-
但是还是登录失败
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.