Problem statement

Maintaining a stable connection between the SAP system (ABC) and a secondary database (DEF), a Microsoft SQL Server, is crucial. However, any of the SAP kernel upgrade might require additional steps to patch the Database Shared Library (DBSL) due to SAP no longer supporting the SQL Server Express edition (SAP Note 3210043).

Background

Certain programmes require this connection to work, and these are maintained under dbacockpit > database connections. SAP doesn’t support the SQL Server Express Edition version anymore; we were made aware of this when the recent sap kernel upgrade replaced the dbmsslib.so library with a new version and it took a lot of troubleshooting to figure this out..

Testing the connection

Choosing and running the report ADBC_TEST_CONNECTION under se38 would let you know if the connection works fine

Attempts to Automate the Solution

Automating this seemingly simple problem is quite arduous.

Approach 1: Alert Setup via Solman or SAP System

Roadblock 1: Unable to find a method to set up alerts from SAP-to-DB connectivity in Solman or CCMS

Roadblock 2: One of the fundamental problems to address while configuring this from Solman was to check connectivity from sap-to-db and not Solman-to-db.

Approach 2: Custom metrics in Solman can trigger an OS-level script, which then launches an SAP program to verify if SAP can still connect to the specified database.

Roadblock 1: OS command console in sap solution manager has been discontinued from SAP Solman 7.2 sps 17, we can’t run scripts from OS level using DAA. (Sap note in references)

Roadblock 2: Using SAP NetWeaver RFC SDK, that support executing programs via RFC in SAP systems often introduces multiple dependencies on Linux packages that need frequent upgrades. Additionally, SAP has stopped active development on tools interfacing with sap. (GitHub projects in references)

Approach 3: Using cmd line tools (like sqlcmd, isql) to connect to database possessed a challenge due to their inherent nature of connecting directly from OS and not via a sap system.

Approach 4: ABAP development could’ve been an alternative, but this would require dev and testing efforts, also scheduling the job on the sap system creates unnecessary load.

Approach 5: After debugging the SAP standard report ADBC_TEST_CONNECTION, we found that several internal function modules cannot be called directly using SE37 and must instead be used as includes.

Roadblock 1: we were planning to write a programme that calls the function module through RFCs but then, there were other problems with this development approach as highlighted in approach 2 -roadblock 2

Final Solution:

The solution that we finally reached to was from sap 1644499, which asks us to set few variables and try connecting to the sql db using R3trans (R3trans uses the DBSL library for sql databases). As per the note, there will be an error message “4 ETW000 Connected to unknown DBMS MSSQL – not supported by R3trans”, but this is not a problem, the connection is still made to the SQL Server. Along with this we get a specific authentication related error, but this again means the sap system was able to connect to db. With keeping in mind, the exact error code for sql db, we’ve prepared a script that returns a 0 value only if we get this code, otherwise 1. We plan to send a mail each time we get an error value of 1 and schedule this program via crontab.

Code:

#!/bin/bash

rm dbsl*
rm *.log

export dbms_type=mss
export MSSQL_SERVER=braagvcontrol01.duni.org
export MSSQL_DBNAME=DUMEtAdProject
#export MSSQL_SCHEMA=ZF
export MSSQL_USER=sapcon
export MSSQL_USEINTEGRATEDSECURITY=0
export MSSQL_PASSWD=xxxxxxxxxxxxxxxx
export MSSQL_DBSLPROFILE=1

R3trans -x > /dev/null 2>&1

References:

3132826 – OS Command Console in SAP Solution Manager3140940 – [CVE-2022-22544] Missing segregation of duties in SAP Solution Manager…https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors?view=sql-server-ver16 – sql error codeshttps://github.com/SAP-archive/PyRFC/issues/372 – pyRFChttps://github.com/SAP-archive/gorfc/issues/42 – goRFC

Problem statementMaintaining a stable connection between the SAP system (ABC) and a secondary database (DEF), a Microsoft SQL Server, is crucial. However, any of the SAP kernel upgrade might require additional steps to patch the Database Shared Library (DBSL) due to SAP no longer supporting the SQL Server Express edition (SAP Note 3210043).BackgroundCertain programmes require this connection to work, and these are maintained under dbacockpit > database connections. SAP doesn’t support the SQL Server Express Edition version anymore; we were made aware of this when the recent sap kernel upgrade replaced the dbmsslib.so library with a new version and it took a lot of troubleshooting to figure this out..Testing the connectionChoosing and running the report ADBC_TEST_CONNECTION under se38 would let you know if the connection works fineAttempts to Automate the SolutionAutomating this seemingly simple problem is quite arduous.Approach 1: Alert Setup via Solman or SAP SystemRoadblock 1: Unable to find a method to set up alerts from SAP-to-DB connectivity in Solman or CCMSRoadblock 2: One of the fundamental problems to address while configuring this from Solman was to check connectivity from sap-to-db and not Solman-to-db.Approach 2: Custom metrics in Solman can trigger an OS-level script, which then launches an SAP program to verify if SAP can still connect to the specified database.Roadblock 1: OS command console in sap solution manager has been discontinued from SAP Solman 7.2 sps 17, we can’t run scripts from OS level using DAA. (Sap note in references)Roadblock 2: Using SAP NetWeaver RFC SDK, that support executing programs via RFC in SAP systems often introduces multiple dependencies on Linux packages that need frequent upgrades. Additionally, SAP has stopped active development on tools interfacing with sap. (GitHub projects in references)Approach 3: Using cmd line tools (like sqlcmd, isql) to connect to database possessed a challenge due to their inherent nature of connecting directly from OS and not via a sap system.Approach 4: ABAP development could’ve been an alternative, but this would require dev and testing efforts, also scheduling the job on the sap system creates unnecessary load.Approach 5: After debugging the SAP standard report ADBC_TEST_CONNECTION, we found that several internal function modules cannot be called directly using SE37 and must instead be used as includes.Roadblock 1: we were planning to write a programme that calls the function module through RFCs but then, there were other problems with this development approach as highlighted in approach 2 -roadblock 2Final Solution:The solution that we finally reached to was from sap 1644499, which asks us to set few variables and try connecting to the sql db using R3trans (R3trans uses the DBSL library for sql databases). As per the note, there will be an error message “4 ETW000 Connected to unknown DBMS MSSQL – not supported by R3trans”, but this is not a problem, the connection is still made to the SQL Server. Along with this we get a specific authentication related error, but this again means the sap system was able to connect to db. With keeping in mind, the exact error code for sql db, we’ve prepared a script that returns a 0 value only if we get this code, otherwise 1. We plan to send a mail each time we get an error value of 1 and schedule this program via crontab.Code:#!/bin/bash

rm dbsl*
rm *.log

R3trans -x > /dev/null 2>&1

if grep “18456” trans.log > /dev/null 2>&1 ; then
echo 0
# below line is a comment
echo “Authentication failure” > /dev/null 2>&1
# it’s fine that authentication is failing but at least
# the mssql server is reachable using R3trans protocol
# command to trigger a mail to sap basis team would be written here.
else
echo 1
# below line is a comment
echo “Connection failed…” > /dev/null 2>&1
fiReferences:3132826 – OS Command Console in SAP Solution Manager3140940 – [CVE-2022-22544] Missing segregation of duties in SAP Solution Manager…https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors?view=sql-server-ver16 – sql error codeshttps://github.com/SAP-archive/PyRFC/issues/372 – pyRFChttps://github.com/SAP-archive/gorfc/issues/42 – goRFC Read More Technology Blog Posts by Members articles

#SAP

#SAPTechnologyblog