[fix]-Error Server is in script upgrade mode. Only administrator can connect at this time
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:
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.
