sa Audit Trail
-
I need to audit the sa login and as such I carried out the following scenario.
I logged on to my XP domain account and then go into Query Analyser as 'sa'. I have switched on Profiler but when I look at the output it tells me the Hostname but it doesn't tell me who is logged on to the host. i.e The NTUserName and NTDomainName are both blank.
Can anyone please help as we need to know who may be using the sa account surreptitiously.
-
change the password without telling anyone but your manager. You will find out VERY quickly.
Seriously though.. I don't know of anything INTERNAL to SQL that will manage this. Possibly IMCEDA or InDepth have this capability....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
When you login as sa, thre is no NTUsername, that's when you log in using Windows authentication.
I agree with AJ, change the pwd and you'll find out, or someone will change it back
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Does this mean that I can't find out who is using the sa login thru any Microsoft product? Profiler can tell me what pc is using it, isn't there anything available to interrogate that pc to see who is logged on?
This all stems from SOX. Who should have what login and privileges and what they do with these privileges including members of the DBA team.
-
I had a situation where i needed to cycle through active connections to any particular database and kill the connection for scheduled maintenance weekend integrity checks. I found that dbo.sysprocesses had the info on active connections. It may not be exactly what you are looking for, but here is the code I use to clean up connections to a database. It should include info on sa activity. keep in mind that dbo.sysprocesses is a very dynamic table and can change many times in a second depending on database traffic.
Good Hunting!!!
--*****************************************
-- Declare database name to check
-- integrity of. Note, All indexes
-- will be repaired. All Active
-- connections to the database
-- must be halted to perform this action.
-- DBCC CheckDB will be called in
-- the next step of this job.
--*****************************************
declare
@database varchar(250),
@tablename varchar(250),
@insert varchar(8000)
select @database = 'siebel' --Insert Database Name here
select @tablename = @database + '.dbo.sysusers'
select @insert = 'insert into #temp
select a.[spid], b.name as dbname, c.name as username, a.cmd, a.program_name as program ,a.hostname
from sysprocesses a, sysdatabases b, ' + @tablename + ' c ' + 'where a.dbid = b.dbid
and a.uid = c.uid
and b.name = ''' + @database + ''''
create table #temp(
[spid] int,
dbname varchar(250),
username varchar(250),
cmd varchar(250),
program varchar(250),
hostname varchar(250))
exec (@insert)
while (select count(*)from #temp) <> 0
begin
Declare
@spid int,
@spidtxt varchar(100),
@username varchar(250),
@hostname varchar(250),
@program varchar(250),
@cmd varchar(250),
@date varchar(50),
@dbname varchar(250),
@kill varchar(250)
select @spid = (select top 1 (spid) from #temp order by spid desc)
select @spidtxt = convert (varchar(100),@spid)
select @username = (select upper(username) from #temp where spid = @spid)
select @hostname = (select (rtrim(hostname)) from #temp where spid = @spid)
select @program = (select (upper(rtrim(program))) from #temp where spid = @spid)
select @cmd = (select (rtrim(cmd)) from #temp where spid = @spid)
select @date =
(select (convert (varchar(4),datepart (year,getdate ())) +
case when len(convert (varchar(4),datepart (month,getdate ()))) = 1
then
(select '0' + convert (varchar(4),datepart (month,getdate ())))
else
(select convert (varchar(4),datepart (month,getdate ())))
end +
case when len(convert (varchar(4),datepart (day,getdate ()))) = 1
then
(select '0' + convert (varchar(4),datepart (day,getdate ())))
else
(select convert (varchar(4),datepart (day,getdate ())))
end +
case when len(convert (varchar(4),datepart (hh,getdate ()))) = 1
then
(select '0' + convert (varchar(4),datepart (hh,getdate ())))
else
(select convert (varchar(4),datepart (hh,getdate ())))
end +
case when len(convert (varchar(4),datepart (mi,getdate ()))) = 1
then
(select '0' + convert (varchar(4),datepart (mi,getdate ())))
else
(select convert (varchar(4),datepart (mi,getdate ())))
end
 
)select @dbname = (upper(@database))
select @kill = 'kill ' + @spidtxt
Raiserror (60000,19,1,@spidtxt,@username,@hostname,@program,@cmd,@date,@dbname ) With Log
exec (@kill)
delete from #temp where spid = @spid
end
drop table #temp
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000 -
If its always from the same machine, put a key-logger on it. That'd give you concrete proof.
Steve
-
Using the host name, can you identify where the machine is physically located? Is it a shared machine, or does it belong to one user? If you can physically visit the PC, maybe you can cross-reference the events in the Security Log in event viewer (on the host in question) with the sa login time in the SQL Server log.
I agree with the others, change the sa password immediately.
Also, I would check the built in server roles, particulary system_administrators and security_administrators, to make sure no one added a login id to those roles. If I hacked into a SQL Server with sa, I would probably create a new login and assign it to the system_administrators role so that if the sa password was later changed, I could still get in with admin rights.
-
You can check if any of your enterprise admins can help you figure out where the host is located ? Something we also do is we have alerts set up on successful sa logins and you can run a script which can kill the session on successfull sa login.(something i might be concerned in doing unless you have some exceptions in the script.)
-
This will cycle and look to see who is using SA, and what applicaiton they are using to connect with it....
/*
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.
*/
drop procedure sp_adhocTracking
GO
create procedure sp_adhocTracking (@StartSnapID int = 1,
@Documentation char(1) = 'N',
@Clear char(1) = 'N')
with encryption
as
declare @SnapID int,
@DomainName varchar(128),
@TimeStamp datetime,
@count int,
@message varchar(1000)
create table #tmp
(ServerRole sysname, MemberName sysname, MemberID varbinary(4000))
insert into #tmp exec sp_helpsrvrolemember 'sysadmin'
if SYSTEM_USER not in (select MemberName from #tmp)
begin
RAISERROR('YOU MUST BE LOGGED IN AS A SYSTEM ADMIN TO EXECUTE THIS PROCEDURE!!! ABORTING!!!', 16, 1)
RETURN
end
DROP TABLE #TMP
if @Clear = 'Y'
begin
truncate table master.[dbo].[AUDIT_TRACE]
RETURN
end
if @Documentation = 'Y'
begin
PRINT 'This procedure will log information on who was using unauthorized software against ' +
CHAR(13) + 'a given database server. The parameters are as follows: ' + CHAR(13) + CHAR(9) +
'@StartSnapID int - This parameter will report back to you all information from a given snapshot' +
CHAR(13) + CHAR(9) +
'@Clear char(1) - ''Y'' or ''N'' - Clear the AUDIT_TRACE table.' + CHAR(13) + CHAR(9) +
'@Documentation char(1) ''Y'' or ''N'' - Display this text.' + REPLICATE(CHAR(13), 2) +
'This procedure must be run as SA, and be aware that each time you run this, if you do not specify' +
CHAR(13) + 'a StartSnapID, then each recorded instance of unauthorized activity will be logged to SQL Server' +
CHAR(13) + 'error log, and the NT Event Log. If you set this up as a job, then it will also record it to the ' +
CHAR(13) + 'job history log.' + REPLICATE(CHAR(13), 2) + REPLICATE(CHAR(9), 2) +
'Email me with any questions at: Jake@Freedomproject.net'
RETURN
end
if NOT exists (select *
from master.dbo.sysobjects
where id = object_id(N'[dbo].[AUDIT_TRACE]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
raiserror('AUDIT_TRACE table does not exist. Now creating...', 16, 1)
create table master.[dbo].[AUDIT_TRACE]
( PK int IDENTITY(1,1) NOT NULL ,
TRACE_ID int NOT NULL ,
DATABASE_NAME varchar(128) NOT NULL ,
SPID varchar(455) NOT NULL ,
LOGIN_NAME varchar(128) NOT NULL ,
PROGRAM_NAME varchar(455) NOT NULL ,
HOSTNAME varchar(128) NULL,
HOSTUSERNAME varchar(128) NULL ,
LOGIN_TIME datetime NULL ,
TIME_STAMP datetime NOT NULL
)
set @SnapID = 1
end
else
begin
select @SnapID = max(TRACE_ID) + 1 from master.dbo.AUDIT_TRACE group by TRACE_ID
if @SnapID <= 0 set @SnapID = 1
end
INSERT INTO master.[dbo].[AUDIT_TRACE]
select @SnapID,
a.name ,
ltrim(rtrim(convert(varchar,b.spid))) ,
ltrim(rtrim(b.loginame)) ,
ltrim(rtrim(b.program_name)) ,
ltrim(rtrim(b.hostname)) ,
nt_username ,
b.login_time,
getdate()
from master.dbo.sysprocesses b (nolock) ,
master.dbo.sysdatabases A
where a.dbid = b.dbid
and ltrim(rtrim(b.loginame)) = 'sa'
-- and ltrim(rtrim(left(program_name,8))) in ('MS SQLEM', 'SQL Quer')
select *
from master.[dbo].[AUDIT_TRACE]
where TRACE_ID BETWEEN @StartSnapID
AND @SnapID
set @count = (select count(*)
from master.[dbo].[AUDIT_TRACE]
where TRACE_ID BETWEEN @StartSnapID
AND @SnapID)
While @count >=1
begin
set @message = (
select 'SQL Security Enhanced Auditing: ' + CHAR(13) +
'SPID =' + SPID + CHAR(13) +
'Database: ' + DATABASE_NAME + CHAR(13) +
'Loginame: ' + LOGIN_NAME + CHAR(13) +
'hostname: ' + HOSTNAME + CHAR(13) +
'Host Username: ' + HOSTUSERNAME + CHAR(13) +
'Program Name: ' + PROGRAM_NAME + CHAR(13) +
'Login Time: ' + CAST(LOGIN_TIME as varchar)
from master.dbo.AUDIT_TRACE where PK = @count)
set @count = @count-1
RAISERROR (@message, 16, 1) with log
end
GO
-
We know the Host Name (computer name) from sysprocesses. I wonder could we use WMI class Win32_LoggedOnUser to get this info. The problem is that the person running a script has to have domain admin rights to get WMI counters from another computer or admin rights on that computer.
I am not suggesting to run SQL Server on the Domain Admin. I think if it is only for Audit purposes, you may store results from Sysprocesses in some table like Audit_Trace suggested by Sim30305 and create a trigger that runs a job that will be executed with Domain Admin credentials to run a VBscript that will use WMI , return LoggedOn user from the other computer and update the table with his name.
Yelena
Regards,Yelena Varsha
-
Just tested this Win32_LoggedOnUser thing. Works well, shows me ALL sessions, not only interactive logon, but again, how you may be sure those people are using SA login from the computer interactively? It could be a terminal Services session, script job running or whatever. This is the part of the code. strComputer is a remote Computer Name, HostName from Sysprocesses for example, FileObject was defined outside of this part of code as a FileSystemObject text File
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
'Getting Logged On User
Set colCurrentUser = objWMIService.ExecQuery _
("Select * from Win32_LoggedOnUser")
For Each objUser in colCurrentUser
LoggedUser = objUser.Antecedent
FileObject.WriteLine "Logged On User " & LoggedUser & NewLine
Next
Yelena
Regards,Yelena Varsha
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply