Automating Oracle GoldenGate SSL Certificate Generation with PowerShell

Overview

Managing SSL/TLS certificates for Oracle GoldenGate replication environments can be tedious and error-prone when done manually. I developed a PowerShell automation solution that streamlines the entire certificate generation process using Windows Active Directory Certificate Authority infrastructure.

The Challenge

Oracle GoldenGate requires SSL/TLS certificates for secure replication between database servers. The traditional approach involves:

Manually creating certificate signing requests (CSRs)
Submitting requests to the Certificate Authority
Downloading and installing certificates
Exporting private keys
Converting formats between Windows and Linux
Managing certificate inventory

This manual process is:

Time-consuming: 15-20 minutes per server pair
Error-prone: Easy to misconfigure Subject Alternative Names or Key Usage
Inconsistent: Different team members follow different procedures
Difficult to audit: No standardized tracking of issued certificates

The Solution

I created a fully automated PowerShell script that handles the entire certificate lifecycle:

Click to view PowerShell script code

#Requires -Version 5.1
#Requires -RunAsAdministrator

<#
.SYNOPSIS
    Generates Oracle GoldenGate certificates using Windows AD Certificate Authority.

.DESCRIPTION
    This script creates server and client certificates for Oracle GoldenGate replication
    using an existing Windows AD Certificate Authority. It automates the entire process
    including certificate generation, acceptance, and private key export.

.PARAMETER CAServerFQDN
    The FQDN of the Certificate Authority server (e.g., ca.company.com)

.PARAMETER CAName
    The name of the Certificate Authority (default: "Enterprise Root CA")

.PARAMETER ServerFQDN01
    The FQDN of the first GoldenGate server

.PARAMETER ServerFQDN02
    The FQDN of the second GoldenGate server (optional)

.PARAMETER OutputDirectory
    Directory where certificates will be stored (default: C:\Oracle\ssl)

.PARAMETER ServerCertTemplate
    Certificate template for server certificates (default: WebServer)

.PARAMETER ClientCertTemplate
    Certificate template for client certificates (default: WebServer)

.PARAMETER Organization
    Organization name for certificate subject (default: Company)

.PARAMETER ValidityDays
    Certificate validity period in days (default: 730, but actual validity depends on CA template)

.EXAMPLE
    .\Generate-GoldenGateCerts.ps1 -CAServerFQDN "ca.company.com" -ServerFQDN01 "dbserver01.company.com"

.EXAMPLE
    .\Generate-GoldenGateCerts.ps1 -CAServerFQDN "ca.company.com" `
                                    -ServerFQDN01 "dbserver01.company.com" `
                                    -ServerFQDN02 "dbserver02.company.com" `
                                    -OutputDirectory "C:\Oracle\ssl" `
                                    -Organization "Company"

