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:
|
1 2 3 4 5 |
USE [SSISDB] GO OPEN MASTER KEY DECRYPTION BY PASSWORD = 'sua_senha_aqui' GO |
If the password is correct, you will see this message here:
However, if the password is wrong, it will be this error message:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- Acessa o banco "SSISDB", que é o banco de dados do SSIS USE [SSISDB]; GO -- Faz o backup da chave atual com o nome "SSISDB_masterkey" para o diretório "C:\". A nova senha dessa chave é "nova_senha" BACKUP MASTER KEY TO FILE = 'C:\SSISDB_masterkey' ENCRYPTION BY PASSWORD = 'nova_senha' GO -- Restaura o backup recém realizado, utilizando a chave "nova_senha" para descriptografar e define essa mesma senha como a chave de criptografia RESTORE MASTER KEY FROM FILE ='C:\SSISDB_masterkey' DECRYPTION BY PASSWORD = 'nova_senha' ENCRYPTION BY PASSWORD = 'nova_senha' GO -- Tenta abrir a master key com a senha "nova_senha" OPEN MASTER KEY DECRYPTION BY PASSWORD = 'nova_senha' GO -- Fecha a master key CLOSE MASTER KEY GO |
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!
Portuguese 
English
Opa Dirceu,
No primeiro script de backup retornou o mesmo erro que estou pegando ao dar deploy de um projeto no SSISDB
Please create a master key in the database or open the master key in the session before performing this operation.
Opa Dirceu, primeiramente obrigado por compartilhar tanto conhecimento.
Tentei seguir o passo a passo mas no primeiro script de backup me retornou erro
Please create a master key in the database or open the master key in the session before performing this operation.