SQL Server - Como resetar e recuperar a senha do catálogo do SSIS (master key do SQL Server)

Introdução

Fala pessoal!
Nesse post vou demostrar para vocês como resetar e recuperar a senha do catálogo do SSIS (master key do SQL Server). Você perdeu a chave de uma master key do SQL Server ou a senha do catálogo do SSIS? Vamos recuperá-la agora 🙂

Como vocês sabe, durante o processo de criação do catálogo do SSIS, você deve definir uma senha de criptografia.

Quando você completa esse passo, internamente o assistente irá criar uma master key no banco de dados SSISDB que serve para criptografar esse banco do SSIS e os pacotes.

E caso você não seja uma pessoa metódica e organizada com suas senhas e as armazene em um cofre de senhas, você pode acabar perdendo essa senha 🙂

Então vamos supor que você esteja tentando adicionar o seu catálogo do SSIS em um nó do AlwaysOn ou simplesmente mover para outro servidor/instância.

Quando você for tentar restaurar, será solicitada a senha utilizada para criptografar o seu banco e você perdeu essa senha.

Simulando o problema

Para testar se a senha que você tem salva é a senha correta do catálogo do SSIS, você pode executar esse comando aqui:

USE [SSISDB]
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'sua_senha_aqui'
GO

Caso a senha esteja correta, você verá essa mensagem aqui:

Entretanto, caso a senha esteja errada, será essa mensagem de erro:

E agora? O que fazer nesse caso?

Como resetar e recuperar a senha do catálogo do SSIS (master key do SQL Server)

Para resetar e recuperar a senha do catálogo do SSIS, o processo é bem fácil e rápido, embora a maioria das pessoas não pense que isso seja possível fazer, de tão simples que é.

Basta conectar no banco SSISDB, fazer um backup da master key especificando uma nova senha e restaurar o backup que você acabou de fazer.

Segue o script abaixo, com todos os passos necessários:

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

Após fazer isso, a chave da master key, que antes era “dirceu”, agora é “nova_senha”:

Agora você já pode adicionar esse catálogo ao seu AlwaysOn, restaurar em outro servidor/instância ou qualquer outra operação que você esteja tentando fazer.

Espero que vocês tenham gostado dessa dica bem prática no dia a dia e até a próxima!