SAP BODS Designer Central Repository Not Showing Up When Adding

Product: SAP Data Services
Version: 4.2.x - 4.3.x

Problem Description

There are multiple BODS central repositories in the system.  Few of the central repositories are cloned from existing central repositories for following reasons:

1. Setting up more central repositories in identical BODS versions for current env
2. Setup another central repositories in identical BODS versions for different env. Issue occurs after 2nd attempt of refresh after DS Designer added the central repositories on 1st attempt
3. During upgrade, clone into a different DB user (Oracle), or logical database (MS SQL Server), then uses BODS Repository Manager to upgrade it without disrupting older BODS version

In DS Designer, menu Tools > Central Repository

After clicked "Add" button, it is either display a screen to add more central repository, or following message:

Even if it shows a list of central repositories to add, users are not able to see the desired central repository from the list.

DSMC central repository user and group setup has been done, even for secured central repository, when login to DS Designer as administrator user account.

Information to Gather

1. Login to central repository and determine the unique ID for the central repository.  This is called GUID, which will be added into local repository when users add it.  Run following SQL

select guid from al_version;

2. Repeat above for each of the central repository to get all the GUID

3. Login to local repository which failed to see other central repository

4. Run following SQL to show the central repository name and central repository GUID

select name, guid, object_key, object_type from al_lang where object_type = 5 and object_key in (select parent_objid from al_setoptions where parent_objid = al_lang.object_key and option_name = 'datastore_repotype' and option_value = 'central');

Sample output:

5. Explanation of above in local repository table AL_LANG

5.1. AL_LANG stores all object entries, including secure and non-secure central repositories

5.2. object_type = 5 is for datastores, secure repositories, non-secure repositories

5.3. al_SetOptions stores more detail for secure and non-secure central repository for Option_Name = datastore_repotype with Option_Value = 'central'

5.4. If you want to further filter by secure repository, then filter al_SetOptions by Option_Name = CENTRAL_REPO_SECURE, Option_Value = yes

Analysis

1. Compares the GUID from Step 1 with Step 4 for the same central repository name

2. Identify which central repository has the same GUID

3. This should be the central repository which is not visible in DS Designer's adding central repository screen

4. If you removed the central repository with the same GUID from DS Designer, then the list will be able to show all the central repository, even their GUID are identical

5. However, once you added one of those duplicate GUID into DS Designer, when try to add central repository again, you won't be able to see the central repository that has duplicate GUID

6. This is a consistent behavior in all local repositories

Root Cause

The AL_Version table was cloned from one central repository to another one, which leads to identical unique ID on column GUID.  Local repositories expect each central repository to have a unique GUID, else DS Designer assumes the central repository (with the same GUID) has been added, and will hide it.

Resolution

The supported approach is to use Repository Manager to re-initialize the central repository (all content will be lost), so that it will assign a new GUID.  Due to all central repository's content will be lost, if you want to keep its existing content, then find a local repository which can be used to check-out all content into it, then check back in to the central repository after it is initialized

SAP IPS/CMC: MS SQL Server Windows Authentication

Product: SAP IPS Information Platform Services (or SAP CMS)
Version: 4.1.x, 4.2.x, 4.3.x
OS: Windows Servers 64-bit

Overview

There is relatively limited information on Java web applications using MS SQL Server's Windows authentication in the internet.  Microsoft's JDBC documentation is relatively incomplete and leaves gaps in its installation, and leads to many wrong information around.

SAP IPS, or also called as SAP CMS, is a web application that is running on Apache Tomcat.  It uses SAP Java 8 for many years covering SAP IPS v4.1.x until now (v4.3.x) in 2025.

This post mainly to use SAP IPS as an example of web application server to discuss about the proper installation when needs to use MS SQL Server's Windows authentication

Background

Main MS SQL Server JDBC Driver Homepage: https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16

As of today, MS SQL Server JDBC version 12.8 is the current version.  It supports Java 8, 11, 17, 21, and 22.  Moreover, it bundled with 32-bit and 64-bit DLL to be used when the application supports MS SQL Server's Windows authentication.  This post mainly focus in this DLL, as improper installation will leads to the Java application, and web application failed to use Windows authentication.

Support of 32-bit Java adds another layer of complexity, where a lot of people is not aware of it, and do not know 64-bit Windows in term of 32-bit Java application support.

Both Microsoft JDBC driver page, as well as Java does not clarify the Windows DLL search order, which often leads to many Internet forums to contain mix messages.  You need to be a DLL developer in order to know Microsoft has long documented DLL search order, which now is located in following web page:

• Title: Dynamic-link library search order
• URL: https://learn.microsoft.com/en-ca/windows/win32/dlls/dynamic-link-library-search-order

This page's content is not "formatted" for Java.exe, so anyone who is poor in Windows OS, or never done any Windows programming will easily lost, and most probably think this page is not applicable.

Preparation

1. Identify your java.exe is 32-bit or 64-bit.  This could be a complicated process, if you install your own Java and forgotten about whether you download 32-bit or 64-bit Java.  For SAP IPS, it bundled with its own 64-bit Java, so this can be skipped

2. If you java.exe is 32-bit, seriously consider to switch to use 64-bit java.exe so that you can leverage more than 4GB RAM (virtual or physical).  Considering Windows Server 2016 only offers in 64-bit, and no 32-bit, so it is not sustainable to use 32-bit java.exe.  For SAP IPS, this can be skipped, as it is using 64-bit java

