How to create an auditing profile to track “users” in a custom system using a single SQL Server login

Applies to
ApexSQL Trigger

Summary
his article explains how you can determine exactly which user made a change on an audited database, even if all the users of the system access to SQL server via only one SQL user account. An example would be a web application that connects to the SQL Server via a connection string using a single SQL Server user. The system has its own custom system to manage users by user name and password. The modification, described in this article, will reconcile the custom “users” with the actual changes made to data in SQL Server

This example will employ a user defined SQL table that contains all users of this system, with their User ID, IP addresses, user name and passwords. ApexSQL Trigger will use this table to determine which users made the change, by linking obtained IP addresses with the IP addresses from the previously mentioned SQL table. In that way ApexSQL Trigger will be able to associate thee UserID and display this info in audit reports.

Description

For the purpose of this article, the table Users is created with columns which contains unique information for each user, UserID is used as auto-incrementing integer primary key.

Modify the auditing architecture

1. Navigate to location %LOCALAPPDATA%\Local\ApexSQL\ApexSQLTrigger

2. Make a copy of ApexSQL2053.audx file. For this article we named this copy ApexSQL2053_Modified.audx

3. Open this file in any text editor and navigate to “KEY4 nvarchar(500)” with search

4. Add new line after that line CUSTOM_USER int

   DATA_TYPE CHAR(1) NOT NULL DEFAULT 'A'
   	,KEY1 NVARCHAR(500)
   	,KEY2 NVARCHAR(500)
   	,KEY3 NVARCHAR(500)
   	,KEY4 NVARCHAR(500)
   	,CUSTOM_USER INT
   

5. Click find next and repeat same step. Add CUSTOM_USER int below the “KEY4 nvarchar(500)” line.

6. Navigate to “KEY4 as” using the search engine and add CUSTOM_USER as below the “KEY4 as ”Key 4”,” line. This ‘’UserID’’ will be name of the column in reports.

   END '' Action ''
   	,KEY1 AS '' KEY 1 ''
   	,KEY2 AS '' KEY 2 ''
   	,KEY3 AS '' KEY 3 ''
   	,KEY4 AS '' KEY 4 ''
   	,CUSTOM_USER AS '' UserID ''
   	,d.COL_NAME '' COLUMN NAME ''
   	,
   

7. Click the Find next button

8. Add CUSTOM_USER as ‘’UserID’’, below the KEY4 as ”Key 4” line

9. @userIP variable is initialized with obtained IP address of the PC which is connected to SQL Server, each time when trigger is triggered by some DLM or DDL changes. After completing previous steps navigate to “–@TABLE_NAME” and insert new lines in the way presented below:

   BEGIN{ % IncTabDepth % }
   
   DECLARE @IDENTITY_SAVE VARCHAR(50)
   	,@AUDIT_LOG_TRANSACTION_ID INT
   	,@PRIM_KEY NVARCHAR(4000)
   	,
   	--@TABLE_NAME				nvarchar(4000),
   	@ROWS_COUNT INT
   	,@userIP VARCHAR(48)
   
   SET NOCOUNT ON
   
   SELECT @userIP = CONVERT(VARCHAR(48), CONNECTIONPROPERTY('client_net_address'));
   
   --Set @TABLE_NAME = '{%Audit.Print Replace(TableFullName, "'", "''")%}'
   SELECT @ROWS_COUNT = count(*)
   FROM deleted
   
   SET @IDENTITY_SAVE = CAST(IsNull(@@IDENTITY, 1) AS VARCHAR(50)) @userIP VARCHAR(50)
   

   Quick tip: This step needs to be repeated 3 times if all insert, update delete trigger where selected

10. In same file navigate to “– Lookup” using find feature, and add following line above:

    UPDATE AUDIT_LOG_DATA
    SET AUDIT_LOG_DATA.CUSTOM_USER = (
    		SELECT UserID
    		FROM Users
    		WHERE @userIP = dbo.Users.IP_Adress
    		)
    WHERE AUDIT_LOG_TRANSACTION_ID = @AUDIT_LOG_TRANSACTION_ID
    

    This statement updates CUSTOM_USER column with UserID which IP address from Users table match with obtained IP address of the users’ PC.

11. Click find next and repeat same step. Add same line as above ”– Lookup” string.

    Quick tip: This step needs to be repeated 3 times if all insert, update delete trigger where selected

12. Go to standards reports

    

13. Click apply button.

When these steps are done, reports will have new UserID column that will contain usernames of the users who made changes on auditing tables:

FAQ’s

Q: Beside user ID, can I display usernames of the users who made changes in the reports?

A: Yes, you can achieve that using Lookup feature in ApexSQL Trigger. Lookups will allow you to display any field beside UserID from the Users table. To see detailed steps on how to configure UserName field as Lookup, follow this link

Q: Which versions of SQL Server support the functions used for obtaining the client IP address in this article?

A: SQL Server 2008 and newer versions support used functions from the article

Q: Which network protocol should be used for connecting to SQL Server, so that they can obtain their IP addresses?

A: All users must use TCP/IP protocol to connect to the SQL Server and you must enable this protocol on your SQL Server

Q: Can I obtain the IP address if I use Shared Memory protocol?

A: No, if you use Shared Memory, th CONNECTIONPROPERTY(‘client_net_address’) function will always return ‘<local machine>’ e

Q: Can I use some other query for obtaining the client IP?

A: Yes, with the query below you can obtain the client IP also:

SELECT client_net_address
FROM sys.dm_exec_connections
WHERE Session_id = @@SPID;

But your SQL user account will need permission VIEW SERVER STATE on the server to gain access to the sys.dm_exec_connections. Also, prior to V12 in Azure SQL Database, client_net_address always returns NULL

Q: Do I need any extra permission for my SQL user account to obtain client IP address from CONNECTIONPROPERTY(‘client_net_address’) system function?

A: You don’t need any extra permission to obtain client IP address from this function

See also:

• Get Client IP Address in SQL Server
• Set right permission to get connection ip for a user mapped to login
• CONNECTIONPROPERTY (Transact-SQL)
• Enable TCP/IP Network Protocol for SQL Server