How do I stop sa_set_http_option 'SessionID' from causing second remote server connections?

[Breck_Carter]

This is a rewrite/repost of How do I debug FORWARD TO creating a persistent idle connection?.

---

Recap: After a recent patch was applied to Foxhound 4, it began creating a second idle persistent connection to each remote server target database after the Monitor Options page is used on that target.

Testing determined that the first FORWARD TO operation in the Monitor Options code creates the second idle persistent connection.

Further testing narrowed down the original cause to this single statement added by the patch:

         CALL sa_set_http_option ( 'SessionID', @new_session_id ); 

In particular, when that single statement IS EXECUTED, much later FORWARD TO operations DO CREATE second connections to each remote server (Foxhound target database).

Whan that statement IS BYPASSED, the much later FORWARD TO operations DO NOT create second connections to each remote server.

---

Here is a code excerpt showing the context; this code is (optionally) executed once when the user wants to change from "read only" mode to "updatable by administrator" mode, and is part of the new "Foxhound Administrator Login" feature implemented by the patch:

CREATE SERVICE rroad_administrator_login TYPE 'RAW' 
   AUTHORIZATION OFF USER DBA_menu
   AS CALL rroad_administrator_login();

CREATE PROCEDURE rroad_administrator_login()
   RESULT ( html_output LONG VARCHAR )
BEGIN
...
DECLARE @new_session_id                         VARCHAR ( 128 );
...
         SET @new_session_id = STRING ( 'foxhound4_session_', SECONDS ( CURRENT TIMESTAMP ), DATEPART ( MILLISECOND, CURRENT TIMESTAMP ) );
         CALL sa_set_http_option ( 'SessionID', @new_session_id ); 

---

FYI since 2013 Foxhound 4 has executed with SET OPTION PUBLIC.extern_login_credentials option = 'Login_user' which answers one question raised by this forum post Why am I getting extern login errors and/or more than one connection to a remote server with SA 16?

Waiving a dead chicken over the keyboard (setting extern_login_credentials option = 'Effective_user') worked as might be expected; i.e., not at all 🙂

---

THIS IS NOT A BIG DEAL... I can live with a goofy dead connection, I can wave my arms and tell customers "it's a cost of extra security"... except I need a way to identify the goofy dead connection apart from a real user error.

In particular, the user now sees this banner message:

Duplicate Foxhound sampling sessions. There is more than one Foxhound connection to this target database.

I'd rather not have to, but... as they say, "any and all suggestions are welcome" 🙂

[VolkerBarth]

It you explicitly drop the second remote connection, is it created again by further FORWARD statements?

[Breck_Carter]

Yes it is.

[Breck_Carter]

Furthermore, if the Foxhound user does a Logout, which executes CALL sa_set_http_option ( 'SessionID', NULL ), the goofy connection disappears and doesn't return if a FORWARD TO is executed.

[Breck_Carter]

Sadly, I am having trouble telling a goofy connection apart from a real duplicate connection caused by separate Foxhound sampling session that just happens to connect to the same target database (say, by a DSN versus a DSN-less connection string).

I.e., to Foxhound Sampling Session 1, the connection from Foxhound Session 2 looks like an idle connection, just like the goofy connection... and vice versa... on Foxhound Session 2 the connection from Foxhound Session 1 looks like an idle connection.

To make things worse, if one of these Foxhound Sampling Sessions is "logged in" as Administrator (there can only be one Administrator at a time), there are now THREE connections to the target... from the view point of either Session 1 or Session 2, TWO of those connections look idle.

From viewpoint of Foxhound Sampling Session 1...

sample_set_number,connection_number,Name,LoginTime,CurrentProcedure,LastReqTime,TransactionStartTime,current_req_status
34384,1,'Foxhound-Monitor-12452',2019-11-03 14:55:49.929000,rroad_connection_properties,2019-11-03 16:09:20.642000,2019-11-03 16:09:20.640000,'Executing'
34384,12,'Foxhound-Monitor-12452',2019-11-03 15:33:39.507000,,2019-11-03 16:09:11.448000,1900-01-01,'Idle'
34384,17,'Foxhound-Monitor-12452',2019-11-03 16:09:14.869000,,2019-11-03 16:09:14.885000,1900-01-01,'Idle'

From viewpoint of Foxhound Sampling Session 2...

sample_set_number,connection_number,Name,LoginTime,CurrentProcedure,LastReqTime,TransactionStartTime,current_req_status
34386,1,'Foxhound-Monitor-12452',2019-11-03 14:55:49.929000,,2019-11-03 16:09:20.689000,1900-01-01,'Idle'
34386,12,'Foxhound-Monitor-12452',2019-11-03 15:33:39.507000,rroad_connection_properties,2019-11-03 16:09:21.314000,2019-11-03 16:09:21.313000,'Executing'
34386,17,'Foxhound-Monitor-12452',2019-11-03 16:09:14.869000,,2019-11-03 16:09:14.885000,1900-01-01,'Idle'

[Breck_Carter]

I am this close to suppressing the "Duplicate Foxhound sampling sessions" message altogether, if the new Foxhound Administrator Authentication feature is enabled.

. . . just ignore the goofy connections, they're not breaking anything 🙂

[Breck_Carter]

...I couldn't let it go, I had to keep looking 🙂

[Breck_Carter]

This is not an answer to the question, but it does suggest a workaround...

The goofy connection to the target database is well and truly idle, and that's how it can be identified...

   IF DATEDIFF ( MILLISECOND, LoginTime, LastReqTime ) <= 1000 THEN 'Goofy!'

Here is a query of the Foxhound database when the following conditions are in effect:

There are two Foxhound sampling sessions (7 and 😎 connected to the same target database ddd17.db, one via DSN and the other via DSN-less connection string.

Each sampling session "sees" three connections on the target database: It's own real connection to the target database, the other session's real connection to the target database, and the goofy connection.

That means Foxhound is recording six sets of data; i.e., 3 connections times 2 sessions.

Here's a snapshot...

Session Target       Conn # Conn Name              current_req_status    msec  What am I? 
------- ------------ ------ ---------------------- ------------------- ------- ---------- 
      7 ddd17             1 Foxhound-Monitor-19744 Executing           3503587    Real!   
      7 ddd17             2 Foxhound-Monitor-19744 Idle                3503619    Real!   
      7 ddd17            23 Foxhound-Monitor-19744 Idle                      7    Goofy!  
      8 ddd17 string      1 Foxhound-Monitor-19744 Waiting for thread  3503571    Real!   
      8 ddd17 string      2 Foxhound-Monitor-19744 Executing           3503603    Real!   
      8 ddd17 string     23 Foxhound-Monitor-19744 Idle                      7    Goofy!  

In this case, there is a legitimate reason to display the "Duplicate Foxhound sampling sessions" message (two sessions for the same physical database is a waste of resources)...

...but it's NOT because of the goofy connection.

Yay!