4

I have been reading about SQL Server Audit, and so far I have assimilated that it tracks the command that has been executed, and some other additional information(SPID, object ID and stuff). But can this be used to uniquely identify the individual that did it, or some type of unique ID of the computer from which the command was executed, such as host name or IP address?

I just do not want to waste my time reading it if it can not uniquely identify the machine or the person executing the command.

5

With SQLAudit you cannot track HostName or IP address.

However, there's an alternative method for auditing, based on streaming extended events. I blogged about it earlier this year.

Basically, you treat locks acquired as indicators of the operations being performed on the tables (shared locks and schema stability locks for reads and exclusive locks for writes) and you capture these events with an extended events session. You will need to hook a script (or an executable) to that session using the streaming API and you can implement your own logic to filter, categorize and persist the events you're interested in.

Another possibility is capturing the "Audit Schema Object Access Event" events with a trace and streaming them to your own custom application. Here's a tutorial. In Extended Events the same event is not available, as it is reserved for the SQLAudit implementation.

  • It would be correct to add code from you blog into the article. A link may become obsolte or it may be moved in that case answer would still stand good. – Shanky Sep 2 '15 at 10:07
  • 1
    @Shanky hmm... it's a LOT of code and explanation. I don't think I can do that. I can do my best to keep it running and URLs unchanged instead. I understand that link-only answers are discouraged, but there's a line to draw at some point. I provided an overview of the technique and a link to the code: I think it's enough. – spaghettidba Sep 2 '15 at 10:17
  • This is sort of costly, creating an Event Session for every event, the production server will take a hit – Ritesh Bhakre Sep 4 '15 at 15:37
  • 1
    Actually the higher cost is on the side of the application that receives the events rather than on the event session itself. I tried it against a rather busy server and I can confirm my findings. – spaghettidba Sep 4 '15 at 15:50
  • @spaghettidba I have been experimenting with your Extended Events code, that employs the principal of tracking by locks acquired. However, i have some system jobs(CDC jobs) running that also get logged in this process. How do i filter them out and track only for users and not system? – Ritesh Bhakre Sep 17 '15 at 9:10
4

To be honest, the SQL Auditing features are pretty poor considering the functionality has been around since 2008. I cannot believe that the host name and application name are not captured as 'normal' fields. Anyway here is a solution that I came up with which basically retrieves the related login record, extracts its hostname (app name not available) and then updates the select records hostname with the data. It then removes all of the unwanted login records.

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'AuditLog')
   DROP TABLE dbo.AuditLog
GO

