SolvedPrivate

SQL Server Audit -- filter for only 'sa'

Posted on 2016-07-20
7
55 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

729 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