3. Determine whether the application uses JDBC 2.0 distributed transactions, which its JDBC class will be SQLServerXADataSource.  It is also called as extended architecture, or XA for short.  Most Java program will not uses this.  SAP IPS does not use this as well

4. Download MS SQL Server JDBC Driver in either zip or tar.gz format

3. Unzip it into a working directory instead of C:\Program Files\Microsoft JDBC DRIVER 12.8 for SQL Server indicated in the tar.gz/zip > install.txt.  SAP IPS only needs 1 DLL responsible for Windows authentication, but not the rest of the files

3.1. This JDBC zip file contains both 32-bit and 64-bit DLL to used for Windows authentication called below

3.1.1. enu\auth\x64\mssql-jdbc_auth-12.8.1.x64.dll - used for 64-bit Java

3.1.2. enu\auth\x86\mssql-jdbc_auth-12.8.1.x86.dll - used for 32-bit Java

3.1.3. This Windows DLL version does not need to match mssql-jdbc-<JDBC version>.jre<jre ver>.jar

3.2. The zip file contains DLL for Java XA extended architecture in both 32-bit and 64-bit.  SAP IPS does not use it, so do not need to install these DLL.  I have not see any Java applications that uses MS SQL Server JDBC XA, so not much to say, except following mapping.  I will recommend to rename the DLL filename to include x64 or x86, as default name can't easily tell the different:

3.2.1. enu\xa\x64\sqljdbc_xa.dll - used for 64-bit Java

3.2.2. enu\xa\x86\sqljdbc_xa.dll - used for 32-bit Java

3.3. Determine your Java version.  Picks the corresponding JAR file based on your Java version.  SAP IPS web application bundled with its own JAR file, so it does not use any of these  For other readers who uses Java 8 - 21, see following table mapping based on latest JDBC 12.8.1:

3.3.1. Java 8 - 10: uses mssql-jdbc-12.8.1.jre8.jar

3.3.2. Java 11 and higher: uses mssql-jdbc-12.8.1.jre11.jar

4. Determine how many java.exe is installed and used in the client machine.  SAP IPS often only has its own Java, and not using external Java that installed by administrator/developer/user

5. Does the Java application's JAR file bundled with its own JDBC JAR file.  If yes, then just need to focus in mssql-jdbc_auth-12.8.1.x64.dll (64-bit) and mssql-jdbc_auth-12.8.1.x86.dll (32-bit) to match the corresponding java.exe bitness

Planning and Installation

1. In the computer that you are setting up, determine how many different version of MS SQL Server daemon/instance your applications, as this related to the JDBC driver, and mssql-jdbc_auth-<version>.<bit>.dll.  For SAP IPS, and SAP Data Services, it is often to work with 1-2 version of MS SQL Server.  This indirectly implies 1 JDBC driver is required in SAP IPS machine, which often support few MS SQL Server version. SAP IPS does not install this DLL, and administrator needs to manually copy mssql-jdbc_auth-12.8.1.x64.dll (64-bit only).  Recommended directory is C:\Windows\system32\ (64-bit only)

2. If the Java applications installed in the same machine required to install multiple different Java JDBC - MS SQL Server JAR file, and different mssql-jdbc_auth.dll, then take note of this

2.1. This implies following files in the same MS SQL Server client machine:

2.1.1. mssql-jdbc-12.8.1.jre8.jar (or jre11) must not resides in jdk\lib\ext\ directory, unless there are multiple Java directory

2.1.2. mssql-jdbc_auth.dll (32-bit and 64-bit) must not resides in C:\Windows\system32\ or C:\Windows\SysWOW64\

3. If multiple Java.exe is used, then decide whether each will has its own JDBC JAR version, and mssql-jdbc_auth-<version>.x64.dll version.  I will ignore anyone will use 32-bit java.exe

3.1. If only 1 JDBC client version is required, then copy the JAR file into each of the jdk\lib\ext\, or jre\lib\ext\ directory.  This is not required for SAP IPS, or any web application which bundled the JDBC jar file in its WEB-INF\lib\ directory

3.2. If only 1 version of Window authentication DLL is required, then copy the DLL file into C:\Windows

3.2.1. 64-bit: Copy into C:\Windows\system32\mssql-jdbc_auth-12.8.1.x64.dll

3.2.2. 32-bit: Copy into C:\Windows\SysWOW64\mssql-jdbc_auth-12.8.1.x86.dll

4. If you want to keep a separate directory to keep mssql-jdbc_auth-<version>.<bitness>.dll, then edit Windows system environment variable, and set PATH to include this custom directory.  I will recommend this as well, but this is a global setup, and will affects all java to use the same DLL in this machine

Troubleshooting

Troubleshooting any applications that use MS SQL Server Windows authentication will require to troubleshoot using the specific Window username.  This is often a ActiveDirectory domain user account.  ActiveDirectory's user group policy can further configured to prevent the user from login interactively (using Windows login screen).

Due to user group policy could disable interactive login to the Windows machine, troubleshooting will require to ask ActiveDirectory admin to temporary enable/allow interactive login.  This page assumes the ActiveDirectory user is able to login to Windows, or uses RunAs command to run CMD to perform troubleshooting.

