SQL Server 감사( Audit )를 활성화하고 감사 로그를 검토하는 방법

Microsoft SQL Server 감사를 수행하는 것은 보안 문제와 침해를 파악하는 데 매우 중요합니다. 또한 SQL Server 감사는 PCI DSS 및 HIPAA와 같은 규정 준수를 위한 요구사항입니다.

첫 단계는 무엇을 감사할지 정의하는 것입니다. 예를 들어 사용자 로그인, 서버 구성, 스키마 변경, 감사 데이터 수정 등을 감사할 수 있습니다. 다음으로는 사용할 보안 감사 기능을 선택해야 합니다. 유용한 기능은 다음과 같습니다:

• C2 감사
• 공통 준수 기준
• 로그인 감사
• SQL Server 감사ing
• SQL 트레이스
• 확장 이벤트(Extended Events)
• 변경 데이터 캡처(Change Data Capture)
• DML, DDL 및 로그인 트리거(DML, DDL, and Logon Triggers)

이 문서는 C2 감사(C2 auditing), Common Compliance Criteria, SQL Server Auditing을 사용하려는 데이터베이스 관리자(DBA)를 위한 글입니다. 제3자 감사 도구는 다루지 않습니다. 다만, 특히 대규모 환경이나 규제 산업에서는 큰 도움이 될 수 있습니다.

C2 감사 및 Common Criteria 준수 활성화(Enabling C2 Auditing and Common Criteria Compliance)

현재 SQL Server를 감사(감사 로그)하고 있지 않다면, 가장 쉬운 시작 방법은 C2 감사를 활성화하는 것입니다. C2 auditing은 SQL Server에서 켤 수 있는 국제적으로 인정된 표준입니다. 이 감사는 사용자 로그인, 저장 프로시저, 그리고 개체의 생성 및 제거 같은 이벤트를 감사합니다. 하지만 전부 또는 아무것도 아닙니다. 즉, 무엇을 감사할지 선택할 수 없고, 데이터가 대량으로 생성될 수 있습니다. 또한 C2 auditing은 유지관리 모드이므로 향후 SQL Server 버전에서 제거될 가능성이 큽니다.

Common Criteria Compliance는 C2 감사를 대체하는 더 새로운 표준입니다. 이는 유럽 연합에서 개발했으며, SQL Server 2008 R2 이상 버전의 Enterprise 및 Datacenter 에디션에서 활성화할 수 있습니다. 다만, 추가 오버헤드를 감당할 만큼 서버 사양이 충분하지 않다면 성능 문제가 발생할 수 있습니다.

SQL Server 2017에서 C2 감사를 활성화하는 방법은 다음과 같습니다.

1. SQL Server Management Studio를 엽니다.

2. C2 감사를 활성화하려는 데이터베이스 엔진에 연결합니다. 서버에 연결(Connect to Server) 대화 상자에서 Server type 이 Database Engine 으로 설정되어 있는지 확인한 다음 Connect 를 클릭합니다.

3. 왼쪽의 Object Explorer 패널에서 상단에 있는 SQL Server 인스턴스를 마우스 오른쪽 버튼으로 클릭한 다음 Properties 메뉴에서 선택합니다.

4. Server Properties 창에서 Security 아래의 Select a page 를 클릭합니다.

5. Security 페이지에서 로그인 모니터링을 구성할 수 있습니다. 기본적으로 실패한 로그인만 기록됩니다. 또는 성공한 로그인만 감사하거나, 실패 및 성공한 로그인을 모두 감사하도록 설정할 수도 있습니다.

그림 1. 액세스 감사 구성

6. Options 아래에서 Enable C2 audit tracing 를 선택합니다.

7. C2 Common Criteria 규정 준수 감사(auditing)를 사용하려면, Enable Common Criteria compliance 를 선택하세요.

Common Criteria (CC) 준수는 1~7 범위의 서로 다른 Evaluation Assurance Level(EAL)로 구현할 수 있는 유연한 표준입니다. EAL이 높을수록 더 까다로운 검증 프로세스를 거칩니다. Enable Common Criteria compliance 를 SQL Server에서 선택하면 CC 준수 EAL1을 활성화하는 것입니다. EAL4+에 대해 SQL Server를 수동으로 구성할 수도 있습니다.

