UD Connection – Integrating an external DB on BW ABAP 7.3 via BW Java 7.3

You want to use an non-SAP external database in your SAP system. You can integrate and connect it to your BW ABAP system by using your BW JAVA system.

Please have a look at the PDFs attached to the SAP Note 1658310 – How to configure UD connect on the J2EE (710,..730) server for JDBC access to external databases and wiki.scn document Configure UD Connect in NWA for 7.3 – SAP NetWeaver Business Warehouse – SCN Wiki

Hints:

-1433 SQL port must be released between SQL DB system and SAP system.

-Suitable MSSQL driver for your version can be downloaded from Microsoft website or OSS. Please check SAP Note Note 639702 – JDBC Driver for Microsoft SQL Server respectively.

-In previous NW versions (before 7.*) , UD connect has been set up via Visual Admin. This document provides NW 7.3 example and configuration is set up via Netweaver Administrator. You can open your NWA via http://bw_java_host:bw_java_port/nwa .

In our case, both systems are in version of NW 7.30 and it’s assumed that ABAP-JAVA integration is already completed. The external DB is MSSQL. In the document, you will find the screenshots that I’ve made.

1-Please create an RFC destination type T (TCP/IP) in ABAP part via sm59. Enter “Program ID” as “Registered Server Program”, ABAP hostname and system number with (sapgwSN) as “Gateway Host” and “Gateway Service” :

2-Please login to your NWA page and navigate to Application Resources. Show “All JDBC drivers” and “Create new resource“. Upload the correct MSSQL driver (e.g. sqljdbc.jar) and name it:

3- Show “All resources” and click resource adapter com.sap.ip.bi.sdk.dac.connector.jdbc .

4- Under “Loader References” tab below, create library:<the driver name from step2> as reference:

5- In “Application Resources” page show “JCA Connection Properties” and find SDK_JDBC. Then click “Configuration Properties” tab and fill the related areas:

UserName     : The user which connects to SQL DB.

Password      : The password of it.

DriverName   : com.microsoft.sqlserver.jdbc.SQLServerDriver

URL              : The URL for the SQL DB : jdbc:sqlserver://SQL_DB_HOST:1433;databaseName=<SQL_DB_NAME>

FixedCatalog :  <SQL_DB_NAME>

Below is screenshot from Visual Admin from previous Java System (NW7.0). The system information remained same but over NWA this time.

6-In “Application Resources” page, show “Resource Adapters” and find “SAPJavaResourceAdapter15“. Then click “Properties” tab and fill the related areas:

ProgramID    : Program ID that you’ve entered in RFC in ABAP part

MaxThreadCount : 0 (1 is also fine)

SAPClient     : ABAP Client

UserName    : The user which makes communication between ABAP and JAVA parts

Password     : Password of it.

Language      : EN

ServerName : ABAP part hostname

PortNumber  : ABAP part system number

7-Open “Start & Stop : JAVA EE Services” page and restart “Connector Container” .

Test Page: http://host:port/TestJDBC_Web/TestJDBCPage.jsp

You should be able to see tables, views, etc of SQL DB.

SOME FREQUENT ERRORS:

*Credential error: “Login failed for user XXXXX”

Your user and password would be in UPPER CASE! Otherwise you can face login problems. Please avoid characters (? , ! , *, etc) in user name and password, if possible.

*Hostname cannot be resolved or the port 1433 is not open. You can check whether 1433 is open, with telnet command.

telnet SQL_DB_HOSTNAME 1433 should be successful.