CREATE TABLE dbo.AuditLog(
    event_time DATETIME2(7) NOT NULL,
    sequence_number INT NOT NULL,
    action_id VARCHAR(4) NOT NULL DEFAULT '',
    succeeded BIT NOT NULL,
    permission_bitmask VARBINARY(16) NOT NULL,
    is_column_permission BIT NOT NULL,
    session_id SMALLINT NOT NULL,
    server_principal_id INT NOT NULL,
    database_principal_id INT NOT NULL,
    target_server_principal_id INT NOT NULL,
    target_database_principal_id INT NOT NULL,
    object_id INT NOT NULL,
    class_type VARCHAR(2) NULL,
    session_server_principal_name NVARCHAR(128) NULL,
    server_principal_name NVARCHAR(128) NULL,
    server_principal_sid VARBINARY(85) NULL,
    database_principal_name NVARCHAR(128) NULL,
    target_server_principal_name NVARCHAR(128) NULL,
    target_server_principal_sid VARBINARY(85) NULL,
    target_database_principal_name NVARCHAR(128) NULL,
    server_instance_name NVARCHAR(128) NULL,
    database_name NVARCHAR(128) NULL,
    schema_name NVARCHAR(128) NULL,
    object_name NVARCHAR(128) NULL,
    statement NVARCHAR(4000) NULL,
    additional_information XML NULL,
    file_name NVARCHAR(260) NOT NULL,
    audit_file_offset BIGINT NOT NULL,
    user_defined_event_id SMALLINT NOT NULL,
    user_defined_information NVARCHAR(4000) NULL,
    audit_schema_version INT NOT NULL,
    sequence_group_id VARBINARY(85) NOT NULL,
    transaction_id BIGINT NOT NULL,
    host_name NVARCHAR(255) NULL,
    related_login_event_time DATETIME2(7) NULL
    CONSTRAINT PKAuditLog PRIMARY KEY CLUSTERED (event_time, action_id, session_id, sequence_group_id)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

TRUNCATE TABLE AuditLog;

INSERT  dbo.AuditLog
        ( event_time ,
          sequence_number ,
          action_id ,
          succeeded ,
          permission_bitmask ,
          is_column_permission ,
          session_id ,
          server_principal_id ,
          database_principal_id ,
          target_server_principal_id ,
          target_database_principal_id ,
          object_id ,
          class_type ,
          session_server_principal_name ,
          server_principal_name ,
          server_principal_sid ,
          database_principal_name ,
          target_server_principal_name ,
          target_server_principal_sid ,
          target_database_principal_name ,
          server_instance_name ,
          database_name ,
          schema_name ,
          object_name ,
          statement ,
          additional_information ,
          file_name ,
          audit_file_offset ,
          user_defined_event_id ,
          user_defined_information ,
          audit_schema_version ,
          sequence_group_id ,
          transaction_id 
         )
        SELECT  fgaf.event_time ,
                fgaf.sequence_number ,
                fgaf.action_id ,
                fgaf.succeeded ,
                fgaf.permission_bitmask ,
                fgaf.is_column_permission ,
                fgaf.session_id ,
                fgaf.server_principal_id ,
                fgaf.database_principal_id ,
                fgaf.target_server_principal_id ,
                fgaf.target_database_principal_id ,
                fgaf.object_id ,
                fgaf.class_type ,
                fgaf.session_server_principal_name ,
                fgaf.server_principal_name ,
                fgaf.server_principal_sid ,
                fgaf.database_principal_name ,
                fgaf.target_server_principal_name ,
                fgaf.target_server_principal_sid ,
                fgaf.target_database_principal_name ,
                fgaf.server_instance_name ,
                fgaf.database_name ,
                fgaf.schema_name ,
                fgaf.object_name ,
                fgaf.statement ,
                fgaf.additional_information ,
                fgaf.file_name ,
                fgaf.audit_file_offset ,
                fgaf.user_defined_event_id ,
                fgaf.user_defined_information ,
                fgaf.audit_schema_version ,
                fgaf.sequence_group_id ,
                fgaf.transaction_id
        FROM    sys.fn_get_audit_file('c:\ESD\PMAudit*', DEFAULT, DEFAULT) AS fgaf
        WHERE   NOT EXISTS ( SELECT 1
                             FROM   dbo.AuditLog al
                             WHERE  al.event_time = fgaf.event_time
                                    AND al.action_id = fgaf.action_id
                                    AND al.session_id = fgaf.session_id
                                    AND al.sequence_group_id = fgaf.sequence_group_id )
                AND fgaf.action_id <> 'AUSC';
GO



CREATE PROCEDURE dbo.AuditLog_Insert
AS

CREATE TABLE #Logins
    (
      event_time DATETIME2(7) NOT NULL ,
      session_id SMALLINT NOT NULL ,
      server_principal_name NVARCHAR(128) NOT NULL ,
      sequence_group_id VARBINARY(85) NOT NULL, 
      host_name NVARCHAR(50) NULL ,
      session_seq BIGINT NOT NULL ,
      CONSTRAINT PKLogins PRIMARY KEY CLUSTERED (event_time, session_id, server_principal_name, sequence_group_id)
    )

;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data' AS ns)
INSERT #Logins
        ( event_time ,
          session_id ,
          server_principal_name ,
          sequence_group_id ,
          host_name ,
          session_seq --Required as its possible to get duplicate event_time + session_id + server_principle_name combinations
        )
SELECT   a.event_time ,
         a.session_id ,
         a.server_principal_name ,
         sequence_group_id ,
         t.c.value ('ns:address[1]', 'varchar(50)') AS host_name,
         ROW_NUMBER() OVER ( PARTITION BY a.event_time,
                                            a.session_id,
                                            a.server_principal_name, t.c.value ('ns:address[1]', 'varchar(50)')  ORDER BY a.event_time DESC ) AS session_seq
FROM     dbo.AuditLog a
OUTER APPLY a.additional_information.nodes('//ns:action_info') AS t(c)
WHERE a.action_id = 'LGIS'

--Update the Select records with the host_name from login detail records .. using max event_time for that session
UPDATE  al
SET     al.host_name = l.host_name,
        al.related_login_event_time = l.event_time
FROM    dbo.AuditLog AS al
        JOIN #Logins l ON l.session_id = al.session_id
                       AND l.server_principal_name = al.server_principal_name
                       AND l.event_time = ( SELECT MAX(l2.event_time)
                                            FROM   #Logins AS l2
                                            WHERE  l2.event_time < al.event_time
                                            AND    l2.session_id = al.session_id
                                            AND    l2.server_principal_name = al.server_principal_name)

WHERE   al.action_id = 'SL'
        AND l.session_seq = 1 --It is possible to have duplicates because of horrible way SSMS operates
        AND al.host_name IS NULL -- Dont update records that have already been populated


--Remove unwanted login records from the Audit Log ... leaving only those related to select records.  Otherwise we are left with many duplicated login records.                 ;
DELETE  al
FROM    AuditLog al
WHERE   al.action_id = 'LGIS'
        AND NOT EXISTS ( SELECT 1
                         FROM   dbo.AuditLog al1
                         WHERE  al1.action_id = 'SL'
                                AND al1.related_login_event_time = al.event_time )


GO
0

I haven't done it myself but as I understand it you can create a login/out audit specification (these events do pull hostname) and then tie that information to other audit results using the SPID and rundate / login/out date information.

If I have time I'll try to come back later with more specifics.

  • Nope, login/logout audit specification do not furnish you with the host name. I'm resorting to the Extended Events feature, it's doing the trick for me! – Ritesh Bhakre Sep 11 '15 at 8:25
-6

At that point I decided to use DDL triggers, which unlike the DML, have not a significant impact in terms of performances. First of all we have to understand which events intercept, using this query you can have a hierarchical view of available events:

WITH EventsReports (name, PARENT_TYPE, type, level, sort) AS
(
    SELECT CONVERT (varchar (255), type_name), PARENT_TYPE, type, 1, CONVERT (varchar (255), type_name)
    FROM sys.trigger_event_types
    PARENT_TYPE WHERE IS NULL
    UNION ALL
    SELECT CONVERT (varchar (255), REPLICATE ( '|', level) + e.type_name)
        e.parent_type, e.type, level + 1,
    CONVERT (varchar (255), RTRIM (sort) + '|' + e.type_name)
    FROM AS and sys.trigger_event_types
        INNER JOIN AS DirectReports d
        ON = e.parent_type d.type
)
SELECT PARENT_TYPE, type, name
FROM EventsReports
--where [name] like '% object%'
ORDER BY sort;

The event name is quite explicative, but in the dubt has a look here

So we decided to track these events: DDL_DATABASE_SECURITY_EVENTS, DDL_DATABASE_EVENTS, DDL_SERVER_SECURITY_EVENTS

Then I have built the DDL trigger.

First let's create a DB and the table that will store our events:

Create DataBase AdminDB;
go
use AdminDB;
go
CREATE TABLE dbo.DDL_Security_Audit_LOG  ( EventId int NOT NULL IDENTITY (1, 1) PRIMARY KEY, EventTime datetime , DbName nvarchar(100), EventType nvarchar(100) , UserName nvarchar(100) ,HostName nvarchar (100) , EventTSQL nvarchar(3000) , Data_XML xml);

Then our DDL Trigger:

Create TRIGGER [DDL_Security_Audit]
ON ALL SERVER
FOR DDL_DATABASE_SECURITY_EVENTS, DDL_DATABASE_EVENTS, DDL_SERVER_SECURITY_EVENTS
AS
BEGIN

Declare @ObjectName nvarchar(100) =  EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)')

Declare @EventType nvarchar(100) =  EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

Declare @DbName  nvarchar(100) = isnull( EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(1000)'),'Master')

Declare @Command nvarchar(1000) =  EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(1000)')

INSERT INTO AdminDB.dbo.DDL_Security_Audit_LOG
 VALUES ( GETDATE() ,  @DbName, @EventType, suser_sname() , HOST_NAME(), @Command, EVENTDATA()  )

END
GO

Then remember to create a JOB to purge old data (if you want to)

Declare @CutOffDate datetime =  DATEDIFF(d, -30, getdate())
Delete from AdminDB.dbo.DDL_Audit_Security_TABLE_LOG where EventTime &lt; @CutOffDate

Now you have all info you need to monitor your security events at Server and DataBase level.

Just notice in SQL Server there a security hole: no DDL events avalaible for disable Trigger action.

Have a look at this solution: http://ruggierolauria.blogspot.it/2016/02/sql-server-audit-security-events.html

  • 2
    The question asks about tracking DML using SQL Server Audit. Your answer is about tracking DDL using Extended Events i.e. completely unrelated – Mark Sinkinson Feb 10 '16 at 10:33
  • 1
    The question asks how to track IPs of users performing DML. This answer mentions nothing about DML or IPs. – ypercubeᵀᴹ Feb 10 '16 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.