Hello everyone,
I'm currently working with Visual Studio 2022 and SQL Server 2019 Enterprise Edition. I am encountering an issue while attempting to deploy SSIS packages to the Integration Catalog, specifically an error related to creating a master key or opening in a new session. I'm seeking advice on how to resolve this problem.
None of my team members seem to have information about the existence of a master key or if it has been created. Ideally, I would like to avoid creating a master key altogether and simply proceed with deploying the SSIS packages to SQL Server and scheduling the job.
I would greatly appreciate any suggestions or insights into resolving this issue.
Thank you!
Thanks,
Charmer
January 31, 2024 at 11:30 am
Can you provide the exact error message?
What is the Protection Level of the SSIS project? (Under Project/Properties)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 31, 2024 at 11:48 am
Protection Level is EncryptSensitiveWithUserKey. The exact error is this "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)".
I heard from my team that this database was restored from another server that could have had master key. But we do not want this key any more as we are setting up a new server. We don't find any one who has an idea about master key from where it was restored.
Thanks,
Charmer
January 31, 2024 at 12:07 pm
If you change the protection level to Don't Save Sensitive and try deploying again, the error should go away.
However, the values of any params marked as sensitive will not be saved or deployed after making this change.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 31, 2024 at 12:07 pm
So you copied SSISDB from one server to another?
Yeah there's a bunch of steps you need to do to make that happen successfully.
When you first create the catalog on the original server it will have created a master key for you, it's just how SSISDB works.
You're not able to get rid of the master key.
https://www.mssqltips.com/sqlservertip/6831/how-to-migrate-ssisdb-to-another-server/ - Need to follow approach 3.
You're going to have to go back and get the key from the old server and follow the steps or destroy everything on the restored side and start again.
January 31, 2024 at 12:53 pm
So you copied SSISDB from one server to another?
Yeah there's a bunch of steps you need to do to make that happen successfully.
When you first create the catalog on the original server it will have created a master key for you, it's just how SSISDB works.
You're not able to get rid of the master key.
https://www.mssqltips.com/sqlservertip/6831/how-to-migrate-ssisdb-to-another-server/ - Need to follow approach 3.
You're going to have to go back and get the key from the old server and follow the steps or destroy everything on the restored side and start again.
Yes, DBA copied from another server due to upgrading old systems to new. But I reconfirmed with entire team we don't want that key or no one knows about the master key. So it is okay to delete it. But I am not sure if I can delete entire SSISDB and recreate it hoping no issues would occur to the integration catalog? Would that be possible?
Thanks,
Charmer
January 31, 2024 at 1:05 pm
You can't delete the key.
Go get the old server back, backup the key, restore the key or start the new server again from scratch and reimport all your packages.
January 31, 2024 at 9:43 pm
If you cannot get the master key from the original source - then *hopefully* you have copies of all projects in VS so you can redeploy to a newly created catalog. You are going to have to recreate the folders, permissions, environments - well pretty much everything if you can't get the master key for the original source.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 1, 2024 at 8:07 am
If you cannot get the master key from the original source - then *hopefully* you have copies of all projects in VS so you can redeploy to a newly created catalog. You are going to have to recreate the folders, permissions, environments - well pretty much everything if you can't get the master key for the original source.
yes, I do have copies of all SSIS packages in VS. I could not create a catalog DB via windows authentication mode. So I am trying to find the other ways to do so.
Thanks,
Charmer
February 1, 2024 at 8:13 am
I could not create a catalog DB via windows authentication mode. So I am trying to find the other ways to do so.
As far as I can remember, Windows auth mode is the only way that you will be able to create a catalog. What was the error you received?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 1, 2024 at 8:28 am
Charmer wrote:I could not create a catalog DB via windows authentication mode. So I am trying to find the other ways to do so.
As far as I can remember, Windows auth mode is the only way that you will be able to create a catalog. What was the error you received?
I already deleted the SSIDB But this Create Catalog option is disabled/Greyed out.
Thanks,
Charmer
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply