Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 200, state 7, severity 25.

We face this error during below scenario’s:

           1 .    When you have done a Service Pack upgrade.

           2 .    When you have changed collation of the instance.

Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 200, state 7, severity 25. 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.

As with many errors even this error comes up due to many reasons. So there are few things you need to verify.

As a first step, I recommend to verify below paths in the REGISTRY of the server where SQL Server installed and make sure those paths exists.

Type regedit in Run.

Once registry opens, go to below path: (Below underlined MSSQL10.MSSQLSERVER, here MSSQLSERVER is your instance name. So keep in mind this will change as per your instance name.)

After clicking on MSSQLServer, you will see ‘BackupDirectory’, ‘DefaultData’ and ‘DefaultLog’ fields along with many other fields which will be on right side.  For all these 3  fields verify the paths mentioned in ‘Data’ column exists in the server.

Next go to below location in registry and click on Setup

Then on right side you will notice ‘SQLDataRoot’. Verify here as well the path given is correct.

For some reasons during few Service Pack installations or instance upgrades the locations of above fields becomes invalid in registry and we start receiving above error.

So if you correct the paths in above fields then it will fix the issue in most of the cases.

As a second step, try to start the SQL services in single user mode by using the switch ‘-m’ and REBUILD the master database.

As a last step,  I would verify if there is any conflict of collation between system databases.

Recently I had same issue and after spending several hours on this, it turned out to be a collation difference between master and msdb databases.

How this happened? We had to upgrade an instance and change collation as well after upgrade. So after performing the upgrade and changing collation, DBA have performed master database restore(in order to get back logins) and unfortunately the backup used was of old collation. So this has messed up the instance.

If you are very lucky instance will start and will be stable until you perform the collation change of the database, you can do it either performing a fresh restore or master database REBUILD(I HAVE NOT FOUND A DBA WITH LUCK TILL NOW J). So what’s happening in my case was, as there was collation difference in system databases, instance was starting but it was going down immediately. During the time it was in started state when we tried to connect to instance, it was throwing above script upgrade error.

So the only option we had was to re-install the instance and this time, no, we didn't changed the collation of master database.

Please feel free to share any other easy way to fix this issue.

Thanks!!

How to backup Encryption Key in SQL Server Reporting Services

Connect to SQL Server “Reporting Services Configuration Manager” from “All Programs”, provide the instance name and click on “Connect”.

Now click on “Encryption Keys” tab, which is second tab from bottom on left side of the window.

Here click on “Backup”

In the pop up window, we need to choose the file location and provide password for the file. Backup file will be of “.snk” extenstion.

Note: We need to know the password of the encryption key backup file to restore it. Without password encryption Key restore is not possible.

After clicking on “OK” and once backup completes you will see below message in the window:

Thanks!!

How to restore ReportServer database in SQL Server

We started receiving below errors after performing a report server database restore.

An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError) Get Online Help Invalid object name 'ReportServerTempDB.dbo.TempCatalog'. Could not use view or function 'ExtendedCatalog' because of binding errors.

Or

The report server installation is not initialized.(rsReportServerNotActivated)

In short, above errors raise when there is mismatch or different names in report server function between the source and destination servers.

The easiest fix or the best way I prefer doing a report server DB’s restore is:

·         Take backup of ReportServer database of source server.

·         Take backup of Encryption Key of source server. (We need to take backup from Report Server Configuration Manager)

·         Copy both the backups files (ReportServer and Encryption Key) to destination server.

·         Restore the ReportServer DB in destination server.

·         Restore the Encryption Key in destination server.

Note: Remember we don’t need to restore ReportServertempdb in destination server in most of the cases.

Another thing to remember is to change the configuration of data sources and existing reports to point to destination server instead of source server. We have to make these changes manually in report URL after restoring report database.

Thanks!!

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (SQL Server 18452)

Another day, another weird error made my day. The day went like this

I had my breakfast and immediately got call from office stating something went down and the world is ending and need someone to save it. You know as always I took the responsibility of saving the world again and ended up spending 8hrs continuously on conference call and figuring out, it got nothing to do with SQL database.

Issue: Application went down and when the application team verifying logs they keep receiving below error:

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (SQL Server 18452)

There are many reasons why we face this error, it’s not a single bolt that needs to be tightened to fix it. In most of cases when you are facing this issue the basic stuff to verify from SQL side:

Login has appropriate permissions to the DB.

Verify if any other user from same domain can access the server.

Try verifying connection with SQL login.

In my case when we changed the application service to run with SQL user it worked fine but with domain account the application service not starting up. The domain account has all required SQL permissions but when verified found that not able to connect to the machine with the domain account.

Tried with other user of same domain and same issue for other user as well, so confirmed domain issue. Domain team verified and found that one of Active Directory domain controller got rebooted during the weekend after a patch installation. Later on it’s found that the patch MS15-027 has been installed during weekend and that has led to the issue. 

After un-installing the patch, the application came up fine with domain account as well.

As mentioned previously, this is a very generic error and reason could be anything. I don’t advise to perform this un-installation unless you are sure it will fix it and after effects of it.

Thanks