Enhanced SQL Server Security Auditing
by MAK [Muthusamy Anantha Kumar]
Applies to: SQL Server 7.0 and 2000.
Security audit in SQL server captures only successful and failed logins. It does not capture the application that uses the login. Login used by an application may have full read and write access on all of the tables and procedures but the application restricts the users by providing a front-end which will allow them to see only a few columns, tables etc. However, certain users out of curiosity may log on to the database using SQL Query tools such as Enterprise manager and Query analyzer, using production login information. The following process will capture such un-authorized users who log on to the SQL server.
Process
All of the processes in SQL Server can be viewed by querying the system table "sysprocesses." This whole article is based on that table.
Step1: Create a job with one job step.
Job:
Job Step
Step2: Copy and paste the code below into the command window of the job step.
select identity(int,1,1) as traceid, a.name as [Database],
ltrim(rtrim(convert(varchar,b.spid))) as spid,ltrim(rtrim(b.loginame)) as loginame,ltrim(rtrim(b.program_name))
as program_name,ltrim(rtrim(b.hostname)) as hostname into #audittrace from master.dbo.sysprocesses b (nolock) ,
master.dbo.sysdatabases A where a.dbid = b.dbid and ltrim(rtrim(loginame)) not in ('DBA1','domain\systemaccount','DBA2','domain\administrator') and ltrim(rtrim(left(program_name,8))) in ('MS SQLEM','SQL Quer')--drop table #audittraceselect * from #audittracedeclare @count intdeclare @message varchar(1000)set @count = (select count(*) from #audittrace)While @count >=1 beginset @message = (select 'SQL Security Enhanced Auditing: SPID =' + spid +' ,
Database: ' + [Database] + ' ,Loginame: ' + loginame + ' ,hostname: '+ hostname +' , Program Name: ' + program_name from #audittrace where traceid = @count)set @count = @count-1RAISERROR (@message, 16, 1) with logenddrop table #audittrace
Step3: Make the job Success/Failure flow to report success on both success and failure.
Step4:
Create schedule for this job to run every 15 minutes on a daily basis.
Result:
When the job executes, if it finds un-authorized users using Enterprise Manager or Query Analyzer it creates an entry in the SQL Server Error log, the Event viewer's Application log and also in the job history.
SQL Server error log:
Application log
Job History
Note: Change the login list in the code [('DBA1','domain\systemaccount','DBA2','domain\administrator') to reflect the list of logins you do not want to capture as an un-authorized user. For capturing different applications, change the application list in the code from ('MS SQLEM','SQL Quer') to the list of applications you would like to capture.
Conclusion:
In this way, you can capture the un-authorized use of logins that connect to SQL Server. This is an enhanced version of SQL Server internal security auditing. This helps not only in capturing the unauthorized user but also for capturing un-authorized applications that are being connected to SQL Server.
=================================================================================================================
-- c2 Audit 모드 설정
EXEC sp_configure 'show advanced option', '1'
go
reconfigure
go
EXEC sp_configure 'c2 audit mode','1'
go
RECONFIGURE
go
-- 감사관련 정보가 들어있는 테이블
select * from master.dbo.sysdatabases
select * from master.dbo.sysusers
select * from master.dbo.sysprocesses
-- Audit Trace 정보를 만드는 부문 : 적절하게 수정해서 사용
drop table #audittrace
select identity(int,1,1) as traceid, a.name as [Database],
ltrim(rtrim(convert(varchar,b.spid))) as spid,
ltrim(rtrim(b.loginame)) as loginame,ltrim(rtrim(b.program_name))
as program_name,ltrim(rtrim(b.hostname))
as hostname, b.login_time as login_time, b.cmd as command, b.net_address as net_address
into #audittrace from master.dbo.sysprocesses b (nolock) ,
master.dbo.sysdatabases A where
a.dbid = b.dbid and ltrim(rtrim(loginame)) not in
('DBA1','domain\systemaccount','DBA2','domain\administrator', 'NT AUTHORITY\SYSTEM')
-- and ltrim(rtrim(left(program_name,8))) in ('MS SQLEM','SQL Quer')
-- 아래는 생성된 정보를 출력하는 루틴
select * from #audittrace
declare @count int
declare @message varchar(1000)
set @count = (select count(*) from #audittrace)
While @count >=1
begin
set @message = (select 'SQL Security Enhanced Auditing: SPID =' + spid +' ,
Database: ' + [Database] +
' ,Loginame: ' + loginame + ' ,hostname: '+ hostname +' , Program Name: ' +
program_name from #audittrace where traceid = @count)
set @count = @count-1
RAISERROR (@message, 16, 1) with log
end
최근 덧글