[스크랩] C2 audit mode 옵션 관련

 

http://www.sqldbadiaries.com/2010/11/04/c2-auditing-mode-brought-down-the-instance/

 

C2 Auditing mode brought down the instance

Yesterday the application team reached out to our team stating that the drive hosting SQL Server databases was running out of space. It is not something new for our team that the Application Support team holds the database as the reason for any issue they face with the Application. But this time even we were alerted that the E drive on that server was running out of space. When the DBA logged on to the server, the drive had 0 KB free space left!

A quick glance on that drive indicated that .trc files ~30 GB in size were there on that drive. The folder hosting these files looked like this.

Since all these files had the naming convention of audittrace*.trc, it was very clear that these files were generated becuase the C2 Audit mode was enabled. In order to check the configuration when the DBA tried connecting to the instance, the fact that the instance was down came into light! The SQL Server Error Log had this information in the end.

?

1 2 3 4 5 6

Error: 566, Severity: 21, State: 1. An error occurred while writing an audit trace. SQL Server is shutting down. Check and correct error conditions such as insufficient disk space, and then restart SQL Server. If the problem persists, disable auditing by starting the server at the command prompt with the "-f" switch, and using SP_CONFIGURE. SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

The SQL Server got stopped when it was trying to write to an audit trace file. Troubleshooting this issue is very easy if one know how C2 Audit mode works. Here are some of the features of C2 Audit mode in SQL Server.

• The output of C2 auditing trace files is saved to the Default Data directory for an instance.
• There is no option to change the path of the C2 auditing trace files to a directory other than this.
• When the trace file size reaches 200MB, it is automatically rolled over and a new trace file is created.
• There is no built-in functionality to delete the old trace files. SQL Server will keep generating the trace files until the drive gets full.
• A custom job needs to be setup which deletes trace files older than x number of days.
• If the drive where the trace files are saved gets full, SQL Server will shutdown itself.

The last point was key in this case. If the drive where the audit trace files are getting saved runs out of space, SQL Server stops the SQL Server service. To overcome this issue there are two options. First is to clear space on the drive where the audit trace files are saved. Second option is to start the SQL Server service with -f flag which bypasses auditing and then disabling C2 Audit mode.

C2 audit mode can be disabled either by T-SQL or SSMS as below.

T-SQL:

?

1 2 3 4

sp_configure 'c2 audit mode',0 go reconfigure with override go

SSMS:

In our case when we asked the client if C2 Audit needs to be enabled, he was clueless why it was enabled in the first place. Most likely when the instance was built, the DBA enabled this feature by mistake. Now that the client does not require this feature to be enabled, we are processing a Change Control to disable it.

Related posts:

1. Start SQL Server in Single User Mode and allow only a particular Application to Connect
2. Stopping the trace because the current trace file is full
3. Error Code 12 while running Server Side Trace
4. SQL Server Profiler and the C drive space issue
5. Change authentication mode from Windows Registry

 

 

 

 

 

http://msdn.microsoft.com/ko-kr/library/ms187634.aspx

 

C2 Audit Mode는 SQL Server Management Studio을 통해 구성하거나 sp_configure에 c2 audit mode 옵션을 사용하여 구성할 수 있습니다. 이 옵션을 선택하면 문과 개체에 대해 실패한 액세스 시도와 성공한 액세스 시도를 모두 기록하도록 서버가 구성됩니다. 이 정보는 시스템 동작을 파악하고 보안 정책 위반을 추적하는 데 도움이 됩니다.

참고
Microsoft SQL Server의 이후 버전에서는 이 기능이 제거됩니다. 새 개발 작업에서는 이 기능을 사용하지 않도록 하고, 현재 이 기능을 사용하는 응용 프로그램은 수정하십시오. C2 보안 표준은 Common Criteria 인증으로 대체되었습니다. common criteria compliance enabled 서버 구성 옵션을 참조하십시오.

감사 로그 파일

---

C2 Audit Mode 데이터는 인스턴스의 기본 데이터 디렉터리에 파일로 저장됩니다. 감사 로그 파일 크기가 제한 크기(200MB)에 도달하면 SQL Server에서 새 파일을 만들고 기존 파일을 닫은 후 새로운 감사 기록을 모두 새 파일에 작성합니다. 이러한 프로세스는 감사 데이터 디렉터리가 꽉 차거나 감사 옵션이 해제될 때까지 계속됩니다. C2 추적의 상태를 확인하려면 sys.traces 카탈로그 뷰를 쿼리합니다.

중요
C2 Audit Mode를 사용하면 로그 파일에 많은 양의 이벤트 정보가 저장되어 파일의 크기가 빠르게 커집니다. 로그 파일이 저장되는 데이터 디렉터리의 공간이 부족하면 SQL Server가 자동으로 종료됩니다. 감사가 자동으로 시작되도록 설정되어 있으면 감사 프로세스를 생략하도록 하는 -f 플래그를 사용하여 인스턴스를 다시 시작하거나 감사 로그를 저장할 수 있는 디스크 공간을 추가로 확보해야 합니다.

 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a8a68a67-7c69-4b7a-91b9-4bb30d960600/c2-audit-tracing-record-question?forum=sqlsetupandupgrade

 

1. Trace file is saved as <audittrace..Year..Month..Date...Timestamp hr:min:sec>

2. There is only 1 trace file per instance for all databases. There should be only 1 trace file (200mb) with the latest time stamp open for logging at any given time for particular instance.

3. C2 Log files always point to the default data files location of the instance. You cannot change the path of C2 audit files but you can change the location of default data location to some other drive. For changing the default path

right click on server->choose database setting change the database default locations to where you want to point the files.

 

http://msdn.microsoft.com/ko-kr/library/dd206993.aspx

이상한게, 2005로 테스트했을 때는 서비스 재시작 없이 바로 디폴트 로케이션이 변경되었는데, 2008 이상은 서비스 재시작해야 적용한다라는 msdn 문서가 있었다.

실제 내가 2008이 테스트 DB가 없어서 테스트는 하지 못하였다.

After SQL restart trace file will be created to the new default location provided in Database setting This will not change the existing database location. However you will need to make sure next time you create database point it to the right location

SO I am afraid, It seems we may be unable to modify the order of the fields in C2 audit trace record.