Starts the troubleshooting using following 2 methods to login as the Windows user who is authorized to login to MS SQL Server (Windows authentication):

1.. In Windows Login screen, login as the Windows user who used to start the Java.  In this example, SAP IPS/CMS

2. Login to Windows as your personal username, or other username that allows to login to the SAP IPS machine.  In Start menu, type CMD > right-click > Run As Other User > enter the Windows user who used to start Java.  You will get Command Prompt, with the username shown as the one you just entered

Once you reached this, the rest of the steps are relatively easy for Java application.  I will use the wording "authorized Windows user" to indicate the Windows username authorized to login to MS SQL Server in the rest of the procedure.

1. Download full MS SQL Server JDBC file as zip (Windows) from official homepage of https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16

2. As of today, JDBC version 12.8.1 is compatible with Java 8 - 21, and MS SQL Server version 2016 - 2022

3. If the Java program needs to connects to MS SQL Server 2014, then download JDBC version 12.6 or older (minimum 3.0)

4. Extract sample Java login program from the zip file

4.1. Directory name: sqljdbc_12.8 > enu > samples > connections > ConnectURL.java

5. Modify ConnectURL.java with the MS SQL Server login, e.g. hostname, port, DB name

5.1. Modify line 30: String connectionUrl = "jdbc:sqlserver://<server>:<port>;databaseName=AdventureWorks;user=<user>;password=<password>";

5.1.1. Fill in <server> as the MS SQL Server hostname

5.1.2. Fill in <port> as the MS SQL Server port number

5.1.3. Replace AdventureWorks as the MS SQL Server logical database nane.  If there is no DB, then master DB will always be there

5.1.4. Removes ";user=<user>;password=<password>"

5.1.5. Append ";integratedSecurity=true" after the databaseName parameter, e.g. "jabc:sqlserver://......databaseName=master;integratedSecurity=true;trustServerCertificate=true"

5.1.6. The parameter "trustServerCertificate=true" is optional, but many DBA has configured to force SSL, as well as used self-sign SSL certificate.  More description can be found in Learn.Microsoft.com: https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-ver16

6. Compile the Java program. Let's keep the filename as ConnectURL.java.  For SAP IPS, please uses Java 8. e.g. AdoptOpenJDK, Oracle Java, Amazon Java 8

6.1.<JAVA JRE 8 PATH>\bin\javac ConnectURL.java.  You can use either 64-bit or 32-bit as this is a small program that won't use up 4GB RAM

6.2. You will see new file ConnectURL.class in the current directory

7. Run the ConnectURL.class program with debug mode.  This step will provides more helpful information in troubleshooting the login issue

7.1. Copy the MS SQL Server JDBC JAR file into the same directory as ConnectURL.class

7.2. Copy mssql-jdbc_auth-12.6.2.x64.dll into the same directory as ConnectURL.class.  SAP BODS 4.2SP3PL2 bundled with JDBC MS SQL Server v12.6.2, so I will use this version.  You will download and test based on the version you use

7.3. Runs Java.exe v8 64-bit to run the program.  Uses following syntax:

7.3.1. Command: java -Djavax.net.debug=ssl  --classpath mssql-jdbc-12.6.2.jre8.jar:. ConnectURL

7.4. If you failed, you can test with different scenarios like below:

7.4.1. java.exe 32-bit or 64-bit

7.4.2. java.exe on different version, e.g. 8, 9, 10, 11, all the way to 21

7.4.3. different JDBC JAR file for mssql-jdbc-<JDBC version>.jre8.jar

7.4.4. different javax.net.debug, such as only shows SSL handshake - "java -Djavax.net.debug=ssl:handshake  --classpath mssql-jdbc-12.6.2.jre8.jar:. ConnectURL"

7.4.5. has multiple copy of ConnectURL.class which compiled with different Java.exe version

7.5. Tries different combination so that you fully understand the issue is in

7.5.1. java.exe - 64-bit, distribution of the java.exe, e.g. SAP Java, IBM Java, Oracle Java, AdoptOpenJDK Java, Amazon AWS Java, RedHat Java

7.5.2. mssql-jdbc-<version>.jre8.jar - could be different JDBC version, jre8.jar or jre11.jar

7.5.3. mssql-jdbc_auth-<version>.x64.dll - Microsoft never say whether the DLL version must match JDBC JAR, so you can try different version that bundled with 3rd party software, e.g. SAP BODS 4.3SP3PL3 bundled with mssql-jdbc_auth-12.6.0.x64.dll with mssql-jdbc-12.6.2.jre8.jar (different version)

7.5.4. OS, machine - easy to copy this to any machine, laptop, server, gateway so that you can repeat this test easily

7.5.5. Copy mssql-jdbc_auth-<version>.x64.dll into a dedicated directory, e.g. D:\ReportServer\JDBC\MSSQLServer12.6.2\, and modify PATH to include this directory (must change PATH to points to the directory where this DLL located)

7.5.6. For above PATH, you can replace it by specifying parameter "-Djava.library.path=D:\ReportServer\JDBC\MSSQLServer12.6.2"

8. mssql-jdbc_auth-<version>.x64.dll - Some recommendations to perform more troubleshooting in this section.  Removes mssql-jdbc_auth-<version>.x64.dll from current directory, and place it in different directories based on the application that you work on

