April 24, 2024 at 9:51 am
So i have ran though the guide to migrate the SSISDB correctly. and unfortunatly i am still recieving the same error.
To set the scene as i noticed the error occured when i setup SSISDB from scratch as a new install so lets use that as an example as i dont want to muddy the waters.
April 24, 2024 at 10:04 am
Seems to be steps missing there.
have you confirmed you can open the master key on the source and the destination
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 24, 2024 at 10:30 am
As part of the guide it ran me though exactly that checking first the password was all good on the source and destination all good.
it is definatly a head scratcher.
April 24, 2024 at 10:57 am
Which guide you use
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 24, 2024 at 11:42 am
This is the guide I used
https://www.sqlshack.com/moving-the-ssisdb-catalog-on-a-new-sql-server-instance/
If you have a better one I will happily review it.
April 24, 2024 at 11:59 am
Recommend you try again and go through in detail following the relevant sections
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 24, 2024 at 6:54 pm
also run the following and supply the query results
select name, is_master_key_encrypted_by_server
from sys.databases
where name='ssisdb'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 3, 2024 at 11:44 am
Just to add my two cents in, I have seen a similar error many years ago with an older version of SQL and it was a certificate issue. The certificate had to either be reinstalled or a new one installed, I can't remember which. We had a server expert who did all that work for us.
Also, what is the SSIS package protection level?
May 5, 2024 at 8:16 am
This was removed by the editor as SPAM
Following up on this issue, I've been working with Microsoft to find a resolution, and I'm happy to report that it has been resolved.
The problem stemmed from using the latest version of SSMS 20. To work around it, you need to use SSMS 19 or 19.3 to create or edit jobs.
Microsoft informed me that they are working on fixing this in the next release of SSMS. In the meantime, I wanted to share this solution with you. I have also requested an official statement from their support team and will post it once I receive it, which should provide more details about the issue.
I didnt think that SSMS would be the cause however it appears it was on this occasion.
May 26, 2024 at 9:46 am
I was hoping for more details from Microsoft around why the application caused the issue however all I was given was a link to the release notes that I believe thee engineers utilises to try and find a fix to the solution but sure enough under the SSIS section is the error I recieve and the fix.
https://learn.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-ver16#known-issues-200
June 10, 2024 at 6:10 pm
DMK is only needed if you want to maintain sensitive values in your package as you restore the SSISDB database to another server instance. If DMK is not restored, the package will still be intact, just the sensitive values within the package will be missing.
In regard to the error, it appears your new server is forcing all connections to be encrypted. You can turn this off by opening up the SQL Server Configuration manager and right click>properties for SQL Native Client 11.0 Configuration for both 32 and 64 bit.
Set "Force Protocol Encryption" and "Trust Server Certificate" to No or both to Yes depending on how secure you want connections to be.
If you're going to use encryption for connections, then on the new server, right click>properties for Protocols for MSSQLSERVEER under "SQL Server Network Configuration". In the Certificate tab, select the appropriate certificate from the drop down list and make sure it isn't expired.
Tung Dang
Azure and SQL Server DBA Contractor / Consultant
DataZip
June 10, 2024 at 6:15 pm
If you want a more secure response, open up the SQL Configuration Manager on the target SQL Server and right click>properties for Protocols for MSSQLSERVEER under "SQL Server Network Configuration". In the Certificate tab, select the appropriate certificate from the drop down list and make sure it isn't expired.
that’s only if using ssl secured connection to the instance which the op hadn’t specified
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 12, 2024 at 3:12 pm
I dodnot know how did you carried out migration from 2019 to 2022 because i also tired to upgrade from older version to newer 2019 but failed to upgrade .i followed all steps mentioned in website but packages were not upgarded or wont work got error. read some microsoft guide which said ssis can be migrated on same version only .for upgrade either upgrade packages or export/import packages
June 19, 2024 at 9:32 pm
A server upgrade would just be a once in a while event and not recurring with high frequency. I'd recommend using SSDT and using the Business Intelligence > Integration Service > Integration Services Import Project wizard to import your SSIS projects. Ensure all database connections pass connection tests, then deploy your projects to the upgraded server.
Tung Dang
Azure and SQL Server DBA Contractor / Consultant
DataZip
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply