How to access an external Microsoft SQL Server database
Quite often someone asks me how an external SQL Server database can be accessed by an SAP system, e.g. to:
- Access data in an external SQL Server database with the SAP system
- Report against data in an external SQL Server database with Business Intelligence / Business Warehouse
- Use DBACockpit to monitor an external SQL Server instance
- Which operating system your SAP application servers run on
- Which purpose you want to use the connection for
- Which type of SAP application servers (ABAP, Java, Dual-stack) are available in the SAP system
There are different connection types, technical requirements and restrictions. This blogpost clarifies the possibilities and restrictions and covers frequently asked questions:
- Options and technical requirements to access an external SQL Server database
- How to setup a connection with UDConnect
- How to setup a connection with DBCon / Multiconnect
- How to monitor an external SQL Server Database using DBACockpit
1. Options and technical requirements to access an external SQL Server Database
The SAP standard ways to connect an external SQL Server instance with an SAP system are:
UDConnect (Universal Data Connect)
Regardless of the way you choose you can only connect to remote databases which are reachable via network from your SAP Application Server.
DBCON / Multiconnect
DBCON / Multiconnect uses the Microsoft SQL Server Native Client Software (SNAC) to establish a connection to the remote SQL Server instance. The Microsoft SQL Server Client Software for Windows consists of several *.dll files. For long time it was available for Windows platforms only. Recently, Microsoft ported its ODBC SQL Native Access driver to Linux. For this reason heterogeneous Linux/Windows scenarios are now possible. DBCON utilizes the SAP ABAP stack to access the external databases so your system requires at least one ABAP-stack-based SAP Application Server running on Windows or Linux x86_64.
UDConnect uses a JDBC (Java Database Connectivity) driver to establish a connection to the remote SQL Server instance. The JDBC driver consists of one or more *.jar files and can be used on Windows, Unix and Linux operating systems. As UDConnect utilizes the J2EE engine of the SAP Application server to access the external databases you need to have at least one Java-Stack-based SAP Application Server in your SAP system in order to use UDConnect.
- If your system comprises solely of ABAP stack-based servers running on Unix platforms you can neither use UDConnect nor DBCON. Why? Because UDConnect requires at least one Java-stack based SAP Application Server (regardless of the operating system) and DBCON requires at least one Windows- or Linux x86_64-based SAP Application Server.
- Using DBCon on a Linux x86_64 based application server can only be used to connect to SQL Server versions 2005 and higher. Predecessor releases are not supported by the Microsoft driver. Furthermore, the driver is only supported for Red Hat Enterprise Linux 5.x and higher and for Suse SLES11 SP2 and higher.
2. How to setup a connection with UDConnect
UDConnect cannot be used for remote monitoring a SQL Server based system. However, you can use it to access data in an external SQL Server database.
Setting up UDConnect in order to access data in an external SQL Server Database with BW/BI requires four steps:
- Adding an RFC server on Java-stack side
- Defining an RFC destination on BW/BI side
- Installing and configuring the JDBC driver on Java-stack side
- Configure the connection URL for the external database on Java-stack side
For step-by-step instructions please see the configuration guide available under:
SAP Netweaver ’04: How to configure UD Connect on the J2EE Server for JDBC Access to External Databases
SAP Netweaver 7.1: see attached guide (UDConnect_for_710.pdf)
3. How to setup a connection with DBCON / Multiconnect
To access data in an external SQL Server Database with DBCON / Multiconnect three steps are required:
Installing the SAP DBSL for SQL Server (dbmssslib.dll / dbmssslib.so)
On a Windows-based server: installing the Microsoft SQL Server Native Client (SNAC) or
On a Linux x86_64 – based server: installing the Microsoft ODBC driver for Linux
Creating a DBCON entry for the external database
SAP note 1774329 explains the steps required to prepare your SAP instance to connect to a remote SQL Server instance.
SAP DBSL for Windows
DBCON utilizes the ABAP-stack to connect to an external database. The ABAP-stack itself requires the Database Shared Library (DBSL) to communicate with a database. For each Relational Database Management System (RDBMS) supported by the ABAP-stack there is a separate DBSL provided by SAP. To install the DBSL:
Determine which kernel your SAP system is using (32 bit /64 bit, Unicode / Non-Unicode, Kernel Release, Operating System)
kernel release: go to ransaction SM51 → place the cursor on the SAP instance → click “Release Info”
bitversion, Unicode / Non-Unicode, Operating System: go to “System” → “Status”
Download the archive containing the most recent SAP DBSL for SQL Server matching your kernel
go to SAP Software Download Center → Browse our Online Catalog → Additional Components → SAP Kernel → SAP KERNEL <bitversion> <Unicode / Non-Unicode> → SAP KERNEL <kernel_release> <bitversion> → <Operating System> → MS SQL Server → lib_dbsl_<patchlevel>-<number>.sar
Extract the downloaded archive using command
sapcar -xvf lib_dbsl_<patchlevel>-<number>.sar
Copy the unpacked dbmssslib.dll file into the kernel directory of all SAP application servers which you want to use to establish the connection.
SAP DBSL for Linux x86_64
Please see SAP note 1644499 if you need to download and install the SAP DBSL for Linux x85_64-based servers. The note describes how to request the DBSL and also explains in detail which steps are required to properly set it up.
The DBCON entry informs the ABAP-stack where to find the external SQL Server Database and how to authenticate. Please see SAP note 178949 to learn how to create a DBCON entry for an external SQL Server Database.
Microsoft SQL Server Client for Windows
The SQL Server native client is used to establish the connection to the external SQL Server instance. To install it you need to run the sqlclni.msi installation package which is available from the SQL Server installation DVD / CD, or from the Microsoft Software Download website.
Microsoft ODBC Driver for Linux x86_64
SAP note 1644499 explains in detail where to download the Linux x86_64 – based ODBC driver and how to install it.
4. How to monitor an external SQL Server instance using DBACockpit
To monitor an SQL Server database with DBACockpit you first need to configure a DBCON connection to the external database. Please refer to section 3 for details.
If your local system is running on SQL Server as well you can skip installing the Microsoft SQL Server Native Client (SNAC) and SAP DBSL for SQL Server as both will already be in place. Then, proceed with the DBACockpit-related configuration steps. You can find detailed guides attached to SAP note 1027512 (sqldba_cockpit.pdf) and in SAP note 1316740.
UDConnect cannot be used for remote monitoring – the only way you can monitor a remote system is by using DBCon.
No shared library found for the database with ID <DBCON_entry_name> or Unable to find library <kernel_directory>/dbmssslib.sl’. ->
DLENOACCESS (0,Error 0) or ERROR => DlLoadLib()==DLENOACCESS – dlopen – (“/usr/sap/<SID>/DVEBMGS00/exe/dbmssslib.so”) FAILED
or could not load library for database connection <DBCON_entry_name> or cannot open shared object
This error indicates that the ABAP stack could not find the SAP DBSL for SQL Server (dbmssslib.dll) in the kernel directory. If you encounter this error on a Unix – based server the root cause is clear: the DBSL does not exist for other platforms than Windows or Linux x84_64. In this case use a Windows-based or a Linux x86_64-based SAP Application Server to establish the connection. If your system does not contain a Windows-based or a Linux x86_64-based Application Server you need to setup a small one as workaround. If you encounter this error on a Windows Application Server or a Linux x86_64 based Application Server make sure that the DBSL is properly installed in the kernel directory as explained in point 3.
- B Wed Jan <timestamp>
B create_con (con_name=<dbcon_name>)
B Loading DB library ‘<kernel_directory>\dbmssslib.dll’ …
M *** ERROR => DlLoadLib: LoadLibrary(<kernel_directory>\dbmssslib.dll) Error 14001
M Error 14001 = “This application has failed to start because the application configuration is incorrect.
Reinstalling the application may fix this problem.”
B *** ERROR => Couldn’t load library ‘<kernel_directory>\dbmssslib.dll’
B ***LOG BYG=> could not load library for database connection <dbcon_name>
The DBSL could be found successfully in the kernel directory but there was a problem while loading it. This can have various reasons. To ensure that the file itself is not corrupt please download and install the file from scratch as explained in point 3. If the error remains afterwards please check the OS Log for further errors at the time of the error.
- Generate Activation Context failed for
Reference error message: The referenced assembly is not installed on your system.
Dependent Assembly Microsoft.VC80.CRT could not be found and Last Error was
The referenced assembly is not installed on your system.
The Microsoft runtime DLL’s which are required by the DBSL are missing on your server. Please install them as explained in SAP Note 684106.
- Could not find stored procedure ‘SAPSolMan<version>.sap_tf_version'”
DBACockpit uses stored procedures to collect monitoring information from a database. These stored procedures need to exist in the database that is being monitored. If you are using the connection for a purpose other than remote monitoring with DBACockpit you can ignore this error. If you want to remote monitor the SQL Server database please make sure that you’ve configured the connection exactly as described in the configuration guide referenced in point 4. Then you need to create the missing stored procedures in the remote database. To do so open transaction DBACockpit in the monitoring system, use the “System”-Dropdown field to select the remote SQL Server system which you want to monitor -> go to Configuration -> SQL Script Execution. If the monitoring schema is missing in the remote database you will be offered a button called “create/repair schema”. After using it to create the schema you will be offered a button called “Execute script(s)”. Click on it to create all required monitoring Stored Procedures in the remote database.
- You want to update the JDBC driver used by your UDConnect connection
Follow the instructions in SAP Note 1009497.
thanks for great and helpful article!
I just have a remark on the mentioned SAP Note for DBCON: 738371, it is not available anymore and the new Note is: 178949
178949 - to set up a DBCON to programmatically access remote databases
Thank u Beate.
This document is nice...
Thats a very informative documentation around the SQL server as external data base. I have a query regarding the following set up
1. We have our ECC system(ABAP stack) installed on HP-UX
2. We have SAP Process Orchestration system (JAVA only stack) on AIX
3. We have MS SQL as an external database installed on WinTel
Given the above set up, my question is
1. Can we connect our ECC system with MS SQL data base via DBCon or UDConnect?
2. Can we connect our SAP Process Orchestration system with MS SQL data base via DBCon or UDConnect?
I am wondering about these questions as I have even put it in a matrix 😉 but ok:
ECC = ABAP STACK in your case, HP-UX = Unix --> no connectivity
Note 1644499 also points out that the required executables for connecting to a SQL Server DB are not available for any platforms other than Windows (which is obvious) and Linux (while HP-UX is not Linux). This means if you ECC system does not include any SAP dialog instance running on a Windows Server, there is NO connectivity.
You can only still connect your ECC to the SQL Server DB if you add a Windows-based SAP Application Server to this system and make sure that you only run your code connecting to this DB on this Windows Application Server.
SAP Process Orchestration is a Java stack according to you, and the platform is AIX (=UNIX). The matrix says: UDConnect which means yes you can connect using UDConnect.
Again: if you can add a Windows-based application server to your ECC system (check the SAP Product Availability Matrix for supported combinations of Windows/your product/your DB) you can have a DBCON connection to the SQL DB, if there is no supported connection or you can't add a Windows based server for another reason you can't.
Thanks a lot for the reply Beate.
Looks like we need to build a scenario where ECC will push data to SAP PI (part of SAP Process Orchestration) and from SAP PI we have to establish a jdbc connectivity to push data in external MS SQL Data base
I will check for the feasibility if customer can add additional application server on Windows for their current ECC instance
Thanks a lot for this helpful post! It's definitely very informative.
I have one question though...
I have previously set up a connection from a BW to an external SQL DB using a BI Portal as the middle man (via UDConnect). Now I want to add a second SQL server to this same BW. I've tried copying the existing Connection Factory as well as the JavaResourceAdapter but no luck as I'm not able to start the new resources I create.
Would you happen to know if that's even possible? If so, would you have any information you could share on adding this second source?
Thanks a lot,
Can we say DBCON always makes ODBC connection regardless of database? Or at least, lets say for Oracle database.
I couldnt find an official documentation explaining which technology DBCON uses.
I have doubts if it uses ODBC or native connectiion.