Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server - How to reset and recover SSIS catalog password (SQL Server master key)

Post Views 2,944 views
Reading time 3 minutes

Introduction

Hey guys!
In this post I will show you how to reset and recover the SSIS catalog password (SQL Server master key). Have you lost a SQL Server master key or SSIS catalog password? Let's get it back now 🙂

As you know, during the SSIS catalog creation process, you must define an encryption password.

When you complete this step, internally the wizard will create a master key in the SSISDB database that serves to encrypt that SSIS database and the packages.

And if you are not methodical and organized with your passwords and store them in a password vault, you might end up losing that password 🙂

So let's assume you are trying to add your SSIS catalog to an AlwaysOn node or simply move to another server/instance.

When you try to restore, you will be asked for the password used to encrypt your bank and you lost that password.

simulating the problem

To test that the password you have saved is the correct SSIS catalog password, you can run this command here:

If the password is correct, you will see this message here:

However, if the password is wrong, it will be this error message:

Msg 15313, Level 16, State 1, Line 4
The key is not encrypted using the specified decryptor.

And now? What to do in this case?

How to reset and recover SSIS catalog password (SQL Server master key)

To reset and recover the SSIS catalog password, the process is very easy and fast, although most people don't think that this is possible to do, it is so simple.

Just connect to the SSISDB database, make a backup of the master key specifying a new password and restore the backup you just made.

Follow the script below, with all the necessary steps:

After doing this, the master key, which was previously “dirceu”, is now “new_password”:

Now you can add this catalog to your AlwaysOn, restore on another server/instance or whatever other operation you are trying to do.

I hope you enjoyed this very practical tip in everyday life and until next time!