Skip to Content
Technical Articles
Author's profile photo Abhinav Shrivastava

How to set up SAP BusinessObjects with SNOWFLAKE using JDBC

This blog is to share my experience setting up the connection of Snowflake from SAP BO

Software/Applications:

Environment Information: SAP BO 4.2 SP03
BO Server Operating System: RHEL 6.x
Database: Snowflake
Keystore Manager: (used portecle-1.11 from windows for BO Client tool, eg: IDT)
Connectivity Type: JDBC
JAR Version: snowflake-jdbc-3.6.0.jar (download Snowflake JDBC)

Audience: SAP BO Admin, SAP BO Developer, SAP BO Consultants

Snowflake?
Snowflake is a Cloud Data Warehouse, which runs completely on public cloud infrastructure. It uses virtual compute instances for its compute needs and a storage service for persistent storage of data. Snowflake cannot be run on private cloud infrastructures (on-premises or hosted)

Connectivity Pre-requisites:

  1. Domain in which BO server is hosted, should have proper handshake with the Snowflake regional server.
    Refer Snowflake connectivity document:
    https://docs.snowflake.net/manuals/user-guide/ecosystem-diagnose-connectivity-issues.html

In our use case, we are using US East region. Check below for more information:
https://docs.snowflake.net/manuals/user-guide/intro-regions.html

  1. Import the snowflake cert in the “cacert” file located on BO server and client using KeyTool and portecle-1.11 respectively.
    cacert location on Linux server:
    <INSTALL_DIR>\enterprise_xi40\lin_x64\sapjvm\jre\lib\security\cacert location for windows client:
    <INSTALL_DIR>\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\sapjvm\jre\lib\security\
  2. After this, create a folder with name “snowflake” under below location and place “snowflake-jdbc-3.6.0.jar” into it:BO server:
    <INSTALL_DIR>\enterprise_xi40\dataAccess\connectionServer\jdbc\drivers\<snowflake> Client Location:
    <INSTALL_DIR>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\snowflake
  3. Update the JDBC.sbo present on server and client using below tag:
BO Server:
<DataBase Active="Yes" Name="Snowflake JDBC Datasource">

<JDBCDriver>

<!--  Uncomment and edit the following lines to define java classes required by JDBC driver

&lt;ClassPath&gt;&lt;Path&gt;your jar or class files directory&lt;/Path&gt;  &lt;/ClassPath&gt;

            -->

<ClassPath>

<Path><INSTALL_DIR>\enterprise_xi40\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>


Client Machine:
<DataBase Active="Yes" Name="Snowflake JDBC Datasource">

<JDBCDriver>

<!--  Uncomment and edit the following lines

to define java classes required by JDBC driver

&lt;ClassPath&gt;

&lt;Path&gt;your jar or class files directory&lt;/Path&gt;

&lt;/ClassPath&gt;

-->

<ClassPath>

<Path><INSTALL_PATH>\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>

 

Steps to create Connection:

    1. Login into IDT.
    2. Under Repository Resource, right click on Connection folder and select “Insert Relational Connection”
    3. After giving the Connection Name, click on Next.
    4. Expand Generic and you should see a Driver “Snowflake JDBC datasource”
    5. Give the service account and password of Snowflake followed by JDBC URL and JDBC Class and test the connection:
   User Name : <user_name>
   Password : <password>
   JDBC URL: jdbc:snowflake://<company>.<snowflake_region>.snowflakecomputing.com:443
   JDBC Class: net.snowflake.client.jdbc.SnowflakeDriver

Additional observation:
– Before executing any SQL on Snowflake interface, we have to define the Virtual Warehouse against which the query execution should take place. Hence used BEGIN_SQL parameter in the Business layer (while universe creation from IDT) to define the Virtual Wareshouse from business objects.

 

Please share your experience. I will keep updating/correcting this blog as per my new observations.

 

Thanks,
Abhinav Shrivastava

