Unable to deploy SSIS to a DB on Cloud

Question

Unable to deploy SSIS to a DB on Cloud

PSB

SSChampion

Points: 13195
More actions
June 8, 2018 at 7:48 am

#406605

Hello,
I am trying to deploy SSIS packages to a server and I receive the following error message
TITLE: SQL Server Integration Services
------------------------------
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'microsoft.sqlserver.integrationservices.server, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException:
   at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
   at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
   at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)
   at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.Load(String assemblyString)
(Microsoft SQL Server, Error: 10314)
Once I change the authorization on SSISDB using commands below , I get a different error while deploying packages
---database owner should have the corresponding permissions
ALTER
AUTHORIZATION ON DATABASE::[SSISDB] TO sa;
---- Enabled TRUSTWORTHY property
ALTER

DATABASE [SSISDB] SET TRUSTWORTHY ON;
USE [SSISDB]
GO
--- Set the ownership for the user
EXEC
sp_changedbowner 'sa'
ERROR : Please create a master key in the database or open the master key in the session before performing this operation. (Microsoft SQL Server, Error: 15581)
Please help on what I need to do . While moving DBS to a different server and deploying SSIS packages before, I never faced this problem.
Thanks,
PSB

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

How was SSISDB created on this server? Did you use the CREATE CATALOG function, or have you transferred the database from another instance?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

PSB SSChampion Points: 13195 More actions · Answer 2

Created back up file from another instance and restored on this server ..

USE [master]

RESTORE DATABASE [SSISDB] FROM DISK = N'C:\Temp\Bkp\SSISDB.bak' WITH FILE = 1,

MOVE N'data' TO N'G:\SQL2017\DATA\SSISDB.mdf', MOVE N'log' TO N'D:\Log\SSISDB.ldf', NOUNLOAD, STATS = 5

GO

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

PSB - Friday, June 8, 2018 8:32 AM
Created back up file from another instance and restored on this server ..
USE [master]
RESTORE DATABASE [SSISDB] FROM DISK = N'C:\Temp\Bkp\SSISDB.bak' WITH FILE = 1,
MOVE N'data' TO N'G:\SQL2017\DATA\SSISDB.mdf', MOVE N'log' TO N'D:\Log\SSISDB.ldf', NOUNLOAD, STATS = 5
GO

SSISDB is encrypted, so you cannot do this.
To be honest, it's far easier to go through the CREATE CATALOG process and then redeploy your packages.
If you really want to go down the backup/restore path, here are some outline steps that I wrote after migrating an SSISDB from a 2016 instance to a 2017 instance:

Take a full backup of SSISDB on the source server.
On the target server, create a new blank SSISDB using the standard method.
Restore over the top of the new SSISDB from the backup file created in (1)
1. You may need to use FORCE and deselect the 'NoRecovery' options
Backup the master key on the source server (in the context of SSISDB)
Restore the master key on the new server (in the context of SSISDB)
Change the compatibility mode of the restored SSISDB to 140 (if you are upgrading to SQL Server 2017)
SSISDB Upgrade
1. Install SSMS on the target server
2. Open SSMS on the target server (this must be done on the server, a remote connection does not work), right click on the SSISDB catalog and select 'Upgrade Database' (to upgrade the schema from 2016 to 2017).
3. Deinstall SSMS on the target server
Run a script to update the values of your environment variables to include the new instance name
Ensure that the ##MS_SSISServerCleanupJobUser## user in SSISDB is associated with the ##MS_SSISServerCleanupJobUser## login.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

PSB SSChampion Points: 13195 More actions · Answer 4

How do I go through the create catalog path ? Thanks for the update on the backup from source server ?

PSB SSChampion Points: 13195 More actions · Answer 5

I dropped the SSISDB database ran the scrip below in master db

sp_configure 'show advancedoptions', 1;

GO

RECONFIGURE;

GO

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

AND created the Catalog . Everything worked fine after that ..

Thank you All,
PSB

Phil Parkin SSC Guru Points: 246962 More actions · Answer 6

PSB - Friday, June 8, 2018 9:58 AM
I dropped the SSISDB database ran the scrip below in master db
sp_configure 'show advancedoptions', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

AND created the Catalog . Everything worked fine after that ..
Thank you All,
PSB

Bravo!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin