SQL Server – Logging Application Name for Pooled Connections

connection-poolingsql serversql-server-2008trigger

I am wondering if I can change the application name of a connection in SQL Server itself, e.g. in the logon trigger? Is that possible or is the application name "read only"?

Edit to explain the motivation: I would like to mark all audited connections in my logon trigger, so that I write only one record per connection through its many logon events. I thought I could modify some info, e.g. the app name, of a connection so that next time around the logon trigger sees that it can ignore this connection attempt because it already logged the info around this connection (like user, host etc.).

Best Answer

No, the connection properties should be immutable. However, even if you could edit them, there is still an easier (and better) way to correlate Logon Events to the same Connection: the connect_time field in sys.dm_exec_connections. You just look up that field based on the session_id (which correlates to SPID in the XML returned from the EVENTDATA() function). If your audit table does not already have fields for SessionID and ConnectionTime you will need to add one or both of them. And it probably would be wise to create a Non-Clustered Index on (ConnectionTime DESC, SessionID). Finally, in your Logon Trigger after you do the lookup for @ConnectionTime, do an IF NOT EXISTS to check your audit table for that combination of @ConnectionTime and @SessionID and only INSERT if not found.

The following is a basic example of how to capture this info, but does not include the Index or the IF NOT EXISTS logic:

Create the audit Table

--DROP TABLE Test.dbo.LogonEvent;
CREATE TABLE Test.dbo.LogonEvent
(
    LogonEventID INT NOT NULL IDENTITY(-2140000000, 1),
    SessionID INT NOT NULL,
    ConnectionTime DATETIME NOT NULL,
    EventInfo XML NULL
) ON [Tables];
GO

Create the Logon Trigger

ALTER TRIGGER CaptureLogonEvents
ON ALL SERVER
FOR LOGON  
AS
SET NOCOUNT ON;

DECLARE @SessionID INT = EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]', 'INT');

INSERT INTO [Test].[dbo].[LogonEvent] (SessionID, ConnectionTime, EventInfo)
    SELECT  @SessionID AS [SessionID],
            sdec.connect_time AS [ConnectionTime],
            EVENTDATA() AS [EventInfo]
    FROM        sys.dm_exec_connections sdec
    WHERE   sdec.session_id = @SessionID;
GO

Related Solutions

Sql-server – What would cause extra records to be inserted during a INSERT trigger

First, you don't need the IF EXISTS part in your trigger code. You have the exact same code in the second part so if there are 0 rows produced, you'll be inserting 0 rows anyway:

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS 
BEGIN
    INSERT INTO Foo(ID, Etc, Flag)              
    SELECT a.ID, a.Etc, 1 FROM Inserted a
        LEFT JOIN Foo b ON b.Flag = 1 AND a.ID = b.ID
        WHERE b.Flag IS NULL AND a.Flag = 0
END ; -- trigger

which can also be written as:

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS 
BEGIN
    INSERT INTO Foo(ID, Etc, Flag)              
    SELECT a.ID, a.Etc, 1 
        FROM Inserted a
        WHERE a.Flag = 0
          AND NOT EXISTS
              ( SELECT *
                FROM Foo b 
                WHERE b.Flag = 1 AND a.ID = b.ID
              )
END ; -- trigger

So, you want any insert on the table with Flag=0 to also trigger another insert, with same data but Flag=1 (unless there is already a row in Foo with that ID and Flag=1.

Second, and what the actual problem is about, Inserted is a special table with all the (just) "inserted" rows, not the full table you inserted into. It obviously has less (than or equal) rows as Foo after the insert. So a join to it will not give same results as a join to Foo.

The trigger that produces the multiple unwanted rows had Inserted b instead of Foo b so it does this:

For any insert on the table with Flag=0 it also triggered another insert, with same data but Flag=1 (unless this same insert had such a row inserted).

You can read more about the special Inserted and Deleted tables at the MSDN:
How to use the inserted and deleted Tables

Sql-server – problem running xp_cmdshell when Application Name set in connection string

the database(s) associated with the application should be trustworthy in order to access the machine's command line.

Try

select name,is_trustworthy_on from sys.databases;

if your target database is not trustworthy then:

alter database db_name set trustworthy on;

but beware the compromise to security

Best Answer

Related Solutions

Sql-server – What would cause extra records to be inserted during a INSERT trigger

Sql-server – problem running xp_cmdshell when Application Name set in connection string

Related Question