8.1. Example #1, SAP IPS/BODS placed it in C:\Windows\system32\ (for 64-bit DLL) and C:\Windows\SysWOW64\ (for 32-bit DLL)

8.2. Example #2, <java JRE>\lib\ext\ directory, which is the recommended directory to store external JAR.  Some developers or 3rd party vendors recommend to put into the application's JAR file, while I recommend to put into dedicated Java directory in its lib\ext for ease of security patching, and upgrade by isolating the Java application from popupar vendor, like MS SQL Server

8.3. Example #3, put it in your own Java JAR's WEB-INF\lib\ directory

8.4. Do not place 32-bit of DLL, e.g. mssql-jdbc_auth-12.8.1.x86.dll into C:\Windows\SysWOW64\.  Window's directory architecture design for this directory is for 32-bit application in 64-bit Windows (in other word, in 32-bit Windows 10, SysWOW64 directory doesn't exists, and you need to put the 32-bit DLL into C:\Windows\system32\ directory).  Java 64-bit application will work with this DLL in SysWOW64, as it is ignoring DLL in 32-bit SysWOW64 directory, even it exits

8.5. Do not place 64-bit of DLL, .g. mssql-jdbc_auth-12.8.1.x64.dll into C:\Windows\system32\ for Windows 64-bit.  This will leads to Java 64-bit failed to work in its Windows authentication, and you will required an OS 

9. By now, you will know the relationship of mssql-jdbc_auth-<version>.x64.dll, 32-bit Java, 64-bit Java, JDBC JAR version, SSL handshake, C:\Windows\system32\ for DLL (if using 64-bit Java.exe), and C:\Windows\SysWOW64\mssql-jdbc_auth-<version>.x86.dll (if using 32-bit Java.exe)

SAP IPS v4.3 - Upgrade from IPV v4.2

Product: SAP Data Services (BODS), Information Platform (IPS)

Version: 4.3

Related Product: SAP Data Services v4.3.x

Overview

This is another cycle of IPS v4.2 -> 4.3 upgrade again, and the same issues happened last time 10+ year ago for IPS v4.1 -> 4.2 is happening again this time.  This is due to no SAP IPS v4.3 upgrade document, and it is based on SAP KB that only those who have paid SAP Support account can see it.

This post is to highlight all the known issues which SAP IPS team think this is the "normal" process that customer should aware of.  My objective is to address the frustration in both IPS v4.3 and Data Services v4.3 upgrade.

Issues Highlight