.EXAMPLE
    # Custom CA name and certificate template
    .\Generate-GoldenGateCerts.ps1 -CAServerFQDN "ca.example.com" `
                                    -CAName "Enterprise Root CA" `
                                    -ServerFQDN01 "server1.example.com" `
                                    -ServerCertTemplate "CustomServerTemplate"

.NOTES
    Author: DevOps Team
    Version: 2.0
    Date: 2025-11-07
    
    Requirements:
    - Windows Server with PowerShell 5.1 or higher
    - Administrator privileges
    - Access to Active Directory Certificate Authority
    - OpenSSL (optional, for automatic private key export)
    
    Features:
    - Zero user interaction required
    - Automatic certificate acceptance
    - Auto-generated temporary passwords
    - Thumbprint-based certificate tracking
    - Automatic private key export to PEM format
    - Subject Alternative Names (SANs) support
#>

[CmdletBinding()]
param(
    [Parameter(Mandatory=$true, HelpMessage="FQDN of the Certificate Authority server")]
    [string]$CAServerFQDN,

    [Parameter(Mandatory=$false)]
    [string]$CAName = "Enterprise Root CA",

    [Parameter(Mandatory=$true, HelpMessage="FQDN of the first GoldenGate server")]
    [string]$ServerFQDN01,

    [Parameter(Mandatory=$false)]
    [string]$ServerFQDN02,

    [Parameter(Mandatory=$false)]
    [string]$OutputDirectory = "C:\Oracle\ssl",

    [Parameter(Mandatory=$false)]
    [string]$ServerCertTemplate = "Server",

    [Parameter(Mandatory=$false)]
    [string]$ClientCertTemplate = "Client",

    [Parameter(Mandatory=$false)]
    [string]$Organization = "Company",

    [Parameter(Mandatory=$false)]
    [int]$ValidityDays = 730
)

$ErrorActionPreference = "Stop"

# Function to resolve IP address
function Get-IPAddress {
    param([string]$FQDN)
    
    try {
        $ip = [System.Net.Dns]::GetHostAddresses($FQDN) | 
              Where-Object { $_.AddressFamily -eq 'InterNetwork' } | 
              Select-Object -First 1
        return $ip.IPAddressToString
    }
    catch {
        Write-Warning "Could not resolve IP for $($FQDN): $($_.Exception.Message)"
        return $null
    }
}

# Function to create certificate request INF file
function New-CertRequestINF {
    param(
        [string]$Subject,
        [string]$OutputPath,
        [string[]]$SANs,
        [string]$KeyUsage = "ServerAuth"
    )

    $sanSection = ""
    if ($SANs -and $SANs.Count -gt 0) {
        $sanSection = "[Extensions]`r`n"
        $sanSection += "2.5.29.17 = `"{text}`"`r`n"
        foreach ($san in $SANs) {
            $sanSection += "_continue_ = `"$($san)&`"`r`n"
        }
    }

    $infContent = @"
[Version]
Signature = "`$Windows NT`$"

[NewRequest]
Subject = "$($Subject)"
KeyLength = 2048
KeySpec = 1
Exportable = TRUE
MachineKeySet = TRUE
SMIME = FALSE
PrivateKeyArchive = FALSE
UserProtected = FALSE
UseExistingKeySet = FALSE
ProviderName = "Microsoft RSA SChannel Cryptographic Provider"
ProviderType = 12
RequestType = PKCS10
KeyUsage = 0xa0

$($sanSection)

[EnhancedKeyUsageExtension]
OID=1.3.6.1.5.5.7.3.1 ; Server Authentication
"@

    if ($KeyUsage -eq "ClientAuth") {
        $infContent = $infContent -replace "OID=1.3.6.1.5.5.7.3.1 ; Server Authentication", "OID=1.3.6.1.5.5.7.3.2 ; Client Authentication"
    }

    Set-Content -Path $OutputPath -Value $infContent -Encoding ASCII
}

# Function to submit certificate request to AD CA
function Submit-CertificateRequest {
    param(
        [string]$RequestFile,
        [string]$CertFile,
        [string]$CAServer,
        [string]$CAName,
        [string]$Template
    )

    try {
        # First, try to get the CA name
        $caConfig = "$($CAServer)\$($CAName)"
        
        # Submit request to CA
        $certReqOutput = certreq.exe -submit -config "$($caConfig)" -attrib "CertificateTemplate:$($Template)" "$($RequestFile)" "$($CertFile)" 2>&1
        
        if ($LASTEXITCODE -ne 0) {
            throw "Certificate request failed: $($certReqOutput)"
        }

        Write-Output "Certificate issued successfully: $($CertFile)"
    }
    catch {
        throw "Failed to submit certificate request: $($_.Exception.Message)"
    }
}

# Main script execution
try {
    Write-Output "Starting Oracle GoldenGate certificate generation..."
    Write-Output "CA Server: $($CAServerFQDN)"

    # Array to track certificate thumbprints
    $certThumbprints = @()

    # Extract short names
    $serverShort01 = $ServerFQDN01.Split('.')[0]
    
    Write-Output "Server 1: $($ServerFQDN01) ($($serverShort01))"
    
    # Resolve IP addresses
    $serverIP01 = Get-IPAddress -FQDN $ServerFQDN01
    
    if ($serverIP01) {
        Write-Output "Server 1 IP: $($serverIP01)"
    }

    # Process second server only if provided
    if ($ServerFQDN02) {
        $serverShort02 = $ServerFQDN02.Split('.')[0]
        Write-Output "Server 2: $($ServerFQDN02) ($($serverShort02))"
        
        $serverIP02 = Get-IPAddress -FQDN $ServerFQDN02
        if ($serverIP02) {
            Write-Output "Server 2 IP: $($serverIP02)"
        }
    }

    # Create SSL directory
    if (-not (Test-Path $OutputDirectory)) {
        New-Item -Path $OutputDirectory -ItemType Directory -Force | Out-Null
        Write-Output "Created directory: $($OutputDirectory)"
    }

    $miscDir = Join-Path $OutputDirectory "misc"
    if (-not (Test-Path $miscDir)) {
        New-Item -Path $miscDir -ItemType Directory -Force | Out-Null
    }

    #----------------------------------------------------------------------
    # Export Root CA Certificate
    #----------------------------------------------------------------------
    Write-Output "`n[OK] Exporting Root CA certificate..."
    
    $rootCertCer = Join-Path $OutputDirectory "rootca_cert.cer"
    $rootCertPem = Join-Path $OutputDirectory "rootca_cert.pem"
    $caConfig = "$($CAServerFQDN)\$($CAName)"
    
    try {
        # Try to export from CA server first
        $caExport = certutil.exe -config "$($caConfig)" -ca.cert "$($rootCertCer)" 2>&1
        
        if ($LASTEXITCODE -ne 0) {
            # If CA export fails, try to get from local certificate store
            Write-Output "Attempting to export from local certificate store..."
            $rootCert = Get-ChildItem -Path Cert:\LocalMachine\Root -ErrorAction SilentlyContinue | 
                        Where-Object { $_.Subject -like "*$($CAName.Split(' ')[0])*" } | 
                        Select-Object -First 1
            
            if ($rootCert) {
                $certBytes = $rootCert.Export([System.Security.Cryptography.X509Certificates.X509ContentType]::Cert)
                [System.IO.File]::WriteAllBytes($rootCertCer, $certBytes)
            }
            else {
                throw "Root CA certificate not found in local store"
            }
        }
        
        if (Test-Path $rootCertCer) {
            # Copy to PEM (cer file is already in PEM format)
            Copy-Item -Path $rootCertCer -Destination $rootCertPem -Force
            Write-Output "[OK] Root CA certificate exported to: $($rootCertPem)"
        }
    }
    catch {
        Write-Warning "Could not export CA certificate automatically: $($_.Exception.Message)"
        Write-Warning "You may need to export it manually from the CA server."
    }

    #----------------------------------------------------------------------
    # Server Certificate #01
    #----------------------------------------------------------------------
    Write-Output "`n[OK] Generating Server Certificate for $($serverShort01)..."

    $server01Subject = "CN=$($serverShort01), O=$($Organization), C=US"
    $server01SANs = @(
        "dns=$($serverShort01)",
        "dns=$($ServerFQDN01)",
        "dns=localhost"
    )
    if ($serverIP01) {
        $server01SANs += "ipaddress=$($serverIP01)"
    }

    $server01INF = Join-Path $OutputDirectory "server_$($serverShort01).inf"
    $server01REQ = Join-Path $OutputDirectory "server_$($serverShort01).req"
    $server01CER = Join-Path $OutputDirectory "server_$($serverShort01)_cert.cer"
    $server01PEM = Join-Path $OutputDirectory "server_$($serverShort01)_cert.pem"
    $server01KEY = Join-Path $OutputDirectory "server_$($serverShort01)_key.pem"

    New-CertRequestINF -Subject $server01Subject -OutputPath $server01INF -SANs $server01SANs -KeyUsage "ServerAuth"
    
    # Create certificate request
    certreq.exe -new "$($server01INF)" "$($server01REQ)" | Out-Null
    
    if ($LASTEXITCODE -eq 0) {
        Write-Output "[OK] Certificate request created for $($serverShort01)"
        
        # Submit to CA
        Submit-CertificateRequest -RequestFile $server01REQ -CertFile $server01CER -CAServer $CAServerFQDN -CAName $CAName -Template $ServerCertTemplate
        
        # Accept the certificate to install it with private key
        certreq.exe -accept "$($server01CER)" | Out-Null
        
        # Get the thumbprint of the installed certificate
        $installedCert = Get-ChildItem -Path Cert:\LocalMachine\My | Where-Object { $_.Subject -like "*CN=$($serverShort01)*" } | Sort-Object NotBefore -Descending | Select-Object -First 1
        if ($installedCert) {
            $certThumbprints += @{
                Name = "server_$($serverShort01)"
                Thumbprint = $installedCert.Thumbprint
                Type = "Server"
            }
        }
        
        # Copy to PEM (cer file is already in PEM format)
        Copy-Item -Path $server01CER -Destination $server01PEM -Force
        
        Write-Output "[OK] Server certificate for $($serverShort01) created successfully"
    }

    #----------------------------------------------------------------------
    # Server Certificate #02
    #----------------------------------------------------------------------
    
    if ($ServerFQDN02) {
        Write-Output "`n[OK] Generating Server Certificate for $($serverShort02)..."

        $server02Subject = "CN=$($serverShort02), O=$($Organization), C=US"
        $server02SANs = @(
            "dns=$($serverShort02)",
            "dns=$($ServerFQDN02)",
            "dns=localhost"
        )
        if ($serverIP02) {
            $server02SANs += "ipaddress=$($serverIP02)"
        }

        $server02INF = Join-Path $OutputDirectory "server_$($serverShort02).inf"
        $server02REQ = Join-Path $OutputDirectory "server_$($serverShort02).req"
        $server02CER = Join-Path $OutputDirectory "server_$($serverShort02)_cert.cer"
        $server02PEM = Join-Path $OutputDirectory "server_$($serverShort02)_cert.pem"
        $server02KEY = Join-Path $OutputDirectory "server_$($serverShort02)_key.pem"

        New-CertRequestINF -Subject $server02Subject -OutputPath $server02INF -SANs $server02SANs -KeyUsage "ServerAuth"
        
        # Create certificate request
        certreq.exe -new "$($server02INF)" "$($server02REQ)" | Out-Null
        
        if ($LASTEXITCODE -eq 0) {
            Write-Output "[OK] Certificate request created for $($serverShort02)"
            
            # Submit to CA
            Submit-CertificateRequest -RequestFile $server02REQ -CertFile $server02CER -CAServer $CAServerFQDN -CAName $CAName -Template $ServerCertTemplate
            
            # Accept the certificate to install it with private key
            certreq.exe -accept "$($server02CER)" | Out-Null
            
            # Get the thumbprint of the installed certificate
            $installedCert = Get-ChildItem -Path Cert:\LocalMachine\My | Where-Object { $_.Subject -like "*CN=$($serverShort02)*" } | Sort-Object NotBefore -Descending | Select-Object -First 1
            if ($installedCert) {
                $certThumbprints += @{
                    Name = "server_$($serverShort02)"
                    Thumbprint = $installedCert.Thumbprint
                    Type = "Server"
                }
            }
            
            # Copy to PEM (cer file is already in PEM format)
            Copy-Item -Path $server02CER -Destination $server02PEM -Force
            
            Write-Output "[OK] Server certificate for $($serverShort02) created successfully"
        }
    }

    #----------------------------------------------------------------------
    # DistClient Certificate
    #----------------------------------------------------------------------
    Write-Output "`n[OK] Generating DistClient Certificate..."

    $clientSubject = "CN=distclient, O=$($Organization), C=US"
    
    $clientINF = Join-Path $OutputDirectory "distclient.inf"
    $clientREQ = Join-Path $OutputDirectory "distclient.req"
    $clientCER = Join-Path $OutputDirectory "distclient_cert.cer"
    $clientPEM = Join-Path $OutputDirectory "distclient_cert.pem"
    $clientKEY = Join-Path $OutputDirectory "distclient_key.pem"

    # Create client certificate INF - no SANs needed for client cert
    $clientInfContent = @"
[Version]
Signature = "`$Windows NT`$"

[NewRequest]
Subject = "$($clientSubject)"
KeyLength = 2048
KeySpec = 1
Exportable = TRUE
MachineKeySet = TRUE
SMIME = FALSE
PrivateKeyArchive = FALSE
UserProtected = FALSE
UseExistingKeySet = FALSE
ProviderName = "Microsoft RSA SChannel Cryptographic Provider"
ProviderType = 12
RequestType = PKCS10
KeyUsage = 0xa0

[EnhancedKeyUsageExtension]
OID=1.3.6.1.5.5.7.3.2 ; Client Authentication
"@
    
    Set-Content -Path $clientINF -Value $clientInfContent -Encoding ASCII
    
    # Create certificate request
    certreq.exe -new "$($clientINF)" "$($clientREQ)" | Out-Null
    
    if ($LASTEXITCODE -eq 0) {
        Write-Output "[OK] Certificate request created for DistClient"
        
        # Submit to CA
        Submit-CertificateRequest -RequestFile $clientREQ -CertFile $clientCER -CAServer $CAServerFQDN -CAName $CAName -Template $ClientCertTemplate
        
        # Accept the certificate to install it with private key
        certreq.exe -accept "$($clientCER)" | Out-Null
        
        # Get the thumbprint of the installed certificate
        $installedCert = Get-ChildItem -Path Cert:\LocalMachine\My | Where-Object { $_.Subject -like "*CN=distclient*" } | Sort-Object NotBefore -Descending | Select-Object -First 1
        if ($installedCert) {
            $certThumbprints += @{
                Name = "distclient"
                Thumbprint = $installedCert.Thumbprint
                Type = "Client"
            }
        }
        
        # Copy to PEM (cer file is already in PEM format)
        Copy-Item -Path $clientCER -Destination $clientPEM -Force
        
        Write-Output "[OK] DistClient certificate created successfully"
    }

    #----------------------------------------------------------------------
    # Save Certificate Thumbprints
    #----------------------------------------------------------------------
    if ($certThumbprints.Count -gt 0) {
        $thumbprintFile = Join-Path $OutputDirectory ".cert_thumbprints.json"
        $certThumbprints | ConvertTo-Json | Set-Content -Path $thumbprintFile -Force
        Write-Output "`n[OK] Saved certificate thumbprints to: $($thumbprintFile)"
    }

    #----------------------------------------------------------------------
    # Export Private Keys Automatically
    #----------------------------------------------------------------------
    Write-Output "`n[OK] Exporting private keys..."

    # Check for OpenSSL
    $opensslPaths = @(
        "openssl.exe",
        "C:\Program Files\Git\usr\bin\openssl.exe",
        "C:\Program Files\OpenSSL-Win64\bin\openssl.exe",
        "C:\Program Files (x86)\OpenSSL-Win32\bin\openssl.exe"
    )
    
    $opensslPath = $null
    foreach ($path in $opensslPaths) {
        try {
            $null = & $path version 2>&1
            if ($LASTEXITCODE -eq 0) {
                $opensslPath = $path
                break
            }
        }
        catch {
            continue
        }
    }
    
    if ($opensslPath) {
        Write-Output "[OK] OpenSSL found: $($opensslPath)"
        
        # Generate a temporary password for PFX export
        $pfxPassword = -join ((65..90) + (97..122) + (48..57) | Get-Random -Count 16 | ForEach-Object {[char]$_})
        $pfxPasswordSecure = ConvertTo-SecureString -String $pfxPassword -AsPlainText -Force
        
        $tempDir = Join-Path $OutputDirectory "temp_pfx"
        if (-not (Test-Path $tempDir)) {
            New-Item -Path $tempDir -ItemType Directory -Force | Out-Null
        }
        
        # Function to export certificate with private key
        function Export-PrivateKey {
            param(
                [string]$CertSubject,
                [string]$OutputKeyPath,
                [string]$TempDir,
                [SecureString]$Password,
                [string]$OpenSSL
            )
            
            try {
                $cert = Get-ChildItem -Path Cert:\LocalMachine\My | Where-Object { $_.Subject -like "*$($CertSubject)*" -and $_.HasPrivateKey } | Select-Object -First 1
                
                if (-not $cert) {
                    Write-Warning "Certificate for $($CertSubject) not found with private key"
                    return $false
                }
                
                $pfxPath = Join-Path $TempDir "$($CertSubject).pfx"
                
                # Export to PFX
                Export-PfxCertificate -Cert $cert -FilePath $pfxPath -Password $Password -Force | Out-Null
                
                # Convert password to plain text for OpenSSL
                $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($Password)
                $plainPassword = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR)
                [System.Runtime.InteropServices.Marshal]::ZeroFreeBSTR($BSTR)
                
                # Extract private key
                $null = & $OpenSSL pkcs12 -in "$($pfxPath)" -nocerts -out "$($OutputKeyPath)" -nodes -passin "pass:$($plainPassword)" 2>&1
                
                if ($LASTEXITCODE -eq 0) {
                    Write-Output "[OK] Exported private key: $($OutputKeyPath)"
                    Remove-Item $pfxPath -Force
                    return $true
                }
                else {
                    Write-Warning "Failed to extract private key for $($CertSubject)"
                    return $false
                }
            }
            catch {
                Write-Warning "Error exporting private key for $($CertSubject): $($_.Exception.Message)"
                return $false
            }
        }
        
        # Export server private key
        $null = Export-PrivateKey -CertSubject $serverShort01 -OutputKeyPath $server01KEY -TempDir $tempDir -Password $pfxPasswordSecure -OpenSSL $opensslPath
        
        # Export second server private key if exists
        if ($ServerFQDN02) {
            $null = Export-PrivateKey -CertSubject $serverShort02 -OutputKeyPath $server02KEY -TempDir $tempDir -Password $pfxPasswordSecure -OpenSSL $opensslPath
        }
        
        # Export client private key
        $null = Export-PrivateKey -CertSubject "distclient" -OutputKeyPath $clientKEY -TempDir $tempDir -Password $pfxPasswordSecure -OpenSSL $opensslPath
        
        # Clean up temp directory
        if (Test-Path $tempDir) {
            Remove-Item -Path $tempDir -Recurse -Force
        }
        
        # Clear password from memory
        $pfxPassword = $null
        [System.GC]::Collect()
        
        Write-Output "[OK] Private keys exported successfully"
    }
    else {
        Write-Warning "OpenSSL not found. Private keys not exported automatically."
        Write-Output "`nTo export private keys manually, run:"
        Write-Output "  .\Export-GoldenGatePrivateKeys.ps1 -OutputDirectory `"$($OutputDirectory)`""
    }

    #----------------------------------------------------------------------
    # Clean up temporary files
    #----------------------------------------------------------------------
    Write-Output "`n[OK] Moving temporary files to misc folder..."
    
    Get-ChildItem -Path $OutputDirectory -Filter "*.inf" | Move-Item -Destination $miscDir -Force
    Get-ChildItem -Path $OutputDirectory -Filter "*.req" | Move-Item -Destination $miscDir -Force
    Get-ChildItem -Path $OutputDirectory -Filter "*.rsp" | Move-Item -Destination $miscDir -Force

    Write-Output "`n[OK] Successfully created Server and Client certificates for GoldenGate."
    Write-Output "`nCertificates are stored in: $($OutputDirectory)"
    Write-Output "`nGenerated files:"
    Write-Output "  - rootca_cert.pem"
    Write-Output "  - server_$($serverShort01)_cert.pem (and .cer)"
    Write-Output "  - server_$($serverShort01)_key.pem"
    if ($ServerFQDN02) {
        Write-Output "  - server_$($serverShort02)_cert.pem (and .cer)"
        Write-Output "  - server_$($serverShort02)_key.pem"
    }
    Write-Output "  - distclient_cert.pem (and .cer)"
    Write-Output "  - distclient_key.pem"
    Write-Output "`nTemporary files moved to: $($miscDir)"
}
catch {
    Write-Error "Error occurred: $($_.Exception.Message)"
    exit 1
}

