Driver OCSP Connection Error with SnowFlake on New Relic

[pwunlee]

Description

SnowFlake integration with New Relic managed to obtain data queried from SnowFlake tables and views but produces an OCSP error when data sent to New Relic, which makes them unusable as they are in JSON format in a single column

Steps to Reproduce

1) Install snowflakeintegration-win.exe in integrations.d>script folder 2) Entered obfuscated credentials in config.yml in integrations.d>script folder 3) Created snowflake.sql file which contains query to obtain data from SnowFlake tables/views in integrations.d>query (with dummy statement e.g. select * from snowflake.account_usage.tables) 4) Configured flex-Snowflake.yml in integrations.d to run execution for snowflakeintegration-win.exe on snowflake.sql file in (3) with a snowflake.bat file 5) Local testing of Flex job by running the following command in command prompt

• cd C:\ProgramFiles\New Relic\newrelic-infra\newrelic-integrations
• nri-flex -config_path C:\ProgramFiles\New Relic\newrelic-infra\integrations.d\flex-Snowflake.yml>C:\Users\Default\Desktop\testSnowFlakeError.log --pretty --verbose

Expected Behavior

Prior to few days ago, expected New Relic output when querying table as below

Relevant Logs / Console output

Currently, the attributes are unusable as they are in JSON, with additional column stating the error message

This is despite the fact that from local testing of the Flex job from step (5), it is showing the expected output

Error message from New Relic column as per below: "\"message\":\"[8:08:42.842 AM]: WARNING!!! using fail-open to connect. Driver is connecting to an HTTPS endpoint without OCSP based Certificated Revocation checking as it could not obtain a valid OCSP Response to use from the CA OCSP responder. Details:\",\"level\":\"INFO\"}

Your Environment

Windows Virtual Machine on Azure

Additional context

[matewilk]

Hello @pwunlee

Thank you for raising the issue.

The integration uses snowflake-sdk driver to connect to Snowflake, by default - and you cannot change it in the integration configuration as we speak- Snowflake supports a fail-open approach.

From Snowflake documentation:

Snowflake supports a fail-open approach by default in terms of evaluating the OCSP CA response. The fail-open approach has the following characteristics:

• A response indicating a revoked certificate results in a failed connection.

• A response with any other certificate errors or statuses allows the connection to occur, but denotes the message in the logs at the WARNING level with the relevant details in JSON format.

If the integration couldn’t connect to snowflake, you would be getting a log saying:

Unable to connect to snowflake: {exact error message here}

From your description it seems like you fall into the second case from Snowflake docs which is:

A response with any other certificate errors or statuses allows the connection to occur, but denotes the message in the logs at the WARNING level with the relevant details in JSON format.

Are you able to make sure your Snowflake certificate is up to date and all the configuration settings including authentication were followed?

[pwunlee]

Hi @matewilk , thanks for the response

Yes. I have verified that the authentication steps were followed correctly as this integration was configured 6 months ago and it was working fine until a week ago. No changes have been made on the configuration since then.

I have also verified that the SnowFlake certificate is still 1 year from expiring.

[matewilk]

Thanks for the response @pwunlee

Have you change anything in the query you're sending to Snowflake?

The integration, expects a response from Snowflake to be a set of rows, where each row has a set of keys (column name) and values.

By the look of it, it seems like the query you're sending returns an array of rows instead of rows by themselves.

Can you validate your queries in the Snowflake UI and post the obscured results here so we can take a look at the structure of the response?

[matewilk]

Hey @pwunlee

I dug a little bit dipper into the problem, it seems to me that Snowflake changes the behaviour of the snowflake-sdk depending whether there is the fail-open warning or not.

Unfortunately I'm unable to replicate the issue myself, and also, I can't find the answer in the official Snowflake docs, so I've asked whether this might be the case on StackOverflow

Hopefully someone from Snowflake can answer my question

Are you able to replicate this issue on your dev env and log the rows response content from here?

[pwunlee]

Hi @matewilk ,

The query was unchanged and as I mentioned in my first comment, it was correctly exhibiting the correct behavior where the query returns the set of keys as column name and individual rows prior to OCSP message

On Snowflake, the query I executed returns the following, which are the exact same columns

When the OCSP error appeared, the behavior is as you have described where the rows themselves are in an array of rows, while the OCSP error message populate another column.

While I am aligning with the network team and SnowFlake support, I do suspect there may be a problem with this newrelic-snowflake integration (Windows version) which may have caused this. Kindly assist me in verifying this. Thank you.

[pwunlee]

Hi @matewilk ,

Just an update, this issue is resolved by downgrading the Infrastructure Agent from version 1.20.5 to 1.20.2 as we are able to see the appropriate response body with no OCSP error message. As such there may be some additions/changes between versions 1.20.2 and 1.20.5 that is causing the OCSP check issue, or conflicting with the current SnowFlake integration? Kindly be informed for investigation on how this problem arise from the later versions of Infrastructure Agents.

From my understanding, if there are any other issues with similar warning messages, the problem with the nested array may still arise? In the meantime, I have forwarded this issue to the SnowFlake support contact relating to the snowflake-sdk response body for your integration enhancements. As for the testing, please find attached the output from the Flex integration (note that we are using the snowflake.js packaged in an exe file developed by @danifitz due to previous incompatibility on running the snowflake.js itself on our Flex)

Currently, this dev server is running on infra agent version 1.20.4.

Hope this helps in investigating the issue further. Thank you for your time.