11

I'm currently using Change Data Capture (CDC) to track data changes, and I wish to track the host name and IP address of the client submitting the query that made the changes. If there are 5 different clients logged in via the same user name, one faces the conundrum of tracking which of the 5 fired the query. Other specious solutions that I found include altering the CDC table with following command:

ALTER TABLE cdc.schema_table_CT 
ADD HostName nvarchar(50) NULL DEFAULT(HOST_NAME())

However, this returns the host-name of the server on which the query was fired, and not the host-name of the client that fires the query.

Is there a way around this problem? Something that would help to log the host-name or the IP address (or some other kind of unique identity) of the client. I do not want to use triggers, as it slows down the system, also CDC generates system tables, so having a trigger on that is apparently not possible.

4

I am not sure about CDC, but if the login has view server state permission you can use DMVs to get some information.

This is given in Books Online here. I changed the query to add columns which would give you the IP address:

SELECT 
    c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme,
    s.host_name, s.program_name, s.client_interface_name,
    c.local_net_address, c.client_net_address, s.login_name, s.nt_domain, 
    s.nt_user_name, s.original_login_name, c.connect_time, s.login_time 
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = SPID;  --session ID you want to track
4

When you say, "without using triggers", do you mean any triggers or just row-by-row triggers on tables?

I ask because you may be able to get what you want with judicious use of the CONTEXT_INFO() function, but you would need to ensure that SET CONTEXT_INFO was called correctly before your operations take place.

One place to do that might be a server-level logon trigger (i.e. not a database/object-level trigger), like so:

USE master
GO
CREATE TRIGGER tr_audit_login
ON ALL SERVER 
WITH EXECUTE AS 'sa'
AFTER LOGON
AS BEGIN
    BEGIN TRY

        DECLARE @eventdata XML = EVENTDATA();

        IF @eventdata IS NOT NULL BEGIN
            DECLARE @spid INT;
            DECLARE @client_host VARCHAR(64);
            SET @client_host    = @eventdata.value('(/EVENT_INSTANCE/ClientHost)[1]',   'VARCHAR(64)');
            SET @spid           = @eventdata.value('(/EVENT_INSTANCE/SPID)[1]',         'INT');

            -- pack the required data into the context data binary
            -- (spid is just an example of packing multiple data items in a single field: you would probably use @@SPID at the point of use, instead)
            DECLARE @context_data VARBINARY(128);
            SET @context_data = CONVERT(VARBINARY(4),  @spid)
                              + CONVERT(VARBINARY(64), @client_host);

            -- persist the spid and host into session-level memory
            SET CONTEXT_INFO @context_data;             
        END

    END TRY
    BEGIN CATCH
        /* do better error handling here...
         * logon trigger can lock all users out of server, so i am just swallowing everything
         */
        DECLARE @msg NVARCHAR(4000) = ERROR_MESSAGE();
        RAISERROR('%s', 10, 1, @msg) WITH LOG;
    END CATCH
END

You could then add the default constraint to your table, to store context (for speed of insert):

ALTER TABLE cdc.schema_table_CT 
ADD ContextInfo varbinary(128) NULL DEFAULT(CONTEXT_INFO())

Once you have that, you could query that ContextInfo column with a bit of slice-and-dice:

SELECT *
    ,spid = CONVERT(INT, SUBSTRING(ContextInfo, 1, 4))
    ,client = CONVERT(VARCHAR(64), SUBSTRING(ContextInfo, 5, 64))
FROM cdc.schema_table_CT

Technically, you could do that SUBSTRING and CONVERT stuff as part of your default constraint, and just store the client IP there, but it may be quicker to store the whole context there (as it is done on every INSERT), and only extract the values in a SELECT when you need them.

I might be inclined to wrap all my SUBSTRING and CONVERT calls in a single-row inline table-valued function, which I would CROSS APPLY when necessary. That keeps the unpacking logic in one place:

CREATE FUNCTION fn_context (
    @context_info VARBINARY(128)
)
RETURNS TABLE
AS RETURN (
    SELECT
         spid = CONVERT(INT, SUBSTRING(@context_info, 1, 4))
        ,client = CONVERT(VARCHAR(64), SUBSTRING(@context_info, 5, 64))
)
GO

SELECT * 
FROM cdc.schema_table_CT s
CROSS APPLY dbo.fn_context(s.ContextInfo) c

Note that CONTEXT_INFO is only a 128-byte VARBINARY. If you need more data than you can fit in 128 bytes, I would create a table to hold all that data, insert as row for that 'session' into the table in the logon trigger and set CONTEXT_INFO to the surrogate key value of that table

You should also note that, because it is only a default constraint, it is trivial for a suitably-privileged user to overwrite that context data in the at-rest table. Of course, the same is true for all the other columns in 'audit'-style tables, too.

It would be nice if it could be a persisted computed column, rather than a default, but the CONTEXT_INFO() function is non-deterministic, so it is a no-go (you might be able to use some FUNCTION trickery around a VIEW, but I wouldn't).

It is also trivial for that user with sufficient access to call SET CONTEXT_INFO themselves and mess up your day (e.g. with fake values, or specially-crafted stored injection), so treat the contents with suspicion and care, encode it prior to display, and handle exceptions well.

As for hostname, I think the ClientHost element of EVENTDATA() gives you the IP address (or a <local machine> indicator). While you technically could use CLR to do reverse-DNS lookups back to hostname, these tend to be too slow to do for every INSERT, so I would recommend not to do so.

If you have to have a hostname, you might want to use a SQL Agent job to periodically populate a separate table with the current leases from your local DHCP server or DNS zone file, as an out-of-band process, and LEFT JOIN to that in future queries (or wrap in a scalar FUNCTION to provide a value to a default constraint, for point-in-time).

Again, you should note that, if the application has any kind of public-facing component, IP addresses and hostnames are unreliable (e.g. due to NAT). Even if it is not public-facing, there is a certain time-based component to most IP/hostname maps, that you may need to factor.

Finally, before implementing your login trigger, it might be worthwhile turning on your server's dedicated admin connection. If the login trigger breaks in any way, it can prevent all users logging in (including the sysadmin accounts):

USE master
GO
-- you may want to do this, so you have a back-out if the login trigger breaks login
EXEC sp_configure 'remote admin connections', 1 
GO
RECONFIGURE
GO

If you do get locked out, the DAC can be used to drop or disable the login trigger:

C:\> sqlcmd -S localhost -d master -A
1> DISABLE TRIGGER tr_audit_login ON ALL SERVER
2> GO
3

Please take a look at the connect bug: Below is the relevant snippet from it

This behavior is by design. CDC is designed to expose the following information about a change: updated columns, type of operation and transaction information. It has not been designed as an audit solution. It has been created to enable efficient Extract Transfer and Load solutions (ETL) through incremental data load which is key to reducing the overall ETL time. Its main goal is expose "what has changed" not who, when... For that I recommend SQL Audit feature.

As of now there is no plan to transform CDC in an Audit solution.

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.