SQL Agent 또는 다른 PC에서 SSIS 패키지가 실행되지 않는 문제 (Updated 2007.05.14)

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

현상

로컬 PC 또는 서버에서는 패키지를 실행하면 제대로 수행이 되는데, 패키지 파일(*.dtsx)을 서버에 저장한 후 SQL Agent에 등록하여  실행하거나 패키지가 만들어진 PC나 서버가 아닌 다른 사용자의 환경에서 패키지를 실행할 경우 에러가 발생할 수 있습니다.

다음과 같이 패키지가 개발된 환경에서 직접 패키지를 실행하면 정상적으로 수행됩니다.

이 패키지를 SQL Agent에 다음과 같이 등록한 후 실행하면 에러가 발생합니다.

(Service Pack2 이전의 에러 메시지)

(Service Pack2 이후의 에러 메시지)

원인

SSIS 패키지의 기본 보안 수준인 ProtectionLevel은 EncryptSensitiveWithUserKey로 설정이 되어 있습니다.

이 값은 패키지의 제어 흐름 영역에서 빈 영역을 클릭한 후, 오른쪽에 있는 속성 창에서 확인할 수 있습니다.

EncryptSensitiveWithUserKey는 중요한 데이터(Sensitive Data)를 현재 패키지가 개발되는 사용자 프로필 기반의 키를 이용하여 암호화 한다는 의미입니다. 여기서 말하는 중요한 데이터란 DB의 연결 정보 중 패스워드, Web 서비스 연결의 패스워드와 같은 값입니다. 따라서 윈도우 인증이 아닌 패스워드를 이용한 OLE DB 연결이나 Web 서비스 작업 등이 포함된 경우, 중요한 데이터가 패키지에 포함된 상태이기 때문에 패키지가 암호화 됩니다.

수동으로 실행할 때에는 패키지가 암호화된 개인 키와 실행 환경의 개인키가 동일하기 때문에 정상적으로 실행됩니다. 하지만, 서버로 파일을 복사한 후, SQL Agent에서 실행하거나, 현재의 개발 PC가 아닌 다른 곳에 패키지를 복사해서 수행하게 되면, 패키지가 암호화된 개인키와 실행 환경의 개인키가 다르기 때문에 오류가 발생합니다.

해결 방안

[방법 1] 패키지의 ProtectionLevel을 EncryptSensitiveWithUserKey 대신, EncryptSensitiveWithPassword 또는 EncryptAllWithPassword로 설정한 후, PackagePassword 항목에 암호를 설정합니다.

이런 방식으로 저장하게 되면 패키지의 중요한 정보 또는 패키지 정보 전체가 개인키 대신 암호를 기반으로 암호화 시키게 됩니다.

이후, SQL Agent에서 이 패키지를 실행하기 위해 설정하는 단계에서는 다음과 같은 암호 입력 창이 나타납니다.

(일반 탭에서 실행할 패키지를 지정한 후, 다른 탭을 클릭할 경우 나타납니다.)

위와 같은 패키지 암호 설정 창에서 지정한 암호를 입력한 후, 패키지를 실행하면 정상적으로 수행됩니다.

[방법 2]  패키지의 ProtectionLevel을 ServerStorage로 임시 변경한 후, 패키지를 실행할 SQL Server에 저장시킵니다.

ProtectionLevel을 ServerStorage로 설정하는 것은 패키지의 보안과 관련된 사항을 SQL Server에게 관리하도록 넘기는 것입니다. 이 때, 주의할 것은 이 설정으로 변경한 경우에는 패키지는 파일 형태로 저장할 수 없고 SQL Server로만 저장할 수 있습니다.

개발 화면의 상단 파일(F) 메뉴 중, 다른 이름으로 ….dtsx의 복사본 저장(C) 메뉴를 클릭합니다.

패키지 위치를 SQL Server로 지정한 후, 인증 유형을 설정합니다. Windows 인증이나 SQL Server 인증이나 상관없이 연결할 수 있는 적절한 권한(msdb의 db_dtsadmin 역할 또는 db_dtsltduser 역할)이 있는 계정으로 지정하면 됩니다. 패키지 경로(K) 부분에서는 저장할 패키지 명을 지정합니다. 옆의 ... 버튼을 눌러 폴더 형식과 같은 적절한 경로를 지정할 수도 있습니다.