1. SAP IPS v4.3 fresh installation does not required to enter license key by using InstallIPS.exe (Windows) or InstallIPS (Linux(, but if you perform in-place upgrade, then you need to enter IPS v4.3 license key which SAP document doesn't say (there is no IPS upgrade doc)

2. When doing IPS v4.2 -> v4.3 in-place upgrade, both InstallIPS.exe and setup.exe will not pass the IPS v4.3 license key into the IPS v4.2.  So you must enter SAP IPS v4.3 license key into it after completed IPS v4.3 upgrade

3. SAP KB title will use the word "BIP 4.3" instead of "IPS 4.3" so it is not easy to find SAP KB to tell you that you need to use their license request page to generate IPS v4.3 license key (SAP's desire process)

4. SAP admin who has SAP Support access is not able to see the IPS product in the license key request page in SAP Support homepage (which in another SAP KB for IPS v4.1 to tell you to raise SAP ticket)

5. To leverage the bundled IPS v4.3 license key, you need to fully uninstall IPS v4.2

6. For those who plan to uninstall IPS

6.1 if you have multiple IPS v4.2 patches installed in the machine (Windows only), the Windows uninstall method might break the IPS v4.2 before you fully uninstall all IPS product patches.  So the Windows machine will not be able to completely uninstall IPS v4.2

6.2. if you didn't select "Reset database" during IPS installation, and reuse your IPS v4.2 DB, then InstallIPS.exe installer still won't register the IPS v4.3 license kehy

7. SAP Support actually recommends fresh IPS and BODS installation instead of in-place upgrade. Any in-place upgrade issues will get their recommendation to perform fresh IPS & BODS installation

8. IPS v4.3 license key should be entered in CMC > License Key screen immediately after IPS v4.3 installation.  Some people proceeds with SAP BODS v4.3 installation without entering IPS v4.3 key, and causes IPS 4.3 and BODS 4.3 to be broken

9. After BODS v4.3 is broken, entering IPS v4.3 license key will not automatically fix it.  It only allows IPS daemons to be able to start

10. SAP BODS v4.2 and v4.3's ATL file is not compatible, but SAP DS Designer v4.2 will allow to import v4.3 ATL file.  For Designer v4.3, it will display a warning of incompatibility, but allows user to proceed to import atl

11.. SAP Data Services v4.2 will get a false login failed message when trying to login to BODS v4.3, which often confuse admin and developer that the credential is wrong, or there is a network issue

12. If developers are doing development in BODS v4.2 and v4.3, then it is a length process to clone BODS v4.2 repo to another DB, runs repoman (Linux) to upgrade it, and finally register into CMC v4.3.  This has to repeat many times until all developers moved to v4.3

Manually Getting IPS v4.3 License Key

1. InstallIPS.exe or InstallIPS contains the IPS v4.3 license key even you can't open the executable file

2. Uses Linux to remove all binary data: strings InstallIPS.exe > /tmp/installips_key.txt

3. Uses Linux to display the IPS v4.3 license key: grep "ProductKey" /tmp/installips_key.txt

Reminder:

1. If you IPS v4.2 is currently not healthy, upgrading to v4.3 won't automatic fix a broken IPS

2. If you installed SAP BODS v4.3 over IPS v4.3 without license key, entering IPS v4.3 license key later might not make BODS fully functional.  This is version specific, so I can't tell for all the IPS and BODS patches

Recommendation for Upgrade

Performs fresh IPS and BODS v4.3 installation instead of in-place upgrade.  Takes the opportunity to upgrade your Windows OS, or RHEL OS, MS SQL Server, Oracle, DB drivers.

Recommended procedure:

1. Backup your both IPS v4.2 and BODS v4.2 repositories

2. Creates a new empty BODS repositories to be used for v4.3

3. You can reuse IPS v4.2 repositories, but BODS v4.2 repositories can be re-use (manually register into SAP CMC > Data Services)

4. Perform fresh IPS v4.3 installation using either old or new IPS DB

5. If you are re-using IPS v4.2 DB, then enable checklist "Reset database" in the installer wizard

6. After installation (~ 1 hr), login to CMC, and ensures License Key screen show the license is valid

7. Proceeds with SAP BODS v4.3 installation, but uses the newly created BODS repository DB

8. After SAP BODS v4.3 installation completed, checks in CMC > Data Services to ensure you are seeing 1 repository

9. Run "Repository Manager" (Windows) or repoman (Linux) and choose "Upgrade" option to upgrade each of the BODS v4.2 repositories.  You need to do this multiple round, as you can only specify 1 DB to upgrade its table content v4.2 -> v4.3

10. Login to CMC v4.3 to register each of the BODS v4.2 (upgraded to v4.3 now).  You should not get version conflict

11. Run Server Manager (Windows) or svrcfg (Linux), and register each of the BODS repositories you added to CMC just now

12. Login to SAP DSMC, and generate new password files

13. In SAP DSMC, create new schedules

14. In SAP DSMC (Linux only), create Excel adapter, if you leverage xls file operations in Dataflow

15. In SAP CMC, configures user/group permission, such as allow user to retrieve BODS repositories credential (DS Designer won't show add'l prompt to ask for BODS repository password)

 16. For Windows, if you want to start the Windows service with specific account (service account), then configures Windows Services startup properties

17. If you enable SSL, then repeat the SSL configuration for DB, IPS, Tomcat, BODS, DS Designer

Windows: Installer/Uninstaller error code 2203 when trying to install iTunes

Product: Windows
Version: 10

Overview

When trying to uninstall many software in Windows 10, the uninstallation failed with following error

"The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2203."

Procedure

1. Get more detail error message from Event Viewer

1.1. Press Windows key, and type "Event Viewer" to run "Event Viewer"

1.2. On left panel, navigates to Windows Logs > Application

1.3. Checks right panel, and focus in Source = MSInstaller

1.4. In lower right panel, check under General tab for msiexecdetail message.  In this example, iTunes failed during uninstallation with error code 2203

1.5. Following is displayed in General tab:

Product: iTunes -- The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2203. The arguments are: C:\windows\Installer\inprogressinstallinfo.ipi, -2147286788, 

1.6. Clicks on Details tab to see more detail, which will contain the product ID that we need for iTune software

1.7. Scrolls down to section "In Bytes" and take note of the hex code, which is iTune product ID.  In this screenshot, the Product ID is {E42D1B4D-78C2-4144-8E4D-FA82C53B68A4}

2. Explanation of above error:

2.1. The MS Windows installer/uninstall called program msiexec.exe at the background with product ID {E42D1B4D-78C2-4144-8E4D-FA82C53B68A4}

2.2. The actual command executed was "msiexec.exe  /uninstall {E42D1B4D-78C2-4144-8E4D-FA82C53B68A4}"

2.3. msiexec.exe will write temporary files to %LOCALAPPDATA%\temp\ of current login user, e.g. administrator

2.3.1. If current login user cannot create any files in directory %LOCALAPPDATA%\temp, then the installer will failed with this error.  So, test create a dummy file or directory in %LOCALAPPDATA%\temp\ using "cmd" prompt

2.4. msiexec.exe will write temporary files to %TEMP% as system user.  The value will be from system environment variable, under section "System variables"

2.4.1. Even you have TEMP variable defined under "User variables," system user will only use TEMP value under "System variables"

2.4.2. You can monitor above TEMP directory while running uninstaller/installer to see whether it is creating any temporary file

2.4.3. You cannot run "cmd" as administrator, and type "set" command to display the value of TEMP, because that will still cause TEMP value inherits from current login user

2.4.4. Ensures that the permission for "SYSTEM" in folder properties has Full Control

2.5. msiexec.exe will creates a temporary file in C:\windows\Installer\inprogressinstallinfo.ipi

2.5.1. Monitor directory "C:\windows\Installer\" in real time during uninstallation.and ensures file inprogressinstallinfo.ipi get created

2.5.2. The file will get deleted when the uninstaller failed

2.5.3. You can try to create a text file in above directory to test you can create file using cmd (run as administrator) to ensure there is no permission issue.  If you do, ensures the SYSTEM user has Full Control permission

2.6. You could find in Internet to suggest to disable Windows Virus Defender temporary.  This is assume the utility created temporary files which Windows Defender automatically deleted the temporary files by assuming it is a virus.  If you are working on common software, such as iTune, Acrobat, Notepad++, then you can ignore those suggestion, as it is not related

3. Manually construct the uninstallation command yourself, and enable debug log

3.1. Run notepad.exe to prepare a command to run later which will create a debug log to show us more info

3.2. Type: msiexec.exe  /uninstall {product ID} /l*v <log file location>

3.3. For my iTune, the product ID that we found in Event Viewer is E42D1B4D-78C2-4144-8E4D-FA82C53B68A4}

3.4. I would like to create the new log file in i:\temp\itune_uninstall.txt

3.5. So the final command is: C:\windows\system32\msiexec.exe  /uninstall {E42D1B4D-78C2-4144-8E4D-FA82C53B68A4} /l*v i:\temp\itune_uninstall.txt

3.6. Run cmd.exe as administrator

3.7. Copy-paste the final msiexec.exe command in cmd to start uninstaller for iTune, and create debug log in log file i:\temp\itune_uninstall.txt

3.8. Let the uninstallation failed with error 2203

4. Gets the detail error message for error 2203

4.1. Open debug log created in above step

4.2. In this example, it is i:\temp\itune_uninstall.txt

4.3. Open the log file in Notepad, or Notepad++

4.4. Search for "DEBUG: Error"

4.5. In this iTune example, the error is below

MSI (s) (68:78) [10:15:46:439]: BeginTransaction: Locking Server

MSI (s) (68:78) [10:15:46:440]: Note: 1: 2203 2: C:\windows\Installer\inprogressinstallinfo.ipi 3: -2147287038 

MSI (s) (68:78) [10:15:46:440]: Note: 1: 2203 2: C:\windows\Installer\inprogressinstallinfo.ipi 3: -2147287038 

MSI (s) (68:78) [10:15:46:440]: Server not locked: locking for product {E42D1B4D-78C2-4144-8E4D-FA82C53B68A4}

MSI (s) (68:78) [10:15:46:441]: Note: 1: 2203 2: C:\windows\Installer\inprogressinstallinfo.ipi 3: -2147286788 

Action start 10:15:46: InstallInitialize.

DEBUG: Error 2203:  Database: C:\windows\Installer\inprogressinstallinfo.ipi. Cannot open database file. System error -2147286788

MSI (s) (68:78) [10:15:48:055]: Product: iTunes -- The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2203. The arguments are: C:\windows\Installer\inprogressinstallinfo.ipi, -2147286788, 

MSI (c) (84:8C) [10:15:46:447]: Font created.  Charset: Req=0, Ret=0, Font: Req=MS Shell Dlg, Ret=MS Shell Dlg

The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2203. The arguments are: C:\windows\Installer\inprogressinstallinfo.ipi, -2147286788, 

Action ended 10:15:48: InstallInitialize. Return value 3.

Action ended 10:15:48: INSTALL. Return value 3.

4.6. This error message is longer that the one you see in Event Viewer, or Control Panel > Uninstall software

4.7. The line to focus on will be below:

DEBUG: Error 2203:  Database: C:\windows\Installer\inprogressinstallinfo.ipi. Cannot open database file. System error -2147286788

4.8. If you review other log entries, you will see no entry is referring to TEMP dir, which in my case, it is "I:\TEMP\Windows"

5. This post does not cover Windows 10 system files minor corruption

6. If your Windows 10 has minor corruption, then fix them using following steps:

6.1. Run cmd as administrator

6.2. Run: DISM.exe /Online /Cleanup-image /Scanhealth

6.3. Run: DISM.exe /Online /Cleanup-image /Restorehealth

7. A quick workaround will be to change System environment variables below:

7.1. TEMP=C:\temp

8. If you are stuck, repeat the installer/uninstaller while monitor following dir to ensure it has the permission.  Don't simply follows many suggestions in Internet to modify directories permission, if you see it is able to create temporary files

8.1. %LOCALAPPDATA%\temp - this is the value of users environment variable

8.2. %temp% - this is the value of the global environment variable, not users environment variable

Oracle RDBMS: Manual Archive Log Clean Up with FAST_RECOVERY_AREA

Product: Oracle RDBMS

Version: 11.1.0 - 21c (19.3.x)

OS: Windows, Linux, HP-UX, AIX

The introduction of FAST_RECOVERY_AREA has been more than 10 years, which automatically housekeep archive logs.  This allow DBA to indirectly delete physical archive log files resides in the OS, even they do not have physical OS access.

However, for those DBA or SA who have access to the OS, or managed the storage and DB, it is often required to cleanup archive log files in OS first, then in Oracle's FAST_RECOVERY_AREA later.

This post show how to perform the manual clean up of it

1. Verify the usage of FAST_RECOVERY_AREA to get a breakdown what used up all the storage

col file_type format a40
set pagesize 50
set line 110

select * from v$recovery_area_usage;

2. For the purpose of this post, this is covering scenario where FILE_TYPE=ARCHIVED LOG.  So the SQL will be

select * from v$recovery_area_usage where FILE_TYPE='ARCHIVED LOG';

3. If archive log have not backup, and you want to backup the archive log to disk, then use following RMAN command to backup to disk, and delete from the disk

RMAN target /

backup archivelog all delete input;

4. If archive log already been deleted, then you need to sync up the status with Oracle DB.  Uses this RMAN command to sync up the status of all the archive log files

RMAN target /

crosscheck archivelog all;

5. After FAST_RECOVERY_AREA detected the archive log files are deleted, then you can run RMAN command to delete it from FAST_RECOVERY_AREA's internal table,  This does not delete archive log which present (no deleted) in FAST_RECOVERY_AREA

RMAN target /

delete noprompt expired archivelog all;

Fresh Tomato Custom Router Firmware: Blocks Incoming Hacker IP

Product: Fresh Tomato Custom Router Firmware

Version: Firmware 2024.2 K26MIPSR2_RTAC USB AIO-64K

Router Model: ASUS RT-AC66U

Overview

Testing out custom firmware in ASUS router above using build-in NGINX web server on custom http port.  Trying to figure out how this custom firmware can blocks incoming hacker by their IP. 

Web server log is able to log visitors' IP, and the URL request URI.  So I can figure out the attackers' IP, which I considered as hacker.

This gives me an idea how aggressive hacker community and search engine will send valid and invalid attacks to a random web site.

I am leveraging build-in software firewall which configures using command iptables, configuration file /etc/iptables, web GUI called "Access Restriction" that stores in NVRAM (access using command "nvram get rrule0" and "nvram set rrule0").  Research of iptables chains and rules found that the menu "Access Restriction" is only restrict WAN to LAN, and doesn't restrict build-in NGINX web server, which is not consider in the LAN subnet.  So this post document how to configure it to leverage "Access Restriction."

Initially, I uses following methods to configures the software firewall to block hackers' IP, but it is not persistent:

1. /etc/iptables configuration file, with scheduler

2. Using iptables command, with scheduler

Every few hours, Fresh Tomato seems to replace the above 2 files and configuration with the value in web GUI, which later found the setting is stored in NVRAM.  So I lost all my firewall setting, and hacker can attack my NGINX web server again.  It took me few days to figure out the web GUI menu called "Access Restriction" will configures iptables, and its setting is permanently stores in NVRAM in addition of above 2 files/configuration, even after reboot or power cycle.

Google search is not returning any info about blocking hacking using Fresh Tomato on build-in NGINX web server, but finally I found following Fresh Tomato documentation that gives me a clue how to configure NVRAM (so that I can blocked hacker by using NGINX access.log file):

• https://wiki.freshtomato.org/doku.php/access_restrictions

Web GUI's "Access Restriction" configuration resides in NVRAM, and needs to set using "nvram set [internal rule name]" command.  Its current configuration can be retrieve using command "nvram get [internal rule name]" command.  The internal rule name starts with rrule0, follows by rrule1, rrule2, and each can be enabled and disabled separately.  Moreover, you can set the time start/end for every day, and the router will automatically trigger iptables command by the schedule.

In this post, I will assume there is no rule set, so I will use rrule0.  This indirectly will create iptables chain name "rdev00."

Every time the "Access Restriction" configuration is changed, it will reload the configurations from NVRAM, as well as certain network related configurations in the router.  I need to adds custom iptables command into "Scripts" section called "Firewall" as well to modify OOTB iptables chain to apply "Access Restriction" configuration in addition of LAN traffic.

Procedure

1. Creates rule #1 with:

1.1. Rule name: Blocks Hacker 2024-07

1.2. Blocks IP: 103.246.195.85, 5.183.103.248, 118.123.105.93

2. Connects to the LAN port using cable, or WiFi

3. Assumes WiFi router's IP is 192.168.100.1, then uses ssh utility such as PuTTY to ssh to port 192.168.100.1

4. Login as root user

5. At the "#" shell prompt, creates a new rule 1 with internal rule name rrule0

5.1. nvram set rrule0="1|-1|-1|127|104.37.174.65>117.146.152.81>118.123.105.93|||0|Blocks Hacker 2024-07"

5.2. Expect output:

root@rt-ac66u:~# nvram set rrule0="1|-1|-1|127|104.37.174.65>117.146.152.81>118.123.105.93|||0|Blocks Hacker 2024-07"

root@rt-ac66u:~#

6. Login to WiFi routner's web GUI to check. URL: http://192.168.100.1

7. On left menu, lick on menu item "Access Restriction" to display all the rules

8. You will see the first rule call "Blocks Hacker 2024-07" on the right panel, and as the 1st rule in the list

9. Following is the expected output

10. The current "Access Restriction" are:

10.1. Only in configuration of NVRAM, but not saved yet

10.2. Current setting will lost after reboot, or power lost

10.3. Router has not run iptables to activate the setting.  There is delay of about 1 hr

11. To make this setting permanent after reboot, enter: # nvram commit

12. Waits for about 1 hr, and checks firewall to see the IP are blacklisted.  Command: iptables -L rdev00 -n

13. Expected output:

Chain rdev00 (1 references)

target     prot opt source               destination

DROP       all  --  104.37.174.65        0.0.0.0/0

DROP       all  --  117.146.152.81       0.0.0.0/0

DROP       all  --  118.123.105.93       0.0.0.0/0

14. If you want to force the setting to activate immediately, runs these 2 commands:

# nvram set action_service=restrict-restart
# kill -USR1 1

15. You will see following in /var/log/messages:

# tail /var/log/messages | grep "Activating rule"

Jul 14 11:23:31 rt-ac66u user.info rcheck[1867]: Activating rule 0

16. Lastly, modify iptables firewall's INPUT chain to make web server port from the web GUI

16.1. In router's web GUI, on left panel, navigates to Administration > Scripts

16.2. Click on tab Firewall

16.3. Enter following command to create a new custom chained called "hacker" and replaces exiting rule for NGINX TCP port 85 to this custom chain:

logger "webUI-Scripts-Firewall Configures NGINX to follow Access Restriction"

iptables -N hacker

iptables -A hacker -j restrict

iptables -A hacker -j logaccept || iptables -A hacker -j ACCEPT

iptables -R INPUT 15 -p tcp -m tcp --dport 85 -j hacker

16.4. Sample screenshot

Maintenance

You might to perform other maintenance of the firewall.

1. Removes/delete the rule, e.g. removes rule 10: nvram unset rrule10

2. Delete firewall rule 10 (rdev09):

2.1. iptables -D restrict -j rdev09

2.2 .iptables --flush rdev09

2.3. iptables -X rdev09

3. Checks current actively blocked IP for rule 1 (chain name rdev00) in memory: iptables -L rdev00 -n

4. Checks current block IP for rule 1 in /etc/iptables configuration file: grep rdev00 /etc/iptables

5. Forces Access Restriction to activate now:

5.1. Command #1: nvram set action_service=restrict-restart

5.2. Command #2: kill -USR1 1 (after above completed)

5.3. Command #3: nvram get action_service (if completed, command will not return any value. If not completed, then returns "restricgt-restart")

6. Uses "top" Linux command to monitor the CPU usage with the new firewall rule. You will need to reduces number of blocked IP, if encountered CPU contention, or buy a faster/newer WiFi router

Known Limitation

1. Web GUI's menu Administration > Logging > Syslog > Connection Logging to control following won't always going to capture above traffic:
1. Inbound: If blocked by Firewall
2. Outbound: If blocked by Firewall
3. Reason: above logic/commands need to modify to use iptables chain "logaccept" and "logdrop" which will make it longer to write (updated to allows logging to syslog /var/log/messages)
2. iptables firewall configuration is based on my configuration of Fresh Tomato on ASUS RT-AC66U, version 2024.2.  It might needs further modification
3. When number of blocked IP is getting long (more than 100), the web GUI's menu "Access Restriction" won't be able to delete or modify rule #1, and will consistently prompt to delete IPs (then it will work)
1. Fresh Tomato firmware seems to enforce a restriction to limit the number of entries inside the rule
2. This error can be ignore, and doesn't affect the IP blocking

Linux: How to get Parent Process (PPID)?

Product: Linux
Version: All
CPU: All

Overview

Many Linux forum and documentation often base on full Linux distribution, such as RedHat, Fedora, Ubuntu, SuSE, MacOS, and left out Linux installed in single box computer (SBC) such as WiFi router, smartphone, tablet, Raspberry Pi, Nano Pi, car's head unit, webcam, etc.  This leads to some standard UNIX design and operation slowly forgotten and replaces with newer commands.

During day-to-day troubleshooting, sometimes in addition of process ID (PID), it is required to check parent process ID (PPID) as well.  For example, what spawn the process, does the parent process terminated, etc. 

Procedure

Most Linux installed in SBC has a reduced version of Linux, such as BusyBox which is ~ 600KB used in DD-WRT and FreshTomoto firmware used in WiFi router.

These smaller footprint of Linux often bundled with "ps" command which can display limited info of a process PID.  For example, it does not display parent PID (PPID).

You can use following command to get the PPID using /proc virtual directory.

For example, assumes the PID is 2914:

root@rt-ac66u:/tmp/home/root# ls -l /proc/2914/status

-r--r--r--    1 root     root             0 Jun 28 09:20 /proc/2914/status

root@rt-ac66u:/tmp/home/root# cat /proc/2914/status

Name:   dropbear

State:  S (sleeping)

SleepAVG:       95%

Tgid:   2914

Pid:    2914

PPid:   1

TracerPid:      0

Uid:    0       0       0       0

Gid:    0       0       0       0

FDSize: 32

Groups:

VmPeak:     1156 kB

VmSize:     1156 kB

VmLck:         0 kB

VmHWM:       256 kB

VmRSS:       256 kB

VmData:      348 kB

VmStk:        84 kB

VmExe:       304 kB

VmLib:       384 kB

VmPTE:        12 kB

Threads:        1

SigQ:   0/2047

SigPnd: 00000000000000000000000000000000

ShdPnd: 00000000000000000000000000000000

SigBlk: 00000000000000000000000000000000

SigIgn: 00000000000000000000000000001000

SigCgt: 00000000000000000000000000024402

CapInh: 0000000000000000

CapPrm: 00000000fffffeff

CapEff: 00000000fffffeff

To display the PPID alone, then uses following command:

root@rt-ac66u:/tmp/home/root# grep PPid /proc/2914/status

PPid:   1

Virtual directory /proc exists as very basic design of UNIX, so it always available in all UNIX, while "ps" command might has stripped off functionality.

Above example is captured from FreshTomato firmware in WiFi router