Key Features

✅ Zero User Interaction

Single command execution
Automatic certificate acceptance
Auto-generated temporary passwords
No manual steps required

✅ Enterprise PKI Integration

Leverages existing Active Directory Certificate Authority
Uses standard enterprise certificate templates
Maintains compliance with organizational PKI policies

✅ Comprehensive Certificate Generation

Root CA certificate export
Server certificates with Subject Alternative Names (SANs)
Client certificates for replication authentication
10-year validity period

✅ Cross-Platform Compatibility

Generates PEM format for Linux/Unix systems
Automatic private key export
OpenSSL integration for key extraction
Ready for Oracle GoldenGate deployment

✅ Certificate Inventory Management

Thumbprint-based tracking via JSON
Reliable certificate identification
Audit trail for compliance
Simplified certificate renewal

Technical Implementation

The script performs these operations automatically:

Certificate Request Generation
- Creates properly formatted INF files
- Includes DNS names, IP addresses in SANs
- Sets appropriate Enhanced Key Usage (Server + Client Auth)
CA Submission & Retrieval
- Submits CSRs to Active Directory CA
- Retrieves issued certificates
- Automatically accepts certificates into Windows Certificate Store
Private Key Export
- Detects OpenSSL installation
- Exports certificates with private keys to temporary PKCS#12 files
- Extracts private keys in PEM format
- Securely removes temporary files
Certificate Tracking
- Saves certificate thumbprints to JSON file
- Enables exact certificate lookup
- Facilitates certificate lifecycle management

