Skip to Content
Technical Articles

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

20 Comments
You must be Logged on to comment or reply to a post.
  • 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

    • Hi Dave,

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

      For eg :

      https://<CompanyName&gt;.<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

      • 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

    • 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

  • 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

  • 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

  • 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

     

    • 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

       

       

  • 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

      • 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 ?

        • 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

          • 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..!

    • 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