CC 준수를 사용하면 SQL Server의 동작이 변경됩니다. 예를 들어, 테이블 수준의 DENY 권한이 열 수준의 GRANT보다 우선 적용되며, 성공한 로그인과 실패한 로그인 모두가 감사됩니다. 또한 Residual Information Protection (RIP)가 활성화되어, 새 리소스에서 사용되기 전에 메모리 할당을 비트 패턴으로 덮어씁니다.

8. OK 를 클릭합니다.

9. 선택한 옵션에 따라 SQL Server를 다시 시작하라는 메시지가 표시될 수 있습니다. 이 메시지가 나타나면 경고 대화 상자에서 OK 를 클릭합니다. C2 Common Criteria 규정 준수를 사용하도록 설정한 경우 서버를 다시 부팅하세요. 그렇지 않으면 Object Explorer에서 SQL Server 인스턴스를 다시 마우스 오른쪽 버튼으로 클릭하고 Restart 를 메뉴에서 선택합니다. 경고 대화 상자에서 Yes 를 클릭하여 SQL Server를 다시 시작하려는 것을 확인합니다.

SQL Server 감사 활성화

SQL Server 감사는 C2 감사 대신 활성화할 수 있으며, 두 가지를 모두 활성화하도록 선택할 수도 있습니다. SQL Server Audit 개체는 서버 수준 또는 SQL Server 데이터베이스 수준에서 이벤트를 수집하도록 구성할 수 있습니다.

서버 감사 개체 만들기

서버 수준의 SQL Server 감사 개체를 만들어 보겠습니다:

1. 왼쪽의 개체 탐색기 패널에서 Security 를 확장합니다.

2. Audits 항목을 마우스 오른쪽 버튼으로 클릭한 다음 New Audit… 메뉴에서 선택하세요. 그러면 서버 수준 감사를 위한 새 SQL Server Audit 개체가 생성됩니다.

3. Create Audit 창에서 감사 설정의 이름을 Audit name

4. On Audit Log Failure를 사용하여 SQL Server 감사가 실패할 경우 어떻게 처리할지 지정합니다. Continue 를 선택하거나, 감사되는 데이터베이스 작업을 중지하거나 서버를 종료하도록 선택할 수도 있습니다. Fail operation 를 선택하면 감사되지 않는 데이터베이스 작업은 계속 정상적으로 작동합니다.

그림 2. 서버 수준 SQL Server 감사 개체 만들기

5. Audit destination 드롭다운 메뉴에서 SQL 감사 트레일을 파일에 기록할지, Windows 보안 로그 또는 애플리케이션 이벤트 로그에서 이벤트를 감사할지 선택할 수 있습니다. 파일을 선택하는 경우, 파일의 경로를 지정해야 합니다.

Windows Security 이벤트 로그에 쓰려면 SQL Server에 권한을 부여해야 합니다. 간단히 하려면 Application 이벤트 로그를 선택하세요. 또한 감사 개체의 일부로 필터를 포함하면 결과를 좁혀서 제공할 수 있습니다. 필터는 Transact-SQL(T-SQL)로 작성해야 합니다.

6. OK 를 클릭합니다.

7. 이제 Audits 아래의 Object Explorer에서 새 감사 구성을 확인할 수 있습니다. 새 감사 구성에 마우스 오른쪽 버튼을 클릭한 다음 메뉴에서 Enable Audit을 선택하세요.

8. Close 를 Enable Audit 대화 상자에서 클릭합니다.

데이터베이스 감사 개체 만들기

데이터베이스 수준 감사를 위해 SQL Server 감사 개체를 생성하려면 과정이 조금 다르며, 먼저 최소 한 개의 서버 수준 감사 개체를 만들어야 합니다.

1. 개체 탐색기에서 Databases 를 확장한 다음 감사를 구성하려는 데이터베이스를 확장합니다.

2. Security 폴더를 확장한 다음, Database Audit Specifications를 마우스 오른쪽 버튼으로 클릭하고 New Database Audit Specification… 메뉴에서 선택합니다.

그림 3. 데이터베이스 수준 감사를 위한 서버 감사 사양 만들기

3. 속성 창에서 작업(Actions) 아래의 드롭다운 메뉴를 사용하여 하나 이상의 감사 작업 유형을 구성합니다. 감사를 원하는 문(예: DELETE 또는 INSERT), 작업이 수행되는 개체 클래스 등을 선택합니다.

4. 완료되면 OK 를 클릭한 다음, 해당 감사 개체를 마우스 오른쪽 버튼으로 클릭하고 Enable Database Audit Specification 를 선택하여 감사 개체를 활성화합니다.

