Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

SAPITUP - SAP BI 4.x - JDBC connectivity

mike_quinn
Explorer
‎01-17-2020 10:15 AM

Introduction


This blog post describes setting up JDBC connectivity to various databases, from SAP BI 4.x client and server machines on MS Windows and linux.  There are a number of useful blog posts covering this topic but hopefully I can add some useful information and troubleshooting.

The target audience are those among us who don't necessarily do 'techie work' day2day but have some task put on them that they just need to get done.

This is my first blog post and there are a couple of supporting blog posts I need to put together to help with the basics on this one.  As it is a WIP I hope to keep on updating it going forward.

Note that as far as SAP BI 4.x connectivity is concerned, and this is documented in the SAP BI 4.x DataAccess Guides, there are two main ways of configuring JDBC connectivity.

They both involve changes to the SAP BI 4.x connection-server files.

  • Editing .sbo files in the connection-server folders

  • Using Extensions approach with sub-folders in the connection-server folders


We'll be using the Extensions approach, which is pretty simple as the other is just the way of pain!

Useful



Check out the SQuirreL SQL section at the bottom of this blog post for a useful connectivity testing tool  and examples of connection strings.

Contents




 

Connecting to MS SQLServer (2017)


In this example we'll connect SAP BI 4.2 SP7 with MS SQLServer 2017.  These links are useful background info.

Step 1 - Download MS SQLServer JDBC drivers from Microsoft


We need to download the correct JDBC driver for the version of SAPJVM that our release of SAP BI 4.x is running on.  For most, hopefully, SAP BI 4.x customers we are looking at the the latest version of the JDBC driver but if you are on earlier releases of SAP BI 4.1 or 4.0 you may need to download earlier versions of the JDBC driver.

  • Check out SAP note 2112338 to work out which version of the SAPJVM you are running in SAP BI 4.x

  • For SAP BI 4.2 SP7 we are running SAPJVM 8.x




  • So we are good to download the Microsoft JDBC Driver 7.4 as that lists Java 8




  • Download the Microsoft JDBC Driver 7.4 driver

  • Hit the 'Download' button ... but do note that the links just below this button are useful for background information

  • We're assuming you are downloading to your Windows client machine

  • The .exe is for Windows clients and servers

  • The .tar.gz is for linux servers and can be ignored if linux isn't in scope




Note that technically, I should have checked the SAP BI 4.2 SP7 PAM.  This list the Microsoft JDBC Driver 6.4 JRE8 driver as supported so you are probably best sticking to that.  The workflow is identical and the download is also available in screen shot above.



 

Step 2 - Install JDBC driver on MS Windows SAP BI 4.x client or server



  • Run the the downloaded sqljdbc_7.4.1.0_enu.exe to unzip the contents to a temporary folder




  • Copy the extracted mssql-jdbc-7.4.1.jre8.jar.  Remember its the JRE8 one we are after and you can ignore the 'shaded' one

  • On both your MS Windows SAP BI 4.x client and server machines create a folder \sqlsrv in C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers


Note that the name of the folder is important.  It will be different for different database vendors.  The list of supported folder names can be found in the corresponding .sbo file located in C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc

So in our case, with MS SQL Server 2017 it would be in sqlsrv.sbo

We can find the supported folder names we could have used as sqlsrv2017, sqlsrv or jdbc.  I chose sqlsrv



  • Paste the mssql-jdbc-7.4.1.jre8.jar file into this folder C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\sqlsrv

  • On the SAP BI 4.x server(s) restart the SIA and you should be good to go


If you are running a split or clustered SAP BI 4.x deployment, you only need to do this on the SAP BI 4.x app tier server(s) and typically not on the SAP BI 4.x web tier server(s)

Step 2b - Install JDBC driver on linux SAP BI 4.x server (tbc)


 

Step 3 - Create a SAP BI 4.x relational connection in UDT or IDT



  • Create a connection as per usual in UDT or IDT



Connecting to Sybase ASE 16.x (tbc)


Connecting to Sybase IQ 16.x (tbc)


Connecting to Sybase SQL Anywhere (tbc)


Connecting to MySQL 5.x (tbc)


Connecting to Oracle 11/12 (tbc)


Connecting to PostgreSQL  (tbc)


 

SQuirreL SQL – JDBC connectivity testing tool


SQuirreL SQL Client is a Java based GUI utility that I've found useful for checking JDBC based connectivity from both client and server machines on both MS Windows and Linux based systems.  I often find that customers either install middle-ware and establish connectivity from say their client machines but forget that they need to do the same from their servers ... or vice versa.  This leads to confusion when for example the client tools connect fine but they can't run any Webi or Crystal reports from the server.