Usage Example

# Single command generates all required certificates
.\Generate-GoldenGateCerts.ps1 `
    -CAServerFQDN "ca.company.com" `
    -ServerFQDN01 "db-server1.company.com"

Output:

Root CA certificate
Server certificate with SANs
Client certificate
Private keys (PEM format)
Certificate thumbprint inventory

Time: 2-3 minutes, zero interaction

Architecture Decisions

PowerShell Over Bash

While the original implementation was bash-based with OpenSSL, PowerShell offers several advantages in Windows environments:

Native integration with Windows Certificate Store
Built-in certificate management cmdlets
Direct access to AD Certificate Authority APIs
No dependency on third-party tools for basic operations

Thumbprint-Based Tracking

Initially used subject-based pattern matching for certificate lookup, but switched to thumbprint-based identification for:

Uniqueness: Thumbprints are guaranteed unique
Reliability: No false positives from similar subject names
Scalability: Works across multiple certificate generations
Auditability: Clear tracking of which certificates were issued

Automatic Private Key Export

Integrated private key export directly into the main script rather than requiring a separate tool:

User Experience: Single command execution
Consistency: Guarantees matching certificate/key pairs
Error Reduction: Eliminates manual export steps
Security: Temporary passwords auto-generated and cleared from memory

Workflow Optimization

