SolvedPrivate

SQL Server Audit -- filter for only 'sa'

Posted on 2016-07-20
7
47 Views
1 Endorsement
Last Modified: 2016-07-25
I have created several Audits in the past, using the SQL Server Audit feature in v2008 forward, but never for auditing login activity.  All I need to do is audit who is coming in with the 'sa' account.  I do not care about sysadmin, I am explicitly looking for 'sa' logins.  I just want to know when it is being done, and from which hostname.

It is v2012.  Is there a way to use a filter in the specification, to only track the success/failures for the 'sa' login ?

CREATE SERVER AUDIT SPECIFICATION audit_spec_name
FOR SERVER AUDIT [auditname]
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP)
WITH (STATE=OFF)
GO
1
Comment
Question by:dbaSQL
  • 3
  • 3
7 Comments
 
LVL 8

Expert Comment

by:LajuanTaylor
ID: 41722056
@dbaSQL You can use the Windows Event Viewer and create a custom filter on the Application Event logs. In addition, the Application event logs can be saved to .csv and easily imported into Excel... I added a few screen shots from my workstation Windows Event viewer.
2016-07-20_1745_EventViewerSummary.png
2016-07-20_1734_FIlterCurrentLog.png
2016-07-20_1738_FilterCustomView.png
1
 
LVL 17

Author Comment

by:dbaSQL
ID: 41722084
Well, I am grateful for the suggestion, LajuanTaylor, but I need something a bit more dynamic than that.  I really like the idea of the SQL Server Audit, but I don't know if I can filter it only on a specific login.  Another idea is the Logon Trigger -- as much as I always try to avoid triggers, maybe the Logon Trigger is more manageable, as I am just looking at one event and one login.  I need to run this for a day, two tops, just to confirm if/when/where the 'sa' login is being used.  I don't think collecting the event logs and then loading to excel and sorting is the route I need to take.

Are you familiar with the Audit or the Logon Trigger?  Or any other suggestions?
0
 
LVL 8

Expert Comment

by:LajuanTaylor
ID: 41722169
@dbaSQL - What about using a SQL Profile that captures logins and stores them in a table. See screen shots from my local test on SQL Server 2012.
2016-07-20_2141_SQL_Profiler.png
2016-07-20_2138_SQL_Trace.png
2016-07-20_2202_SQL_DB.png
2016-07-20_2201_SQL_TableStructure.png
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 17

Author Comment

by:dbaSQL
ID: 41723381
No, I believe the profiler is far too invasive.  Yes, it can be tweaked to collect at a very fine level, but the environment that I must run it in is already very complex.  Very busy.  I don't want to add the overhead of the trace running behind the scenes throughout the whole duration of this effort.
0
 
LVL 8

Accepted Solution

by:
LajuanTaylor earned 500 total points
ID: 41725537
@dbaSQL -  I tested this proof of concept on my local dev. You can choose another DB besides the master.  I typically will use a separate database that only I can manage when dealing with security related stuff. Please see inline snippets. Note, I didn't use any rollback logic in the trigger because the objective isn't to block the 'sa' account from logging in. BTW, you can get creative with the IF logic in trigger to restrict server access to business hours only...

--- Create proof of concept auditing table
CREATE TABLE LogonAuditing
(
    SessionId int,
    LogonTime datetime,
    HostName varchar(50),
    ProgramName varchar(500),
    LoginName varchar(50),
    ClientHost varchar(50)
)

--- Create login trigger
CREATE TRIGGER [LogonAuditTrigger]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
 
BEGIN
DECLARE @LogonTriggerData xml,
@EventTime datetime,
@LoginName varchar(50),
@ClientHost varchar(50),
@LoginType varchar(50),
@HostName varchar(50),
@AppName varchar(500)
 
SET @LogonTriggerData = eventdata()
 
SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
SET @HostName = HOST_NAME()
SET @AppName = APP_NAME()
 
--- Add condition to log only succesful sa logins
IF     (      (ORIGINAL_LOGIN() = 'sa')              
       )
INSERT INTO master..LogonAuditing
(
SessionId,
LogonTime,
HostName,
ProgramName,
LoginName,
ClientHost
)
SELECT
@@spid,
@EventTime,
@HostName,
@AppName,
ORIGINAL_LOGIN(),
@ClientHost
 
END
 
GO
/***** Maintenance snippets
--- Remove login audit trigger
DROP TRIGGER LogonAuditTrigger ON ALL SERVER

--- Clear audit log table
TRUNCATE TABLE LogonAuditing
******/



/****** (Test audit results)
Remove seconds and use select distinct to filter out the 'sa' logins across minutes instead of seconds 
 ******/
SELECT DISTINCT CAST(CONVERT(CHAR(17), [LogonTime],113) AS datetime) AS [LogonTime]
      ,[HostName]
      ,[ProgramName]
      ,[LoginName]
      ,[ClientHost]
  FROM [master].[dbo].[LogonAuditing]

Open in new window

0
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 41728220
Thank you for the example, LajuanTaylor.  While I had hoped to avoid the trigger, I believe it will work, as it is only targeting the 'sa' login.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question