Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
SolvedPrivate

SQL Server Audit -- filter for only 'sa'

Posted on 2016-07-20
7
Medium Priority
?
59 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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 ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

719 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