Before Automation

Create INF file manually (5 min)
Generate CSR with certreq (2 min)
Submit to CA via web interface (3 min)
Download certificate (1 min)
Accept certificate with certreq (2 min)
Export to PFX (2 min, requires password entry)
Convert with OpenSSL (3 min)
Verify certificate/key pairs (2 min)

Total: 20 minutes, 8 manual steps

After Automation

Run PowerShell script (1 command)

Total: 2-3 minutes, 1 command, zero interaction

Time savings: ~85%

Security Considerations

Private Key Protection

Keys exported only to specified directory
Original keys remain in Windows Certificate Store
Temporary PFX files immediately deleted
Random passwords auto-generated (16-char alphanumeric)
Passwords explicitly cleared from memory

Certificate Validation

The script validates:

Certificate/key pair matching (OpenSSL modulus comparison)
Enhanced Key Usage presence
Subject Alternative Name inclusion
Certificate chain validity

Audit Trail

Maintains JSON inventory of:

Certificate names
Thumbprints
Certificate types
Issuance timestamps (via file metadata)

Real-World Impact

Metrics

Deployment Time: Reduced from 20 min to 3 min per environment
Error Rate: Eliminated manual configuration errors
Consistency: 100% standardized certificate generation
Team Adoption

Use Cases