SQL Server 감사 로그 보기

C2 SQL Server 감사 로그는 SQL Server 인스턴스의 기본 데이터 디렉터리에 저장됩니다. 각 로그 파일의 최대 크기는 200MB입니다. 제한에 도달하면 새 파일이 자동으로 생성됩니다.

SQL Server 감사 로그를 보기 위해 권장되는 기본 제공 도구인 Log File Viewer가 있습니다. 사용하려면 다음 단계를 수행합니다:

1. SQL Server Management Studio에서 개체 탐색기(Object Explorer) 창을 열고 Security 를 확장한 다음

2. 확인하려는 감사( audit ) 개체를 마우스 오른쪽 버튼으로 클릭한 다음 View Audit Logs 메뉴에서 선택합니다.

3. 로그 파일 뷰어(Log File Viewer)에서 로그가 오른쪽에 표시됩니다. 로그가 파일에 기록되는지 Windows 이벤트 로그에 기록되는지와 관계없이 Log File Viewer는 로그를 표시합니다.

4. Log File Viewer 상단에서 Filter 를 클릭하여 표시할 로그 항목을 사용자 지정할 수 있습니다. SQL Server 파일 로그는 .sqlaudit 형식으로 저장되며 읽을 수 없으므로 Log File Explorer에서 Export 를 클릭해 로그를 쉼표로 구분된 .log 파일 형식으로 저장할 수 있습니다.

그림 4. Log File Viewer에서 SQL Server 감사(audit) 로깅 검토

FAQ

SQL Server 감사(audit)가 활성화되어 있는지 확인하는 방법은?

SQL Server 감사(audit)가 활성화되어 있는지 확인하려면 sys.dm_server_audit_status 동적 관리 뷰(dynamic management view)를 쿼리하거나 SQL Server Management Studio(SSMS)에서 Security 폴더를 확인하세요. SSMS에서 Security > Audits를 확장하면 구성된 모든 감사와 현재 상태를 볼 수 있습니다. 활성화된 감사는 녹색 아이콘이 표시되고, 비활성화된 감사는 빨간색 아이콘으로 표시됩니다. 또한 다음 쿼리를 실행해 감사 상태를 프로그래밍 방식으로 확인할 수도 있습니다:

      SELECT name, is_state_enabled FROM sys.server_audits
      

완전한 감사(coverage)를 위해서는 서버 감사와 데이터베이스 감사 스펙(audit specification) 모두를 활성화해야 합니다. Data security가 Identity에서 시작하려면 누가 어떤 데이터를 액세스하는지에 대한 포괄적인 가시성이 필요하며, SQL Server audit은 올바르게 구성하고 검증했을 때 그 기반을 제공합니다.

SQL Server 감사(audit) 파일이 왜 이렇게 크게 증가하나요?

감사(audit) 파일이 과도하게 증가하는 현상은 보통 너무 많은 이벤트를 감사 중이거나 파일 관리 설정을 적절히 구성하지 않았을 때 발생합니다. 가장 흔한 원인은 ALL 감사 액션 그룹을 모두 활성화하거나 트래픽이 많은 테이블에서 SELECT 문을 감사하는 경우, 또는 회전(rollover) 없이 파일 증가를 무제한으로 설정하는 경우입니다. 증가를 제어하려면 규정 준수(compliance)를 위해 실제로 필요한 이벤트만 감사하는 데 집중하세요. 일반적으로 LOGIN_CHANGE_PASSWORD_GROUP, DATABASE_PERMISSION_CHANGE_GROUP, 그리고 중요(민감) 테이블에 대한 특정 DML 작업입니다. 최대 파일 크기 제한을 구성하고 MAXSIZE 및 MAX_ROLLOVER_FILES 옵션으로 파일 롤오버(file rollover)를 활성화하세요. 트래픽이 많은 환경에서는 APPLICATION_LOG 대상을 FILE 대상 대신 사용하는 것을 고려하거나, WHERE 절을 사용한 감사 필터링을 구현해 불필요한 이벤트 수집을 줄이세요. 현명한 감사란 데이터 잡음에 휩쓸리지 않으면서 중요한 내용을 추적하는 것을 의미합니다.

SQL Server 감사(audit)가 시작되지 않을 때 문제를 해결하는 방법은?

