SolvedPrivate

SQL Server Audit -- filter for only 'sa'

Posted on 2016-07-20
7
49 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

860 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