Users may want to know why a database connection is reused after running a standard report, but after running a data mart report (or any report containing user-defined SQL) the connection is closed in Strategy 9.x.
To clarify this question a quick example is in order. Viewing a Strategy Intelligence Server 9.x's connection monitor shows the following:
Here, users can see that there are two connections using the database instance Statistics, one connection with the <Repository Connection> database instance, and a single connection to the warehouse, the Onimusha Warehouse database instance. All of these connections have a Status of Cached>. Now when a typical grid report is run, users can see what happens to the connection:
Here the single warehouse connection, connection ID 3, is used. The Status of the connection has been changed to Busy. Once the report finishes, the database connection monitor shows the following:
It can be seen that the same connection has gone back to a state of Cached. If another standard grid report was to be run, the database connection monitor would again show the state as Busy, then return back to Cached when the report was done.
However, the sequence of events when a data mart report is executed is slightly different. When the report is executed we can see the status as Busy. However, once the report is finished the connection is removed from the connection monitor.
In fact this behavior can be seen using any of the following report types:
This is working as designed. Strategy 9.x does not cache any connections which are used to run user-defined SQL; this is for both security and consistency. Since there is no checking on user defined SQL it is possible that the additional SQL statements have changed the database session in a manner which has lasting side-effects on the connection. This could then cause subsequent reports to behave in an unexpected manner. So to avoid these problems, Strategy 9.x drops any connection which was used to run user-defined SQL.
The following contrived scenario depicts what could happen if a malicious report is designed to circumvent implemented security features on a database through Strategy 9.x if the database connection is not dropped:
In this scenario a data mart report contains post SQL which is designed to change which database the connection points to. If the database connection is not dropped this state carries over. So when subsequent reports are run using this connection, they will be executed against Database B. If Database A contained publicly accessible data and Database B contained sensitive internal only data, this could pose a very large security risk.
For more information regarding connection cache settings see the following Technical Note:
KB5598 (KB5300-7X0-0135): What are the `Connection lifetime` and `Connection Idle Timeout` options under the Database Connection Caching section in Strategy Intelligence Server?