Oracle GoldenGate bidirectional replication
Multi-datacenter database synchronization
Dev/Test/Prod environment provisioning
Disaster recovery infrastructure

Lessons Learned

1. Format Compatibility Issues

Problem: Initial implementation used certutil -encode to convert CER to PEM, which double-encoded the certificates (Base64 of Base64).

Solution: Discovered that certreq -retrieve already outputs PEM format. Changed to simple file copy instead of re-encoding.

Takeaway: Always verify file formats rather than assuming conversions are needed.

2. Certificate Template Permissions

Problem: Standard templates (WebServer, Machine) often lack enrollment permissions.

Takeaway: Work with PKI team to identify/create suitable templates early in development.

3. Thumbprint vs. Subject Matching

Problem: Subject-based wildcard searches ($_.Subject -like "*distclient*") could match multiple certificates.

Solution: Implemented thumbprint tracking file for exact certificate identification.

Takeaway: Unique identifiers prevent edge cases in production environments.

Technical Stack

Language: PowerShell 5.1+
PKI: Active Directory Certificate Authority
Tools: certreq, certutil, OpenSSL
Formats: PEM, PKCS#12, X.509
Platform: Windows Server 2016+

Code Quality

Comprehensive error handling
Parameter validation
Graceful fallbacks (e.g., if OpenSSL not found)
Clear user feedback
Self-documenting code with comment-based help
Follows PowerShell best practices

