Set up SAP BusinessObjects with Snowflake using JDBC

[former_member211289]

Hello All,

I have read below SAP Blog to set up SAP BusinessObjects with SNOWFLAKE using JDBC.

https://blogs.sap.com/2018/11/20/how-to-set-up-sap-businessobjects-with-snowflake-using-jdbc/comment...

Queries: Please provide your inputs on below queries

1.Domain in which BO server is hosted, should have proper handshake with the Snowflake regional server.

I read the attached Snowflake connectivity document:

https://docs.snowflake.net/manuals/user-guide/ecosystem-diagnose-connectivity-issues.html

How can we correlate ‘Domain in which BO server is hosted’ with ‘Snowflake regional server’?

2: Import the snowflake cert in the “cacert” file located on BO server and client using KeyTool and portecle-1.11 respectively.

I heard that first Export the Snowflake certificate on our local machine, and later Import that certificate using keytool in BO cacert file.

cacert location for windows client: <INSTALL_DIR>\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\sapjvm\jre\lib\security\

where do we get snowflake certificate?

Do we need to generate java key store first?

OR Can we import certificate in BO cacert file using below keytool?

keytool -importkeystore -srckeystore mypfxfile.pfx -srcstoretype pkcs12 -destkeystore clientcert.jks -deststoretype JKS

Please explain me with detail steps.

Thanks

Ram

[denis_konovalov]

I have fixed your tags.
Please select more careful next time.

You're attempting to connect to data source, that sort of information is in the Data Connectivity guide.

[Patrick_Perrier]

Hi. Now that it is officially supported with SAP BI 4.2 SP08, I managed to do this without certificates: https://blogs.sap.com/2020/03/12/snowflake-for-sap-businessobjects-4.2-sp08/

[former_member182521]

Did you configured the JDBC properly? Here is the entry from a working environment which I configured a month back.

Server
----------

<DataBase Active="Yes" Name="Snowflake JDBC Datasource">
<JDBCDriver>
<ClassPath>
<Path>E:\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\snowflake\snowflake-jdbc-3.6.24.jar </Path>
</ClassPath>
<Parameter Name="JDBC Class">$JDBCCLASS$</Parameter>
<Parameter Name="URL Format">$DATASOURCE$</Parameter>
</JDBCDriver>
<Parameter Name="Array Fetch Size">10</Parameter>
</DataBase>

Client
---------

<DataBase Active="Yes" Name="Snowflake JDBC Datasource">
<JDBCDriver>
<ClassPath>
<Path>C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\snowflake\snowflake-jdbc-3.6.0.jar </Path>
</ClassPath>
<Parameter Name="JDBC Class">$JDBCCLASS$</Parameter>
<Parameter Name="URL Format">$DATASOURCE$</Parameter>
</JDBCDriver>
<Parameter Name="Array Fetch Size">10</Parameter>
</DataBase>

Also make sure you imported the complete certificate chain imported by accessing the Web Portal something like below

https://Your Company.us-east-1.snowflakecomputing.com

Syntax Certificate Import - Desktop
---------------------------

C:\Program Files\Java\jre1.8.0_191\bin>keytool -importcert -trustcacerts -alias snowflake -import -file "C:\New folder\snowflake.cer" -keystore "C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\sapjvm\jre\lib\security\cacerts"


Syntax Certificate Import - Server
---------------------------

E:\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\sapjvm\jre\bin> keytool -importcert -trustcacerts -alias snowflakeRoot -import -file "E:\Snowflake\snowflake.cer" -keystore "E:\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\sapjvm\jre\lib\security\cacerts"

[former_member211289]

Dear Manikandan,

Sincere thanks for your information.

Now we understood that downloading JDBC driver and configuration in .sbo file, also from where we can download snowflake certificate and imported to server/client using syntax.

But we still have one query with Connectivity Pre-requisites:

Domain in which BO server is hosted, should have proper handshake with the Snowflake regional server.

What does it mean?

Found below links,

Refer Snowflake connectivity document: https://docs.snowflake.net/manuals/user-guide/ecosystem-diagnose-connectivity-issues.html Is it verifying communication is not blocked?

i.e., Retrieve the URL used by Snowflake for OCSP checks on our signed Snowflake certificate Online Certificate Status Protocol (e.g. http://ocsp.netsolssl.com)

and test the URL whether any network issues prevent the Snowflake client from accessing the URL.

For example, your firewall may be blocking access to the sites used by Snowflake.

The reason behind above query is Post passing all the connection information, user getting below error message:

https://<RemovedServername>.us-east-1.snowflakecomputing.com:443/session/v1/login-request?databaseNa... failed: CURLerror (curl_easy_perform() failed) - code=60 msg='Peer certificate cannot be authenticated with given CA certificates' osCode=2 osMsg='No such file or directory'.

We understood that this error message was due to Company's Firewall. Check whether our snowflake URL has as an Exception in the firewall.

If yes, do we need to turn off windows firewall? or

Do we need to report the issue to our network administrator to diagnose further. They might need to explicitly whitelist the OCSP host used to check your certificate?

Thanks

Ram

[former_member211289]

Dear Manikandan,

You mentioned that "Did you configured the JDBC properly?" Is it just downloading .jar file (driver) and configuring .sbo?

Please let me know, if there is anything to configure about JDBC.

Like,

Configuring logging.properties

File Caches

Connecting using a proxy server.

Please share some detail steps.

Thanks

Ram

[former_member605167]

Hi Manikandan,

I imported the snowflake file and from my URL and imported to SAP server.

I can see the ceritficate. when I check the keystore list.

But in IDT, while creating connection, I am seeing below error.

JDBC driver encountered communication error. Message: Exception encountered for HTTP request: sun.security.validator.ValidatorException: No trusted certificate found.

Please explain me with detail steps, if I miss anything in process.

Thanks,

Bala