First published on MSDN on Mar 23, 2012
Integration Services catalog (SSISDB) uses the encryption mechanism available in SQL Server to protect its sensitive data. So backup...
Updated Mar 25, 2019
Version 2.0
Blog Post
I'd like to point out that the steps do work but will break any patching that is done to the instance afterwards because the upgrade scripts try to drop the assymetric key but fail because it is linked to login MS_SQLEnableSystemAssemblyLoadingUser
This corrupts the master database and the service won't start. Then the master database has to be recreated from scratch. Using the SSIS export method seems to be the alternative.
USE MASTER
CREATE ASYMMETRIC KEY MS_SQLEnableSystemAssemblyLoadingKey FROM EXECUTABLE FILE = 'C:\Program Files\Microsoft SQL Server\110\DTS\Binn\ISServerExec.exe'
CREATE LOGIN MS_SQLEnableSystemAssemblyLoadingUser FROM ASYMMETRIC KEY MS_SQLEnableSystemAssemblyLoadingKey
GRANT UNSAFE ASSEMBLY TO MS_SQLEnableSystemAssemblyLoadingUser
Below is the sequence of events in the log:
Starting assembly upgrade
Cannot drop asymmetric key 'MS_SQLEnableSystemAssemblyLoadingKey' because there is a login mapped to it.
Script level upgrade for database 'master' failed because upgrade step 'ISServer_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.
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.