단, 이 때 SQL Server에 저장시킨 후, 다시 로컬 파일로 저장할 경우 저장이 되지 않습니다. 이는 앞서 언급한 바와 같이 ProtectionLevel을 ServerStorage로 설정하였기 때문입니다. 따라서 패키지를 별도의 파일로 저장하기 위해서는 ProtectionLevel의 속성값을 ServerStorage 이외의 값으로 변경하셔야 합니다.

이후, SQL Agent에서 패키지를 실행하는 작업을 등록할 경우, 다음 그림과 같이 패키지 원본을 SQL Server로 지정한 후, 저장한 패키지를 선택하면 됩니다.

[방법 3]  SQL Agent의 작업 중, 데이터 원본 탭에서 DB 연결에 대한 Password를 지정합니다.

패키지를 실행하는 작업 단계 중, 데이터 원본 탭을 클릭하면 패키지에서 이용하는 데이터 원본이 표시됩니다. 이 때, SQL 인증을 사용하는 DB 연결을 선택한 후, 연결 문자열 부분에 Password=... 구문을 추가합니다.

예) Data Source=dshan\ss2005;User ID=sa;Initial Catalog=tempdb;Provider=SQLOLEDB.1;Auto Translate=False;Password=dbpassword

[방법 4]  DB 정보를 포함하는 구성 파일을 만든 후, 이를 구성 탭에서 지정합니다.

패키지 내의 모든 개체에 대한 속성은 구성 이라는 기능으로 지정할 수 있습니다. 또한 구성 정보는 파일로 저장할 수도 있습니다.

(구성에 관한 자세한 내용은 다음 링크의 강좌를 참고하시기 바랍니다.

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=454)

패키지에서 사용하는 DB 연결 정보를 별도의 구성 파일로 만든 후에 이 구성 파일을 지정하는 식으로 문제를 해결할 수 있습니다. 예를 들어 다음과 같은 구성 파일을 생성합니다.

예) D:\dbconnectionstring.dtsconfig 라는 이름으로 구성 파일 생성

이 구성 파일은 “.\ss2005.tempdb” 라는 연결에 대해 ConnectionString 정보를 가지고 있는 구성 파일입니다. 이 구성 파일을 SQL Agent의 작업 단계 설정 중 구성 부분에서 추가합니다.

이 방식은 프로젝트 단위의 여러 패키지가 존재하며, 각 패키지에서 같은 DB 정보를 이용할 때 일괄적으로 관리할 수 있는 장점이 있습니다. 또한, 직접 데이터 원본 부분에서 password를 지정해주는 대신 별도의 파일에 기록함으로써 DB Password의 노출을 방지할 수 있습니다. (파일에 대한 접근 권한은 SQL Agent 실행 계정 및 Administrator에게만 부여하도록 Windows 환경에서 설정하면 됩니다.)

[방법 5] 패키지의 ProtectionLevel을 DontSaveSensitive로 변경한 후, DB 연결 정보의 값은 식을 이용하여 지정하는 방식으로 저장합니다. 여기서 ProtectionLevel을 DontSaveSensitive로 설정한 것은 다른 사용자가 패키지를 열었을 때에도 보안 경고를 발생시키지 않도록 하기 위함입니다.

우선 패키지의 보안 수준을 DontSaveSensitive로 변경합니다.

연결 관리자에서 SQL 인증을 이용하는 연결을 선택한 후, 속성의 Expression 부분에서 ... 버튼을 클릭하여 해당 연결의 속성에 대한 식을 지정합니다.

속성 식 편집기에서 ConnectionString 속성을 선택하고 해당 연결에 대한 연결 문자열을 직접 지정합니다. 이 때, 연결 문자열에 Password를 포함해서 지정해야 합니다.

[참고] 직접 식으로 지정하는 대신, 연결 문자열을 포함하는 String형 변수를 지정하고, 속성 식에서 이 변수를 사용하도록 설정할 수도 있습니다.

본 게시판에 실린 글은 누구나 복사하셔서 이용하셔도 되지만, 반드시 출처(SQLLeader.com) 및 링크를 밝혀주셔야 합니다.