[fix]-Error Server is in script upgrade mode. Only administrator can connect at this time

Wikitechy | 6805 Views | sql server | 08 Jun 2016

 

Scenario:

When connecting to SqlServer instance with Sql Server Management Studio the error occurs as:

Error connecting to 'wikitechy\MSSQLSERVER2008'. Additional information: Login failed for user '...'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. (Microsoft SQL Server, Error: 18401). 

Reason: 

This occurs when the Service Pack installation completes fully but few scripts such as sqlagentxxx_msdb_upgrade.sq is functional only after the SQL Server service starts the next time. This is done via Script Upgrade that happens when SQL is restarted for the first time after applying the patch. Through this process, SQL Server tries to create the “.mdf” file in the default data location, If the default path unavailable, then the error occurs. The default path can be accessed from the below registry key:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer

Error:

Error Server is in script upgrade mode. Only administrator can connect at this time

Fix 1:

Check the event viewer which surely will depict a clear picture of what the issue is all about and act accordingly.

Fix 2:

Wait for some time after restarting SQL Server Services, as the patches may be applied with a delay after start. Check for Windows event log and SQL Server error log and connect when no issues are found.

Otherwise check and update the registry path.

Fix 3:

Check and update the below registry entry:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer

Update the path for “DefaultData” with an existing location and restart the SQL Services along with monitoring Errorlog file.

Fix 4:

Enable trace flag 902 as follows:

1. Open SQL Server Configuration Manager -> SQL Server Services.

2. Double-click the SQL Server Services.

3. Select SQL Server Properties dialog box -> Advanced tab.

4. Locate Startup Parameters item -> Add ;-T902 to the end of the existing string value, and click OK. 

5. Now right-click on the SQL Server Service, and then click Start.

6. When SQL Server Agent service is running, right-click and Stop it.

7. Now the connection to instance of SQL Server 2008 R2 is made possible via SQL Server Management Studio.

Fixes are applicable to the following versions of SQL Server:

  • SQL Server 2008
  • Azure SQL DB
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016

Related Error Tags:

  • Login Failed For User - Reason Server is in Script Upgrade Mode
  • SQL Server Instance in Script Upgrade mode
  • The SQL Server service cannot start after you install cumulative update package 1 for SQL Server 2008 R2 if a UCP exists in the instance of SQL Server
  • Login failed for user ''. Reason: Server is in script upgrade mode
  • FIX: The SQL Server service cannot start after you install
  • Error: SQL Server is in script upgrade mode.
  • Server is in script upgrade mode. Only administrator can connect at this time?
  • Login failed for user. Reason: Server is in script upgrade mode.



Workshop

Bug Bounty
IOT Hackathon
Webinar

Join our Community

Advertise