3

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

  • Look into the possibility of creating a synonym to allow the application to access and indirect object. That ought to allow either end to change without modifying either the application or the database. – CoveGeek Feb 16 '16 at 1:29
  • (Next time, please focus on what you're trying to actually do, rather than hide that behind how you've decided it needs to be done. My answer is now completely useless because that wasn't even really the question you wanted to ask.) – Aaron Bertrand Feb 16 '16 at 12:07
  • @AaronBertrand Apologies, I wanted to be as concise as possible, didn't mean to hide anything. – ManOnAMission Feb 16 '16 at 12:45
4

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
3

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

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.