SolvedPrivate

SQL Server Audit -- filter for only 'sa'

Posted on 2016-07-20
7
40 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now