In this article I will share the multiple issues faced during the migration of SSISDB from the on-prem SQL Server 2016 to SQL Server 2017 version on an Azure VM. The moving of SSISDB is not a straight forward way as the regular databases migration to Azure VM. So, if it is not planned and executed properly there will be multiple issues we need to deal with. So, I am trying to collate all the issues I have faced during SSISDB migration during different scenarios into this single article.
After taking backup of the on-prem SSISDB database and
restored it in the Azure VM having SQL Server 2017 we need to upgrade the
SSISDB catalog. To upgrade we need to right click on the SSISDB catalog and
click on the upgrade option. While doing this upgrade we started receiving
below error.
“The system cannot
find the file specified (System)”
As the upgrade through GUI is failed another way of
upgrading is by directly executing the "ISDBUpgradeWizard.exe". This exe will be in the location : “C:\Program Files\Microsoft SQL
Server\150\DTS\Binn\ISDBUpgradeWizard.exe”. Once you double click on the exe it
will start upgrading the SSISDB.
In my scenario this also failed with below error:
“The SELECT
permission was denied on the object ‘object_permissions’, database ‘SSISDB’,
schema ‘internal’. Drop the certificate the user the user module signing (Microsoft.SqlServer.IntegrationServices.ISServerDBUpgrade)”
There could be many reasons why this fails
Issue : 1 SSISDB was not restored correctly.
Resolution : Please follow the steps mentioned in this
article
which clearly mentioned how to restore SSISDB.
Issue : 2 The user having any DENY privileges assigned.
Resolution : Verify the account and make sure there is
no DENY READER, DENY WRITER permissions by mistake assigned to the user. Grant
all the required permissions.
Issue : 3 Registry not having the correct path of the DTSPath
Resolution: Many online articles suggest a missing ‘\’
in the registry path also leads to this issue. So make sure you are
not having the same issue.
In the registry : “Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft
SQL Server\140\SSIS\Setup\DTSPath”
Correct path should be “C:\Program Files\Microsoft SQL
Server\140\DTS\” and not “C:\Program Files\Microsoft SQL Server\140\DTS”
Issue : 4 After correcting the registry path and above-mentioned other changes some
SQL jobs started executing fine but while deploying new SSIS packages received
below error:
“The required
components for the 64 bit edition of Integration Services cannot be found. Run
SQL Server setup to install the required components.”
To double check if the issue is recurring, we can run
below command:
EXECUTE [catalog].[check_schema_version]
@uesr32bitruntime=1
The above command also will throw the same error.
Resolution:
Make sure the account ‘##MS_SQLEnableSystemAssemblyLoadingUser##’
is created and has Unsafe Assembly permissions granted.
Create Login ##MS_SQLEnableSystemAssemblyLoadingUser##
FROM Asymmetric Key MS_SQLEnableSystemAssemblyLoadingKey
Grant Unsafe Assembly to
##MS_SQLEnableSystemAssemblyLoadingUser##
After granting permission to above login the 64 bit
*** error was not appearing but the SSISDB upgrade was still not happening.
Issue : 5
To fix the SSISDB upgrade issue go to the stored
procedure in SSISDB and script the stored procedure [catalog].[create_environment]
here if you notice the script of the stored procedure it is created with ‘WITH
EXECUTE AS “accountname”.
Make sure the account mentioned in place of “accountname”
has full permission on the SSISDB. In my case the account was “ALLSchemaOwner”.
Once I granted the permissions to the account “ALLSchemaOwner” and tried upgrading
the SSISDB, the upgrade happened successfully this time.
Now the SSISDB got upgraded successfully, all the SSIS
packages are running successfully and we are able to deploy new packages
without any errors.
Please let me know in the comments have you faced any issues while migrating or upgrading the SSISDB and how you fixed those issues.
Note : Please keep in mind you don’t have to apply all the fixes mentioned in the article. As per your issue you can apply the fix.
Thanks VV!!
PowerShell script that displays all SQL Server folder locations from the registry.
