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

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

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. The query is restricted from execution in order to ensure that, for example, no unwanted SQL injection is being performed. There are two different levels of trust which affect this.

Symptoms:
Spotfire users may run into this issue while trying 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)
   ...

Cause:
In Spotfire environment, trust for custom queries works much like the trust for IronPython scripts - 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. 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 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 Spotfire (Analyst / Desktop / Professional) client.
• Go to the top menu and choose Data -> Data Connection properties.
• 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 Spotfire (Analyst / Desktop / Professional) client.
• Go to the top menu and choose Data -> Manage Data Connections.
• 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 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.
• Follow the steps to trust the custom query in the local cache (above).
• 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.
• 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.