Friday, August 9, 2019

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).



In one newly configured Integration Services Catalog instance while deploying a project through *.ispac file, the deployment failed at ‘Deploying project’ step as shown below and has thrown below error.




Below is the error received:




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).


To fix this error we need to open the master key manually by using below command.


USE [SSISDB]
OPEN master Key decryption by password = 'Password'


But I was not able to run the above command due to a special character (which is a single code symbol ) in my master key password. So to overcome this error I have to change the password of the master key first.


How to change the password of the master key?

To change the password of the master key, use the below command as mentioned here. Make sure you run this on the SSISDB database if not it will throw an error.


USE DatabaseName;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'New_Password';
GO

Now I tried deploying the project again using the same *.ispac file and this time it completed successfully.

If you noticed I have tried deploying the project without opening the master key and it completed successfully so in my scenario the issue was with the special characters in the password so after changing the password the project deployment completed successfully.


Points to Note:

·        If you are receiving an error stating create a new master key or open the existing master key, then you need to open the master key manually with the command provided above.
·        If you want to change the password of the master key you can use ALTER MASTER KEY command as mentioned above.


Let me know is there any other way you fixed this issue in your environment in the comment section below.




Thanks VV!!




#SSISDB; #SQLServer; #SQL; #SSISCatalog #IntegrationServicesCatalog

5 comments:

  1. while opening master key am getting below error. Could you please advice on it.

    Msg 15151, Level 16, State 1, Line 1
    Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.

    ReplyDelete
    Replies
    1. Hi Vijay, If master key is encrypted you don't need to open it again. Have you tried without opening it.

      Delete
    2. Getting same error by exicuting alter query

      Delete
  2. I'm in the habit of creating long strong passwords - I got exactly this problem! Thanks for the solution, Vinay-ji!

    ReplyDelete