[SSIS] 패키지 파일 패스워드 확인 쿼리

SSIS의 패키지의 패스워드를 잃어버렸다.

그런데 아무리 머리를 쥐어짜도 모르겠다.

만약 해당 패키지가 Job에 등록 되어있다면 찾을 수 있다.

업무를 인수인계 받았는데 담당자가 부재거나 퇴사 했는데 이런 경우가 발생했다면 정말 난감하다. 애초에 정말 패스워드 관리를 잘해야 하는게 맞지만... 사람 일이 뭐 매번 뜻대로 되는 것은 아니니 빨리 해결할 수 있는 방법을 찾아보자.

조건: 해당 패키지가 SQL 서버에 JOB으로 등록되어 있을 경우이다.

쿼리는 아래와 같다.

USE msdb GO -- 패키지 파일 암호 확인 쿼리 DECLARE @jobname VARCHAR(128) = NULL     , @stepname VARCHAR(128) = NULL; SELECT sj.name AS JobName         , sjs.step_name         , sjs.subsystem         , sjs.command         ,CASE WHEN sjs.command LIKE '%/DECRYPT "\"%'                  THEN SUBSTRING(sjs.command, CHARINDEX('/DECRYPT "\"',sjs.command,1)+12                 , CHARINDEX('\""',SUBSTRING(sjs.command                 , CHARINDEX('/DECRYPT "\"',sjs.command,1)+12, LEN(sjs.command)),1)-1)             WHEN sjs.command NOT LIKE '%/DECRYPT "\"%' AND sjs.command LIKE '%/DECRYPT "%'                 THEN SUBSTRING(sjs.command                 , CHARINDEX('/DECRYPT "',sjs.command,1)+10                 , CHARINDEX('"',SUBSTRING(sjs.command                 , CHARINDEX('/DECRYPT "',sjs.command,1)+10, LEN(sjs.command)),1)-1)             ELSE LTRIM(RTRIM(SUBSTRING(sjs.command,                 CHARINDEX('/DECRYPT', sjs.command, 1) + 9,                 CHARINDEX('/', sjs.command,                 CHARINDEX('/DECRYPT', sjs.command, 1)                 + 1) - CHARINDEX('/DECRYPT',                 sjs.command, 1)                 - 9)))         END AS pkgpassword     FROM msdb.dbo.sysjobs sj         INNER JOIN msdb.dbo.sysjobsteps sjs             ON sj.job_id = sjs.job_id     WHERE sjs.subsystem = 'SSIS'         AND sjs.command LIKE '%/DECRYPT%'          AND sj.name = ISNULL(@jobname, sj.name)         AND sjs.step_name = ISNULL(@stepname, sjs.step_name);

해당 쿼리로 암호를 찾을 수 있는 이유는 SQL Server Agent에서 Job을 실행할 때 Command 형태로 명령을 내리는데 이때 패키지 파일의 암호도 옵션으로 들어가 있기 때문이다.

어떻게 보면 보안에 정말 취약한 부분이다.

해당 쿼리는 참고를 통해 내가 관리하는 DB에 맞게 변경한 것이다. 

예외 사항이 있을 수 있겠지만 암호를 확인할 수 있고, sqlcommand 컬럼에서 해당 패키지의 경로, 패스워드, 옵션을 정확하게 확인할 수 있다. 

참고쿼리 <- 출처를 확인 하고 싶으면 클릭하면 된다.