SQL Server/SQL Server Tip

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

SungWookKang 2015. 7. 17. 10:02
반응형

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

 

 

반응형