Being able to use the same tool to troubleshoot connectivity issues from both clients and servers is really useful, especially as you can 'cut' SAP BI 4.x out of the troubleshooting chain.

If the machine you are working on can connect to your database via SQuirreL but has a problem connecting via the SAP BI 4.x tool-set, then you can be fairly confident that the problem is downstream with SAP BI 4.2 rather than upstream with networks, firewalls, credentials etc

The tool doesn't necessarily cater for all database platforms supported by SAP BI 4.2 so it is what it is.

Note that SQuirreL SQL will also allow you to view the structure of a JDBC compliant database, browse tables, execute SQL commands etc

Pre-requites



  • Java installed on the machine you want to connect from.  (I'll cover this in a future blog post)

  • On linux based server systems you will either need access to the GUI desktop or you'll need X11  configuring.  (I'll cover both of these in future blog posts)


Steps



  • Step 1 - Download the SQuirreL SQL install media

  • Step 2 - Install SQuirreL SQL on MS Windows client or server

  • Step 3 - Test connectivity from window client or server

  • Step 4 - Install SQuirreL SQL in linux server

  • Step 5 - Test connecitivity from linux server

  • SquirreL SQL – connection string examples


Step 1 - Download the SQuirreL SQL install media


Assuming you are on your MS Windows client machine.  You need to download the .jar installer.  The same installer can be used on MS Windows and linux servers.

Step 2 -  Install SQuirreL SQL on Windows client or server



  • Open a command prompt, preferably in elevated mode

  • Change directory to C:\temp\squirrel

  • Check that java is in your environments path (I've got JRE 1.8.x installed)

  • Run the .jar installer using java -jar 


cd C:\temp\squirrel                                              

                                                                  

java -version                                                     

java version "1.8.0_121"                                          

Java(TM) SE Runtime Environment (build 1.8.0_121-b13)             

Java HotSpot(TM) Client VM (build 25.121-b13, mixed mode, sharing)

                                                                  

java -jar squirrel-sql-4.0.0-standard.jar

 



  • GUI installer starts up




  • I have JRE 1.8.x installed and I'm installing SQuirreL 4.x so all good




  • Choose an install location




  • Choose all the optional plugins - especially the language ones if you like to waste disk space 🙂  It is important to choose the plugins for the database platforms you expect to connect to.




  • Choose desktop options




  • We're done



Step 3 - Test connectivity from Windows client or server


We'll test MS SQLServer connectivity as an example workflow but its the same for any supported JDBC drivers you've installed.  We need to make SQuirreL SQL aware of where the JDBC driver is located on our machine.

  • Start SQuirreL SQL client on your Windows machine

  • Click on the Drivers tab

  • Double click on the driver that you want to use (MicrosoftMSSQL Server JDBC Driver in this case)




  • Click on the Extra Class Path tab

  • Click Add and browse to the SAP BI 4.2 connection server folder where you installed the JDBC .jar in the previous steps ... C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\sqlsrv

  • Select the JDBC .jar file ... mssql-jdbc-7.4.1.jre8.jar

  • Click Open

  • Click OK


The icon next to the driver in the left hand panel should turn to a tick mark and the driver is configured.



Now we can create a connection (Alias) using the driver

  • Click on the Aliases tab

  • Click on the '+' button

  • Give your connection a Name

  • Select the Driver you want to use

  • Edit the URL with your databases details

  • Click OK


Note that when you select the driver, the basic URL format is filled out for you.

Complications with the URL may arise if you are using say different ports, load balancers, named instances etc across various database platforms.

You'll also want to make sure that host names (either short or FQDN) resolve to avoid having to use IP addresses in the URL.  Firewalls can often be an issue here too.

Also, when you are logged on to the SAP BI 4.x servers, its best to try and carry out the test whilst logged on as the actual service account that runs the SAP BI 4.2 services.



Now you've created a connection in the Aliases window you should be able to connect

  • Double-click you Alias (connection name) in the Aliases tab

  • Click Connect




Great - you're connected from the machine you are working on, to the database in question, via your JDBC driver, located in the SAP BI 42. connection server sub-folder you created.

If this machine has a problem connecting via the SAP BI 4.x tool-set, you can now be fairly confident that the problem is downstream with SAP BI 4.2 rather than upstream with networks, firewalls, credentials etc.

The basic workflow is the same for any of the supported drivers.


Step 4 - Install SQuirreL SQL in linux server


 

Step 5 - Test connecitivity from linux server



 

SquirreL SQL - connection string examples



  • MSSQLServer


building-the-connection-url-sql-server-2017


  1. SQL Server running on port 1433 jdbc:sqlserver://192.168.1.99:1433;databaseName=mart


 

 

 

 

 

 
Labels in this area
Popular Blog Posts