Assigned Tags

      29 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Krishnaprasad MT
      Krishnaprasad MT

      Thank you for the blog post!

      Author's profile photo David Hays
      David Hays

      Thank you for this post, it is very timely as we attempt to connect our bobj cluster to snowflake!

      I am having a little bit of trouble - Can you elaborate on the step

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

      I am unfamiliar with this process;  do I need to generate a snowflake cert first? and then import it into the cacert file?

      Also, we use a proxy server, is there a way for me to inform the jdbc driver of my proxy host?

      -dave hays

      Author's profile photo Abhinav Shrivastava
      Abhinav Shrivastava
      Blog Post Author

      Hi Dave,

      Snowflake must have set up an environment for your company over there cloud environment.

      For eg :

      https://<CompanyName>.<Region>.snowflakecomputing.com/

      On opening that URL from Chrome, you can use below steps to download the Client certificate:

      https://support.globalsign.com/customer/portal/articles/1211541-install-client-digital-certificate---windows-using-chrome

      So to answer your question,
      Yes first Export the Snowflake certificate on your local machine, and later Import that certificate using keytool in BO cacert file. (BO admin should able to help you with this.)

      -Abhi

      Author's profile photo Ramanaidu Kolasani
      Ramanaidu Kolasani

      Dear Abhinav,
      Thanks for providing the blog and it’s really useful.

      Kindly help me with below queries in priority case.
      Currently, I’m working as a BO Admin and I’m going to join in a project (Oracle to SnowFlake Migration).

      Regarding connecting pre-requisites,
      I totally understood and was aware of .jar files and .sbo files.

      Could you please help me with 1st and 2nd pre-requisites to get clear understanding.

      Query1:
      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

      Verifying Communication with Your CA Site or OCSP Responder
      To verify that communication is not blocked:

      Step 1: Retrieve the URL for Your Certificate
      Step 2: Test the URL

      How can we correlate ‘Domain in which BO server is hosted’ with ‘Snowflake regional server’?
      Please share some screenshots of query1 if you have.

      Query2:
      Import the snowflake cert in the “cacert” file located on BO server and client using KeyTool and portecle-1.11 respectively.
      You said ‘first Export the Snowflake certificate on your local machine’,

      Is snowflake certificate nothing but .pfx file? if not, where do we get .pfx file?

      where do we get snowflake certificate?

      Which .pfx file we can download from https://support.globalsign.com/customer/portal/articles/1211541-install-client-digital-certificate—windows-using-chrome

      Also, you said later Import that certificate using keytool in BO cacert file. (BO admin should able to help you with this.)
      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

      I saw your post in snowflake community and you said Locate the cacert file from the JAVA folder, which BI tool is using and add those certificates into it,

      is cacert file or folder?
      https://support.snowflake.net/s/question/0D50Z00007wxwVMSAY/snowflake-connectivity-with-sap-businessobjects-or-any-other-bi-client-tool

      Moreover, you said  I have fixed the problem for BusinessObjects Clients and Tableau desktop, Still working for server side, can you share server side setup as well?

      Query3:
      How do we check whether our snowflake URL has as an Exception in the firewall?

      Please provide your inputs asap.

      Thanks
      Ram

      Author's profile photo Abhinav Shrivastava
      Abhinav Shrivastava
      Blog Post Author

      Hi Ram,

      Sorry for delay in response.

      Are your questions still open?

      -Abhi

       

      Author's profile photo Ramanaidu Kolasani
      Ramanaidu Kolasani

      Yes Abhinav.

      Please respond with some detail information as I'm totally new for snowflake.

      Thanks

      Ram

      Author's profile photo Ramanaidu Kolasani
      Ramanaidu Kolasani

      Hello David,

      Please let me know, where do we get snowflake certificate?
      Once we get the certificate, how to 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\

      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

      Thanks

      Ram

      Author's profile photo Ramanaidu Kolasani
      Ramanaidu Kolasani

      Dear Abhinav,

      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

      Author's profile photo Balamani Enjamoori
      Balamani Enjamoori

      Hi Abhinav,

      I followed the steps given here for snowflake connection.

      I imported the cacerts to specified locations. But when I try to connect from IDT it is giving error as:

      JDBC driver encountered communication error. Message: Exception encountered for HTTP request: java.lang.RuntimeException: Failed to find the root CA..

       

      Could you help me with this.

      Thanks,

      Bala

      Author's profile photo Abhinav Shrivastava
      Abhinav Shrivastava
      Blog Post Author

      This error shows, still there is no trust generated from the machine you are creating connection.

      -Abhi

      Author's profile photo Haroon Malik
      Haroon Malik

      Hi Abhi,

      we’ve got 4.2 SP6 running on RHEL7 and facing issues as well.

      1. Used openssl s_client -connect server.com:443 -showcerts
      2. Import certficate in CACERT

      ./keytool -importcert -file /tmp/server_com.crt -keystore ../lib/security/cacerts -alias server_com

      3. Added the folder snowflake along with the jar file

      4. Altered the jdbc.sbo

      <ClassPath><Path><Install_Dir>//enterprise_xi40/dataAccess/connectionServer/jdbc/drivers/snowflake/snowflake-jdbc-3.8.4.jar</Path></ClassPath>

      with and without an extra entry/tag as well. Also restarted the connection servers.

            5. But it still doesn’t show up in IDT under generic JDBC.<<<

      Am i missing out something?

      Thanks

       

      Author's profile photo Abhinav Shrivastava
      Abhinav Shrivastava
      Blog Post Author

      Hi Haroon,

       

      Are you trying to create a new connection from repository resource or from Local project?

       

      For JDBC, try restarting APS which hold adaptive connectivity service.

       

      -Abhi

       

       

      Author's profile photo Ananth Joshi
      Ananth Joshi

      Hi Abhinav,

       

      Thanks for the detailed blog on setting up the Snowflake JDBC connection on BO.

      I followed all the steps mentioned by you, however while connecting  i get the below error :

      JDBC driver encountered communication error. Message: Exception encountered for HTTP request: Connect to <account name>.snowflakecomputing.com:443 [<account name>.snowflakecomputing.com/xx.xx.xx.xx, <account name>.snowflakecomputing.com/xx.xx.xx.xx, <account name>.snowflakecomputing.com/xx.xx.xx.xx] failed: Connection timed out: connect (local port 61293 to address 0:0:0:0:0:0:0:0, remote port 443 to address xx.xx.xx.xx (ec2-xx-xx-xx-xx.compute-1.amazonaws.com)).

       

      Any idea what could be the reason ? Any help appreciated….!

       

      Thanks,

      Ananth

      Author's profile photo Abhinav Shrivastava
      Abhinav Shrivastava
      Blog Post Author

      Hi Ananth,

       

      Were you able to add the snowflake certificate in cacert?

       

      -Abhi

      Author's profile photo Ananth Joshi
      Ananth Joshi

      Yes , I have added the certificate in the BO installation path you mentioned in this blog:

       

      <INSTALL_DIR>\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\sapjvm\jre\lib\security\

      Is there any other place this needs to be added ?

      Author's profile photo Abhinav Shrivastava
      Abhinav Shrivastava
      Blog Post Author

      No the only file where we have to add the certificate is in 'cacert' file, present under above location.

      Can you show me in the screenshot, how did you added the certificate?

      Also please get it confirmed with internal network security team or with snowflake support that it's the right certificate.

      -Abhinav

      Author's profile photo Ananth Joshi
      Ananth Joshi

      I have downloaded the certificate from snowflake as mentioned by you in instructions. I have added it to cacert using portecle 1.11.

       

      Also, I have added proxy variable to system variables , but not sure whether BO IDT picks it up or not.

       

      Thank You..!

      Author's profile photo Abhinav Shrivastava
      Abhinav Shrivastava
      Blog Post Author

      Hi Ananth,

      I am present in CST time zone, do you want to set up a Screen sharing session.

       

      Regards,

      Abhinav

      Author's profile photo Narayanamurty Kavikondala
      Narayanamurty Kavikondala

      Could you please let me know the complete steps how to export the snowflake certificate to BO server?

      Thanks!

      Author's profile photo Abhinav Shrivastava
      Abhinav Shrivastava
      Blog Post Author

      Hi Narayanmurty,

       

      There is a utility/tool named portecle-1.11.

      Google it and It can be downloaded easily.

       

      Once you download it, you can open the cacert provided by BO in this tool, and can import the snowflake certificate in the same.

      The default password to open cacert in this tool will be 'changeit'.

       

      Regards,

      Abhinav

      Author's profile photo Quaero Administrator
      Quaero Administrator

      Hi Abhinav Shrivastava,

      i have followed above steps but when i try to test connection in the universe design tool it failing with the error "CS: Building ConnectString/ParamString failed : URL Format" . can you please advice on this ?

      Author's profile photo Vijay Pydala
      Vijay Pydala

      Thank you Abhinav, this lead us to configure JDBC correctly, am trying to enable the Keypair Authentication in SAP BO to connect Snowflake through JDBC, could you please redirect me if any SAP blogs available for this Keypair Authentication, I found few from snowflake docs, but looking for some from SAP. Thank you in advance.

      Author's profile photo Paul Ward
      Paul Ward

      Hi Vijay.  Did you have any luck with this?  We're using BOBJ 4.3, but struggling to set up the key pair authentication too.

      Author's profile photo Vijay Pydala
      Vijay Pydala

      Hi Abhinav Shrivastava,

      Any docs on enabling Keypair authentication in BOBJ ? if yes, could you please redirect us to those.

      Author's profile photo Patrick Perrier
      Patrick Perrier

      Snowflake is now supported in SAP BusinessObjects 4.2 SP8.

      Here is how to do it out of the box.  It worked on my client and server:

      https://blogs.sap.com/2020/03/12/snowflake-for-sap-businessobjects-4.2-sp08/

      Author's profile photo David Hays
      David Hays

      Hi,

      I got my jdbc connection to snowflake Azure region (east-us-2.azure.snowflakecomputing.com) but had to do one additional thing:

      When configuring the IDT Connection, I had to provide additional driver properties:

      useProxy=true,proxyHost=proxy.mycompany.com,proxyPort=80

      Thank you very much for this post, I could not have succeeded without this.

       

      -dave

      Author's profile photo Tony Sansico
      Tony Sansico

      Hi all, we are hosting a webinar on the topic Thursday, April 30th, 2020 : https://360suite.io/webinar/live-stream-event-sap-businessobjects-north-america/

      Author's profile photo Anish Palan
      Anish Palan

      Hi Abhinav,

      we are on BI 4.2 SP7 and Linux RHEL7

      trying to setup snowflake DB connection and test exisitng unv and webi reports to see if nay syntax fixes are necessary

      Folllowed all steps  provided in your blog and are success until JDBC connectivity.server side we checked and its connecting through isql command and in IDT when connection test done its says server responding and connection established

      but when clicking on the table values or run webi reprot it gives generic erro '523 650'

      Not sure what it is still looking at when connection is able to go to DB and says success

      Any insights is appreciated

      Sujay

      Author's profile photo Anish Palan
      Anish Palan

      Unvtablevaluescheck: