question

Ankitshah-1904 avatar image
0 Votes"
Ankitshah-1904 asked Ankitshah-1904 commented

SSISDB Error in AG: Error Number: 15581 Please create a master key in the database or open the master key in the session before performing this operation. (.Net SqlClient Data Provider)

Hi Everyone,
I wanted to see if anyone has came across below SSISDB error in AG environment.

I did implement SSIS package on primary SQL server- 2019 using SSIS catalog. Added SSISDB to part of AG and using listener to connect. Every thing went well as expected.

Below error is coming when I did fail over and trying to run SSIS package from the second node. Can anyone let me know what steps needs to be taken on secondary node since this is new for SSIDB?

Error Number: 15581 Please create a master key in the database or open the master key in the session before performing this operation. (.Net SqlClient Data Provider)

Thank you and be safe and healthy.

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered Ankitshah-1904 commented

Hi @Ankitshah-1904 ,

Here is a blog about the same issue in AG environment that need to create a master key.

His resolution is that while adding the SSISDB database to an Always on Availability Group on the “Select Initial Data Synchronization” step, you have to choose “Full database and log backup” as the data synchronization preference instead of the Automatic.

You may take a reference to see if it will help.

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October







· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you very much for the Kind information Zoehui. This solution worked perfectly for my issue.

1 Vote 1 ·
Ankitshah-1904 avatar image
0 Votes"
Ankitshah-1904 answered

So I tried couple of solution but below one seems working and after executing this statements on node-2, i was able to run package.

USE SSISDB;
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'yourpassword';
GO

USE SSISDB;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'yourpassword';
GO

But when I failed back to Node-1 , I had to re run the same statement to execute package.
So does anyone know, whenever failover happens this statements needs to run or is there any setting which I have missed to set?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.