Error "The CustomQuery: 'QueryName' was not allowed to execute because it is not trusted" in TIBCO Spotfire

Product: TIBCO Spotfire Analyst

Versions: All

Summary:

When attempting to execute a custom query in a TIBCO Spotfire Data Connector, opening it through the library or loading a report containing it, you may run into the following message: "The CustomQuery: 'QueryName' was not allowed to execute because it is not trusted."

Details:

Description:
When attempting to execute a custom query in a TIBCO Spotfire Data Connector, opening it through the library or loading a report containing it, you may run into the following message: "The CustomQuery: 'QueryName' was not allowed to execute because it is not trusted."

Where "QueryName" is a place holder for the name of the custom query you are trying to execute. This means that the software does not recognize this as a query that has been explicitly trusted. It therefore hinders it from executing to ensure that, for example, no unwanted SQL injection is being performed. There are two different levels of trust which affect this.

Symptoms:
Unable to import data using a custom query, either saved directly in the library or embedded in a report, followed by a prompt with an error surrounding the custom query not being trusted. Looking more into the error details, you can see that it resembles the following:

Error message: Unable to import data. See the details for more information.

ImportException at Spotfire.Dxp.Data:

An internal connector error has occurred.

Internal error:

The CustomQuery: 'QueryName' was not allowed to execute because it is not trusted.

Data connection:

DataConnectionName

Server:

server.address

Database:

DatabaseName (HRESULT: 80131500)

Stack Trace:

   at Spotfire.Dxp.Data.Producers.ConnectorImportColumnProducer.GetColumnsAndProperties()

   at Spotfire.Dxp.Data.Persistence.DataItem.PerformUpdate(DataColumnProducer producer, Boolean forceUpdate)

   at Spotfire.Dxp.Data.Persistence.DataItem.Update(DataColumnProducer producer, Boolean forceUpdate)

   at Spotfire.Dxp.Data.Persistence.DataPool.GetDerivedData(DataColumnProducer producer, Boolean loadingPre50CalculatedColumnProducer)

   at Spotfire.Dxp.Data.Persistence.DataPool.GetData(DataColumnProducer producer, Boolean loadingPre50CalculatedColumnProducer)

   ...​

Cause:
Trust for custom queries works much like the trust for IronPython scripts in the TIBCO Spotfire environment - it is a two step verification process that begins with looking at the trust for the custom query inside the library.

When a custom query author that has the required licenses saves a custom query in the library, either on it's own or as part of a report, a hash value of the custom query is saved as a trust inside the library. And as long as this hash corresponds to the custom query content, the custom query is seen as a trusted query that is safe to execute.

Now if someone that does not have the required licenses saves a custom query, this hash is not created and the query is seen as unsafe in the library. This is also true if someone without the required licenses edits the custom query content inside the library or report, as it will no longer correspond to the hash value saved before. That is when you will see the error discussed in this article.

Resolution:

There are two ways to resolve the issue:
 

Option 1 - Trust the custom query locally for this computer and Windows user only (All versions)

Each installation of TIBCO Spotfire (Analyst / Desktop / Professional) client has a local trust cache available for the Windows users executing the client. This cache works in the same manner as the trust hash in the library, but is saved locally on the computer instead of in the library database. To trust the custom query locally, follow the below steps:

If it is saved as part of a report

• Open the report in the TIBCO Spotfire (Analyst / Desktop / Professional) client.
• Go to the top menu and choose Data -> Data Connection properties (if using TIBCO Spotfire 10.0 and higher), or Edit -> Data Connection properties (if using TIBCO Spotfire 7.14 and lower)
• Highlight the connection and press the "Settings" button.
• On the "General" tab, click the "Edit" button.
• Highlight your custom query and choose "Edit custom query" from the menu right above it.
• Press the "Verify" button, then the "OK" button.
• Press OK all the way until the Data Connection Properties dialogue window is closed.

If the connection is saved in the library

• Start the TIBCO Spotfire (Analyst / Desktop / Professional) client.
• Go to the top menu and choose Data -> Manage Data Connections (if using TIBCO Spotfire 10.0 and higher), or Edit -> Manage Data Connections (if using TIBCO Spotfire 7.14 and lower)
• Find your connection, mark it and press the "Edit" button at the top of the dialogue.
• On the "General" tab, click the "Edit" button.
• Highlight your custom query and choose "Edit custom query" from the menu right above it.
• Press the "Verify" button, then the "OK" button.
• Then press OK all the way until Data Connection Properties dialogue window is closed.

The custom query should now be trusted in your local trust cache.

Option 2 - Trust the custom query in the library database for all users using the query or report it is embedded in.

From TIBCO Spotfire 7.0 and higher

• Ensure that your user is a member of the "Custom Query Author" group. It is not enough to just have the license. Note: Groups and licenses can be managed using the Administration Manager tool.  
• Follow the steps to trust the custom query in the local cache.
• Save the custom query or the report containing the custom query into the library.

For TIBCO Spotfire 6.5 and lower

In TIBCO Spotfire 6.5 and prior, the steps provided for 7.0 and onward are not sufficient. To ensure that the custom query is trusted in the library, it needs to be saved as part of a report. If you want it to be trusted as it's own library item, you need to:

• Ensure that your user is a member of the "Custom Query Author" group. It is not enough to just have the license. Note: Groups and licenses can be managed using the Administration Manager tool
• First save it as an untrusted connection in the library.
• After having saved it as an untrusted connection in the library, follow the steps to trust it locally.
• Having followed those steps, use the custom query connection to create a new report.
• Save the newly created report to the library.

This will then trust both the custom query in the report but also the custom query saved in the library prior to using it in the report.