SQL Server 감사(audit)가 시작되지 않으면, 보통 파일 권한, 경로 접근성, 또는 설정 충돌과 관련된 문제입니다. 먼저 SQL Server 서비스 계정에 감사 파일 디렉터리에 대한 쓰기 권한이 있는지 확인하세요. 이는 시작 실패의 가장 흔한 원인입니다. SQL Server 오류 로그를 확인해 구체적인 오류 메시지를 찾으면, 대개 문제 해결에 대한 명확한 단서를 제공합니다. 대상 디렉터리가 존재하고 SQL Server 인스턴스에서 접근 가능한지 확인하세요. 특히 공유 스토리지 경로가 모든 노드에서 유효해야 하는 클러스터 환경에서는 더욱 중요합니다. 대상이 Windows Application Log인 경우, 서비스 계정에 적절한 이벤트 로그 쓰기 권한이 있는지도 확인해야 합니다. 중복된 감사 이름이나 잘못된 파일 경로 같은 구성 오류도 시작을 막을 수 있습니다. 핵심은 체계적인 트러블슈팅입니다. 먼저 권한을 확인한 다음 경로, 마지막으로 구성 구문을 확인하세요. Netwrix는 이러한 일반적인 함정을 제거하는 중앙 집중식 감사 관리 기능을 제공함으로써 이 복잡함을 단순화합니다.

SQL Server 감사(audit)는 성능에 어떤 영향을 주나요?

SQL Server 감사(auditing)는 올바르게 구성하면 성능 영향이 최소이며, 대부분의 환경에서 일반적으로 2~5% 정도의 오버헤드만 추가됩니다. 실제 영향은 세 가지 핵심 요소에 따라 달라집니다. 어떤 이벤트를 감사할지, 그 이벤트가 얼마나 자주 발생하는지, 그리고 스토리지 하위 시스템의 성능입니다. 트래픽이 많은 OLTP 시스템에서 SELECT 문과 같은 고빈도 작업을 감사하면, 로그인, 권한 변경, 중요 테이블에 대한 DML 작업처럼 보안과 관련된 이벤트에 집중하는 경우보다 더 많은 오버헤드가 발생합니다. 비동기 감사 대상(기본값)은 동기 옵션보다 성능이 더 좋지만, 이벤트 기록이 약간 지연될 수 있습니다. 영향을 최소화하려면 WHERE 절을 사용한 감사 필터링을 적용하고, 불필요한 시스템 작업은 감사하지 않으며, 감사 파일 저장소가 충분한 I/O 처리 능력을 갖추었는지 확인하세요. 대량 처리 시나리오에서는 Extended Events가 대체로 SQL Server Audit보다 오버헤드가 낮지만, SQL Server Audit은 더 우수한 컴플라이언스 기능과 더 쉬운 관리성을 제공합니다. 스마트한 감사 설계는 포괄적인 로깅보다 보안 가치를 우선합니다. 성능을 마비시키지 않으면서도 보호하는 가시성이 필요합니다.

SQL Server 감사(audit) vs SQL Trace: 무엇을 사용해야 하나요?

SQL Server Audit은 신규 구현을 위한 현대적인 선택인 반면, SQL Trace는 더 이상 사용되지 않는(Deprecated) 기능이므로 신규 프로젝트에서는 사용을 피해야 합니다. SQL Server Audit은 기존의 SQL Trace 기능에 비해 더 나은 보안, 성능, 관리 기능을 제공합니다. SQL Trace와 달리 SQL Server Audit 이벤트는 사용자(시스템 관리자 포함)가 수정하거나 삭제할 수 없으므로, 컴플라이언스 요구사항을 충족하기 위한 감사(audit) 무결성을 보장합니다. 감사 프레임워크는 더 나은 성능을 위한 비동기 처리, 내장된 필터링 기능, 그리고 Windows Security Event Log와의 통합을 제공합니다. SQL Trace는 저장 프로시저를 이용한 수동 코딩이 필요하며, 향후 SQL Server 버전에서 제거될 예정으로 표시되어 있습니다. Extended Events는 SQL Trace의 진단(diagnostic) 기능을 대체하기 위한 권장 솔루션이며, SQL Server Audit은 보안 및 컴플라이언스 모니터링을 담당합니다. 현재 보안 감사 목적으로 SQL Trace를 사용 중이라면 즉시 SQL Server Audit으로 마이그레이션하세요. 이는 진정한 데이터 보안이 요구하는 변조 방지 감사 추적을 제공합니다. Netwrix 솔루션은 이러한 기본 감사 기능을 기반으로 하여, 전체 데이터 환경에 걸친 중앙 집중형 가시성을 제공합니다.