July 31, 2012 at 11:02 am
Reason: Server is in script upgrade mode. Only administrator can connect at this time
I tried connecting as the domain admin and it still won't connect - please help
Here's the error log:
2012-07-31 11:40:22.76 Server Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)
Sep 21 2011 22:45:45
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)
2012-07-31 11:40:22.76 Server (c) 2005 Microsoft Corporation.
2012-07-31 11:40:22.76 Server All rights reserved.
2012-07-31 11:40:22.76 Server Server process ID is 5108.
2012-07-31 11:40:22.76 Server Authentication mode is MIXED.
2012-07-31 11:40:22.76 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL\MSSQL\Log\ERRORLOG'.
2012-07-31 11:40:22.76 Server This instance of SQL Server last reported using a process ID of 3356 at 7/31/2012 11:40:05 AM (local) 7/31/2012 4:40:05 PM (UTC). This is an informational message only; no user action is required.
2012-07-31 11:40:22.76 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL\MSSQL\DATA\mastlog.ldf
2012-07-31 11:40:22.76 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2012-07-31 11:40:22.76 Server Detected 8 CPUs. This is an informational message; no user action is required.
2012-07-31 11:40:22.76 Server Large Page Extensions enabled.
2012-07-31 11:40:22.76 Server Large Page Granularity: 2097152
2012-07-31 11:40:22.76 Server Large Page Allocated: 32MB
2012-07-31 11:40:22.76 Server Large Page Allocated: 32MB
2012-07-31 11:40:22.78 Server Using locked pages for buffer pool.
2012-07-31 11:40:22.83 Server 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-07-31 11:40:22.86 Server Node configuration: node 0: CPU mask: 0x00000000000000f0 Active CPU mask: 0x00000000000000f0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2012-07-31 11:40:22.86 Server Node configuration: node 1: CPU mask: 0x000000000000000f Active CPU mask: 0x000000000000000f. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2012-07-31 11:40:22.87 spid9s Starting up database 'master'.
2012-07-31 11:40:23.09 spid9s 24 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
2012-07-31 11:40:23.09 spid9s 0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
2012-07-31 11:40:23.09 spid9s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2012-07-31 11:40:23.11 spid9s CHECKDB for database 'master' finished without errors on 2012-07-30 03:19:26.830 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:23.12 spid9s Resource governor reconfiguration succeeded.
2012-07-31 11:40:23.12 spid9s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2012-07-31 11:40:23.12 spid9s SQL Server Audit has started the audits. This is an informational message. No user action is required.
<{39B75A24-0837-4CEC-AFDF-B960027AE07E}>RsFxNso initialized. InstanceId = 00000004
<{50080099-5EC4-4EAF-A2A2-63C3DA97F8EB}>FsAgent is initialized
<{09C4480B-DBA4-49B7-956F-68A8B8B2445D}>FsAgent is up and running
2012-07-31 11:40:23.12 spid9s FILESTREAM: effective level = 2, configured level = 2, file system access share name = 'MSSQLSERVER'.
2012-07-31 11:40:23.13 spid9s SQL Trace ID 1 was started by login "sa".
2012-07-31 11:40:23.13 spid9s Starting up database 'mssqlsystemresource'.
2012-07-31 11:40:23.15 spid9s The resource database build version is 10.00.5500. This is an informational message only. No user action is required.
2012-07-31 11:40:23.24 spid18s Starting up database 'model'.
2012-07-31 11:40:23.24 spid9s Server name is 'NAS2-DBR'. This is an informational message only. No user action is required.
2012-07-31 11:40:23.44 Server A self-generated certificate was successfully loaded for encryption.
2012-07-31 11:40:23.44 Server Server is listening on [ 'any' <ipv4> 1433].
2012-07-31 11:40:23.44 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2012-07-31 11:40:23.44 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
2012-07-31 11:40:23.45 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2012-07-31 11:40:23.45 Server Dedicated admin connection support was established for listening locally on port 1434.
2012-07-31 11:40:23.46 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. 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-07-31 11:40:23.46 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2012-07-31 11:40:23.68 spid18s CHECKDB for database 'model' finished without errors on 2012-07-30 03:19:32.500 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:23.68 spid18s Clearing tempdb database.
2012-07-31 11:40:23.87 Logon Error: 18401, Severity: 14, State: 1.
2012-07-31 11:40:23.87 Logon Login failed for user 'FLEETONE\F1Onsitelog'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 172.20.36.109]
2012-07-31 11:40:23.98 Logon Error: 18401, Severity: 14, State: 1.
2012-07-31 11:40:23.98 Logon Login failed for user 'FLEETONE\UC_SVC'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 172.20.22.13]
2012-07-31 11:40:24.14 spid18s Starting up database 'tempdb'.
2012-07-31 11:40:25.25 Logon Error: 18401, Severity: 14, State: 1.
2012-07-31 11:40:25.25 Logon Login failed for user 'FLEETONE\F1Onsitelog'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 172.20.36.109]
2012-07-31 11:40:25.44 spid21s The Service Broker protocol transport is disabled or not configured.
2012-07-31 11:40:25.44 spid21s The Database Mirroring protocol transport is disabled or not configured.
2012-07-31 11:40:25.44 spid21s Service Broker manager has started.
2012-07-31 11:40:33.83 spid32s Starting up database 'ReportServer'.
2012-07-31 11:40:33.83 spid33s Starting up database 'loadprogress'.
2012-07-31 11:40:33.83 spid36s Starting up database 'F1Reporting'.
2012-07-31 11:40:33.83 spid35s Starting up database 'Cost_plus'.
2012-07-31 11:40:33.83 spid34s Starting up database 'ReportServerTempDB'.
2012-07-31 11:40:33.83 spid37s Starting up database 'F1Util'.
2012-07-31 11:40:33.83 spid17s Starting up database 'msdb'.
2012-07-31 11:40:33.83 spid44s Starting up database 'COREISSUE'.
2012-07-31 11:40:33.83 spid46s Starting up database 'F1Settings'.
2012-07-31 11:40:33.83 spid45s Starting up database 'MAINDB'.
2012-07-31 11:40:33.83 spid47s Starting up database 'F1WebUtil'.
2012-07-31 11:40:33.83 spid51s Starting up database 'Permits'.
2012-07-31 11:40:33.84 spid39s Starting up database 'JAMS_FLEETONE'.
2012-07-31 11:40:33.84 spid50s Starting up database 'SSRS_Run_Reports'.
2012-07-31 11:40:33.84 spid48s Starting up database 'COREACQUIRE'.
2012-07-31 11:40:33.85 spid42s Starting up database 'ANI'.
2012-07-31 11:40:33.85 spid43s Starting up database 'LBMC'.
2012-07-31 11:40:33.85 spid38s Starting up database 'FleetNetwork'.
2012-07-31 11:40:33.86 spid40s Starting up database 'loadNATData'.
2012-07-31 11:40:33.87 spid41s Starting up database 'COREAUTH'.
2012-07-31 11:40:33.87 spid49s Starting up database 'CORELIBRARY'.
2012-07-31 11:40:34.04 spid38s CHECKDB for database 'FleetNetwork' finished without errors on 2012-07-30 03:39:30.153 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:34.05 spid42s CHECKDB for database 'ANI' finished without errors on 2012-07-30 03:58:17.160 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:34.19 spid47s CHECKDB for database 'F1WebUtil' finished without errors on 2012-07-30 05:21:17.557 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:34.48 spid46s CHECKDB for database 'F1Settings' finished without errors on 2012-07-30 05:21:15.753 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:34.59 spid40s CHECKDB for database 'loadNATData' finished without errors on 2012-07-30 03:39:58.567 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:34.68 spid45s CHECKDB for database 'MAINDB' finished without errors on 2012-07-30 05:20:47.640 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:35.31 spid43s CHECKDB for database 'LBMC' finished without errors on 2012-07-30 03:58:17.737 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:35.53 spid17s Recovery of database 'msdb' (4) is 0% complete (approximately 21 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2012-07-31 11:40:35.55 spid35s CHECKDB for database 'Cost_plus' finished without errors on 2012-07-30 03:39:20.110 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:35.55 spid17s Recovery of database 'msdb' (4) is 0% complete (approximately 21 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2012-07-31 11:40:35.55 spid49s CHECKDB for database 'CORELIBRARY' finished without errors on 2012-07-30 08:05:49.873 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:35.58 spid17s Recovery of database 'msdb' (4) is 0% complete (approximately 20 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2012-07-31 11:40:35.58 spid36s CHECKDB for database 'F1Reporting' finished without errors on 2012-07-30 03:39:21.513 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:35.61 spid44s CHECKDB for database 'COREISSUE' finished without errors on 2012-07-30 03:58:37.437 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:35.69 spid17s Recovery of database 'msdb' (4) is 4% complete (approximately 3 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2012-07-31 11:40:36.36 spid51s CHECKDB for database 'Permits' finished without errors on 2012-07-30 08:20:28.160 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:36.43 Logon Error: 18401, Severity: 14, State: 1.
2012-07-31 11:40:36.43 Logon Login failed for user 'FLEETONE\F1Onsitelog'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 172.20.36.109]
2012-07-31 11:40:36.55 spid32s CHECKDB for database 'ReportServer' finished without errors on 2012-07-30 03:23:18.120 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:36.65 spid17s 2048 transactions rolled forward in database 'msdb' (4). This is an informational message only. No user action is required.
2012-07-31 11:40:36.73 spid17s CHECKDB for database 'msdb' finished without errors on 2012-07-30 03:19:33.207 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:36.73 spid17s 0 transactions rolled back in database 'msdb' (4). This is an informational message only. No user action is required.
2012-07-31 11:40:36.73 spid17s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
2012-07-31 11:40:36.78 spid17s Recovery completed for database msdb (database ID 4) in 1 second(s) (analysis 44 ms, redo 1074 ms, undo 50 ms.) This is an informational message only. No user action is required.
2012-07-31 11:40:38.14 spid37s CHECKDB for database 'F1Util' finished without errors on 2012-07-30 03:39:22.990 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:38.94 spid50s CHECKDB for database 'SSRS_Run_Reports' finished without errors on 2012-07-30 08:13:59.230 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:39.27 spid34s CHECKDB for database 'ReportServerTempDB' finished without errors on 2012-07-30 03:23:20.150 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:42.18 Logon Error: 18401, Severity: 14, State: 1.
2012-07-31 11:40:42.18 Logon Login failed for user 'FLEETONE\F1Onsitelog'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 172.20.36.109]
2012-07-31 11:40:42.36 spid33s CHECKDB for database 'loadprogress' finished without errors on 2012-07-30 03:23:21.187 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:45.25 spid41s CHECKDB for database 'COREAUTH' finished without errors on 2012-07-30 03:40:03.500 (local time). This is an informational message only; no user action is required.
2012-07-31 11:40:46.30 spid48s CHECKDB for database 'COREACQUIRE' finished without errors on 2012-07-30 05:21:20.270 (local time). This is an informational message only; no user action is required.
2012-07-31 11:41:02.58 spid39s CHECKDB for database 'JAMS_FLEETONE' finished without errors on 2012-07-30 03:39:30.873 (local time). This is an informational message only; no user action is required.
2012-07-31 11:41:03.16 spid9s Database 'master' is upgrading script 'sqlagent100_msdb_upgrade.sql' from level 0 to level 3.
2012-07-31 11:41:03.16 spid9s ----------------------------------------
2012-07-31 11:41:03.16 spid9s Starting execution of PREINSTMSDB100.SQL
2012-07-31 11:41:03.16 spid9s ----------------------------------------
2012-07-31 11:41:03.29 spid9s Setting database option COMPATIBILITY_LEVEL to 100 for database msdb.
2012-07-31 11:41:03.38 spid9s Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.
2012-07-31 11:41:03.38 spid9s Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.
2012-07-31 11:41:05.71 spid9s Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2012-07-31 11:41:05.72 spid9s Using 'xpstar.dll' version '2007.100.5500' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2012-07-31 11:41:05.73 spid9s DBCC TRACEOFF 1717, server process ID (SPID) 9. This is an informational message only; no user action is required.
2012-07-31 11:41:05.73 spid9s DBCC execution completed. If DBCC printed error messages, contact your system administrator.
2012-07-31 11:41:05.73 spid9s
2012-07-31 11:41:05.73 spid9s Creating table temp_sysjobschedules
2012-07-31 11:41:05.88 spid9s
2012-07-31 11:41:05.88 spid9s Alter table sysdownloadlist...
2012-07-31 11:41:05.90 spid9s
2012-07-31 11:41:05.90 spid9s Alter table sysjobhistory...
2012-07-31 11:41:05.95 Logon Error: 18401, Severity: 14, State: 1.
2012-07-31 11:41:05.95 Logon Login failed for user 'FLEETONE\F1Onsitelog'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 172.20.36.87]
2012-07-31 11:41:06.42 Logon Error: 18401, Severity: 14, State: 1.
2012-07-31 11:41:06.42 Logon Login failed for user 'FLEETONE\F1Onsitelog'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 172.20.36.109]
2012-07-31 11:41:06.78 spid9s
2012-07-31 11:41:06.78 spid9s Alter table systargetservers...
2012-07-31 11:41:06.78 spid9s Check if syssubsystems table exists...
2012-07-31 11:41:06.93 spid9s Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.
2012-07-31 11:41:06.93 spid9s Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.
2012-07-31 11:41:06.93 spid9s FILESTREAM: effective level = 2, configured level = 2, file system access share name = 'MSSQLSERVER'.
2012-07-31 11:41:06.96 spid9s DMF pre-upgrade steps...
2012-07-31 11:41:06.97 spid9s DC pre-upgrade steps...
2012-07-31 11:41:06.97 spid9s Check if Data collector config table exists...
2012-07-31 11:41:06.97 spid9s Data Collector state before upgrade: 0
2012-07-31 11:41:07.02 spid9s Deleting cached auto-generated T-SQL Data Collection packages from msdb...
2012-07-31 11:41:07.02 spid9s End of DC pre-upgrade steps.
2012-07-31 11:41:07.02 spid9s
2012-07-31 11:41:07.02 spid9s ----------------------------------------
2012-07-31 11:41:07.02 spid9s Execution of PREINSTMSDB100.SQL complete
2012-07-31 11:41:07.02 spid9s ----------------------------------------
2012-07-31 11:41:40.58 spid9s Executing: GRANT SELECT ON dbo.[sysutility_mi_session_statistics_internal] TO [UtilityIMRReader]
2012-07-31 11:41:40.59 spid9s Executing: GRANT SELECT ON dbo.[sysutility_mi_dac_execution_statistics_internal] TO [UtilityIMRReader]
2012-07-31 11:41:40.60 spid9s Executing: GRANT SELECT ON dbo.[sysutility_mi_volumes_stage_internal] TO [UtilityIMRReader]
2012-07-31 11:41:40.60 spid9s Executing: GRANT SELECT ON dbo.[sysutility_mi_cpu_stage_internal] TO [UtilityIMRReader]
2012-07-31 11:41:40.61 spid9s Executing: GRANT SELECT ON dbo.[sysutility_mi_smo_stage_internal] TO [UtilityIMRReader]
2012-07-31 11:41:40.61 spid9s Executing: GRANT SELECT ON dbo.[sysutility_mi_smo_properties_to_collect_internal] TO [UtilityIMRReader]
2012-07-31 11:41:40.61 spid9s Executing: GRANT SELECT ON dbo.[sysutility_mi_smo_objects_to_collect_internal] TO [UtilityIMRReader]
2012-07-31 11:41:40.61 spid9s Executing: GRANT EXECUTE ON dbo.[sp_sysutility_mi_collect_dac_execution_statistics_internal] TO [UtilityIMRWriter]
2012-07-31 11:41:40.61 spid9s Executing: GRANT EXECUTE ON dbo.[sp_sysutility_mi_get_dac_execution_statistics_internal] TO [UtilityIMRWriter]
2012-07-31 11:41:40.61 spid9s Executing: GRANT SELECT ON dbo.[fn_sysutility_mi_get_batch_manifest] TO [UtilityIMRWriter]
2012-07-31 11:41:40.61 spid9s Executing: GRANT EXECUTE ON dbo.[fn_sysutility_mi_get_cpu_architecture_name] TO [UtilityIMRReader]
2012-07-31 11:41:40.61 spid9s Executing: GRANT EXECUTE ON dbo.[fn_sysutility_mi_get_cpu_family_name] TO [UtilityIMRReader]
2012-07-31 11:41:40.61 spid9s Executing: GRANT EXECUTE ON dbo.[fn_sysutility_get_culture_invariant_conversion_style_internal] TO [UtilityIMRReader]
2012-07-31 11:41:41.59 spid9s Signing sps ...
2012-07-31 11:41:41.59 spid9s Dropping existing Agent certificate ...
2012-07-31 11:41:41.59 spid9s Error: 15559, Severity: 16, State: 1.
2012-07-31 11:41:41.59 spid9s Cannot drop certificate '##MS_AgentSigningCertificate##' because there is a user mapped to it.
2012-07-31 11:41:41.59 spid9s Error: 912, Severity: 21, State: 2.
2012-07-31 11:41:41.59 spid9s Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 15559, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2012-07-31 11:41:41.59 spid9s Error: 3417, Severity: 21, State: 3.
2012-07-31 11:41:41.59 spid9s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2012-07-31 11:41:42.14 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
July 31, 2012 at 11:15 am
Key error:
Cannot drop certificate '##MS_AgentSigningCertificate##' because there is a user mapped to it.
Start SQL with the /f parameter and traceflag 3608 on.
eg net start mssqlserver /f /T3608
Connect to SQL via the DAC (use SQLCMD) and remove the user mapped to that certificate. Then restart SQL normally.
If that doesn't work, call PSS and open a case with them. If it's production down it's critical and not something to be taken to the forums (slow as they are)
p.s. Could you please edit and remove the code blocks around the error log or change them to code plain? Trying to view that lot froze firefox twice (its the js highlighting script)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2012 at 11:16 am
FYI, this all began after installing Service Pack 3. While at the end of the installation it said everything was successful and to reboot, after rebooting it hasn't worked since
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
July 31, 2012 at 11:41 am
I did try this, and was able to successfully start it
Start SQL with the /f parameter and traceflag 3608 on.
eg net start mssqlserver /f /T3608
however,
Connect to SQL via the DAC (use SQLCMD) and remove the user mapped to that certificate. Then restart SQL normally.
In the same command window i get this error running the second command:
Msg 18461, Lvl 14...Login failed for 'DOMAIN\ADministrator'. ReasonL Server is in single user mode, Only one administrator can connect at this time
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
July 31, 2012 at 11:59 am
MyDoggieJessie (7/31/2012)
I did try this, and was able to successfully start itStart SQL with the /f parameter and traceflag 3608 on.
eg net start mssqlserver /f /T3608
however,
Connect to SQL via the DAC (use SQLCMD) and remove the user mapped to that certificate. Then restart SQL normally.
In the same command window i get this error running the second command:
Msg 18461, Lvl 14...Login failed for 'DOMAIN\ADministrator'. ReasonL Server is in single user mode, Only one administrator can connect at this time
Somebody else attached and took your session. Is somebody else trying to work on the issue as well?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2012 at 12:09 pm
FYI, I edited the error log file data and removed the failed login things and a few other status messages that did not seem overly important - hopefully this helps to not freeze up the browser window
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
July 31, 2012 at 12:20 pm
SQLRNNR (7/31/2012)
MyDoggieJessie (7/31/2012)
I did try this, and was able to successfully start itStart SQL with the /f parameter and traceflag 3608 on.
eg net start mssqlserver /f /T3608
however,
Connect to SQL via the DAC (use SQLCMD) and remove the user mapped to that certificate. Then restart SQL normally.
In the same command window i get this error running the second command:
Msg 18461, Lvl 14...Login failed for 'DOMAIN\ADministrator'. ReasonL Server is in single user mode, Only one administrator can connect at this time
Somebody else attached and took your session. Is somebody else trying to work on the issue as well?
Yup, or SQL agent or an application. Make sure nothing is trying to connect, shut SQL down, try again. /f means single user
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2012 at 12:29 pm
Unfortunately EVERYTHING is trying to connect to this server...
I opened two command windows, started the service in one and immediately alt-tabbed over to connect in the other (both windows have the actual commands up and ready to go...
Not sure how I can get it done any quicker. Options?
By the way, I am on with MS support now...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
July 31, 2012 at 1:08 pm
MyDoggieJessie (7/31/2012)
Unfortunately EVERYTHING is trying to connect to this server...
Then shut EVERYTHING down. You need to have nothing at all trying to connect to the server (and MS support will tell you the same thing). Shut down all apps, stop services, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2012 at 3:07 pm
MyDoggieJessie (7/31/2012)
Unfortunately EVERYTHING is trying to connect to this server...I opened two command windows, started the service in one and immediately alt-tabbed over to connect in the other (both windows have the actual commands up and ready to go...
Not sure how I can get it done any quicker. Options?
By the way, I am on with MS support now...
Disable the NIC card if you're physically by the server.
July 31, 2012 at 7:09 pm
GilaMonster (7/31/2012)
Cannot drop certificate '##MS_AgentSigningCertificate##' because there is a user mapped to it.
This was the culprit.
Started Server in command mode with trace flags -T902 and -T3601 (this avoided the pending scripts from the service pack install) and from that point I was able to login to the server via SSMS (albeit, everything had a red "x" everywhere...but I was in!)
Executed SELECT principal_id FROM sys.server_principals WHERE name = '##MS_AgentSigningCertificate##'.
GO This will return principle id of login for '##MS_AgentSigningCertificate##' and it returned 313
Then executed SELECT * FROM sys.server_permissions WHERE grantor_principal_id = 313.
GO Zero records returned meant there are no permissions for this user - not sure how this happened but oh well...
Dropped the user mentioned above by running
USE msdb
sp_dropuser '##MS_AgentSigningCertificate##'
Restarted the instance normally, and everything came back up. DB was accessible, logshipping resumed, replication started to catch up, and reporting Agent jobs kicked off. According to Microsoft, this AgentSigningCertificate isn't even used by MSSQL (by default) and wasn't needed...didn't even have to recreate the login...go figure!!!
What a day...
Thought I'd share the steps for resolution in case anyone else gets jabbed by this bug when installing SP3 on SQL 2008 EE (64-bit)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
July 31, 2012 at 7:14 pm
In my desperate time of need, thanks to all who jumped in to help - it's always good to know that this forum has members willing to step up and offer suggestions to those of us who're still trying to find our way in what appears to be a never-ending world of learning...
Merci-buckets to all!!! :hehe:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 24, 2013 at 6:46 am
Hi Guys
Is there any direct or indirect correlation of a delayed connection to SQL through SSMS?
I noticed in Errorlog file that, once the below 2 entry came in Errorlog, then only I was able to connect to SQL Server through SSMS screen!
Thanks
2013-10-24 06:34:02.98 spid61 Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2013-10-24 06:34:03.03 spid61 Using 'xpstar.dll' version '2007.100.5500' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
Thanks.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply