SQL 서버 감사가 있는 데이터베이스 복원 후 설정

SQL 서버 감사가 있는 데이터베이스 복원 후 설정

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

데이터베이스에 서버 감사(Server Audit)가 설정되어 있는 경우 해당 데이터베이스를 복원 하면 감사 메커니즘은 감사 데이터를 캡처 할 수 없는 문제가 발생한다. 다음 방법을 통하여 감사 데이터를 캡처 할 수 있도록 설정 하자.

 

기존 데이터베이스에 감사를 만든다.

USE master GO   CREATE SERVER AUDIT [Test_Audit] TO FILE ( FILEPATH = N'C:\SQL_Data\' ,MAXSIZE = 100 MB ,MAX_ROLLOVER_FILES = 90 ,RESERVE_DISK_SPACE = ON ) WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ) GO   USE SQLTAG GO   CREATE DATABASE AUDIT SPECIFICATION [Test_Audit_DB] FOR SERVER AUDIT [Test_Audit] ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP), ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (SCHEMA_OBJECT_CHANGE_GROUP), ADD (DELETE ON DATABASE::[SQLTAG] BY [dbo]), ADD (INSERT ON DATABASE::[SQLTAG] BY [dbo]), ADD (UPDATE ON DATABASE::[SQLTAG] BY [dbo]), ADD (SELECT ON DATABASE::[SQLTAG] BY [dbo]) WITH (STATE = ON) GO

 

다음과 같이 SQLTAG 데이터베이스에 감사가 생성된 것을 확인 할 수 있다.

 

SQLTAG에 감사가 정상적으로 캡처 되는지 확인해 보자. 실습 방법은 테이블을 생성하여 해당 작업이 캡처되었는지 확인 한다.

USE SQLTAG GO   CREATE TABLE TBL_B (COL1 INT) GO   SELECT * FROM FN_GET_AUDIT_FILE('C:\SQL_DATA\*', DEFAULT, DEFAULT)

 

 

 

[SQLTAG] 데이터베이스를 백업 한다.

BACKUP DATABASE SQLTAG TO DISK = 'C:\SQL_BACKUP\SQLTAG_AUDIT.BAK'

 

 

[SQLTAG] 데이터베이스의 백업 파일을 [SQLTAG2] 이름으로 복원 한다.

restore headeronly from disk = 'C:\SQL_BACKUP\SQLTAG_AUDIT.BAK' restore filelistonly from disk = 'C:\SQL_BACKUP\SQLTAG_AUDIT.BAK'   RESTORE DATABASE SQLTAG2 FROM DISK = 'C:\SQL_BACKUP\SQLTAG_AUDIT.BAK' WITH RECOVERY, MOVE 'SQLTAG' TO 'C:\SQL_DATA\SQLTAG.MDF', MOVE 'SQLTAG_LOG' TO 'C:\SQL_DATA\SQLTAG_LOG.LDF'

 

복원 후 [SQLTAG2]의 감사를 살펴보면 기존의 감사 사양이 그대로 복원 된 것을 확인 할 수 있다.

 

새로 복원한 [SQLTAG2]의 경우 감사는 정의되어 있지만 서버 수준의 감사 정의는 SQLTAG2에 존재 하지 않는다. 따라서 감사 데이터가 캡처 되지 않는다. 이 문제를 해결하기 위하여 감사에 사용된 특정 GUID를 이용하여 서버 감사 명령을 실행 헤애 한다.

 

[기존 데이터베이스에 접근 할 수 있는 경우 GUID 추출하기] – 다음과 같이 SSMS에서 마우스 오른쪽을 클릭하여 스크립트 구문을 생성하면 GUID를 확인 할 수 있다.

 

[기존 데이터베이스에 접근 할 수 없는 경우 GUID 추출하기] – 새로 복원한 [SQLTAG2]에서 다음의 스크립트를 실행하여 GUID를 추출한다.

SELECT * FROM SYS.DATABASE_AUDIT_SPECIFICATIONS

 

 

GUID 추출이 완료 되었으면 다음 스크립트를 이용하여 감사를 설정한다. (기존 데이터베이스를 이용하여 스크립트를 생성한 경우 FILE_PATH를 변경하여 사용 할 수 있다.)

CREATE SERVER AUDIT [Test_Audit] TO FILE ( FILEPATH = N'C:\SQL_Data2\' ,MAXSIZE = 100 MB ,MAX_ROLLOVER_FILES = 90 ,RESERVE_DISK_SPACE = ON ) WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ,AUDIT_GUID = '1FA32A8E-9A82-4B0F-9C34-8B79826F62CF' ) GO

 

이 작업이 완료 되면 데이터베이스가 감사 데이터를 수집을 시작 한다.

 

 

2012-11-05 / 강성욱 / http://sqlmvp.kr