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 #audittrace

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

drop 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.