Guidewire, SAP, Oracle, UNIX, Genesys Technology Blog

PowerShell - Getting Remote MS SQL Server 2017 SSL Certificate Using Low Level Network Communication

2026-09-25T11:48:50.275-04:00

Product: Microsoft MS SQL Server
Version: 2017

Overview

There are few scenarios where you would like to download the SSL certificate and save as file while working with MS SQL Server client or server programs. I come into situation where the old version of IBM Cognos Analytics required to add remote MS SQL Server's self-signed SSL cert into its own Java keystore. The challenge is that the MS SQL Server does not has any self-signed SSL cert, and it dynamically creates a SSL cert (MS called it as fallback SSL cert) in memory during MS SQL Server startup. So it is not possible to save the certificate file.

Following are few more scenarios where you might want to save the remote MS SQL Server's SSL cert:

MS SQL Server DBA does not has Windows admin privilege to run Windows Certificate Manager (certmgr.msc) to save the SSL cert
Windows admin is not familiar with Windows Certificate Manager (certmgr.msc) and not able to provide the SSL cert
MS SQL Server DBA does not know anything about SSL fallback cert, when MS SQL Server does not has any SSL cert

The MS SQL Server API Microsoft.Data.SqlClient in any programming, e.g. Visual C#, Visual C++, Java, Python, Perl, is not possible to obtain the SSL cert.

openssl.exe utility has drop MS SQL Server TDS (Tabular Data Stream) support, so it is not possible to use this utility to get the SSL cert.

As the result, I develop a PowerShell program that uses low level TCP network communication that can download SSL certificate from local or remote MS SQL Server through TCP/IP protocol.

Technical Design

Low level TCP programming uses PowerShell System.Net.Sockets.TcpClient. MS SQL Server login is always SSL encrypted. It contains 2 phase, which is called PRELOGIN and LOGIN7. During PRELOGIN phase, MS SQL Server will provide SSL certificate to the DB client program, which then can proceeds with LOGIN7 phase that will login to MS SQL Server with username and password (assumes DB authentication, not Windows user).

The most challenging part is research MS SQL Server PRELOGIN protocol, as this is poorly documented by Microsoft. You can find the official document from learn.microsoft.com below:

Title: Open Specifications > Protocols > SQL Server Protocols > Technical Documents > [MS-SSTDS]: Tabular Data Stream Protocol Version 4.2 > Messages > Message Syntax > Packet Header Message Type Stream Definition > PRELOGIN

Ref: https://learn.microsoft.com/en-us/openspecs/sql_server_protocols/ms-sstds/75e62f67-f057-4d46-82b3-6920fe0ebada

The PowerShell program will make use of hex and binary to convert the PRELOGIN data structure back and forte with remote MS SQL Server server.

While reading MS' document, we need to ignore any document that tell encryption is optional, as well as TLS v1.2 is optional. In fact, many versions of MS SQL Server have long enforced TLS v1.2, and encryption during login. So there are many obsolete content which confuses reader like us. The login sequence is called as Tabular Data Stream with short name of TDS. My program is written for MS SQL Server 2017, which is using TDS v7.4, but newer MS SQL Server uses TDS v8.0. So this program might not work with newer MS SQL Server due to a change in PRELOGIN phase.

My program set the encryption as 0x01, which in MS' doc called "Encryption is available and on." Again, all MS SQL Server support SSL cert, so you can safely accept encryption is available.

Some variables in my program call "stream" which represent TCP network communication. At the beginning of the low level TCP network connection, a variable of NetworkStream is created against TcpClient connection. This is to facilitate back and fort communication with remote MS SQL Server.

Soon after open TCP connection, my program will send a static PRELOGIN to remote MS SQL Server to tell it that my program needs to login. This part of the communication is not encrypted, and in clear text. Don't simply change this static PRELOGIN array value, as 2 of the bytes (2 array elements) contains number of bytes inside this array. You can refer to MS doc for exact array element, if you want to change this array.

After that, MS SQL Server will reply back to tell my program its MS SQL Server version, whether it supports encryption (it will be always supported), number of bytes it will send back. My program will retrieve this reply (MS doc called message or message stream) using 2 separate steps (command NetworkStream.Read()). The reason is because the 1st part is a constant 8 bytes, which I called "payload header" while MS doc implies it is payload as well, with short name of PL. My program then find out number of bytes remote MS SQL Server going to send to me, and in the 2nd part, my program will read the remaining reply (message stream). I called the 2nd part of it as "prelogin payload option token" and "prelogin payload data." MS doc is not clear about how they called them. For my program, I mainly determine the remote MS SQL Server will tell my program that it accept my PRELOGIN request, and it does support SSL encryption. This is very important as SSL cert will only be send to my program if it replied that it supports SSL encryption. This will double confirm new MS SQL Server standard that LOGIN7 is always SSL encrypted.

The next part is the most challenging part as I need to continue to talk to MS SQL Server using TDS header structure, while enforcing SSL TLS v1.2. For this part, I can't hard code the static array like earlier as I need to call System.Net.Security.SslStream.AuthenticateAsClient which will indirectly calls System.IO.Stream over TCP. So there is an extra class called TdsTlsStream that helps me to perform read() and write() over the TCP network communication. Inside this class, in read() function, it will read first 8 bytes to determine network packet length, and in write() function, it will create extra 8-bytes packets to insert before sending over the data to remote MS SQL Server.

System.Net.Security.SslStream.AuthenticateAsClient is the main call that will obtain SSL certificate from remote MS SQL Server. Anything that is wrong in the program will leads to timeout, then remote MS SQL Server will close the network connection. If it success, then the program will be able to extract the SSL certificate using System.Security.Cryptography.X509Certificates.X509Certificate2.

You can further modify the program to add following line to save the SSL certificate as a file locally:

E.g. Export-Certificate -Cert $<yr variable name> -FilePath db_certificate_DER_binary.cer -Type CERT

Above command will create a new SSL certificate file called db_certificate_DER_binary.cer in current directory

PowerShell Program

Following is the PowerShell program

[CmdletBinding()]

param()

# Define your SQL Server instance details, default port 1433

$SqlServer = "your-ms-sql-server-hostname-or-IP"

$SqlPort = 20277

# Enforce TLS 1.2 for the handshake protocol selection

$TlsVersion = [System.Security.Authentication.SslProtocols]::Tls12

# --- SQL Server Pre-Login Handshake Protocol Payload ---

# This standard 8-byte TDS header tells SQL Server a client login attempt is starting.

[byte[]]$TdsPreLoginPacket = 0x12, 0x01, 0x00, 0x2F, 0x00, 0x00, 0x01,0x00, 0x00, 0x00, 0x1A, 0x00, 0x06, 0x01,0x00, 0x20,0x00, 0x01, 0x02, 0x00, 0x21, 0x00, 0x01,0x03, 0x00, 0x22, 0x00, 0x04, 0x04, 0x00,0x26, 0x00,0x01, 0xFF, 0x09, 0x00, 0x00, 0x00, 0x00,0x00, 0x01, 0x00, 0xB8, 0x0D, 0x00, 0x00,0x01

# Create a TCP Client to connect to the SQL port

$TcpClient = New-Object System.Net.Sockets.TcpClient

try {

$TcpClient.Connect($SqlServer, $SqlPort)

$TcpClient.SendTimeout = 10000;

$TcpClient.ReceiveTimeout = 10000;

$NetworkStream = $TcpClient.GetStream()

$NetworkStream.Write($TdsPreLoginPacket, 0, $TdsPreLoginPacket.Length)

# 3. Read the server's TDS Pre-Login Response Header

$responseHeader = New-Object byte[] 8

$bytesRead = $NetworkStream.Read($responseHeader, 0, $responseHeader.length)

if ($bytesRead -lt $responseHeader.length) {

Write-Error "PRELOGIN - $bytesRead bytes read, but expects 8 bytes"

throw "Failed to read a valid PRELOGIN TDS header response"

}

$respPayloadLength = ($responseHeader[2] -shl 8) + $responseHeader[3] - $responseHeader.length

Write-Host "--- TDS HEADER RECEIVED ---" -ForegroundColor Cyan

Write-Host "Raw Bytes Header: " ($responseHeader -join ' ')

Write-Host "Remaining Payload Length: $respPayloadLength bytes" -ForegroundColor Cyan

# Continue to read the token data and data

if ($respPayloadLength -gt 0) {

$responsePayload = New-Object byte[] $respPayloadLength

$bytesRead = $NetworkStream.Read($responsePayload, 0, $respPayloadLength)

$intEndofTokenHeader = [array]::IndexOf($responsePayload, [byte]0xff)

$intEndofTokenData = $intEndofTokenHeader + 1

Write-Host "Finished reading payload: $respPayloadLength bytes" -ForegroundColor Cyan

Write-Host "Offset of FF terminator: " $intEndofTokenHeader -ForegroundColor Cyan

Write-Host "Raw Bytes: " ($responsePayload -join ' ') -ForegroundColor Gray

Write-Host "Raw Bytes Token Header: " ($responsePayload[0..$intEndofTokenHeader] -join ' ') -ForegroundColor Gray

Write-Host "Raw Bytes Token Data: " ($responsePayload[$intEndofTokenData..($respPayloadLength - 1)] -join ' ') -ForegroundColor Gray

}

$isServerEncrypted = 0

$posPayload = 0

while ($posPayload -lt $intEndofTokenHeader) {

$plOptionType = $responsePayload[$posPayload]

Write-Host "PRELOGIN respond header - Position $posPayload=$plOptionType"

# Exit loop if reach header terminator character 0xFF

if ($plOptionType -eq 0xFF) {

Write-Host "PRELOGIN respond header - Reached end of header"

break

}

elseif ($plOptionType -ne 1) {

$posPayload += 5

Write-Host "PRELOGIN respond header - Skipping 5 bytes to offset $posPayload"

continue

}

# Calculate the address of encryption byte

$valueOffset = [System.BitConverter]::ToUInt16([byte[]]@($responsePayload[$offset + 2], $responsePayload[$offset + 1]), 0)

Write-Host "PRELOGIN respond header - Found encryption byte in offset address $posPayload with value $valueOffset"

$isServerEncryptedRaw = $responsePayload[$valueOffset]

$isServerEncrypted = $isServerEncryptedRaw -band 0x0F

Write-Host ("PRELOGIN respond header - offset $valueOffset's encryption byte value = {0} (0x{0:X2}) -> {0} (0x{1:X2})" -f $isServerEncrypted,$isServerEncrypted ) -ForegroundColor Cyan

break

}

Write-Verbose "Entryption is supported, so it is possible to get SSL cert. $isServerEncrypted is not 0 means DB server will perform encryption"

Write-Verbose "Setup TLS stream"

$tlsStreamToWrap = [TdsTlsStream]::new($NetworkStream)

# Wrap the network stream in an SslStream object for the TLS handshake

# We pass a callback to automatically trust self-signed SQL dev certificates

Write-Verbose "Setup SSL stream inside TLS stream"

$SslStream = New-Object System.Net.Security.SslStream($tlsStreamToWrap, $false, {

param($sender, $certificate, $chain, $sslPolicyErrors)

# Always return true to capture the certificate even if untrusted/self-signed fallback

return $true

})

Write-Host "SslProtocol negotiated #1: $($sslStream.SslProtocol)"

# Authenticate as client to trigger the handshake

Write-Verbose "Specified SSL to use $TlsVersion, and obtain remote DB server's SSL cert"

$SslStream.AuthenticateAsClient($SqlServer, $null, $TlsVersion, $false)

Write-Host "SslProtocol negotiated #2: $($sslStream.SslProtocol)"

# Extract the server certificate

$ServerCertificate = [System.Security.Cryptography.X509Certificates.X509Certificate2]($SslStream.RemoteCertificate)

if ($ServerCertificate) {

Write-Host "`n=== SQL Server TLS 1.2 Certificate Details ===" -ForegroundColor Green

$ServerCertificate | Format-List Subject, Issuer, SerialNumber, Thumbprint, NotBefore, NotAfter

# Verify if it is the fallback certificate

if ($ServerCertificate.Subject -like "*SSL_Self_Signed_Fallback*") {

$Host.PrivateData.VerboseForegroundColor = 'Cyan'

Write-Verbose "This is the internal SQL Server Self-Signed Fallback Certificate, so it is in RAM, and will change after DB server start/restart/reboot"

}

} else {

Write-Warning "Successfully connected with TLSv1.2, but no remote certificate was returned by the server."

}

} catch {

$lineText = $_.InvocationInfo.Line

$lineNumber = $_.InvocationInfo.ScriptLineNumber

$position = $_.InvocationInfo.PositionMessage

Write-Host "❌ Error occurred on line $lineNumber" -ForegroundColor Red

Write-Host "Code block: $lineText" -ForegroundColor Yellow

Write-Host "Position details:`n$position"

} finally {

# Properly close and dispose of connections

if ($SslStream) { $SslStream.Dispose() }

if ($TcpClient) { $TcpClient.Close(); $TcpClient.Dispose() }

}

class TdsTlsStream : System.IO.Stream {

[System.IO.Stream]$InnerStream

[int]$PayloadLength = 0

TdsTlsStream([System.IO.Stream]$InnerStream) {

$this.InnerStream = $InnerStream

}

[bool] get_CanRead() { return $this.InnerStream.CanRead }

[bool] get_CanWrite() { return $this.InnerStream.CanWrite }

[bool] get_CanSeek() { return $this.InnerStream.CanSeek }

[Int64] get_Length() { return $this.InnerStream.Length }

[Int64] get_Position() { return $this.InnerStream.Position }

[void] set_Position([Int64]$Value) { $this.InnerStream.Position = $Value }

[int] get_ReadTimeout() { return $this.InnerStream.ReadTimeout }

[int] get_WriteTimeout() { return $this.InnerStream.WriteTimeout }

[void] Flush() { $this.InnerStream.Flush() }

[Int64] Seek([Int64]$Offset, [System.IO.SeekOrigin]$Origin) { return $this.InnerStream.Seek($Offset, $Origin) }

[void] SetLength([Int64]$Value) { $this.InnerStream.SetLength($Value) }

[int] Read([byte[]]$Buffer, [int]$Offset, [int]$Count) {

# Removes TDS header before setting the network Buffer

if ($this.PayloadLength -eq 0) {

$tlsHeader = [byte[]]::new(8)

$read = 0

while ($read -lt 8) {

$read += $this.InnerStream.Read($tlsHeader, 0, 8)

}

$lengthBeforeHeader = [System.BitConverter]::ToUInt16([byte[]]@($tlsHeader[3], $tlsHeader[2]), 0)

$lengthBeforeHeader -= 8

$this.PayloadLength = $lengthBeforeHeader

}

if ($Count -gt $this.PayloadLength) {

$Count = $this.PayloadLength

}

$read = $this.InnerStream.Read($Buffer, $Offset, $Count)

$this.PayloadLength -= $read

return $read

}

[void] Write([byte[]]$Buffer, [int]$Offset, [int]$Count) {

$newPayload = $this.GenerateTdsHeader($Buffer, $Offset, $Count)

$this.InnerStream.Write($newPayload, 0, $newPayload.Length)

}

[byte[]] GenerateTdsHeader([byte[]]$Payload, [int]$Offset, [int]$Count) {

# The length is big endian encoded so it is inserted in reverse order

$lengthBytes = [System.BitConverter]::GetBytes([uint16]($Count + 8))

$newPayload = [byte[]]::new(8 + $Count)

$newPayload[0] = 0x12 # Type - Pre-Login

$newPayload[1] = 0x01 # Status - End of message (EOM)

$newPayload[2] = $lengthBytes[1]

$newPayload[3] = $lengthBytes[0]

$newPayload[4] = 0 # SPID

$newPayload[5] = 0 # SPID

$newPayload[6] = 0 # PacketID

$newPayload[7] = 0 # Window

[System.Array]::Copy($Payload, $Offset, $newPayload, 8, $Count)

return $newPayload

}

SAP Data Services Web Service (SOAP) Calling - Example

2026-04-01T14:29:00.004-04:00

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

Overview

SAP BODS (Data Services) has DSMC browser GUI which offers web service functionality. This allows external scheduler to easily integrate with it.

This blog post is going to show using PowerShell Invoke-WebRequest, so that you can see its SOAP communication in low level. For experience person, it should be easy to figure out how to configure the external job scheduler to use BODS WSDL 2.1 web service (SOAP) call.

For this example, I'm using following dummy value to illustrate

BODS URL: http://localhost:8080/DataServices/servlet/webservices?ver=2.1
Server hostname: localhost
Authentication Method: Build in Enterprise
Username: administrator
Password: hidden_hash_value
Job Server local repository name: LOCAL_REPO_DEV1
Job ID: 2
Going to simulate the OOTB web service call Get_BatchJob_Status, which will return 2 values of whether the web service call success or not (0=success), as well as job status in string of Running/Succussed/Error/Warning

BODS doc: https://help.sap.com/docs/SAP_DATA_SERVICES/ab33122a997f40d89e340549ff0bced8/5748962b6d6d1014b3fc9283b0e91070.html

For full SAP BODS web service reference doc, see https://help.sap.com/docs/SAP_DATA_SERVICES/ab33122a997f40d89e340549ff0bced8/574996556d6d1014b3fc9283b0e91070.html

Using PowerShell Invoke-WebRequest to Call BODS Web Service

1. Login

$headers1 = @{"SOAPAction" = "function=Logon"}

$soapBody1 = @"

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://www.businessobjects.com/DataServices/ServerX.xsd">

<soapenv:Header/>

<soapenv:Body>

<ser:LogonRequest>

<username>administrator</username>

<password>hidden_hash_value</password>

<cms_system>localhost</cms_system>

<cms_authentication>secEnterprise</cms_authentication>

</ser:LogonRequest>

</soapenv:Body>

</soapenv:Envelope>

$response1 = Invoke-WebRequest -UseBasicParsing -Method Post -ContentType "text/xml;charset=UTF-8" -Headers $headers1 -Body $soapBody1 http://localhost:8080/DataServices/servlet/webservices?ver=2.1

[xml]$xmlResponse1 = $response.Content

$dsmc_SessionID = $xmlResponse1.Envelope.Body.session.SessionID

$dsmc_SessionID

2. Call Get_BatchJob_Status

$headers2 = @{"SOAPAction" = "jobAdmin=Get_BatchJob_Status"}

$soapBody2 = @"

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://www.businessobjects.com/DataServices/ServerX.xsd">

<soapenv:Header>

<ser:session>

<SessionID>$dsmc_SessionID</SessionID>

</ser:session>

</soapenv:Header>

<soapenv:Body>

<ser:batchJobStatusRequest>

<runID>2</runID>

<repoName>LOCAL_REPO_DEV1</repoName>

</ser:batchJobStatusRequest>

</soapenv:Body>

</soapenv:Envelope>

$response2 = Invoke-WebRequest -UseBasicParsing -Method Post -ContentType "text/xml;charset=UTF-8" -Headers $headers2 -Body $soapBody2 http://localhost:8080/DataServices/servlet/webservices?ver=2.1

[xml]$xmlResponse2 = $response2.Content

$xmlResponse2.Envelope.Body.batchJobStatusResponse

localtypes returnCode status
---------- ---------- ------
http://www.businessobjects.com/DataServices/ServerX.xsd 0 error

Using PowerShell New-WebServiceProxy to Call BODS Web Service

This is using SOAP web service call.

$url_dsmc = "http://localhost:8080/DataServices/servlet/webservices?ver=2.1&wsdlxml"

# First call to BODS web service server to get the wsdl

$response1 = New-WebServiceProxy -Uri $url_dsmc -Namespace dsmc_namespace

$dsmc_namespace = $dsmc.GetType().Namespace

$dsmc_conn_ops = New-Object -TypeName "$dsmc_namespace`.Connection_Operations"

$param = New-Object "dsmc_namespace.LogonRequest"

$param.username = "administrator"

$param.password = "hidden_hash_value"

$param.cms_system = "localhost"

$param.cms_authentication = "secEnterprise"

# 2nd call - Triggers logon, and it will return Session ID
# Stores the session_id for next call

$session_id = $dsmc_conn_ops.Logon($param)

# Next is to check Job status

# Local repo: LOCAL_REPO_DEV1

# Job's run ID 2

$job_request = New-Object "dsmc_namespace.batchJobStatusRequest"

$job_request.repoName = "LOCAL_REPO_DEV1"

$job_request.runID = 2

$dsmc_Batch_Job_Admin = New-Object -TypeName "$dsmc_namespace`.Batch_Job_Admin"

$dsmc_Batch_Job_Admin_namespace = $dsmc_conn_ops.GetType().Namespace

$dsmc_Batch_Job_Admin.sessionValue = $session_id

# 3rd time calling BODS SOAP web service

$dsmc_Batch_Job_Admin.Get_BatchJob_Status($job_request)

Output

returnCode status

---------- ------

0 error

Analysis

The parameter and web service methods required to referring to BODS web service wsdl. For example:

New-Object "dsmc_namespace.LogonRequest" - wsdl contains "<xsd:element name="LogonRequest">" with complexType, and 4 string parameters of username , password , cms_system , cms_authentication
Logon() - wsdl indicated that the pass-in parameter is call "LogonRequest" directly under the root. It does not belongs to any class (PowerShell), or wsdl:binding (wsdl)
PowerShell needs to call New-Object -TypeName $dsmc_namespace`.<wsdl:binding> to switch between 5 different binding of Batch_Job_Admin, Repo_Operations, Batch_Jobs, Connection_Operations, Realtime_Service_Admin (default)

FreeNAS, TrueNAS, FreeBSD - Shrink ZFS File System

2025-12-21T22:12:00.003-05:00

Product: Debian distribution, TrueNAS, FreeNAS

Version: Any version which uses ZFS File System

Overview

ZFS always create mount point as volume group equivalent volume even you assigns 1 partition to a mount point. In Debian, TrueNAS or FreeNAS, it always creates as volume group (ZDEV), regardless you assigns entire hard drive (1 device) or 1 partition to it.

ZFS does not has build-in functionality to shrink partition, but we can remove partition after adding another one. So the strategy to shrink partition is below:

In the first disk, which contains the partition that we would like to shrink, finds out how much space is used by the mount point or partition. For example, let's assume 8GB is used in a partition which is 500GB
Locates another disk or partition which can hold all the data used by above partition. We will later leverage ZFS feature to auto-copy its content over to this new partition. For simplicity, let's call this as 2nd disk
In the 2nd disk, creates a partition which is bigger than 8GB. For example, creates a new 16GB partition
Unmount the mount point to avoid any user copying any data into the partition which we are going to shrink
Adds the new 16GB partition from 2nd disk into the same partition (volume, or ZDEV) which we would like to shrink. Don't worry that we are making the volume 500GB + 16GB larger. ZFS will not copy anything into this volume since we unmount it
Detach the partition we would like to shrink from the volume (ZDEV) to force ZFS to copy all the used data from this partition into 2nd disk's new partition (16GB partition size)
Now, you can delete and re-create the partition with smaller size in 1st disk
Adds back this partition into the volume or ZDEV. Do not mount the mount point yet
Detach the 2nd disk's 16GB partition from the volume, so what ZFS will copy all the content to the newly added but smaller partition
This complete the partition shrink

Procedure

Assumes the partition we would like to shrink is called boot-pool (FreeNAS' name for boot volume). It contains partition sda3 inside disk 1 (device name called sda). The 2nd partition name that going to temporary add is called vg00/zvol0.

Checks how much space is used in boot-pool

Command: zpool list -v boot-pool

% zpool list -v boot-pool
NAME SIZE ALLOC FREE CKPOINT EXPANDSZ FRAG CAP DEDUP HEALTH ALTROOT
boot-pool 928G 3.18G 925G - - 0% 0% 1.00x ONLINE -
sda3 931G 3.18G 925G - - 0% 0.34% - ONLINE

Above shown 3.18GB is used. It contains only 1 partition called sda3 in disk sda
Locates a device which is bigger than 3.18GB. Let's say 8GB

Option 1: If TrueNAS, then create a new Zvol from any of the VDEV (storage pool)
Option 2: Creates a new partition sdb1, sdb2, or sdb3 from 2nd disk (or 3rd, 4th, etc)

For this post, I will use TrueNAS to create new Zvol called vg00/zvol0 (storage pool vg00, Zvol/dataset zvol0)
Adds vg00/zvol0 into boot-pool

Command: zpool add boot-pool vg00/zvol0

Detach sda3 from boot-pool

Command: zpool remove boot-pool sda3
This command will take some time as it is going to copy 3.18GB into vg00/zvol0

Re-create partition sda3 to smaller size, e.g. 100GB

Command: parted sda
Refers to parted command on how to delete partition using menu driven command
Refers to parted command on how to create partition using menu driven command. It must be larger than 3.18GB to hold all the data

Adds sda3 back into boot-pool

Command: zpool add boot-pool sda3

Detach vg00/zvol0 from boot-pool

Command: zpool remove boot-pool vg00/zvol0
This command will take some time as it is going to copy 3.18GB into sda3

OpenVPN: Windows RDC Remote Desktop Configuration

2025-08-01T19:40:00.002-04:00

Product: OpenVPN, Windows OS
Software: MS Remote Desktop Services
Version: all

Sharing my experience of configuring OpenVPN that bundled with ASUS router which released in 2015.

Login to ASUS WiFi router as admin
Click on VPN button
Click on tab "VPN Server"
Enable OpenVPN server

For my ASUS access point (I will use AP in later post), it allows me to configure 2 OpenVPN servers
The "Server instance" for them are:

Server 1
Server 2

Under list box "VPN Details", clicks on "Advance" in the list box to show more VPN server configurations
Sets following 3 VPN server parameters

Advertise DNS to clients = Yes
Respond to DNS = Yes
Optionally set Direct clients to redirect Internet traffic = Yes. This will allows other application think your traffic is originating from home computer instead of you current position. E.g. Facebook, YouTube, eBay, Amazon

Following server parameters are optional to change to increase security, and minimize hacker

Server port
Firewall
Username/Password authentication
TLS control channel security
Auth digest
VPN subnet/netmask

Clicks on "Apply" button to start the VPN server
Now the OpenVPN server is completed
Navigate back to the same screen, if it forward you to other page
"VPN Details" will change back to "General" which will then shows button "Export" for you to download .opvn file
The browser will prompt you to save the OpenVPN client config file with .opvn file extension
Inside the browser's file download area, it will ask to reject or keep the file. For me, it doesn't prompt me anything, and I have to click on download button (in Chrome), then I can see it is showing an additional "Keep" button next to the .opvn file

Clicks on "Keep" button next to the .opvn file to confirm file download

Now, distribute the OpenVPN client configuration files to machines, and mobile devices where you want to use OpenVPN client. For examples

Windows PC - copy the file to the copy through file sharing, or USB thumbdrives
Apple iOS such as iPhone, iPad - uses e-mail file attachment to send to the email addresses where your iOS devices (iPhone, iPad) has e-mail configured. If you have not setup e-mail, please set it up, and you can remove it after you have OpenVPN client setup
Android - I recommend to use e-mail as well, unless you have SD card

For each of the devices or PC you have, install OpenVPN client, which then you can use to import the .opvn file
By now, OpenVPN has been running inside the ASUS AP, and OpenVPN client configuration files are readily to be used (and distributed)
In Windows OS where you want to RDC into it, open "Windows Defender Firewall"

For Windows 11, navigation nis Control Panel > System and Security > Windows Defender Firewall

You should see following 3 network area:

Private network - this is the firewall configuration for machines which has the identical IP address of this Windows OS. For example, if the Windows's IP is 192.168.1.1, then this is for incoming RDC request from 192.168.1.1 - 192.168.1.254
Domain network - this is optional. If it shows up, then typically it is for local LAN but with different IP than 192.168.1.1
Guest or public networks - starting Windows 11, both WiFi and LAN adapters are set to public network, and will block incoming RDC connection
Follows this step if want to change the LAN or WiFi adapter to "Private" network for Windows 11

Click on Start > Your name
Next to your Avator icon, click on "My Microsoft account" to open configuration menu
You should land on Home menu
Look at the 4th menu, called "Network & internet"
Clicks on menu "Network & internet"
You can only change its profile if the network adapter is connected. So if you want to change

WiFi adapter, then connects your WiFi
LAN adapter, then connects LAN cable

Clicks on WiFi or LAN adapter
Clicks on WiFi SSID which is the 2nd button below WiFi on/off button
For example
Clicks on "Private network" to change its profile to "Private network" which will allow incoming RDC connection
If your computer belongs to a domain, then clicks on "Domain network" instead
Now you are clear which network profile to pick in subsequent step of Windows Defender Firewall when choosing network profile of private, domain, public
Runs "Windows Defender Firewall with Advanced Security"

Clicks on "Inbound Rules"
Scroll down to "Remote Desktop - User Mode (TCP-In)"
Confirms the Profile = All, which means it allows incoming TCP connection to default RDC port 3389 from private, public, and domain profile
Right clicks and select "Enable Rule"

Now your computer will be able to accept incoming RDC connection from any IP in this network adapter/IP
By now, following are done

ASUS AP configured with OpenVPN server
OpenVPN server is running
OpenVPN client configuration .opvn file is distributed
Windows machine configured to accept incoming RDC connection

In each of the device you would like to run OpenVPN client, import the .opvn file

Installs OpenVPN client software
Windows - download from https://openvpn.net/client/
iOS - search and download from Apple Store
Android - search and download from Google Play

Import the .opvn file

Windows - double click on .opvn file
iOS - open the e-mail which contains the .opvn attachment. Touch the .opvn attachment to show various options of opening the file. Choose "OpenVPN" icon

OpenVPN app will run
It will automatically trigger import profile screen
Gives the profile your desire name, if you don't like the default cryptic name

Android - open the e-mail which contains the .opvn attachment. Touch the .opvn attachment to show various options of opening the file. Choose "OpenVPN" icon

OpenVPN app will run
It will automatically trigger import profile screen
Gives the profile your desire name, if you don't like the default cryptic name

If you device and the Windows (RDC target host) are connecting to the same network, then in your OpenVPN client device, disconnect from the LAN/WiFi, and uses the Internet from your cellphone
Turn on the OpenVPN to connect to ASUS AP against the newly imported profile
Now, the device should be able to RDC to the target Windows using mstsc.exe command

Linux: Dynamically/Realtime Changing ulimit for existing process

2025-07-17T11:48:00.001-04:00

Product: RedHat Enterprise Linux RHEL

Version: All

Overview

Many UNIX/Linux software requesting to set process' limit using ulimit command. This includes Oracle RDBMS, Apache httpd, Apache Tomcat, any file server, Cognos Analytics, call center servers, media server, Java base server, etc.

No vendor in any major application explain the objective of setting ulimit, and eventually the daemon/process hang or crash due to hitting such OS limit per user.

For reader who has no idea what is ulimit, the short description is that this is to configure user level's OS limit for any process run and own by the user. This includes number of active open network connection, number of active open files, max file limit, max process RAM, etc. This is not available in Windows, so anyone who never proper learn UNIX, they will miss this, or even miss this configuration.

Often, may IT personnel don't follow vendor's documentation to install the software. Some vendor's document might even provided low value instead of a formula to properly tune the ulimit. You need to understand that many developers do not understand OS, including UNIX OS, so it is challenging for a someone who don't understand OS to provide OS tuning parameter and value.

Eventually, this leads to many daemons hanging and even crash. As an OS administrator, or software support personnel, it will be helpful to resolve the hanging, or even prevent it reach that critical state when the daemon start to behaving poorly, if you are able to catch it before it crash.

This post introduce a Linux commands which you can change the ulimit at in real time without restarting the daemon, or OS. This command required to be run as root:

RedHat RHEL - prlimit

Documentation: https://man7.org/linux/man-pages/man1/prlimit.1.html
Documentation: https://linux.die.net/man/2/prlimit

Ubuntu - chpst

Documentation: https://manpages.ubuntu.com/manpages/lunar/man8/chpst.8.html

For the rest of the post, I will just refer to RHEL command of prlimit, as most company will use RHEL to run their software.

Usage

Command prlimit can be used to change the ulimit for a specific process in real time without shutting it down, or reboot OS.

You need to always specify the process ID, or called PID in "ps" command's output. So the syntax will always to include "-p <PID" such as "prlimit -p <PID of Oracle>"

Following ulimit configuration can be configure in real time:

RAM related

max data size (RAM) - parameter -d
max resident set size RSS (RAM) - parameter -m
max stack size (RAM > stack) - parameter -s

Storage related

max file size (storage) - parameter -f
max number of open files - parameter -n
max number of file locks - parameter -x

Messaging related

max number of bytes in POSIX message queue - parameter -q

Process

max number of processes - parameter -u

Each OS resource usage has a different command to check their current utilization, so you can search in Internet to identify how much is the usage before tune up these values. I might write a new blog post if this page is getting sufficient hit, such as more than 50,000 hit.

By adjusting the ulimit of the active process in real time, you can avoid unplan downtime during business hour, and schedule a proper maintenance window to tune the OS.

SAP IPS Logs

2025-07-09T13:44:00.004-04:00

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

Version: 4.2.x - 4.3.x

OS: Windows, Linux

There is no documentation about the log files created by SAP CMS software that bundled with SAP BODS software. This post is mainly covering the log files

Following are the log files created by SAP CMS software, and they mix together with CMS' Tomcat application server:

aps_ORDSUPWDS01.AdaptiveProcessingServer_gc.log

aps_ORDSUPWDS01.AdaptiveProcessingServer_ncs.trc

aps_ORDSUPWDS01.AdaptiveProcessingServer_trace.000001.glf

aps_ORDSUPWDS01.EIMAdaptiveProcessingServer_gc.log

aps_ORDSUPWDS01.EIMAdaptiveProcessingServer_ncs.trc

aps_ORDSUPWDS01.EIMAdaptiveProcessingServer_trace.000001.glf

cms_ORDSUPWDS01.CentralManagementServer_ncs.trc

cms_ORDSUPWDS01.CentralManagementServer_trace.000001.glf

DS.AdminService_2025079_7444820959_0.log

DS.AdminService_2025079_7444820959_0.log.lck

DS.JobLauncherService_2025079_7444817762_0.log

DS.JobLauncherService_2025079_7444817762_0.log.lck

DS.RFCService_2025079_7444811058_0.log

DS.RFCService_2025079_7444811058_0.log.lck

fileserver_ORDSUPWDS01.InputFileRepository_ncs.trc

fileserver_ORDSUPWDS01.OutputFileRepository_ncs.trc

ICC.MetadataService_ord-sup-wds01_2025079_74448304_60_0.log

ICC.MetadataService_ord-sup-wds01_2025079_74448304_60_0.log.lck

ICC.ViewdataService_ord-sup-wds01_2025079_74448177_63_0.log

ICC.ViewdataService_ord-sup-wds01_2025079_74448177_63_0.log.lck

jobserver_ORDSUPWDS01.AdaptiveJobServer_ncs.trc

jobserver_ORDSUPWDS01.AdaptiveJobServer_trace.000001.glf

ORDSUPWDS01_gc.log

SIA_ORDSUPWDS01_trace.000001.glf

Purpose

File extension ".lck" is lock file or a flag file which to imply the CMS daemon is running. CMS has following daemons, and each has their own .lck lock file

DS.AdminService_2025079_7444820959_0.log.lck
DS.JobLauncherService_2025079_7444817762_0.log.lck
DS.RFCService_2025079_7444811058_0.log.lck
ICC.MetadataService_ord-sup-wds01_2025079_74448304_60_0.log.lck
ICC.ViewdataService_ord-sup-wds01_2025079_74448177_63_0.log.lck

File suffix "_gc" and extention ".log" (...._gc.log) is Java garbage collection log. CMS daemon has no reason to has high Java memory usage, so these log files can be ignore
The rest of the log files are useful for troubleshoot SAP CMS daemons, and needs to read everyone of them

SAP BODS Designer Central Repository Not Showing Up When Adding

2025-06-06T11:22:00.002-04:00

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:

Setting up more central repositories in identical BODS versions for current env
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
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

2024-12-10T17:14:00.003-05:00

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

2024-11-08T15:07:00.001-05:00

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

2024-09-22T14:21:00.006-04:00

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]: 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

2024-09-12T12:36:00.001-04:00

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

2024-07-14T11:07:00.010-04:00

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

~~Web GUI's menu Administration > Logging > Syslog > Connection Logging to control following won't always going to capture above traffic:~~

~~Inbound: If blocked by Firewall~~
~~Outbound: If blocked by Firewall~~
~~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)

iptables firewall configuration is based on my configuration of Fresh Tomato on ASUS RT-AC66U, version 2024.2. It might needs further modification
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)

Fresh Tomato firmware seems to enforce a restriction to limit the number of entries inside the rule
This error can be ignore, and doesn't affect the IP blocking

Linux: How to get Parent Process (PPID)?

2024-06-28T09:40:00.002-04:00

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

Java SQL Programming - Tuning SELECT Statement Performance, JDBC Performance Tuning

2024-05-21T14:11:00.005-04:00

Product: Java Programming
Version: 0.1 - current
OS: Any operating system, Windows, Linux, UNIX, AIX, Solaris, HP-UX
Related Product: Any programming language that uses SELECT in the world
Java API, Java class, Java method: java.sql.Statement, java.sql.PreparedStatement
Java method: java.sql.PreparedStatement.setFetchSize, java.sql.Statement.setFetchSize

Overview

Any programming that can work with databases will offer an API to write SELECT statement. In term of Java, they are following Java classes:

java.sql.Statement
java.sql.PreparedStatement

Certainly there are more way to run a SELECT statement without using above, but this post will focus in these 2, as they are indirectly called by any SELECT statement execution by any programming languages in the world. This has been since the 1980 when a database programming was introduces, and many programmer today has no clue how this begin, especially why this is required.

This post is going to explain how to use Java programming language to tune a SELECT statement for a very specific use case:

SELECT statement is returning a lots of data, such as 1000 rows and above

The tuning will be to tune either of following Java JDBC class:

java.sql.Statement
java.sql.PreparedStatement
java.sql.ResultSet

Architecture

In order to explains the tuning of these 2 JDBC class, first I need to explain how SELECT statement work in low level. I will use programmer's perspective to explain, and skip many low level database execution, and pick Oracle DB as an example. This is mainly I worked more in Oracle, and Oracle KB and blogs are sharing more of its internal architecture than MS SQL Server, for example.

A SELECT statement execution goes through following process:

Crafts a SELECT statement

In Java programming, this often be stored as a String variable, or many SELECT statements in many String variables

Creates a String variable to store above SELECT statement
Create a new variable of JDBC class java.sql.PreparedStatement by using above String (or SELECT statement) to that it becomes an object that is parsed (Oracle's keyword), and in programmer's terminology, it means compiled by DB

PreparedStatement or DB SQL parsing is always required to run any SQL, including SELECT, INSERT, DELETE, CREATE, DROP, TRUNCATE, function call, store procedure call. Let's call these DDL (data definition) and DML (data manipulation)
PreparedStatement will indirectly getting call by any JDBC if the program doesn't defined/specify/create this object when executing any SQL
There is no way to bypass SQL parsing (Java PrepareStatement) when executing any DDL and DML
The object in Java takes up very little memory (Java called heap memory)

In Java documentation, you often read this will allocate some memory, but the document was written when max desktop RAM was 512MB

The object in Oracle takes up very little memory

In Oracle documentation, you often read this will allocate some memory, but the document was written when max desktop RAM was 2GB

The variable will auto set its row fetch size of 10 rows, i.e. PreparedStatement.setFetchSize(10). It will download 10 rows on every ResultSet..next() method. You can call PreparedStatement.getFetchSize() to display its row fetch size
This PreparedStatement variable is ready to run by calling PreparedStatement.executeQuery()
The FetchSize doesn't take up memory in object PreparedStatement

Creates a commonly used variable for JDBC class of java.sql.Statement that either

Uses above PreparedStatement variable
Runs above String variable contains a SELECT statement
Runs a a SELECT statement, typically used to get current timestamp
The variable will auto set its row fetch size of 10 rows, i.e. Statement.setFetchSize(10). It will download 10 rows on every ResultSet..next() method. You can call Statement.getFetchSize() to display its row fetch size
Oracle DB will indirectly performs SQL parsing inside the DB, which takes up a little memory in Oracle DB server
The FetchSize doesn't take up memory in object Statement

Run the SQL by calling either following JDBC method:

PreparedStatement.executeQuery()
Statement.executeQuery()

Oracle DB will takes time to execution the SQL statement. It will take longer time if Oracle DB has poor SQL execution plan, or high data volumes that DB takes time to load the data from hard disk, or joins them using CPU. Java program doesn't do any processing, and no data is available for the Java program to download. In other word, Java program will stop and wait until DB finished execute the SQL, and ready to return the data. executeQuery() will wait as long as the Oracle DB takes to run the SELECT statement. If the SELECT takes 50 hr to execute, then executeQuery() will wait for 50 hr
After Oracle completed executing the SELECT statement, fetch/download the rows into Java using JDBC class ResultSet

Creates a new variable of JDBC class ResultSet
Download 10 rows (record) of records using ResultSet.next() method
If you set FetchSize to be higher using setFetchSize(), then ResultSet.next() will download more rows from the DB server
ResultSet.next() method will pause longer the higher the FetchSize set to. Imagine you are downloading files from Internet with bigger size. The bigger the file, the longer it takes to download
The more columns the SELECT statement indicates, the bigger the data volume and longer it takes to run next() method
So total time to fetch 10 rows (or FetchSize if set to diff value) depends on (excludes time waiting for DB to run SELECT statement):

Number of columns defined in SELECT statement
Number of columns with data. If no data, then no impact
Data size for each column
Number of rows to fetch that defined by setFetchSize. Default is 10 rows

FetchSize can be modify in real time JDBC method ResultSet.setFetchSize(new_value) anytime

SELECT Statement Tuning

After lengthy explanation of the SELECT statement architecture, let's get into SELECT statement tuning.

We will not discuss about tuning of following as some are business requirement, and some are SQL tuning beyond Java programming tuning:

slow running SELECT statement that DB takes time to process
poorly written SELECT statement
SELECT statement is joining plus expecting to retrieve all data from very large tables, such as 10GB - 300TB

The performance tuning in Java program is for following Java JDBC class in their FetchSize:

java.sql.Statement
java.sql.PreparedStatement
java.sql.ResultSet

To further elaborate, performance tuning in Java program is increase number of rows to download from Oracle DB each time by calling following Java JDBC method:

java.sql.Statement.setFetchSize()
java.sql.PreparedStatement.setFetchSize()
java.sql.ResultSet.setFetchSize()

Why Tune Default Row Fetch Size

Default of 10 rows (FetchSize) is too small by today's standard. A desktop computer often comes with 1Gbps network card, while servers are using 10Gbps network card. Back to 30 years ago, network card was running at 10Mbps. Many residential internet are between 50Mbps and 1Gbps.
Data size are significantly bigger, such as storing image, video, audio.
Max column size has increased. Oracle LONG column type is changed to CLOB (character) and BLOG (binary) which can hold 8TB - 128TB for 1 column.
Business users have valid usage to display 1000 rows per screen, such as viewing reports, YouTube video, X tweets, so 10 rows is too little, and anonying
Too frequent pause when calling next() after it finished process/display 10 rows due to network latency
It is safe to set a proper FetchSize, such as 1000 rows when the SELECT returns 1 row. It doesn't take longer to download 1 row, when FetchSize=1000

Tuning Consideration

Fully study following when considering tuning FetchSize:

Does users/application really needs to retrieve so many columns in the SELECT statement?
How big (in bytes) the column size in the SELECT statement?
How big (in bytes) the total size of all columns in the SELECT statement?
How many records it will return by the SELECT statement?
Can user adjust the FetchSize from the application?

Scenario 1: Showing number, strings, and time

90% of regular applications design often returns these column types in their SELECT statement, and their max size in Oracle as non-Unicode (byte):

number - 40 bytes
varchar2 - 32 KB
datetime - 16 bytes

Let's assume SELECT statement is returning so many number of column types, with total of 10 columns:

number - 3
varchar2 - 5
datetime - 2

So total max bytes that the next() will download from the DB server is

Max row size in byte = 3 * 40 + 5 * 32KB + 2 * 16

= 120 bytes + 160 KB + 32 bytes

= 163992 bytes

= 160.1484375 KB

Assumes that business users is able to accept 1 sec pause to display the data (download 10 columns from the Oracle DB server), then following is the size of data download size for different network speed of 1 sec:

1 sec for 1Mbps = 1 sec * 1,000,000 bit/s = 122.070 KB

1 sec for 10Mbps = 1 sec * 10,000,000 bit/s = 1220.703 KB

1 sec for 1Gbps = 1 sec * 1,000,000,000 bit/s = 119.209 MB

1 sec for 2Gbps = 1 sec * 2,000,000,000 bit/s = 237.418 MB

1 sec for 10Gbps = 1 sec * 10,000,000,000 bit/s = 1192.093 MB

Uses above data download size for 10 columns (160.1484375 KB max per row size) and determine the acceptable FetchSize:

1Mbps: FetchSize=1
10Mbps: FetchSize=7
1Gbps: FetchSize=762
2Gbps: FetchSize=1524
10Gbps: FetchSize=7622

If business users said even the varchar2 column type can hold 32KB of data, but 99% of the data is below 2 KB, then max column size in bytes (keeping other columns type as their max size):

Max row size in byte = 3 * 40 + 2 * 2KB + 2 * 16

= 120 bytes + 4 KB + 32 bytes

= 4248 bytes

= 4.148 KB

Then the acceptable FetchSize for different network download speeds are below:

1Mbps: FetchSize=29
10Mbps: FetchSize=294
1Gbps: FetchSize=29425
2Gbps: FetchSize=58851
10Gbps: FetchSize=294256

Default FetchSize of 10 rows is too small for business servers which often uses 1Gbps between Oracle server and Java application.

Over Tune Warning

Be aware about maximum column size (in bytes) in the SELECT statement when adjusting FetchSize:

varchar2 - 32KB max
LONG - 2GB max
BLOB - 8 - 128TB max
CLOB - 8 - 128TB max
BFILE - 4GB max

Not all SELECT statement are identical, so don't simply reuse a very high FetchSize value that used by another SELECT statement.

Following are bad example of over tune FetchSize:

Business users often enter 4000 or more characters for a Note column inside a table. The application set to display 1000 rows (FetchSize=1000) which easily download 4MB of data into Java application/servlet.. The Java application is used by 2000 concurrent users. Programmer designed above screen in a frequently used home screen, which has 2000 concurrent users (4MB * 2000 ~ 8GB)

Business users should consider fetching the first 100 characters from the Note column instead of displaying entire Note. If business users need to read specific Note, then click and display the specific Note

Business users want to auto download monthly pdf when click on the "Download Monthly Check Deposit" button, similar to an invoice screen that the application has (FetchSize=50). However, business users didn't consider that the pdf in this screen often contains images of checks that are taken with cellphone, and each are 2MB easily. This screen is used by business customers who deposit max 100 of check daily, and the screen suppose to display 1 months of records. This means the button will download 100 checks * 31 days = 3,100 rows of checks in pdf file. When JDBC FetchSize was set to 100 rows in this check deposit screen, it will download 302GB of data for the entire month per business customer

This functionality to download entire month's check should not be offered unless end-to-end network speed is 10Gbps (this still takes ~ 30 sec to download the data into Java application)

Reporting users designed a simple monthly operation reports which are showing 5,000 columns from few tables with big data volume, and high transactions volume daily. When setFetchSize to a high value, the report always crash the Java application (run out of heap memory)

setFetchSize should set to a lower value
Java heap memory needs to increase
Controls number of rows to display the output to the screen
Encourage users to save to file
Offers pagination in the screen, and set setFetchSize to match the number of rows display on the screen, or 1/5 (so next() will indirectly download 5 times from the DB server) of the total rows. Uses following Java code to determine number of rows (not all DB type supports last())

ResultSet rs = stmt.executeQuery(sql);
int rowCount = rs.last() ? rs.getRow() : 0;
rs.beforeFirst();

SAP BODS JobServer: Enabling IPv6

2024-04-22T13:20:00.006-04:00

Product: SAP Data Services

Version: 4.2.x

Overview

SAP BODS product bundled with following 2 software:

IPS
Data Services JobServer - I will refer to it as JobServer
Tomcat

Following are the default ports each listening on

IPS - TCP port 6400
JobServer - TCP port 3500
Tomcat - TCP port 8080

This post mainly write about IPv6, as certain people might have IPv6 setup, and would like to use it. I won't discuss about Tomcat, as its IPv4 & IPv6 network configuration follows Apache Tomcat's documentation.

IPS has both IPv4 and IPv6 enabled during installation. However, JobServer only enable IPv4, and the product document does not show how to enable IPv6. The confusing part is that their product document, and SAP KB have several places that indicate IPv6 is supported, yet no documentation how to enable it.

Enabling IPv6 for JobServer

Follow these steps to enable IPv6 (and keeps IPv4) for JobServer

1. cd $LINK_DIR/bin

2. Append SAP_IPv6_ACTIVE=Y into al_env.sh: echo "export SAP_IPv6_ACTIVE=Y >> al_env.sh"

3. Restart JobServer: svrcfg

3.1. In the menu, choose stop JobServer, then start JobServer

Verify IPv6 enabled for JobServer

When I raised SAP support ticket, SAP keeps saying IPv6 is enabled, so I have to provide proof that it is not. You can use this command to verify that IPv6 is enable

1. Linux: netstat -an | grep 3500 | grep LISTEN

2. Windows: Run cmd as Administrator

2..1. netstat -an | findstr "3500" | findstr "LISTEN"

Sample output that shows IPv6 works in port 3500

tcp 0 0 0.0.0.0:3500 0.0.0.0:* LISTEN

tcp6 0 0 0.0.0.0:3500 0.0.0.0:* LISTEN

The first column, "tcp6"means it is for IPv6, while "tcp" means it is for IPv4

IBM Cognos Analytics: Content Store JDBC Configuration for Oracle

2024-04-16T16:24:00.000-04:00

Product: IBM Cognos Analytics

Version: 10.2.1 - 12.0.0

OS: Windows, Linux

IBM Cognos Analytics documentation about Content Store's configuration using Oracle database is very limited.

This post will list down several configurations

Option 1: Using TNS from tnsnames.ora. Most Recommended

Using TNS name is the most preferred method for following reasons:

1. Changing of Oracle hostname doesn't need to change Cognos configuration, and can be done in real time

2. You can leverage Oracle scan listener functionality in tnsnames.ora

3. You can leverage fail-over Oracle functionality in tnsnames.ora

4. You can leverage standby fail-over Oracle functionality in tnsnames.ora

5. You can troubleshoot Cognos content store connectivity to Oracle DB using tnsping [TNS name] command

6. Supports Oracle RAC through tnsnames.ora

Steps:

1. Delete all entries under Data Access > Content Manager

2. Create new Content Store, and pick "Oracle database (Advanced)"

3. Enter the pre-configured Oracle TNS name from tnsnames.ora under "Database specifier"

4. In this example, "ora21c" is the TNS name entry that already configured in tnsnames.ora

Option 2: Uses URL syntax

Uses a JDBC URL syntax of //[hostname]:[port]/[service name] will leverage Oracle Service functionality, as well as making the entry easy to maintain.

There is not much advantage to this approach compare to above, so I will only list down disadvantage:

1. Changing of Oracle hostname, port, service name will required to shutdown Cognos daemon

2. Not possible to perform connectivity test using exact URL

3. Doesn't support Oracle RAC

4. Doesn't support Oracle standby

5. Doesn't support Oracle fail-over

Steps:

1. Delete all entries under Data Access > Content Manager

2. Create new Content Store, and pick "Oracle database (Advanced)"

3. Enter //[hostname]:[port]/[service name] under "Database specifier"

3.1. hostname - Oracle database server's hostname

3.2. port - Oracle listener's port. Default is 1521

3.3. service name - Oracle listener's service name as shown in "lsnrctl status [listener]"

4. In this example, "//ora-db-21c:1521/ora21c.abc.com" will connects to host ora-db-21c on port 1521, with Oracle service name ora21c.abc.com

Option 3: Uses long TNS syntax

This is an lengthy and poor readability configuration, but it offers some advantages:

1. You can leverage Oracle scan listener functionality in tnsnames.ora

2. You can leverage fail-over Oracle functionality in tnsnames.ora

3. You can leverage standby fail-over Oracle functionality in tnsnames.ora

4. You can troubleshoot Cognos content store connectivity to Oracle DB using tnsping [TNS name] command

5. Supports Oracle RAC through tnsnames.ora

Disadvantage:

1. Length to type, and potentially make mistake

2. Difficult and time consuming to modify

3. Modification of hostname, port, service name will require to restart Cognos daemon

4. using tnsping to perform connectivity test is challenging for those who doesn't know the syntax that has been long forgotten

Steps:

1. Delete all entries under Data Access > Content Manager

2. Create new Content Store, and pick "Oracle database (Advanced)"

3. Under "Database specifier," enter "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora-db-21c)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora21c.abc.com)))"

3.1. HOST - Oracle database server's hostname

3.2. PORT - Oracle listener's port. Default is 1521

3.3. SERVICE_NAME - Oracle listener's service name as shown in "lsnrctl status [listener]"

Option 4: Uses SID

This is the least recommended approach, but remains in Cognos product documentation for the longest time. The main reason is that Oracle documentation has recommended not to use SID to login for any application since late 1990s.

The syntax is hostname:port:SID, which is very similar to URL syntax above, but it uses ":" instead of "/"

There is not much advantage to this approach compare to above, so I will only list down disadvantage:

1. Changing of Oracle hostname, port, service name will required to shutdown Cognos daemon

2. Not possible to perform connectivity test using exact URL

3. Doesn't support Oracle RAC

4. Doesn't support Oracle standby

5. Doesn't support Oracle fail-over

6. SID might be different than service name that Oracle DBA is familiar, and might cause communication gap, and confusion during connectivity troubleshooting

Steps:

1. Delete all entries under Data Access > Content Manager

2. Create new Content Store, and pick "Oracle database (Advanced)"

3. Under "Database specifier," enter "[hostname]:[port]:[SID]"

3.1. hostname- Oracle database server's hostname

3.2. port- Oracle listener's port. Default is 1521

3.3. SID - Oracle DB's SID, but not service name

Oracle sqlplus Login Failure Handling in Shell Script

2024-02-05T13:01:00.006-05:00

Product: Oracle RDBS

Command: sqlplus, shell script

Overview

I would like to document how to create a shell script that will report whether the username credential that stores in a file can successfully login to Oracle DB, or filed.

Without specifying any sqlplus parameter, sqlplus will always return exit code of 0 to the shell script. I have seen people uses text processing utility to search for the login failure command to detect login failure, but they should use parameter "/l" to do that.

Moreover, in a shell script, I will suggest to turn off the login banner to make the output less verbose by using parameter "/s"

Instruction

Suggested command is "sqlplus /s /l username@TNS"

To automate the password, my suggested command is "echo [password] | sqlplus /s /l username@TNS"

Later, the shell script can uses the OS return code's build-in parameter $? with following meaning:

0 - login successful
1 - login failed

SSL Setup - Java https, smtp, MS SQL Server

2023-10-20T12:51:00.005-04:00

Technology: Java using SSL Certificate, or TLS1.2 - 1.3 encryption

Product: web server (https), SMTP email, force encryption MS SQL Server

There are several steps involve in setup SSL for various servers besides following standard

1. Create new SSL certificate for the virtual hostname used by the applications

2. Import the SSL certificate to the application servers

2.1. For Java, you need to import into Java keystore. For OpenJDK or Oracle Java (JRE), it is located in [java_jre_home]\lib\security, or [java_jdk_home\jre\lib\security.

2.2. For Linux or Windows binary programs, you need to import into OS. For RHEL, the SSL certificate file will be /etc/ssl/certs

3. Configures the application server to enable SSL, and indirectly specified the SSL certificate to use

4. Configures the application client to enable SSL, and optionally

4.1. Java - optionally specifies SSL keystore if not using default

Now, let's pick an application that leverages multiple technologies, and gives real picture what needs to be configured to enable SSL

Cognos Analytics report server

This reporting server has following functionality (typical, but could be more) that will has SSL encryption:

1. Web server - it is severing the report in html, so it is a web server

2. Web client - it is able to forward its traffic to a customer facing web server (IIS, Apache httpd, IBM httpd), so for this communication, it is a web client

3. SMTP email client - its interactive report and scheduled report can be sent out as email attachment (pdf, html)

4. DB client - it needs a "Content Store" DB to stores user credential and report template. It can has additional audit DB to store additional application data for audit functionality. Any DB that it is querying to display the reports are all act as a DB client

5. Authentication client - OOTB LDAP (Windows only) server, custom LDAP, Okta, IBM OpenConnect, etc authentication that it supports, it is acting as client

Therefore, for above 5 typical server & client functionality, SSL can be enabled and encrypts the traffic between remote servers and clients.

For all the above functionality, you need to understand that the product is a pure Java J2EE application. Therefore, all the SSL configuration will follow Java (standard) and IBM documentation (application specific).

Several and multiple SSL certificates involve in this encryption setup:

1. Java keystore located in [cognos_home]\configuration\certs\CAMKeystore (default password NoPassWordSet)

1.1. CAMKeystore keeps the SSL cert for web/https access for Cognos Analytics server. If there are multiple virtual hostname (such as internal access, intranet access, internet access), then there are multiple SSL certs (unless the SSL cert contains multiple virtual hostname)
1.2. CAMKeystore keeps the SSL cert for Cognos Analytics server's databases access, e.g. Content DB, audit DB, data store DB. Example is MS SQL Server force encryption. Each remote DB server uses different SSL cert, and CA root
1.3. CAMKeystore keeps the SSL cert for SMTP server, which used for email of report, or email notification functionality. Example is smtp protocol with STARTTLS that enforce SSL, or smtps protocol. Typically only 1 SMTP server, so only 1 SSL cert
1.4. CAMKeystore keeps the SSL cert for client side https with remote customer users facing web server
1.5. CAMKeystore keeps the SSL cert for remote authentication server, e.g. LDAP , ActiveDirectory, and SSO. Each authentication server has its own SSL cert

Procedure:

1. Determine the root certificate for all 5 components above. Each server will have its own SSL cert, and might not use the same CA root certificate

2. Verify in [cognos_home]\configuration\certs\CAMKeystore that the CA root certificate is already there, and will be recognize

2.1. Follows Cognos Analytics doc: DLS_SSL_CertImportTool.bat (Windows), or sh (Linux) - https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=server-enabling-secure-tls-connection-your-email

3. Even if it is in CAMKeystore, verify that it is not expired

4. If not there, or expired, then export the CA root cert from remote server

5. Import into CAMKeystore, which is the IBM Java keystore

6. For DB used in data store, import the SSL cert into [cognos_home]\ibm-jre\jre\lib\security\cacerts

6. Follows various IBM Cognos Analytics document to enable SSL encryption

6.1. For Cognos Analytics 12.0.0 SMTP - https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=server-enabling-secure-tls-connection-your-email

6.2. For Cognos Analytics 11.2.0 Content DB, audit DB, logging DB as database client - https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=communications-enabling-ssl-db2-informix-databases

6.3. For data store as DB client - https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=communications-enabling-ssl-db2-informix-databases

Tools

There are various DOS utilities that can troubleshoot SSL communication, but none for Java. Some sample Java programs are available, but you have to compile, as well as familiar with SSL logging parameters that you have to manually specify.

Tools for

1. Windows: openssl

2. UNIX, Linux - openssl, curl

3. Java SMTP - Oracle javamail-samples.zip that based on Oracle javax.mail.jar library that you need to download. It contains sample SMTP with STARTTLS sample code that you need to modify the Java program to specify SMTP port (default port 25)

4. Java MS SQL Server client - Microsoft provided sample https://learn.microsoft.com/en-us/sql/connect/jdbc/connection-url-sample?view=sql-server-ver16

Tool - opemnssl for SMTP

For SMTP that uses STARTTLS to enable encryption, uses following command to troubleshoot SSL issue

openssl s_client -starttls smtp -crlf -connect [remote email server hostname]:[smtp port]

This relies on OS SSL certificate store, so not applicable for Java SMTP troubleshooting

Tool - opemnssl for SMTPS

Same as above, but uses SMTPS SSL encryption, not SMTP + STARTTLS

openssl s_client smtps -crlf -connect [remote email server hostname]:[smtp port]

Tool - curl for SMTP

curl is preinstalled in Linux. It can login to SMTP email server, and send out email. Therefore, it is useful to ensure the remote SMTP server is properly configured to allow your application server to send out email. This uses SMTP + STARTTLS protocol

curl -v smtp://email-smtp.us-east-1.amazonaws.com:587 --user "login-user-name:password-to-be-filled" --mail-from "[username that allow to be used to send out" --mail-rcpt "email addr to receive the email" -T emailcontent.txt --ssl

Tool - curl for SMTPS

Same as above, but uses SMTPS instead of SMTP + STARTTLS

curl -v smtps://email-smtp.us-east-1.amazonaws.com:587 --user "login-user-name:password-to-be-filled" --mail-from "[username that allow to be used to send out" --mail-rcpt "email addr to receive the email" -T emailcontent.txt

Tool - Test-NetConnection

PowerShell Test-NetConnection can be used to verify the application server machine can reach remote SMTP/DB/LDAP/Okta/SSO/web server. This will give you a quick indication that the firewall, router, DNS, AWS Security Group, etc are configured to allow them to talk before you perform application side of testing, such as openssl, or curl above

test-netconnection [remote server hostname] -port [remote server port]

Example for MS SQL Server: test-netconnection [remote MS SQL Server virtual hostname] -port 1433

Example for web server: test-netconnection [remote web server virtual hostname] -port 443

Example for Oracle DB: test-netconnection [remote Oracle virtual hostname] -port 1521

Tool - Java

Enable following SSL debug parameter when running sample MS SQL Server, Oracle sample JavaMail program, etc

Example for using Oracle JavaMail sample called smtpsend.java: java -cp .;..\javax.mail.jar;..\javax.activation-api-1.2.0.jar -Djavax.net.debug=ssl:handshake smtpsend

SAP IPS - setup.exe doesn't recognize Product Key

2023-05-26T17:07:00.003-04:00

Product: SAP IPS
Version: 4.1 - 4.2

Symptom

Run setup.exe, and enter a valid product key. Installer returns following error

Error
An error has occurred.

The product key is not valid. INS00140

Checked installer log in /tmp/yyyy.mm.dd.hh24.mi.ss/setupengine.log for key validation failure. It is showing following

20:58:18.743 RawArgFormatter - property not set yet: shared.library.keycode.props-4.0-core$ProductKey

20:58:25.692 [validate_ProductKey]argument: -keycode ******** -version 142 -property BOE.EnableBOE

20:58:26.056 [run_external_executable] cmdLine: cd "/opt/download/sap/bods42sp14pl21/ips42sp9pl7/dunit/product.minibip-4.0-core-32/actions/"; /opt/download/sap/bods42sp14pl21/ips42sp9pl7/dunit/product.minibip-4.0-core-32/actions//isKeycodeValid -keycode ******** -version 142 -property BOE.EnableBOE

20:58:26.056 [validate_ProductKey]keyTypes [BOE.EnableBOE:142] is bad

Solution

For for full path of program "isKeycodeValid" shown in above log, then run following

chmod u+x <full path from log above>/isKeycodeValid

You should be able to manually test this is success

cd "/opt/download/sap/bods42sp14pl21/ips42sp9pl7/dunit/product.minibip-4.0-core-32/actions/"

isKeycodeValid -keycode <non case sensitive product key> -version 142 -property BOE.EnableBOE

Cognos Analytics 11.1.x - 11.2.x - Installer failed with JRE libraries missing or not compatible

2023-05-16T14:26:00.002-04:00

Product: IBM Cognos Analytics
Version: 11.1.0 - 11.2.2
OS: Linux, AIX, PowerPC, all UNIX distributions
Related IBM KB: https://www.ibm.com/support/pages/jre-libraries-are-missing-or-not-compatible

Problem

When executing the Cognos installer on a Linux,Aix server the installer fails to run the following error:

Preparing to install...
Extracting the JRE from the installer archive...
Unpacking the JRE...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...

Launching installer...

JRE libraries are missing or not compatible....
Exiting...."

Symptom

The installer consistently fail to run with below false alarm:

JRE libraries are missing - not applicable as the installer is downloaded many times from IBM as a valid zip file, as well as across many different version of Cognos from 11.1.0 - 11.2.2
JRE not compatible - not possible, as the installer bundled IBM Java in /tmp/install.dir.[nnnnn]/Linux/resource/jre/bin

(login as UNIX cognos user that created to own the application)

$ echo $TMP

[empty]

$ grep noexec /etc/fstab

UUID=c7aac85c-4dfd-4e40-bd6c-bf57c8284c0c / xfs defaults,noexec

$ echo "echo 012345" > /tmp/test_exec.sh

$ sh /tmp/tst_exec.sh

/tmp/tst_exec.sh: Permission denied

$ /tmp/install.dir.[nnnnn]/Linux/resource/jre/bin/java -version

/tmp/install.dir.[nnnnn]/Linux/resource/jre/bin/java: : Permission denied

Cause #1

/tmp mount point is mounted without execute permission for security reason (checks /etc/fstab with value noexec).

It is confirmed by tst_exec.sh that created above that it failed to run

Cause #2

A wrong installer for other OS is downloaded. For Intel Linux, you should expect following output

$ file analytics-installer-3.6.46-linuxx86.bin

analytics-installer-3.6.46-linuxx86.bin: data

Following is expected for Intel x86 64-bit Linux

$ file /tmp/install.dir.*/Linux/resource/jre/bin/java

/tmp/install.dir.15939/Linux/resource/jre/bin/java: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped

Following is for PowerPC Linux, not Intel x86

$ file /tmp/install.dir.*/Linux/resource/jre/bin/java

/tmp/install.dir.15939/Linux/resource/jre/bin/java: ELF 64-bit LSB executable, 64-bit PowerPC or cisco 7500, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, BuildID[sha1]=c07e00748dd7b5ca30b6feb0e2d7c911cec024d7, not stripped

Resolving the Problem

Option #1: Identify a directory that user is allow to run the installer program, i.e. java. Specify Cognos installer OS environment variable IATEMPDIR to that location before execute the installer

$ export IATEMPDIR=~/tmp

$ analytics-installer-3.6.46-linuxx86.bin

Option #2: Coordinate with Linux SA to remove "noexec" from /etc/fstab, or provides another mount point that doesn't contain "noexec" security option. Assumes the mount point is called /tmp2, then set Cognos installer OS environment variable IATEMPDIR to that mount point before execute the installer

$ export IATEMPDIR=/tmp2

$ analytics-installer-3.6.46-linuxx86.bin

Oracle Timezone: Clarification of 2 Timezone Setting

2023-02-27T09:18:00.007-05:00

Product: Oracle Database
Version: All
OS: All

History

Many years ago, Oracle database relies on OS clock, as well as no date column type that can store timezone setting (similar to MS SQL Server today).

Timezone aware column type, TIMESTAMP WITH TIME ZONE, introduced much later, but many DBA and developers don't have a good knowledge about OS timezone.

Date DB Function

In Oracle 12c to 21c, DB has following 4 date functions, which has different behavior on timezone

sysdate - Uses OS timezone

E.g. select sysdate from dual;
E.g. insert into [user table] values (sysdate);

systimestamp - Uses OS timezone

E.g. select systimestamp from dual;
E.g. insert into [user table] values (systimestamp);

current_date - uses DB session timezone, similar to sysdate

E.g. select sessiontimezone from dual;
E.g. insert into [user table] values (sessiontimezone);

LOCALTIMESTAMP
- uses DB session timezone, similar to systimestamp

E.g. select sessiontimezone from dual;
E.g. insert into [user table] values (sessiontimezone);

Timezone Configuration At DB Level

I won't discuss OS timezone configuration, as this post is mainly clarifying DB level timezone setting that confused many DBA or developers.

In above DB functions, the DB session's timezone depends on sessiontimezone. Default is DB timezone, which set by following:

ALTER DATABASE SET TIME_ZONE = 'US/New_York';
CREATE DATABASE ..... TIME_ZONE = 'US/New_York';

At user level, you can configure it using

ALTER SESSION SET TIME_ZONE='US/Eastern';

You can check its current value by using:

sessiontimezone - uses DB session timezone, similar to sysdate

E.g. select sessiontimezone from dual;

Troubleshooting Highlight

When verifying it, always display the sessiontimezone together with 1 systimestamp, and 1 current_date, e.g.

select sessiontimezone , sysdate, current_date from dual;

This will ensure that you cover both OS and DB timezone, and displaying DB timezone value

Note: systimestamp will display OS timezone value

Ref: Oracle 19c DB Time Functions: https://docs.oracle.com/en/database/oracle/oracle-database/19/nlspg/datetime-data-types-and-time-zone-support.html

Oracle No more data to read from socket

2023-01-27T12:00:00.005-05:00

Product: Oracle Database
Version: All

I often found from StackOverflow.com that people are posting following error from their application:

No more data to read from socket

The application could be Java, Springframework, etc, and they often blame the application problem, as the error is captures by the application, e.g. Java application/daemon.

Often people will suggest possible cause, or possible resolution. Some people will even suggest not related resolution which confused the entire IT community.

The full Oracle error is below:

ORA-17410 No more data to read from socket

There are several possible causes, but many people don't put when each is applicable. The issue is 100% not due to the application, but problem with the infrastructure (network) or Oracle database:

Cause #1: Intermittent Network issue

Behavior #1: Application will fail with this error intermittently

Behavior #2: Point of failure will be random, and won't be in the same table/index/SELECT SQL, as network failure is intermittent

Behavior #3: Program should failed when pull data into the application, such as SELECT, but should not fail with CREATE TABLE, CREATE INDEX, DELETE, DROP, ALTER

Behavior #4: Any application will be affected by intermittent network issue, instead of just 1 application

Behavior #5: Perform a few GB size file transfer between the application and remote DB server to simulate intermittent network issue. The duration must be longer than the SQL execution time. E.g. if it is running a SELECT that take 5 min (then failed), then the file transfer time must be more than 5 min, such as 10 min. So the test is not actually based on file size, but file x'fer time that last as long as the failed SQL

Problem Simulation: Simulate a 5 - 30 min long file transfer to confirm the issue is network related. The source and target server used to simulate file transfer must include the application server with the remote DB server

Workaround: Once confirmed, you can move the DB and application into the same machine to eliminate network issue. The network admin can take longer time to provide a permanent workaround

Cause #2: Intermittent DB Failure

Behavior #1: Application could fail with this error randomly or consistently

Behavior #2: Oracle alert log will contain more detail error message

Behavior #3: Oracle alert log could throw ORA-00600 that DBA needs to raise ticket with Oracle Support due to DB patch/bug

Behavior #4: Even putting both application and Oracle database into the same machine, this error will continue to exists intermittently

Problem Simulation: Capture the failed SQL, and run it manually using SQL Developer or sqlplus. You might need to create a shell script (PowerShell for Windows) to run it few 1000s time and last for 1 hr to simulate the error

Workaround #1: If it is ORA-00600 Oracle error, then try it with higher or lower Oracle database version as temporary workaround. There are potential Oracle bug that affect specific version and patch.

Workaround #2: There could be other OS issue that affected the Oracle database. Move Oracle database to another different machine (OS)

Workaround #3: Check RAM and storage to ensure no low storage, or extremely high RAM usage when the application hitting the error

Cause #3: Wrong JDBC driver

Behavior #1: Java application consistently failed and not intermittent

Problem Simulation: Create a simple Java program and run the same SQL by using the exact JDBC driver. This should consistently failed

Workaround: Replace the Oracle JDBC driver with Oracle's

Cause #4: Special data or table structure that is not supported

Behavior #1: Application is creating table, or inserting data that is more advance, e.g. Spatial. The program will consistently failed for the same SQL

Behavior #2: This is not intermittent. If you think it is intermittent, then capture all the of failed SQL, and look for the pattern. They must use some special data type that is not varchar2, number, date, long, clob, blob, function, partition

Problem Simulation: Capture the SQL, and create a simple Java program to run the same SQL. The Java program will consistently fail

Workaround #1: Rewrite the Java program on this SQL. It could be wrong Java object that is not compatible

Workaround #2: Very old JDBC driver that doesn't support that new feature can be another cause. Replace with newer Oracle JDBC driver

Cause #5: Firewall Timeout - idle connection clearing

Behavior #1: Some enterprise firewall can forcefully close idle connection, and leads to long running SQL's DB connection will dropped by firewall server after it hit its idle timeout value

Behavior #2: Find out the time between connection dropped and SQL is fired. The time is constant, such as 30 sec

Workaround #1: Network admin to review the firewall rule, and exclude TCP port 1521 connection to & from Oracle database server

Workaround: Replace the Oracle JDBC driver with Oracle's

Cause #6: Router Timeout - idle connection clearing

Behavior #1: Some enterprise router can forcefully close idle connection, and leads to long running SQL's DB connection will dropped by firewall server after it hit its idle timeout value

Behavior #2: Find out the time between connection dropped and SQL is fired. The time is constant, such as 30 sec

Workaround #1: Network admin to review the router rule, and exclude TCP port 1521 connection to & from Oracle database server

Oracle: ORA-01555: snapshot too old Tuning

2022-12-13T10:09:00.003-05:00

Product: Oracle Databse
Version: 9.0.0 - 18.0

Since Oracle Database 9i, many DBA have been keeping UNDO_MANAGEMENT=AUTO setting, and developers/support often encountered following UNDO related error on long running SQL

Oracle <ORCL> error message for operation <OCIStmtExecute>: <ORA-12801: error signaled in parallel query server P07G
ORA-01555: snapshot too old: rollback segment number 271 with name "_SYSSMU271_3009956035$" too small>.

If DBA don't study Oracle documentation on "Automatic Tuning of Undo Retention" but simply search Google/Internet for answer, then they often comes to following incorrect workaround:

Increase UNDO_RENTENTION time - no change in the long running SQL
Modify code to split the long running SQL to have several shorter running SQL, e.g. store into intermedia table, then select from the intermedia table
Asks developer to tune the long running SQL to run faster, e.g. create index, don't select unnecessary columns, filter out unrequired data (more WHERE criteria)
Don't insert/update/delete on the table that the long running SQL is SELECTing

So what does DBA missed in above analysis is he/she only focus in ORA-01555 error without considering the rest of the message. Following are the things that DBA missed:

Parallel query server is enabled
Above PQ number is P07G with starting number as P000
Number of concurrent running SQL that that point of time, without considering Parallel query server (excludes parallel query coordination and slave)
Size of the UNDO tablespace, which could be few TB in size, and bigger than entire DB size
UNDO tablespace's dbf files are not in auto extend mode
Even if UNDO dbf files are in auto extend mode, it hit max dbf file size

As the result, tuning UNDO_RETENTION or even increase the size of UNDO dbf to 300GB larger doesn't resolve the issue.

The resolution is in fact totally not related to modify UNDO_RETENTION.

Post your comment if you want to know the answer, as I want to know how many visitors are actually reading my blog post

SAP Data Services Job Server - SSL Configuration

2022-05-30T16:44:00.006-04:00

Product: SAP Data Services
Component: JobServer
Version: 4.2SP0 - 4.2SP14
OS: Windows

SAP Doc #1: https://help.sap.com/docs/SAP_DATA_SERVICES/2d2abbb0fab34071a4c53b7de873241b/571a69626d6d1014b3fc9283b0e91070.html?locale=en-US

SAP Doc #2: https://help.sap.com/docs/SAP_DATA_SERVICES/e54136ab6a4a43e6a370265bf0a2d744/571dc02c6d6d1014b3fc9283b0e91070.html?locale=en-US&version=4.2.8

SAP Doc #3: https://help.sap.com/docs/SAP_DATA_SERVICES/e54136ab6a4a43e6a370265bf0a2d744/571db2eb6d6d1014b3fc9283b0e91070.html?locale=en-US&version=4.2.8

SAP has a relatively poor documentation about custom SSL certificate setup for BODS Data Services, which is Job Server. They break it into several different chapters and failed to leverage "Related information" section to help customer to bridge the information together.

There are 2 main directories, and 1 tool (Data Services Server Manager in Windows, and svrcfg in Linux) to configure the SSL certificate:

%LINK_DIR%\ssl\server
%LINK_DIR%\ssl\trusted_certs

The most common mistaken about SSL certificate is that administrator failed to read SAP's doc that their program only recognize PEM format SSL certificate (there are DER, PKCS12 that are common).

Let me list down a list of files and directory of each, which is applicable to both self-signed, or paid SSL cert:

Custom root CA SSL cert > %LINK_DIR%\ssl\trusted_certs
Custom root CA SSL cert private key, password file > not required
Custom intermediate #1 CA SSL cert > %LINK_DIR%\ssl\trusted_certs
Custom intermediate #1 CA SSL cert private key, password file
Custom intermediate #2 CA SSL cert > %LINK_DIR%\ssl\trusted_certs
Custom intermediate #2 CA SSL cert private key, password file
Custom intermediate #3 CA SSL cert > %LINK_DIR%\ssl\trusted_certs
Custom intermediate #3 CA SSL cert private key, password file
Custom BODS server SSL cert > %LINK_DIR%\ssl\server
Custom BODS server SSL cert private key > %LINK_DIR%\ssl\server
Custom BODS server SSL cert password file > %LINK_DIR%\ssl\server
There is no SSL certificate keystore for Job Server configuration

There are several catch that SAP document doesn't cover, as well as misleading info in SAP KB:

%LINK_DIR%\ssl\trusted_certs can contains additional SSL certs files
%LINK_DIR%\ssl\trusted_certs cannot contains other files that are not SSL cert files, or it will complain error "Unable to load verify locations"
If %LINK_DIR%\ssl\trusted_certs contains private key PEM file, Server Manager will complain error "Unable to load verify locations" as the same reason explained in #2 above
%LINK_DIR%\ssl\trusted_certs can contains unused SSL certs, such as put new self-signed or paid cert files together with OOTB self-signed cert files
SSL cert that is V1 or V3 are both accepted
SSL cert that is RSA 1024-bit or 4096-bit are all accepted
The program is not able to produce meaningful log, and no way to enable debugging log
The openssl command in SAP's doc implies to generate PEM format certificate, but that command is actually generates "certificate request" file, and not the final certificate
SAP document wrote to put all the signed SSL certs into "%LINK_DIR%\ssl\trusted_certs" but you just need to put the root, intermediate, and parent SSL certs into it (as PEM format)
You can have SSL file extension other than crt: crt, cer, pem
The SSL cert that issue to BODS server should put under %LINK_DIR%\ssl\server directory, which SAP doc failed to mention. This is for consistency of their design, and won't break application functionality. I recommend to keep existing architecture instead of follow their doc
In Data Services Server Manager > SSL tab, configures

Server certificate file: Location of the PEM SSL cert file that issued to this BODS server. It should be in %LINK_DIR%\ssl\server directory
Server private key file: Part 2/3 file for above SSL cert. It should be PEM format, and located in same dir as above
Use Server private key password file: Recommends to has a password file for above SSL cert. Recommends to keep in same dir as above
Trusted certificates folder: No need to change, but ensures copy all the root and intermediate cert files into dir %LINK_DIR%\ssl\trusted_certs (default). You do not need to change this location

You can use both Windows, or bundled openssl utility to convert SSL certificate to PEM format that recognized by BODS. PEM format file is ASCII file, which you can open it up in text editor to confirm that it is not binary file.

Following is openssl command to convert DER format SSL cert file to PEM format:

%LINK_DIR%\bin\openssl x509 -inform DER -in custom.cert1.crt -out custom.cert1.pem.crt

Error: Not able to read the specified certificate file error BODI-320039

SAP Note 2287520 - Not able to read the specified certificate file error BODI-320039
https://launchpad.support.sap.com/#/notes/2287520
Cause: cer file generated by Microsoft CA was not in the format Data Services expects
Resolution: Regenerate the cer file in correct format as per Administrator Guide. Internet Explorer may be able to open the cer file properly but check from application like notepad the correct format expected by DataServices contains "BEGIN CERTIFICATE"

My comment: Data Services only recognize PEM format SSL cert, but not DER or PKCS12 format even the file has cer, crt, or PEM extension.

Error: Unable to load verify locations

SAP Note 2409684 - Error: Unable to load verify locations is prompted when making changes in Data Service Server Manager

URL: https://launchpad.support.sap.com/#/notes/2409684
Cause: You have unnecessary certificates under trusted_certs which are 3rd party products(Coupa and GoDaddy). They were saved into the Data Server SSL folder
Resolution: Move the 3rd party SSL certificates to a different location

My comment: %LINK_DIR%\ssl\trusted_certs can contains any 3rd party SSL certificates, as well as unused, or inactive, or extra, or duplicate, or expired SSL certificates. It can contains non-PEM format SSL as well. Above error is not due to additional SSL certificate.

My resolution: Ensures the directory only contains SSL certificate, but no other files, such as private key, or password file

Powershell: List AD Group for Specific User and Domain

2022-02-03T12:52:00.017-05:00

Product: Windows
Utility: Powershell, ActiveDirectory

Windwos AD domain users who are not administrator of ActiveDirectory users are often getting confusing push back from AD admin when the business users are requesting for access that controlled by AD.

This article is going to show various commands that business users (end users) who are not AD admin, and allows them to find out which AD group they belongs to directly, or indirectly (Windows term if group nesting).

CMD

Following command will display all the nesting group that a user belongs to

1. net user <username> /domain

Limitation #1: You cannot change the domain, if the computer you login to belongs to multiple domain

Limitation #2: Doesn't show child groups. It will always display direct groups the user belongs to

Limitation #3: Display "Description" name of the group, not the actual group name

Limitation #4: Group name is truncated after 21 characters

2. gpresult /v

Limitation #1: can't specify which domain if you belongs to multiple AD domain

Limitation #2: Cannot show direct group. It will always display nesting child groups

3. whoami /groups

Limitation #1: can't specify which domain if you belongs to multiple AD domain

Limitation #2: Cannot show direct group. It will always display nesting child groups

PowerShell

Note: Windows 10 - 11 required to use Settings > Optional features to add module "RSAT: Active Directory Domain Services and Lightweight Directory Services Tools"

1. Show AD group the current login user directly belongs to:

(Get-ADuser $env:username -server $env:userdomain -Properties memberof).memberof | get-adgroup -server $env:userdomain | select-object -ExpandProperty name

Limitation #1: It cannot show nesting group

1.1. Show AD group of username tester1

(Get-ADuser tester1 -server $env:userdomain -Properties memberof).memberof | get-adgroup -server $env:userdomain | select-object -ExpandProperty name

1.2. Similar to above, but sorted

(Get-ADuser tester1 -server $env:userdomain -Properties memberof).memberof | get-adgroup -server $env:userdomain | select-object -ExpandProperty name | sort

2. Show AD group user in specific domain, e.g. domain GAME-ADM

(Get-ADuser $env:username -server GAME-ADM -Properties memberof).memberof | get-adgroup -server GAME-ADM | select-object name

3. Show AD group user GAMER1 in domain GAME-ADM

(Get-ADuser GAMER1 -server GAME-ADM -Properties memberof).memberof | get-adgroup -server GAME-ADM | select-object -ExpandProperty name

4. Show AD group is managed by who

General syntax: Get-ADGroup [group name] -server [domain name] -properties ManagedBy

Example: group name GAMEVIPUSR, domain name GAME-ADM, the command will be

Get-ADGroup GAMEVIPUSR -server GAME-ADM -properties ManagedBy

Get-ADGroup GAMEVIPUSR -server $env:userdomain -properties ManagedBy

5. Show members belong to AD group SAP-ADMIN

Get-ADGroupMember SAP-Admins -server $env:userdomain | select-object -ExpandProperty name

6. Similar to #5, but don't display user. Only display child AD group that belongs to it

Get-ADGroupMember SAP-Admins -server $env:userdomain | where-object objectClass -ne "user" | select-object -ExpandProperty name

7. Show a child group TESTER-GRP belongs to which parent group

Get-ADPrincipalGroupMembership GWRE-CASE-DataManagement -server $env:userdomain | select-object -ExpandProperty name

8. Show a child group TESTER-GRP belongs to which parent group like "PROD*" and not case-sensitive

Get-ADPrincipalGroupMembership GWRE-CASE-DataManagement -server $env:userdomain | | where name -match "PROD*" | select-object -ExpandProperty name