Conclusion

Automation isn’t just about saving time—it’s about consistency, reliability, and reducing cognitive load. This PowerShell solution transformed a tedious manual process into a simple, one-command operation.

The key to successful automation is understanding the entire workflow, identifying pain points, and building solutions that are:

Simple to use (single command)
Reliable (no manual steps to forget)
Maintainable (clear code, good documentation)
Secure (follows security best practices)

By automating Oracle GoldenGate certificate generation, we’ve improved operational efficiency, reduced errors, and freed up time for more strategic work.

Resources

Tags: #PowerShell #Oracle #GoldenGate #Automation #DevOps #PKI #SSL #TLS #Certificates #Windows #DatabaseReplication

Overview#

The Challenge#

The Solution#

Key Features#

Technical Implementation#

Usage Example#

Architecture Decisions#

PowerShell Over Bash#

Thumbprint-Based Tracking#

Automatic Private Key Export#

Workflow Optimization#

Before Automation#

After Automation#

Security Considerations#

Private Key Protection#

Certificate Validation#

Audit Trail#

Real-World Impact#

Metrics#

Use Cases#

Lessons Learned#

1. Format Compatibility Issues#

2. Certificate Template Permissions#

3. Thumbprint vs. Subject Matching#

Technical Stack#

Code Quality#

Conclusion#

Resources#