Edit Application Name to Log Connection once for Logons on Pooled Connections

Question

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.).

Answer 1

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

Answer 2

You can spoof this in the application's connection string, or you can store override info elsewhere (like CONTEXT_INFO()) but, no, I don't believe there is any way to change what SQL Server shows in APP_NAME() (or the undocumented PROGRAM_NAME()) from inside SQL Server.

Now that you've explained what you're really trying to do...