Configure Azure (SAML as idP) For PowerSchool

Component Role
PowerSchool Service Provider (SP)
Azure AD (Entra ID) Identity Provider (IdP)
User Browser-based authentication
  • PowerSchool can only be setup to point to one and only one SAML identity provider.
  • PowerSchool SIS currently supports only one IdP at a time
    • SAML/WS-Trust
    • OIDC/OpenID Connect
  • user ID attribute is used in the SAML authentication are
    • psguid aka ‘PowerSchool’s own global unique identifiers
    • state-id aka ‘State identifiers

PowerSchool will expect an authenticationId attribute from the identity provider during a successful single sign-on

User Type Attribute Value Source
admin authenticationId psguid U SERS.PSGUID
admin authenticationId state-id USERS.SIF_STATEPRID
guardian authenticationId psguid GUARDIAN.PSGUID
guardian authenticationId state-id GUARDIAN.STATE_GUARDIANNUMBER
student authenticationId psguid STUDENTS.PSGUID
student authenticationId state-id STUDENTS.STATE_STUDENTNUMBER
teacher authenticationId psguid USERS.PSGUID
teacher authenticationId state-id USERS.SIF_STATEPRID

Step 1: PowerSchool SSO Plugin

plugin.xml

<?xml version="1.0" encoding="UTF-8"?>
<plugin xmlns="http://plugin.powerschool.pearson.com"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://plugin.powerschool.pearson.com plugin.xsd"
    name="Azure SSO - SAML IdP" version="1.0.2" description="Azure SSO - SAML as IdP Plugin">
    <saml
        name="mantle-test-powerschool"
        idp-name="azure-identity-provider"
        idp-entity-id="https://sts.windows.net/<tenant_id>"
        idp-metadata-url="https://login.microsoftonline.com/<tenant_id>/federationmetadata/2007-06/federationmetadata.xml?appid=<app_id>">
        <attributes>
            <user type="teacher">
                <attribute name="authenticationId" attribute-value="state-id" />
                <!-- <attribute name="authenticationId" attribute-value="psguid" /> -->
            </user>
            <user type="admin">
                <attribute name="authenticationId" attribute-value="state-id" />
                <!-- <attribute name="authenticationId" attribute-value="psguid" /> -->
            </user>
            <user type="student">
                <attribute name="authenticationId" attribute-value="state-id" />
                <!-- <attribute name="authenticationId" attribute-value="psguid" /> -->
            </user>
            <user type="guardian">
                <attribute name="authenticationId" attribute-value="state-id" />
                <!-- <attribute name="authenticationId" attribute-value="psguid" /> -->
            </user>
        </attributes>
    </saml>
    <publisher name="PrincePARK">
        <contact email="[email protected]"></contact>
    </publisher>
</plugin>

saml Attributes

Property Description Can Modify
name The name of the service provider. Must provide a short name that will become part of the addresses used during SAML communication. No
idp-name The name of the identity provider. Yes
idp-entity-id The entity ID of the identity provider. Defined in the form of a URI. Yes
idp-metadata-url The URL from which the PowerSchool can obtain a copy of the identity provider metadata. . Yes

The identity provider must also be configured to return an authenticationId attribute on a successful single-sign on. This attribute must contain the either or identifier (as defined in the plugin).

From https://sts.windows.net/<tenant_id> and https://login.microsoftonline.com/<tenant_id>/federationmetadata/2007-06/federationmetadata.xml?appid=<app_id>">, <tenant_id> and <app_id> wil be replaced by the respective value

Step 2: Plugin Installation

  • Install the Azure SSO - SAML IdP Plugin to PowerSchool
  • Enable the plugin
  • Restart the PowerSchool Instance

Step 3: Plugin Configuration

Open PowerSchool Plugin Page > ‘SAML Service Provider Setup’ Page

Local Service Provider Settings > Name field serve as the entity_key for the Azure Single Sign On > Identifier (Entity ID)

Step 4: Configuring Azure SAML IdP

  • Azure Portal → Enterprise Applications
  • Create Non-gallery new application (app name PowerSchool SSO)
  • Enable Single Sign-On
  • Select SAML
  • from Set up Single Sign-On with SAML Configure Basic SAML Settings (Azure)
    • Identifier (Entity ID) → from PowerSchool https://mantleai-test.powerschool.com:443/saml/entity-id/mantle-test-powerschool
    • Reply URL (ACS URL) → from PowerSchool https://mantleai-test.powerschool.com:443/saml/SSO/alias/mantle-test-powerschool
  • from Set up Single Sign-On with SAML Configure Claims & Attributes

Typical PowerSchool-required claims:

Claim Azure Source
Unique User Identifier (Name ID) user.userprincipalname
emailaddress user.mail
givenname user.givenname
name user.userprincipalname
surname user.surname

Make Sure the below claim is avaliable

Claim Azure Source
authenticationId user.mail

Step 5: Finish PowerSchool Plugin SAML Service Provider Setup

From Azure Set up Single Sign-On with SAML page copy App Federation Metadata Url and Microsoft Entra Identifier

Open PowerSchool Plugin Page > ‘SAML Service Provider Setup’ Page,

  • Copy Microsoft Entra Identifier from Azure to External Identity Provider Settings > Entity ID in PowerSchool SAML Service Provider Setup Page
  • Copy App Federation Metadata Url from Azure to External Identity Provider Settings > Metadata URL in PowerSchool SAML Service Provider Setup Page

Notes Assertion Consumer Service (ACS) URL: Where Azure sends the SAML response

Traefik with Static Wildcard and Dynamic Let’s Encrypt Certificates

A comprehensive guide for configuring Traefik to use both static wildcard certificates and dynamic Let’s Encrypt certificates simultaneously.

Table of Contents

Overview

This configuration enables Traefik to:

  1. Use a static wildcard certificate for all *.apps.abc.com domains
  2. Automatically obtain and manage Let’s Encrypt certificates for other domains (e.g., app1.com, app2.com)
  3. Automatically select the correct certificate based on the domain being accessed

Use Cases

  • Internal applications: Use wildcard certificate for *.apps.abc.com (webapp.apps.abc.com, api.apps.abc.com, etc.)
  • Customer-facing domains: Use Let’s Encrypt for app1.com, app2.com, etc.
  • Mixed environments: Same application accessible via both internal and external domains

How Certificate Selection Works

Traefik automatically selects certificates using this priority logic:

┌─────────────────────────────────────────────────────────────┐
│ Incoming HTTPS Request for domain: example.com              │
└──────────────────┬──────────────────────────────────────────┘
                   │
                   ▼
┌─────────────────────────────────────────────────────────────┐
│ Step 1: Does domain match *.apps.abc.com pattern?           │
└──────────────────┬──────────────────────────────────────────┘
                   │
          ┌────────┴─────────┐
          │                  │
         YES                NO
          │                  │
          ▼                  ▼
    ┌──────────┐      ┌──────────────────────────────────────┐
    │ USE      │      │ Step 2: Is certresolver specified?   │
    │ STATIC   │      └────────┬─────────────────────────────┘
    │ WILDCARD │               │
    │ CERT     │      ┌────────┴──────┐
    └──────────┘      │               │
                     YES             NO
                      │               │
                      ▼               ▼
               ┌──────────────┐  ┌──────────┐
               │ USE/REQUEST  │  │ ERROR    │
               │ LET'S        │  │ or       │
               │ ENCRYPT      │  │ DEFAULT  │
               │ CERT         │  │ CERT     │
               └──────────────┘  └──────────┘

Certificate Matching Examples

Domain Matches Pattern? Label Config Certificate Used
webapp.apps.abc.com ✅ Yes (*.apps.abc.com) tls=true Static Wildcard
api.apps.abc.com ✅ Yes (*.apps.abc.com) tls=true Static Wildcard
admin.apps.abc.com ✅ Yes (*.apps.abc.com) tls=true Static Wildcard
app1.com ❌ No tls.certresolver=letsencrypt Let’s Encrypt
app2.com ❌ No tls.certresolver=letsencrypt Let’s Encrypt
www.app1.com ❌ No tls.certresolver=letsencrypt Let’s Encrypt

Quick Reference Table

The Two Label Patterns

Certificate Type Domain Examples Docker Label Auto-Renewal
Static Wildcard *.apps.abc.com tls=true ❌ Manual
Dynamic Let’s Encrypt app1.com, app2.com tls.certresolver=letsencrypt ✅ Automatic

Critical Label Difference

✅ CORRECT for *.apps.abc.com:

- "traefik.http.routers.myapp.tls=true" # No certresolver

❌ WRONG for *.apps.abc.com:

- "traefik.http.routers.myapp.tls.certresolver=letsencrypt" # Will bypass wildcard!

✅ CORRECT for app1.com:

- "traefik.http.routers.app1.tls.certresolver=letsencrypt" # With certresolver

❌ WRONG for app1.com:

- "traefik.http.routers.app1.tls=true" # Won't get Let's Encrypt cert

Directory Structure

Create the following directory structure on your host:

traefik/
├── docker-compose.yml                  # Traefik service definition
├── traefik.yml                         # Static configuration
├── dynamic/                            # Dynamic configuration files
│   └── tls-certificates.yml           # Static wildcard certificate config
├── certs/                              # Your static certificates
│   └── wildcard.apps.abc.com/
│       ├── fullchain.pem              # Certificate + intermediate chain
│       └── privkey.pem                # Private key
├── letsencrypt/                        # Let's Encrypt storage (auto-generated)
│   └── acme.json                      # Auto-created, stores LE certificates
└── logs/                               # Traefik logs
    ├── traefik.log
    └── access.log

Configuration Files

1. Traefik Static Configuration

File: traefik.yml

# Traefik Static Configuration
# Main configuration file

global:
  checkNewVersion: true
  sendAnonymousUsage: false

api:
  dashboard: true
  insecure: false

entryPoints:
  web:
    address: ":80"
    http:
      redirections:
        entryPoint:
          to: websecure
          scheme: https
          permanent: true

  websecure:
    address: ":443"

providers:
  docker:
    endpoint: "unix:///var/run/docker.sock"
    exposedByDefault: false
    network: web
    watch: true

  # File provider for static wildcard certificate
  file:
    directory: "/etc/traefik/dynamic"
    watch: true

# Let's Encrypt Configuration for Dynamic Certificates
certificatesResolvers:
  letsencrypt:
    acme:
      email: "[email protected]" # ← CHANGE THIS
      storage: "/letsencrypt/acme.json"

      # HTTP Challenge (recommended for simple setups)
      httpChallenge:
        entryPoint: web

      # OR TLS Challenge (uncomment if preferred)
      # tlsChallenge: {}

      # OR DNS Challenge (uncomment for wildcard LE certs or firewall scenarios)
      # dnsChallenge:
      #   provider: cloudflare  # or your DNS provider
      #   delayBeforeCheck: 0
      #   resolvers:
      #     - "1.1.1.1:53"
      #     - "8.8.8.8:53"

log:
  level: INFO
  filePath: "/var/log/traefik/traefik.log"

accessLog:
  filePath: "/var/log/traefik/access.log"

Important: Change [email protected] to your actual email address for Let’s Encrypt notifications.

2. Dynamic TLS Configuration

File: dynamic/tls-certificates.yml

# Dynamic TLS Configuration
# Defines your static wildcard certificate for *.apps.abc.com

tls:
  certificates:
    # Static WILDCARD certificate for *.apps.abc.com
    - certFile: /etc/traefik/certs/wildcard.apps.abc.com/fullchain.pem
      keyFile: /etc/traefik/certs/wildcard.apps.abc.com/privkey.pem
      stores:
        - default

  stores:
    default:
      defaultCertificate:
        # Optional: Set a default fallback certificate
        certFile: /etc/traefik/certs/wildcard.apps.abc.com/fullchain.pem
        keyFile: /etc/traefik/certs/wildcard.apps.abc.com/privkey.pem

  options:
    default:
      minVersion: VersionTLS12
      sniStrict: true
      cipherSuites:
        - TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
        - TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
        - TLS_ECDHE_RSA_WITH_CHACHA20_POLY1305
        - TLS_AES_128_GCM_SHA256
        - TLS_AES_256_GCM_SHA384
        - TLS_CHACHA20_POLY1305_SHA256

3. Docker Compose for Traefik

File: docker-compose.yml

version: "3.8"

services:
  traefik:
    image: traefik:v2.10
    container_name: traefik
    restart: unless-stopped
    security_opt:
      - no-new-privileges:true
    networks:
      - web
    ports:
      - "80:80"
      - "443:443"
    volumes:
      # Traefik static configuration
      - ./traefik.yml:/traefik.yml:ro

      # Dynamic configuration directory
      - ./dynamic:/etc/traefik/dynamic:ro

      # Static wildcard certificate
      - ./certs:/etc/traefik/certs:ro

      # Let's Encrypt certificate storage
      - ./letsencrypt:/letsencrypt

      # Logs
      - ./logs:/var/log/traefik

      # Docker socket
      - /var/run/docker.sock:/var/run/docker.sock:ro
    labels:
      - "traefik.enable=true"

      # Dashboard (uses static wildcard certificate)
      - "traefik.http.routers.traefik.rule=Host(`traefik.apps.abc.com`)"
      - "traefik.http.routers.traefik.service=api@internal"
      - "traefik.http.routers.traefik.entrypoints=websecure"
      - "traefik.http.routers.traefik.tls=true"

      # Dashboard authentication
      # Generate password: echo $(htpasswd -nb admin yourpassword) | sed -e s/\\$/\\$\\$/g
      - "traefik.http.routers.traefik.middlewares=auth"
      - "traefik.http.middlewares.auth.basicauth.users=admin:$$apr1$$8evjzfst$$YgiYLjSK1e5RJTyNvR4QH0"

networks:
  web:
    external: true
# Create the network first: docker network create web

4. Example Application Configurations

File: docker-compose-apps.yml (separate file for your applications)

version: "3.8"

services:
  # ================================================================
  # APPS USING STATIC WILDCARD CERTIFICATE (*.apps.abc.com)
  # ================================================================

  # Example 1: Web application on *.apps.abc.com
  webapp:
    image: nginx:alpine
    container_name: webapp
    restart: unless-stopped
    networks:
      - web
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.webapp.rule=Host(`webapp.apps.abc.com`)"
      - "traefik.http.routers.webapp.entrypoints=websecure"
      # Key: tls=true WITHOUT certresolver
      - "traefik.http.routers.webapp.tls=true"
      - "traefik.http.services.webapp.loadbalancer.server.port=80"

  # Example 2: API on *.apps.abc.com
  api:
    image: nginx:alpine
    container_name: api
    restart: unless-stopped
    networks:
      - web
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.api.rule=Host(`api.apps.abc.com`)"
      - "traefik.http.routers.api.entrypoints=websecure"
      - "traefik.http.routers.api.tls=true"
      - "traefik.http.services.api.loadbalancer.server.port=80"

  # Example 3: Admin panel on *.apps.abc.com
  admin:
    image: nginx:alpine
    container_name: admin
    restart: unless-stopped
    networks:
      - web
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.admin.rule=Host(`admin.apps.abc.com`)"
      - "traefik.http.routers.admin.entrypoints=websecure"
      - "traefik.http.routers.admin.tls=true"
      - "traefik.http.services.admin.loadbalancer.server.port=80"

  # ================================================================
  # APPS USING DYNAMIC LET'S ENCRYPT CERTIFICATES
  # ================================================================

  # Example 4: App1 with Let's Encrypt certificate
  app1:
    image: nginx:alpine
    container_name: app1
    restart: unless-stopped
    networks:
      - web
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.app1.rule=Host(`app1.com`) || Host(`www.app1.com`)"
      - "traefik.http.routers.app1.entrypoints=websecure"
      # Key: tls.certresolver=letsencrypt
      - "traefik.http.routers.app1.tls.certresolver=letsencrypt"
      - "traefik.http.services.app1.loadbalancer.server.port=80"

  # Example 5: App2 with Let's Encrypt certificate
  app2:
    image: nginx:alpine
    container_name: app2
    restart: unless-stopped
    networks:
      - web
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.app2.rule=Host(`app2.com`) || Host(`www.app2.com`)"
      - "traefik.http.routers.app2.entrypoints=websecure"
      - "traefik.http.routers.app2.tls.certresolver=letsencrypt"
      - "traefik.http.services.app2.loadbalancer.server.port=80"

  # Example 6: App3 with Let's Encrypt certificate
  app3:
    image: nginx:alpine
    container_name: app3
    restart: unless-stopped
    networks:
      - web
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.app3.rule=Host(`app3.com`)"
      - "traefik.http.routers.app3.entrypoints=websecure"
      - "traefik.http.routers.app3.tls.certresolver=letsencrypt"
      - "traefik.http.services.app3.loadbalancer.server.port=80"

  # ================================================================
  # SPECIAL CASE: App accessible via both certificate types
  # ================================================================

  # Example 7: App with both internal and external domains
  multiapp:
    image: nginx:alpine
    container_name: multiapp
    restart: unless-stopped
    networks:
      - web
    labels:
      - "traefik.enable=true"

      # Router 1: Internal access (static wildcard)
      - "traefik.http.routers.multiapp-internal.rule=Host(`multi.apps.abc.com`)"
      - "traefik.http.routers.multiapp-internal.entrypoints=websecure"
      - "traefik.http.routers.multiapp-internal.tls=true"
      - "traefik.http.routers.multiapp-internal.service=multiapp"

      # Router 2: External access (Let's Encrypt)
      - "traefik.http.routers.multiapp-external.rule=Host(`multiapp.com`)"
      - "traefik.http.routers.multiapp-external.entrypoints=websecure"
      - "traefik.http.routers.multiapp-external.tls.certresolver=letsencrypt"
      - "traefik.http.routers.multiapp-external.service=multiapp"

      # Shared service definition
      - "traefik.http.services.multiapp.loadbalancer.server.port=80"

networks:
  web:
    external: true

Step-by-Step Setup

Step 1: Create Directory Structure

# Create directories
mkdir -p traefik/{dynamic,certs/wildcard.apps.abc.com,letsencrypt,logs}
cd traefik

Step 2: Place Your Wildcard Certificate

# Copy your wildcard certificate files
cp /path/to/your/wildcard-fullchain.pem certs/wildcard.apps.abc.com/fullchain.pem
cp /path/to/your/wildcard-privkey.pem certs/wildcard.apps.abc.com/privkey.pem

# Verify the files exist
ls -la certs/wildcard.apps.abc.com/

Note: Your certificate must be valid for *.apps.abc.com (wildcard).

Step 3: Set Permissions for Let’s Encrypt Storage

# Create acme.json with correct permissions
touch letsencrypt/acme.json
chmod 600 letsencrypt/acme.json

Critical: The acme.json file must have 600 permissions or Traefik will refuse to start.

Step 4: Create Configuration Files

Create the following files with the content from the Configuration Files section:

  1. traefik.yml (main configuration)
  2. dynamic/tls-certificates.yml (wildcard certificate definition)
  3. docker-compose.yml (Traefik deployment)

Important: Update the email address in traefik.yml:

email: "[email protected]" # ← Change this

Step 5: Create Docker Network

docker network create web

This network will be shared by Traefik and all your applications.

Step 6: Deploy Traefik

# Start Traefik
docker-compose up -d

# Check logs
docker logs traefik

# Follow logs
docker logs -f traefik

Step 7: Verify Traefik is Running

# Check container status
docker ps | grep traefik

# Check if wildcard certificate is loaded
docker logs traefik | grep -i certificate

You should see messages like:

Configuration loaded from file: /traefik.yml
Loading certificate from file: /etc/traefik/certs/wildcard.apps.abc.com/fullchain.pem

Step 8: Deploy Your First Application

Create a simple test application:

# test-app.yml
version: "3.8"

services:
  test:
    image: nginx:alpine
    container_name: test-app
    networks:
      - web
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.test.rule=Host(`test.apps.abc.com`)"
      - "traefik.http.routers.test.entrypoints=websecure"
      - "traefik.http.routers.test.tls=true"
      - "traefik.http.services.test.loadbalancer.server.port=80"

networks:
  web:
    external: true
# Deploy test app
docker-compose -f test-app.yml up -d

# Test it (replace with your server IP)
curl -k https://test.apps.abc.com

Step 9: Verify Certificate

# Check which certificate is being served
echo | openssl s_client -servername test.apps.abc.com -connect YOUR_SERVER_IP:443 2>/dev/null | openssl x509 -noout -subject -issuer -dates

Docker Label Patterns

Pattern 1: Static Wildcard Certificate (*.apps.abc.com)

Use this for any subdomain of apps.abc.com:

labels:
  - "traefik.enable=true"
  - "traefik.http.routers.ROUTER_NAME.rule=Host(`subdomain.apps.abc.com`)"
  - "traefik.http.routers.ROUTER_NAME.entrypoints=websecure"
  - "traefik.http.routers.ROUTER_NAME.tls=true" # ← No certresolver
  - "traefik.http.services.SERVICE_NAME.loadbalancer.server.port=PORT"

Key Points:

  • Use tls=true (without certresolver)
  • Traefik automatically uses the static wildcard certificate
  • Works for: webapp.apps.abc.com, api.apps.abc.com, anything.apps.abc.com

Pattern 2: Dynamic Let’s Encrypt Certificate

Use this for custom domains like app1.com, app2.com:

labels:
  - "traefik.enable=true"
  - "traefik.http.routers.ROUTER_NAME.rule=Host(`app1.com`)"
  - "traefik.http.routers.ROUTER_NAME.entrypoints=websecure"
  - "traefik.http.routers.ROUTER_NAME.tls.certresolver=letsencrypt" # ← With certresolver
  - "traefik.http.services.SERVICE_NAME.loadbalancer.server.port=PORT"

Key Points:

  • Use tls.certresolver=letsencrypt
  • Traefik automatically requests certificate from Let’s Encrypt
  • Certificate is automatically renewed
  • Can include multiple domains: Host(\app1.com`) || Host(`www.app1.com`)`

Pattern 3: Multiple Domains with Different Certificates

labels:
  - "traefik.enable=true"

  # Router 1: Internal domain (static wildcard)
  - "traefik.http.routers.app-internal.rule=Host(`app.apps.abc.com`)"
  - "traefik.http.routers.app-internal.entrypoints=websecure"
  - "traefik.http.routers.app-internal.tls=true"
  - "traefik.http.routers.app-internal.service=app"

  # Router 2: External domain (Let's Encrypt)
  - "traefik.http.routers.app-external.rule=Host(`app.com`)"
  - "traefik.http.routers.app-external.entrypoints=websecure"
  - "traefik.http.routers.app-external.tls.certresolver=letsencrypt"
  - "traefik.http.routers.app-external.service=app"

  # Service (shared)
  - "traefik.http.services.app.loadbalancer.server.port=3000"

Common Scenarios

Scenario 1: Simple Internal Web Application

services:
  myapp:
    image: myapp:latest
    networks:
      - web
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.myapp.rule=Host(`myapp.apps.abc.com`)"
      - "traefik.http.routers.myapp.entrypoints=websecure"
      - "traefik.http.routers.myapp.tls=true"
      - "traefik.http.services.myapp.loadbalancer.server.port=3000"

Result: Uses static wildcard certificate *.apps.abc.com

Scenario 2: Customer-Facing Application

services:
  customer-app:
    image: customer-app:latest
    networks:
      - web
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.customer.rule=Host(`myapp.com`) || Host(`www.myapp.com`)"
      - "traefik.http.routers.customer.entrypoints=websecure"
      - "traefik.http.routers.customer.tls.certresolver=letsencrypt"
      - "traefik.http.services.customer.loadbalancer.server.port=80"

Result: Traefik automatically requests and manages Let’s Encrypt certificate for myapp.com and www.myapp.com

Scenario 3: API with Authentication Middleware

services:
  api:
    image: api:latest
    networks:
      - web
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.api.rule=Host(`api.apps.abc.com`)"
      - "traefik.http.routers.api.entrypoints=websecure"
      - "traefik.http.routers.api.tls=true"
      - "traefik.http.routers.api.middlewares=api-auth"
      - "traefik.http.middlewares.api-auth.basicauth.users=user:$$apr1$$..."
      - "traefik.http.services.api.loadbalancer.server.port=8080"

Result: Uses static wildcard certificate with basic authentication

Scenario 4: Application with Custom Headers

services:
  webapp:
    image: webapp:latest
    networks:
      - web
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.webapp.rule=Host(`webapp.apps.abc.com`)"
      - "traefik.http.routers.webapp.entrypoints=websecure"
      - "traefik.http.routers.webapp.tls=true"
      - "traefik.http.routers.webapp.middlewares=webapp-headers"
      - "traefik.http.middlewares.webapp-headers.headers.customrequestheaders.X-Custom-Header=value"
      - "traefik.http.services.webapp.loadbalancer.server.port=80"

Certificate Selection Decision Tree

User accesses: https://example.apps.abc.com
                        │
                        ▼
┌──────────────────────────────────────────────┐
│ Does "example.apps.abc.com" match            │
│ wildcard pattern "*.apps.abc.com"?           │
└──────────────────┬───────────────────────────┘
                   │
          ┌────────┴─────────┐
          │                  │
        YES                 NO
          │                  │
          ▼                  ▼
┌─────────────────┐  ┌──────────────────────────┐
│ Check router    │  │ Check if router has      │
│ configuration   │  │ certresolver defined     │
└────────┬────────┘  └──────────┬───────────────┘
         │                      │
         ▼                      ▼
┌─────────────────┐  ┌──────────────────────────┐
│ Has tls=true    │  │ certresolver=letsencrypt │
│ (no resolver)?  │  │                          │
└────────┬────────┘  └──────────┬───────────────┘
         │                      │
         ▼                      ▼
┌─────────────────┐  ┌──────────────────────────┐
│ USE STATIC      │  │ Check acme.json for      │
│ WILDCARD CERT   │  │ existing certificate     │
│ *.apps.abc.com  │  └──────────┬───────────────┘
└─────────────────┘             │
                       ┌────────┴─────────┐
                       │                  │
                   EXISTS            DOESN'T EXIST
                       │                  │
                       ▼                  ▼
              ┌─────────────────┐  ┌──────────────┐
              │ USE EXISTING    │  │ REQUEST NEW  │
              │ LET'S ENCRYPT   │  │ CERTIFICATE  │
              │ CERTIFICATE     │  │ FROM LE      │
              └─────────────────┘  └──────────────┘

Testing and Verification

Test 1: Verify Wildcard Certificate is Loaded

# Check Traefik logs for certificate loading
docker logs traefik | grep -i "certificate"

# Expected output:
# Loading certificate from file: /etc/traefik/certs/wildcard.apps.abc.com/fullchain.pem

Test 2: Check Certificate Details

# Check what certificate is served for a *.apps.abc.com domain
echo | openssl s_client -servername test.apps.abc.com -connect YOUR_SERVER_IP:443 2>/dev/null | openssl x509 -noout -text | grep -A2 "Subject Alternative Name"

# Expected output for wildcard:
# DNS:*.apps.abc.com

Test 3: Verify Let’s Encrypt Certificate

# Check certificate issuer for a custom domain
echo | openssl s_client -servername app1.com -connect YOUR_SERVER_IP:443 2>/dev/null | openssl x509 -noout -issuer

# Expected output:
# issuer=C=US, O=Let's Encrypt, CN=...

Test 4: Check All Certificates in Traefik

# View Let's Encrypt certificates
docker exec traefik cat /letsencrypt/acme.json | jq '.letsencrypt.Certificates[] | {domains: .domain.main}'

# View static wildcard certificate
docker exec traefik openssl x509 -in /etc/traefik/certs/wildcard.apps.abc.com/fullchain.pem -noout -text

Test 5: Test HTTPS Connection

# Test with curl
curl -I https://test.apps.abc.com

# Expected: HTTP/2 200 (or appropriate status)

# Test certificate validation
curl -v https://test.apps.abc.com 2>&1 | grep -E "subject|issuer|SSL"

Test 6: Access Traefik Dashboard

# Access dashboard (replace with your server IP or domain)
https://traefik.apps.abc.com/dashboard/

# Default credentials (if you didn't change them):
# Username: admin
# Password: password (from the bcrypt hash in docker-compose.yml)

Troubleshooting

Issue 1: Wrong Certificate Being Used

Symptom: *.apps.abc.com domain shows Let’s Encrypt certificate instead of wildcard.

Cause: Using tls.certresolver=letsencrypt instead of tls=true

Solution:

# Change this:
- "traefik.http.routers.X.tls.certresolver=letsencrypt"

# To this:
- "traefik.http.routers.X.tls=true"

Issue 2: Certificate Not Loading

Symptom: Traefik logs show errors about certificate files

Checks:

# 1. Verify files exist
docker exec traefik ls -la /etc/traefik/certs/wildcard.apps.abc.com/

# 2. Check dynamic config is loaded
docker exec traefik cat /etc/traefik/dynamic/tls-certificates.yml

# 3. Verify certificate is valid
docker exec traefik openssl x509 -in /etc/traefik/certs/wildcard.apps.abc.com/fullchain.pem -noout -text

# 4. Check file permissions
ls -la certs/wildcard.apps.abc.com/

Solution:

  • Ensure certificate files are readable (644 permissions)
  • Verify paths in tls-certificates.yml are correct
  • Check certificate is valid and not expired

Issue 3: Let’s Encrypt Certificate Not Being Requested

Symptom: App accessible via HTTP but not HTTPS, or shows default certificate

Checks:

# 1. Check if certresolver is specified
docker inspect CONTAINER_NAME | grep certresolver

# 2. Check Traefik logs
docker logs traefik | grep -i "acme\|certificate\|error"

# 3. Verify acme.json permissions
ls -la letsencrypt/acme.json
# Must be: -rw------- (600)

Solution:

# Ensure you have:
- "traefik.http.routers.X.tls.certresolver=letsencrypt"

# Not:
- "traefik.http.routers.X.tls=true"

Issue 4: Let’s Encrypt Rate Limit Error

Symptom: Error in logs: too many certificates already issued

Cause: Let’s Encrypt rate limits (50 certificates per domain per week)

Solution:

# Use staging environment for testing
# In traefik.yml:
certificatesResolvers:
  letsencrypt:
    acme:
      caServer: https://acme-staging-v02.api.letsencrypt.org/directory
      # ... rest of config

Note: Staging certificates will show as untrusted in browsers. Remove caServer line for production.

Issue 5: Container Not Getting Certificate

Symptom: Container accessible via HTTP but not HTTPS

Checklist:

  • ✅ Container is on the web network
  • traefik.enable=true label exists
  • ✅ Entry point is set to websecure
  • ✅ TLS is properly configured
  • ✅ DNS points to your server
  • ✅ Ports 80 and 443 are open

Verification:

# Check container is on correct network
docker inspect CONTAINER_NAME | grep NetworkMode

# Check container labels
docker inspect CONTAINER_NAME | jq '.[0].Config.Labels'

# Check Traefik sees the container
docker logs traefik | grep CONTAINER_NAME

Issue 6: “Unable to obtain ACME certificate” Error

Symptom: Traefik logs show ACME errors

Common Causes:

  1. Port 80 not accessible (for HTTP challenge)
  2. DNS not pointing to server
  3. Firewall blocking traffic
  4. Invalid email address

Solution:

# Test port 80 is accessible
curl -I http://YOUR_SERVER_IP

# Test DNS resolution
dig app1.com
nslookup app1.com

# Check firewall
sudo ufw status
sudo iptables -L

Issue 7: Self-Signed Certificate Warning

Symptom: Browser shows “Your connection is not private”

Causes:

  1. Using Let’s Encrypt staging environment
  2. Certificate not yet issued
  3. Wrong certificate being served

Solution:

# 1. Check which certificate is being served
echo | openssl s_client -servername YOUR_DOMAIN -connect YOUR_SERVER_IP:443 2>/dev/null | openssl x509 -noout -issuer

# 2. Wait a few minutes for Let's Encrypt issuance
# Check logs:
docker logs traefik | grep -i acme

# 3. If using staging, remove caServer line from traefik.yml

Issue 8: Wildcard Certificate Not Covering Subdomain

Symptom: Certificate error for domain that should be covered

Important: Wildcard certificates *.apps.abc.com are valid for:

  • test.apps.abc.com
  • api.apps.abc.com
  • anything.apps.abc.com
  • apps.abc.com (base domain)
  • sub.test.apps.abc.com (two levels deep)

Solution:

  • For base domain: Get separate certificate or include both apps.abc.com and *.apps.abc.com in certificate
  • For multi-level: Get certificate that includes multiple levels or use separate certificate

Certificate Management

Updating the Wildcard Certificate

When your wildcard certificate expires or needs renewal:

# 1. Replace certificate files
cp /path/to/new-fullchain.pem certs/wildcard.apps.abc.com/fullchain.pem
cp /path/to/new-privkey.pem certs/wildcard.apps.abc.com/privkey.pem

# 2. Traefik automatically reloads (due to watch: true)
# Verify reload in logs:
docker logs traefik | tail -20

# 3. No restart needed!

Let’s Encrypt Certificate Renewal

Let’s Encrypt certificates are automatically renewed by Traefik:

  • Renewal starts 30 days before expiration
  • Happens automatically in the background
  • No manual intervention required
  • Stored in letsencrypt/acme.json

Monitor renewals:

# Check certificate expiration dates
docker exec traefik cat /letsencrypt/acme.json | jq '.letsencrypt.Certificates[] | {domain: .domain.main, cert: .certificate}' | head -50

# Check Traefik logs for renewal activity
docker logs traefik | grep -i "renew"

Certificate Expiration Monitoring

# Check wildcard certificate expiration
docker exec traefik openssl x509 -in /etc/traefik/certs/wildcard.apps.abc.com/fullchain.pem -noout -enddate

# Check specific Let's Encrypt certificate (from outside)
echo | openssl s_client -servername app1.com -connect YOUR_SERVER_IP:443 2>/dev/null | openssl x509 -noout -dates

Backup Important Files

What to backup:

# 1. Let's Encrypt certificates
cp letsencrypt/acme.json letsencrypt/acme.json.backup

# 2. Static wildcard certificate
tar -czf wildcard-cert-backup.tar.gz certs/

# 3. Configuration files
tar -czf config-backup.tar.gz traefik.yml dynamic/ docker-compose.yml

Restore from backup:

# Restore acme.json
cp letsencrypt/acme.json.backup letsencrypt/acme.json
chmod 600 letsencrypt/acme.json

# Restart Traefik
docker-compose restart traefik

Security Best Practices

1. File Permissions

# acme.json must be 600
chmod 600 letsencrypt/acme.json

# Certificate files should be 644
chmod 644 certs/wildcard.apps.abc.com/*.pem

# Private keys should be 600
chmod 600 certs/wildcard.apps.abc.com/privkey.pem

2. Use Read-Only Mounts

In docker-compose.yml, use :ro for static files:

volumes:
  - ./traefik.yml:/traefik.yml:ro
  - ./dynamic:/etc/traefik/dynamic:ro
  - ./certs:/etc/traefik/certs:ro

3. Enable Dashboard Authentication

Always protect the Traefik dashboard:

# Generate password hash
echo $(htpasswd -nb admin yourpassword) | sed -e s/\\$/\\$\\$/g

# Add to docker-compose.yml
- "traefik.http.middlewares.auth.basicauth.users=admin:HASH"

4. Use Strong TLS Configuration

Already configured in tls-certificates.yml:

  • Minimum TLS 1.2
  • Strong cipher suites
  • SNI strict mode enabled

5. Regular Updates

# Update Traefik image
docker-compose pull
docker-compose up -d

# Check for updates
docker images | grep traefik

6. Monitor Logs

# Watch for security issues
docker logs -f traefik | grep -i "error\|warning\|fail"

# Set up log rotation
# Add to /etc/logrotate.d/traefik:
/path/to/traefik/logs/*.log {
    daily
    rotate 14
    compress
    missingok
    notifempty
}

7. Firewall Configuration

# Allow only necessary ports
sudo ufw allow 80/tcp   # HTTP
sudo ufw allow 443/tcp  # HTTPS
sudo ufw enable

# Block Traefik dashboard from internet (if needed)
# Access via VPN or SSH tunnel

8. Use Secrets for Sensitive Data

For production, use Docker secrets or environment variables:

environment:
  - CLOUDFLARE_EMAIL=${CLOUDFLARE_EMAIL}
  - CLOUDFLARE_API_KEY=${CLOUDFLARE_API_KEY}

Advanced Configuration

Using DNS Challenge for Let’s Encrypt

If you need wildcard Let’s Encrypt certificates or are behind a firewall:

# In traefik.yml
certificatesResolvers:
  letsencrypt:
    acme:
      email: "[email protected]"
      storage: "/letsencrypt/acme.json"
      dnsChallenge:
        provider: cloudflare # or your DNS provider
        delayBeforeCheck: 0
        resolvers:
          - "1.1.1.1:53"
          - "8.8.8.8:53"

Required environment variables:

# In docker-compose.yml
environment:
  - [email protected]
  - CF_API_KEY=your-api-key
  # OR
  - CF_DNS_API_TOKEN=your-api-token

Supported DNS providers: Cloudflare, Route53, DigitalOcean, OVH, GoDaddy, and many more

Multiple Certificate Resolvers

certificatesResolvers:
  letsencrypt-http:
    acme:
      email: "[email protected]"
      storage: "/letsencrypt/acme-http.json"
      httpChallenge:
        entryPoint: web

  letsencrypt-dns:
    acme:
      email: "[email protected]"
      storage: "/letsencrypt/acme-dns.json"
      dnsChallenge:
        provider: cloudflare

Use in labels:

- "traefik.http.routers.X.tls.certresolver=letsencrypt-http"
# or
- "traefik.http.routers.Y.tls.certresolver=letsencrypt-dns"

Custom Middleware

Rate Limiting:

labels:
  - "traefik.http.middlewares.ratelimit.ratelimit.average=100"
  - "traefik.http.middlewares.ratelimit.ratelimit.burst=50"
  - "traefik.http.routers.X.middlewares=ratelimit"

IP Whitelist:

labels:
  - "traefik.http.middlewares.ipwhitelist.ipwhitelist.sourcerange=192.168.1.0/24,172.16.0.0/16"
  - "traefik.http.routers.X.middlewares=ipwhitelist"

CORS Headers:

labels:
  - "traefik.http.middlewares.cors.headers.accesscontrolallowmethods=GET,OPTIONS,PUT,POST,DELETE"
  - "traefik.http.middlewares.cors.headers.accesscontrolalloworigin=*"
  - "traefik.http.routers.X.middlewares=cors"

Automatic HTTPS Redirect

Already configured in traefik.yml, but you can customize:

entryPoints:
  web:
    address: ":80"
    http:
      redirections:
        entryPoint:
          to: websecure
          scheme: https
          permanent: true # 301 redirect

Custom Default Certificate

If you want a different default certificate:

# In tls-certificates.yml
tls:
  stores:
    default:
      defaultCertificate:
        certFile: /etc/traefik/certs/default/fullchain.pem
        keyFile: /etc/traefik/certs/default/privkey.pem

Additional Resources

Official Documentation

Useful Tools

  • htpasswd: Generate password hashes for basic auth
  • openssl: Test SSL/TLS certificates
  • curl: Test HTTP/HTTPS endpoints
  • jq: Parse JSON (for acme.json)

Certificate Authorities

Quick Command Reference

# Create structure
mkdir -p traefik/{dynamic,certs/wildcard.apps.abc.com,letsencrypt,logs}

# Set permissions
chmod 600 letsencrypt/acme.json

# Create network
docker network create web

# Start Traefik
docker-compose up -d

# View logs
docker logs -f traefik

# Check certificate
echo | openssl s_client -servername DOMAIN -connect IP:443 2>/dev/null | openssl x509 -noout -text

# Restart Traefik
docker-compose restart traefik

# Update Traefik
docker-compose pull && docker-compose up -d

# Backup acme.json
cp letsencrypt/acme.json letsencrypt/acme.json.backup

# Generate auth hash
echo $(htpasswd -nb admin password) | sed -e s/\\$/\\$\\$/g

# View Let's Encrypt certificates
docker exec traefik cat /letsencrypt/acme.json | jq

Summary

This configuration provides a robust solution for managing both static and dynamic certificates in Traefik:

  1. Static wildcard certificate (*.apps.abc.com): Manually managed, used for internal applications
  2. Dynamic Let’s Encrypt certificates: Automatically obtained and renewed for customer-facing domains
  3. Automatic certificate selection: Traefik intelligently chooses the right certificate based on domain and labels

Key Takeaways

  • Use tls=true for *.apps.abc.com domains (static wildcard)
  • Use tls.certresolver=letsencrypt for other domains (dynamic Let’s Encrypt)
  • Traefik handles all certificate selection automatically
  • Let’s Encrypt certificates are automatically renewed
  • Static wildcard certificate must be manually renewed

The Two-Label Rule

Remember this simple rule:

Domain Type Label Certificate
*.apps.abc.com tls=true Static Wildcard
Everything else tls.certresolver=letsencrypt Let’s Encrypt

Last Updated: January 2025

PFX/PKCS12 Certificate Management Guide

This guide provides comprehensive instructions for managing SSL/TLS certificates in PFX (PKCS12) format, including extraction, verification, and conversion to formats commonly used by web servers like Nginx, Apache, and Traefik.

Prerequisites

  • OpenSSL installed on your system
  • Access to your PFX file
  • Password for the PFX file (if protected)

Understanding PFX Files

What is a PFX file?

PFX (Personal Information Exchange) or PKCS#12 is a binary format that bundles:

  • Private Key – Used to decrypt traffic
  • Certificate – Your domain/server certificate (leaf certificate)
  • Certificate Chain – Intermediate and optionally root CA certificates

Why extract components from PFX?

Many web servers and applications require certificates in PEM format with separate or combined files:

  • Nginx: Requires fullchain.pem and privkey.pem
  • Apache: Requires certificate, private key, and chain as separate files
  • Traefik: Can use either PFX or PEM formats
  • HAProxy: Requires combined certificate + key file
  • Docker containers: Often expect PEM format

Common Use Cases

  1. Setting up SSL/TLS on web servers (Nginx, Apache, Traefik)
  2. Migrating certificates between different platforms
  3. Verifying certificate chain completeness before deployment
  4. Converting Windows-exported certificates to Linux-compatible format
  5. Troubleshooting SSL certificate issues

Working with PFX Files

Setup Environment

First, set your PFX password as an environment variable for convenience:

export PFX_PASSWORD='your_password_here'

Note: For empty passwords, use: export PFX_PASSWORD=''

Check if PFX Contains Full Chain

Before extracting, verify that your PFX file contains the complete certificate chain.

Method 1: Count Certificates

openssl pkcs12 -in star.yourfile.pfx -nodes -nokeys -passin pass:$PFX_PASSWORD | grep -c "BEGIN CERTIFICATE"

Expected Results:

  • 1 = Only your certificate (⚠️ incomplete chain)
  • 2 = Your certificate + 1 intermediate CA (✅ typical)
  • 3+ = Your certificate + multiple intermediates (✅ complete chain)

Method 2: Display Certificate Details

openssl pkcs12 -in star.yourfile.pfx -nodes -nokeys -passin pass:$PFX_PASSWORD -info

This command displays:

  • All certificates with their subject and issuer
  • Certificate validity dates
  • Complete certificate chain hierarchy

Extract Components from PFX

Extract Full Certificate Chain (fullchain.pem)

This creates a file containing your certificate and all intermediate certificates.

openssl pkcs12 -in star.yourfile.pfx -out star.yourfile.pem -nodes -nokeys -passin pass:$PFX_PASSWORD

Flags Explained:

  • -in – Input PFX file
  • -out – Output PEM file
  • -nodes – Don’t encrypt the output (removes passphrase)
  • -nokeys – Export only certificates, not the private key
  • -passin pass:$PFX_PASSWORD – Provide password non-interactively

Output: star.yourfile.pem (fullchain.pem)

Extract Private Key (privkey.pem)

This extracts only the private key from the PFX file.

openssl pkcs12 -in star.yourfile.pfx -out star.yourfile.key -nodes -nocerts -passin pass:$PFX_PASSWORD

Flags Explained:

  • -nocerts – Export only the private key, not certificates
  • -nodes – Output key without encryption

Output: star.yourfile.key (privkey.pem)

⚠️ Security Warning: Protect this file! Set proper permissions:

chmod 600 star.yourfile.key

Extract Everything in One File

If you need certificate + chain + private key in a single file:

openssl pkcs12 -in star.yourfile.pfx -out combined.pem -nodes -passin pass:$PFX_PASSWORD

Use Case: HAProxy, some load balancers

Verification Methods

1. View Certificate Subjects and Issuers

openssl crl2pkcs7 -nocrl -certfile star.yourfile.pem | openssl pkcs7 -print_certs -noout

What to look for:

  • Each certificate’s subject should match the next certificate’s issuer
  • Forms a chain from your certificate to the root CA

Example Output:

subject=CN=*.example.com
issuer=CN=Intermediate CA

subject=CN=Intermediate CA
issuer=CN=Root CA

2. Verify Certificate Chain Integrity

openssl verify -CAfile star.yourfile.pem star.yourfile.pem

Expected Output:

star.yourfile.pem: OK

If verification fails, your chain is incomplete or corrupted.

3. Check Certificate Expiration

openssl x509 -in star.yourfile.pem -noout -dates

Output:

notBefore=Jan  1 00:00:00 2024 GMT
notAfter=Dec 31 23:59:59 2025 GMT

4. Display Certificate Details

openssl x509 -in star.yourfile.pem -text -noout

Shows complete certificate information including:

  • Subject and Issuer
  • Validity period
  • Subject Alternative Names (SANs)
  • Key usage
  • Extensions

5. List All Certificates in Chain

openssl storeutl -certs star.yourfile.pem

Displays each certificate in the chain with its details.

Creating Full Chain Certificates

Scenario 1: PFX Missing Intermediate Certificates

If your PFX only contains the leaf certificate, you need to add the chain manually.

Step 1: Extract components

# Extract certificate
openssl pkcs12 -in star.yourfile.pfx -out cert.pem -nodes -nokeys -clcerts -passin pass:$PFX_PASSWORD

# Extract private key
openssl pkcs12 -in star.yourfile.pfx -out privkey.pem -nodes -nocerts -passin pass:$PFX_PASSWORD

Step 2: Obtain intermediate certificates

Download the chain file from your Certificate Authority (CA):

  • Let’s Encrypt: Included automatically
  • DigiCert, Sectigo, etc.: Available in your CA account
  • Or download from: https://www.example-ca.com/chain.pem

Step 3: Combine certificate with chain

cat cert.pem chain.pem > fullchain.pem

Step 4: Create new PFX with complete chain

openssl pkcs12 -export -out newfile.pfx \
  -inkey privkey.pem \
  -in cert.pem \
  -certfile chain.pem \
  -name "*.example.com" \
  -passout pass:$PFX_PASSWORD

Scenario 2: Already Have Separate Files

If you have certificate and chain as separate files:

# Combine certificate and chain
cat star_asb_bh.crt star_asb_bh-chain.pem > fullchain.pem

# Verify the chain
grep -c "BEGIN CERTIFICATE" fullchain.pem
openssl verify -CAfile fullchain.pem fullchain.pem

Optional: Create PFX from separate files:

openssl pkcs12 -export -out newfile.pfx \
  -inkey star_asb_bh.key \
  -in star_asb_bh.crt \
  -certfile star_asb_bh-chain.pem \
  -name "*.asb.bh" \
  -passout pass:$PFX_PASSWORD

Alternative Scenarios

Using with Nginx

Create the required files:

# Full chain certificate
openssl pkcs12 -in star.yourfile.pfx -out /etc/nginx/ssl/fullchain.pem -nodes -nokeys -passin pass:$PFX_PASSWORD

# Private key
openssl pkcs12 -in star.yourfile.pfx -out /etc/nginx/ssl/privkey.pem -nodes -nocerts -passin pass:$PFX_PASSWORD

# Set permissions
chmod 644 /etc/nginx/ssl/fullchain.pem
chmod 600 /etc/nginx/ssl/privkey.pem

Nginx configuration:

server {
    listen 443 ssl;
    server_name example.com;
    
    ssl_certificate /etc/nginx/ssl/fullchain.pem;
    ssl_certificate_key /etc/nginx/ssl/privkey.pem;
}

Using with Traefik

Option 1: Use PFX directly (if Traefik supports it)

tls:
  stores:
    default:
      defaultCertificate:
        certFile: /path/to/star.yourfile.pfx

Option 2: Convert to PEM format

# Extract both cert and key
openssl pkcs12 -in star.yourfile.pfx -out combined.pem -nodes -passin pass:$PFX_PASSWORD

Traefik configuration:

tls:
  certificates:
    - certFile: /path/to/fullchain.pem
      keyFile: /path/to/privkey.pem

Using with Apache

# Certificate
openssl pkcs12 -in star.yourfile.pfx -out /etc/apache2/ssl/cert.pem -nodes -nokeys -clcerts -passin pass:$PFX_PASSWORD

# Private Key
openssl pkcs12 -in star.yourfile.pfx -out /etc/apache2/ssl/privkey.pem -nodes -nocerts -passin pass:$PFX_PASSWORD

# Chain (intermediate certificates)
openssl pkcs12 -in star.yourfile.pfx -out /etc/apache2/ssl/chain.pem -nodes -nokeys -cacerts -passin pass:$PFX_PASSWORD

Apache configuration:

<VirtualHost *:443>
    SSLEngine on
    SSLCertificateFile /etc/apache2/ssl/cert.pem
    SSLCertificateKeyFile /etc/apache2/ssl/privkey.pem
    SSLCertificateChainFile /etc/apache2/ssl/chain.pem
</VirtualHost>

Troubleshooting

Error: “wrong tag” or “nested asn1 error”

Cause: Corrupted file, wrong format, or legacy encryption

Solutions:

# Try with legacy provider (OpenSSL 3.x)
openssl pkcs12 -in star.yourfile.pfx -nodes -nokeys -legacy -passin pass:$PFX_PASSWORD

# Try with both providers
openssl pkcs12 -in star.yourfile.pfx -nodes -nokeys -provider legacy -provider default -passin pass:$PFX_PASSWORD

Error: File shows all zeros (0x00)

Check file integrity:

hexdump -C star.yourfile.pfx | head -20

Valid PFX should start with: 30 82 or 30 80 or 30 84

If all zeros: File is corrupted. Re-download or re-export from source.


Error: “MAC verification failed”

Cause: Incorrect password

Solution:

  • Verify your password
  • Try empty password: export PFX_PASSWORD=''
  • Re-export the PFX with known password

Certificate Chain Verification Fails

Check the chain order:

openssl storeutl -certs fullchain.pem

Expected order:

  1. Your domain certificate (leaf)
  2. Intermediate CA certificate(s)
  3. Root CA (optional)

Fix incorrect order:

# Manually reorder certificates in a text editor
# Ensure leaf certificate comes first

Missing Intermediate Certificates

Symptoms:

  • Browser shows “NET::ERR_CERT_AUTHORITY_INVALID”
  • SSL Labs test shows “Chain issues”
  • grep -c "BEGIN CERTIFICATE" returns only 1

Solution: Download intermediate certificate from your CA and combine:

cat your-cert.pem intermediate.pem > fullchain.pem

Quick Reference Commands

# Set password
export PFX_PASSWORD='your_password'

# Check certificate count
openssl pkcs12 -in file.pfx -nodes -nokeys -passin pass:$PFX_PASSWORD | grep -c "BEGIN CERTIFICATE"

# Extract full chain
openssl pkcs12 -in file.pfx -out fullchain.pem -nodes -nokeys -passin pass:$PFX_PASSWORD

# Extract private key
openssl pkcs12 -in file.pfx -out privkey.pem -nodes -nocerts -passin pass:$PFX_PASSWORD

# Verify chain
openssl verify -CAfile fullchain.pem fullchain.pem

# View certificate details
openssl x509 -in fullchain.pem -text -noout

# Check expiration
openssl x509 -in fullchain.pem -noout -dates

# Create PFX from separate files
openssl pkcs12 -export -out new.pfx -inkey privkey.pem -in cert.pem -certfile chain.pem

# Combine certificate files
cat cert.pem chain.pem > fullchain.pem

Security Best Practices

  1. Protect Private Keys

    chmod 600 privkey.pem
    chown root:root privkey.pem
    
  2. Never commit certificates to version control

    # Add to .gitignore
    *.pfx
    *.pem
    *.key
    *.crt
    
  3. Use strong passwords for PFX files

    • Minimum 12 characters
    • Mix of letters, numbers, symbols
  4. Regularly rotate certificates

    • Monitor expiration dates
    • Automate renewal where possible
  5. Store backups securely

    • Encrypted storage
    • Access control
    • Regular backup verification

Additional Resources


License

This documentation is provided as-is for educational and reference purposes.


Last Updated: January 2026

Restoring SQL Server BACPAC Files on Mac M3

Overview

This guide covers how to restore a .bacpac database backup file to SQL Server running in Docker on Mac M3 (Apple Silicon) using SqlPackage.

Prerequisites

  • SQL Server running in Docker
  • .bacpac backup file
  • Terminal access
  • Docker container running and accessible

Installation Steps

1. Download SqlPackage for macOS

SqlPackage is Microsoft’s command-line utility for importing and exporting SQL Server databases.

# Navigate to Downloads folder
cd ~/Downloads

# Download SqlPackage for macOS (universal binary - works on ARM64/M3)
curl -L -o sqlpackage.zip https://aka.ms/sqlpackage-macos

# Extract the package
unzip sqlpackage.zip -d sqlpackage

# Make the binary executable
chmod +x sqlpackage/sqlpackage

Restoring a BACPAC File

Basic Restore Command

./sqlpackage/sqlpackage /Action:Import \
  /SourceFile:"your-database.bacpac" \
  /TargetServerName:"localhost,PORT" \
  /TargetDatabaseName:"YourDatabaseName" \
  /TargetUser:"sa" \
  /TargetPassword:'YourPassword' \
  /TargetTrustServerCertificate:True

Example Usage

./sqlpackage/sqlpackage /Action:Import \
  /SourceFile:"SampleDatabase.bacpac" \
  /TargetServerName:"localhost,5433" \
  /TargetDatabaseName:"ESOLAut0mater12" \
  /TargetUser:"sa" \
  /TargetPassword:'SecureP@ssword' \
  /TargetTrustServerCertificate:True

Example with Extended Timeout (for large databases)

./sqlpackage/sqlpackage /Action:Import \
  /SourceFile:"SampleDatabase.bacpac" \
  /TargetServerName:"localhost,5433" \
  /TargetDatabaseName:"ESOLAut0mater12" \
  /TargetUser:"sa" \
  /TargetPassword:'SecureP@ssword' \
  /TargetTrustServerCertificate:True \
  /TargetTimeout:600

Important Parameters Explained

Parameter Description
/Action:Import Specifies that we’re importing a BACPAC file
/SourceFile Path to your .bacpac file
/TargetServerName Server address with port (format: hostname,port)
/TargetDatabaseName Name for the restored database
/TargetUser SQL Server username (typically sa)
/TargetPassword SQL Server password
/TargetTrustServerCertificate:True Required for Docker SQL Server – trusts self-signed certificates
/TargetTimeout:600 Optional – Connection timeout in seconds (use for large databases)

Common Issues and Solutions

1. SSL Certificate Error

Error: The remote certificate was rejected by the provided RemoteCertificateValidationCallback

Solution: Add /TargetTrustServerCertificate:True parameter (already included in the example above)

2. Password with Special Characters

If your password contains special characters like !, @, #, etc., wrap it in single quotes:

/TargetPassword:'SecureP@ssword'

3. Connection Format

  • ✅ Correct: localhost,5433 (comma separator)
  • ❌ Incorrect: localhost;5433 (semicolon separator)
  • ❌ Incorrect: localhost:5433 (colon separator)

4. Port Numbers

Common SQL Server ports:

  • Default: 1433
  • Custom Docker: 5433 (or whatever you mapped in your Docker run command)

Find your Docker port mapping:

docker ps
# Look for something like: 0.0.0.0:5433->1433/tcp

5. Transport-Level Errors with Large Tables

Error: A transport-level error has occurred when receiving results from the server

Symptoms:

  • Import fails consistently at the same tables (often audit or log tables)
  • Error message: Transport-level error or Invalid argument
  • Import works up to a certain point, then crashes

Root Cause: Some tables (like AbpAuditLogs, AbpEntityChanges, AbpEntityPropertyChanges) can contain massive amounts of data that cause timeout or memory issues during bulk import on Mac M3 with Docker SQL Server.

Solution: Manually Remove Large Table Data from BACPAC

Since BACPAC is essentially a ZIP file, you can extract it, remove problematic table data, and re-package it.

Step-by-Step Process
# Navigate to your BACPAC location
cd ~/Downloads

# Remove previous folders if they exist
rm -rf ESOLAut0mater_Modified.bacpac
rm -rf bacpac_extracted

# Unzip the BACPAC
unzip YourDatabase.bacpac -d bacpac_extracted

# Navigate to the Data folder
cd bacpac_extracted/Data

# Remove the large table data files (BCP files contain the actual data)
# Common problematic tables (adjust based on your error messages):
rm -rf dbo.AbpAuditLogs/*.BCP
rm -rf dbo.AbpEntityChanges/*.BCP
rm -rf dbo.AbpEntityPropertyChanges/*.BCP

# Add more tables if needed:
# rm -rf dbo.YourLargeTable/*.BCP

# Return to parent directory and re-zip
cd ..
zip -r ../YourDatabase_Modified.bacpac *

# Go back to Downloads folder
cd ..

# Import the modified BACPAC
./sqlpackage/sqlpackage /Action:Import \
  /SourceFile:"YourDatabase_Modified.bacpac" \
  /TargetServerName:"localhost,5433" \
  /TargetDatabaseName:"YourDatabaseName" \
  /TargetUser:"sa" \
  /TargetPassword:'YourPassword' \
  /TargetTrustServerCertificate:True \
  /TargetTimeout:600
Complete Example
cd ~/Downloads

# Clean up previous attempts
rm -rf ESOLAut0mater_Modified.bacpac
rm -rf bacpac_extracted

# Unzip the BACPAC
unzip SampleDatabase.bacpac -d bacpac_extracted

# Remove problematic table data
cd bacpac_extracted/Data
rm -rf dbo.AbpAuditLogs/*.BCP
rm -rf dbo.AbpEntityChanges/*.BCP
rm -rf dbo.AbpEntityPropertyChanges/*.BCP

# Re-zip it
cd ..
zip -r ../ESOLAut0mater_Modified.bacpac *

# Import the modified BACPAC
cd ..
./sqlpackage/sqlpackage /Action:Import \
  /SourceFile:"ESOLAut0mater_Modified.bacpac" \
  /TargetServerName:"localhost,5433" \
  /TargetDatabaseName:"ESOLAut0mater12" \
  /TargetUser:"sa" \
  /TargetPassword:'SecureP@ssword' \
  /TargetTrustServerCertificate:True \
  /TargetTimeout:600
How to Identify Problematic Tables
  1. Look at the import output – it shows which table was processing when it failed:

    Processing Table '[dbo].[AbpAuditLogs]'.
    *** A transport-level error has occurred...
    
  2. Check the Data folder structure in the extracted BACPAC:

    cd bacpac_extracted/Data
    du -sh */ | sort -h
    # This shows folder sizes - large folders are likely culprits
    
Important Notes
  • Schema Preservation: This method only removes table DATA, not the table structure. The tables will exist but will be empty.
  • Foreign Keys: If other tables reference the data you’re removing, you may need to handle those dependencies.
  • Audit Tables: Tables like AbpAuditLogs, AbpEntityChanges, AbpEntityPropertyChanges are typically audit/log tables and are safe to empty for development environments.
  • Production Warning: Do not use this method for production imports where you need complete data integrity.
  • Alternative: If you need the data from these tables, consider using Azure Data Studio which sometimes handles large imports better, or increase Docker memory allocation significantly (8GB+).
Verify After Import

After importing the modified BACPAC, verify your data:

USE YourDatabaseName;
GO

-- Check table count
SELECT COUNT(*) AS TotalTables FROM sys.tables;

-- Check row counts for all tables
SELECT 
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
    SUM(p.rows) AS RowCount
FROM sys.tables t
INNER JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)
GROUP BY t.schema_id, t.name
ORDER BY RowCount DESC;

-- Verify important business tables have data
SELECT COUNT(*) FROM [YourSchema].[YourImportantTable];

Optional: Install Globally

To use SqlPackage from any directory:

# Move to permanent location
sudo mkdir -p /usr/local/sqlpackage
sudo cp -r sqlpackage/* /usr/local/sqlpackage/

# Create symbolic link
sudo ln -s /usr/local/sqlpackage/sqlpackage /usr/local/bin/sqlpackage

# Now you can run from anywhere
sqlpackage /Action:Import \
  /SourceFile:"~/path/to/database.bacpac" \
  /TargetServerName:"localhost,5433" \
  /TargetDatabaseName:"DatabaseName" \
  /TargetUser:"sa" \
  /TargetPassword:'YourPassword' \
  /TargetTrustServerCertificate:True

Verifying the Restore

After successful import, verify the database using VS Code with mssql extension or any SQL client:

-- List all databases
SELECT name FROM sys.databases;

-- Check table count in restored database
USE YourDatabaseName;
SELECT COUNT(*) AS TableCount 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE';

Alternative Tools

If SqlPackage doesn’t work for your use case:

  1. Azure Data Studio (Recommended GUI option)

    brew install --cask azure-data-studio
    
    • Right-click “Databases” → “Import Data-tier Application”
    • Browse to your .bacpac file
  2. Direct Docker approach (if SqlPackage is available in container)

    docker cp database.bacpac container_name:/tmp/
    docker exec -it container_name /path/to/sqlpackage /Action:Import ...
    

Additional SqlPackage Actions

SqlPackage supports other database operations:

# Export database to BACPAC
./sqlpackage/sqlpackage /Action:Export \
  /SourceServerName:"localhost,5433" \
  /SourceDatabaseName:"DatabaseName" \
  /SourceUser:"sa" \
  /SourcePassword:'Password' \
  /TargetFile:"output.bacpac" \
  /SourceTrustServerCertificate:True

# Extract to DACPAC (schema only)
./sqlpackage/sqlpackage /Action:Extract \
  /SourceServerName:"localhost,5433" \
  /SourceDatabaseName:"DatabaseName" \
  /SourceUser:"sa" \
  /SourcePassword:'Password' \
  /TargetFile:"schema.dacpac" \
  /SourceTrustServerCertificate:True

Resources

Notes

  • BACPAC files contain both schema and data
  • DACPAC files contain only schema (no data)
  • SqlPackage on Mac M3 runs natively on ARM64 architecture
  • Self-signed certificates in Docker SQL Server require the TrustServerCertificate parameter
  • For production environments, consider using proper SSL certificates
  • Large Tables: If import fails with transport-level errors on specific tables (especially audit/log tables), you can manually remove their data from the BACPAC by unzipping it, deleting the problematic .BCP files, and re-zipping (see section 5 in Common Issues)
  • Docker Resources: For large database imports, ensure Docker has sufficient resources allocated (4GB+ RAM, 2+ CPU cores)
  • Development vs Production: The manual BACPAC modification method is suitable for development environments where audit history is not critical

🚀 Installing Odoo 16/17/18 on a Free Cloud Server (AWS Lightsail, DigitalOcean, etc.)

🔹 Scenario

If you need to install Odoo 16, 17, or 18 on a free cloud server like AWS Lightsail, DigitalOcean Droplets, or similar, this guide will help you set up an Odoo instance at zero cost. This setup is perfect for testing functionalities, running demos, or short-term development.

🛠 Supported Versions

  • Odoo Versions: 16, 17, 18, 19 (tested)
  • Ubuntu Version: 24.04 LTS

✅ Step-by-Step Installation Guide

1️⃣ Create a Free Ubuntu 24.04 Server

  • Sign up for AWS Lightsail and create a 90-day free Ubuntu 24.04 instance.
  • Choose a basic server configuration (e.g., 1GB RAM, 1vCPU, 20GB SSD).

2️⃣ Apply the Launch Script

During the instance creation process, paste the following launch script in the “Launch Script” section:

https://github.com/princeppy/odoo-install-scripts/blob/main/lightsail.aws/launch_script.sh

This script automates the initial setup, including system updates, package installations, and preparing the Odoo environment.

3️⃣ Access the Server via Browser-Based SSH

Once your instance is up and running:

  • Open AWS Lightsail and select your instance.
  • Click “Connect using SSH” to access the terminal.

4️⃣ Monitor Installation Progress

Run the following command to track installation logs in real time:

tail -f /tmp/launchscript.log

• Wait until you see:

Preinstallation Completed........

This indicates that the server setup is complete.

5️⃣ Elevate to Root User

Once the installation completes, switch to the root user to run administrative commands:

sudo su

6️⃣ Run the Odoo Installation Script

Now, execute the Odoo installation script:

bash /InstallScript/install_odoo.sh

• The script will download, install, and configure Odoo on your server. • Once completed, look for the confirmation message:

Done

• Your Odoo instance is now ready to use! 🎉

📌 References & Additional Resources

For further reading and alternative installation scripts, check out these resources: • Odoo Install Script by Yenthe666 • Odoo Install Script by Moaaz • Odoo Install Script by Ventor Tech

🚀 Conclusion

By following this guide, you can quickly deploy Odoo 16/17/18/19 on a free Ubuntu 24.04 server using AWS Lightsail or similar platforms. This setup allows you to test Odoo functionalities, run demos, or perform short-term development—all without any cost.

💡 Got questions or need help? Drop a comment below! 🚀

Azure Data Lake Storage Gen1 vs. Gen2

Feature Data Lake Storage Gen1 Data Lake Storage Gen2
Architecture Standalone hierarchical file system Built on Azure Blob Storage with Hierarchical Namespace (HNS)
Performance Slower due to standalone architecture Optimized performance with tiered storage & caching
Security ACLs (Access Control Lists) & RBAC RBAC, ACLs, Azure AD (more granular access control)
Cost Efficiency Higher cost, no tiered storage Lower cost with hot, cool, and archive tiers
Integration Limited compatibility with Azure services Fully compatible with Blob APIs, Synapse, Databricks, Spark
Scalability Limited to single-region storage Globally distributed, supports Geo-redundancy (GRS)
Protocol Support Proprietary protocol, limited interoperability Supports HDFS, Blob APIs, better integration with analytics tools
Availability Regional storage only Supports multi-region & geo-redundant storage
Migration No easy migration to Blob storage Can integrate with Azure Blob Storage, simplifying migration
Support Status Deprecated (support ends Feb 29, 2024) Actively developed & recommended for new workloads

PySpark Vs Pandas

Pandas Transformations

  • df.count() – Returns the count of each column (the count includes only non-null values).
  • df.corr() – Returns the correlation between columns in a data frame.
  • df.head(n) – Returns first n rows from the top.
  • df.max() – Returns the maximum of each column.
  • df.mean() – Returns the mean of each column.
  • df.median() – Returns the median of each column.
  • df.min() – Returns the minimum value in each column.
  • df.std() – Returns the standard deviation of each column
  • df.tail(n) – Returns last n rows.

PySpark Transformations

  • df.select() – Choose specific columns from a DataFrame.
  • df.filter() – Filter rows based on a condition.
  • df.groupBy() – Group rows based on one or more columns.
  • df.agg() – Perform aggregate functions (e.g., sum, average) on grouped data.
  • df.orderBy() – Sort rows based on one or more columns.
  • df.dropDuplicates() – Remove duplicate rows from the DataFrame.
  • df.withColumn() – Add a new column or replace an existing column with modified data.
  • df.drop() – Remove one or more columns from the DataFrame.
  • df.join() – Merge two DataFrames based on a common column or index.
  • df.pivot() – Pivot the DataFrame to reorganize data based on column values.

How to Use Let’s Encrypt on Windows Server with IIS

This guide explains how to enable a FREE SSL certificate using Let’s Encrypt on a Windows Server running IIS. Specifically, it addresses the challenges of using wildcard certificates for multiple websites with different domains and subdomains.

Scenario:

You have a Windows Server 2019 with IIS 10, a single IP address, and multiple HTTPS websites hosted with different domain names. For subdomains under the same primary domain (like *.example.com), a wildcard certificate works perfectly. However, complications arise when adding websites from different domains, such as mydomain.com.

Initial Setup Example:

IIS 10 hosts the following sites:

  • ABC Server (Website)
    • abc.api.example.com – HTTPS @ 443
  • ABC Client (Website)
    • abc.example.com – HTTPS @ 443
    • bcd.example.com – HTTPS @ 443
    • cde.example.com – HTTPS @ 443
    • admin.example.com – HTTPS @ 443
  • XYZ App (Website)
    • xyz.example.com – HTTPS @ 443
  • SEQ (Website)
    • seq.mydomain.com – HTTPS @ 443

Managing these SSL certificates with multiple domain names can be tricky, but Let’s Encrypt simplifies the process.

Steps to Enable Let’s Encrypt SSL on IIS

  • Enable IIS and Create the .well-known Folder
    • Follow this guide to create the .well-known directory for SSL validation
      • Create a folder on the C drive named well-known. Inside, create another folder called pki-validation. Example: C:\well-known\pki-validation.
      • Place the required validation file in the pki-validation folder.
      • Open IIS Manager and for each site, right-click and select Add Virtual Directory.
      • In the Alias field, enter .well-known. In the Physical Path field, enter the path to the folder you created, e.g., C:\well-known\pki-validation.
      • Confirm with OK. The folder and files should now be accessible via the web.
  • Set Proper Permissions for the C:\well-known\pki-validation Folder
    • Follow this IIS 403 Forbidden solution:
      • Right-click the .well-known folder and select Properties.
      • Navigate to the Security tab.
      • Click Edit and ensure IIS_IUSRS is listed. If not, click Add
      • In the Enter the object names box, type IIS_IUSRS and click OK.
      • Set Read & execute, List folder contents, and Read permissions for IIS_IUSRS.
  • Validate DNS Entries for Each Domain/Subdomain
    • Use a tool like Google Dig to validate DNS entries for the following domains:
      • abc.api.example.com
      • abc.example.com
      • bcd.example.com
      • cde.example.com
      • admim.example.com
      • xyz.example.com
      • seq.mydomain.com
  • Download and Install win-acme
  • Run win-acme to Generate SSL Certificates
    • Navigate to C:\win-acme and run win-acme.exe as Administrator.
    • Follow the prompts to select the appropriate site for which you want to generate the SSL certificate.
    • Once complete, your sites will be secured with Let’s Encrypt SSL certificates.

By following these steps, you can manage multiple websites with different domains and subdomains on a single IIS server with Let’s Encrypt SSL certificates, solving the issues typically associated with wildcard certificates for different domains.

Other Resources

Connecting and Downloading Kaggle Dataset from colab

Register https://www.kaggle.com and generate API token via https://www.kaggle.com/settings

# Run this cell and select the kaggle.json file downloaded
# from the Kaggle account settings page.

from google.colab import files
files.upload()

# This will prompt the file upload control, so that we can uppload the file to the temporark work space.
# Next, install the Kaggle API client.
!pip install -q kaggle

# The Kaggle API client expects this file to be in ~/.kaggle, so move it there.
!mkdir -p ~/.kaggle
!cp kaggle.json ~/.kaggle/

# This permissions change avoids a warning on Kaggle tool startup.
!chmod 600 ~/.kaggle/kaggle.json

# Searching for dataset
!kaggle datasets list -s dogbreedidfromcomp

# Downloading dataset in the current directory
!kaggle datasets download catherinehorng/dogbreedidfromcomp

# Unzipping downloaded file and removing unusable file
!unzip dog_dataset/dogbreedidfromcomp.zip -d dog_dataset

SelfSigned Certificate for WebApplications (PowerSchool Test Server)

When we are creating a PowerSchool in OnPrimise, One of the difficult part is to generate a proper self-signed certificate. This blog is for all the PowerSchool administrators out there who have to Build the PowerSchool Test server or they want to Dump the Latest database backup to the test server.

OpenSSL

commonly we use OpenSSL to generate the certificate, by default OpenSSL is not available in Windows Server. Below are the steps (Credits to: https://tecadmin.net/install-openssl-on-windows)

Step 1 – Download OpenSSL Binary

You need to download the latest OpenSSL Windows installer file. Click the below link to visit the OpenSSL download page https://slproweb.com/products/Win32OpenSSL.html

Step 2 – Run OpenSSL Installer

Now run the OpenSSL installer on your system. The OpenSSL required Microsoft Visual C++ to be installed on your system. If your system doesn’t have Microsoft Visual C++ installed, the installer will show your message like:

Click Yes to download and install required Microsoft Visual C++ package on your system.

Then again run the OpenSSL installer and follow the wizard.

Make sure you change the folder to “C:\OpenSSL-Win64” for easy handling in later stage

Step 3 – Setup Environment Variables

Now set the environment variables to function OpenSSL properly on your system. You are required to set OPENSSL_CONF and Path environment variables.

Use the following commands to set the environment for the permanently (use PowerShell):

[System.Environment]::SetEnvironmentVariable('OPENSSL_CONF','C:\OpenSSL-Win64\bin\openssl.cfg', 'Machine')
$Path = [Environment]::GetEnvironmentVariable('PATH', 'Machine') + [IO.Path]::PathSeparator + 'C:\OpenSSL-Win64\bin'
[System.Environment]::SetEnvironmentVariable('Path',$Path, 'Machine')

Step 4 – Run OpenSSL Binary

Open cmd / powershell and test ‘openssl’

Generating Self-Signed certificate via OpenSSL

Please change the below names according to your situations
(credits to https://stackoverflow.com/questions/10175812/how-to-generate-a-self-signed-ssl-certificate-using-openssl)

  • powerschool.local.school => to your test server FQDN
  • -subj “/C=BH/ST=Riffa/……CN=powerschool.local.school” => to your version
  • subjectAltName => alter DNS and IP to match your server settings
# generate selfsigned certificate and key for 10 years with specific Subject and Additional subjectAltName

openssl req -x509 -newkey rsa:4096 -sha256 -days 3650 \
  -nodes -keyout powerschool.local.school.key -out powerschool.local.school.crt -subj "/C=BH/ST=Riffa/L=Riffa/O=School/OU=PowerSchool/[email protected]/CN=powerschool.local.school" \
  -addext "subjectAltName=DNS:powerschool.local.school,DNS:powerschool-altername.local.school,IP:172.10.1.151,IP:172.10.1.152"

the above code process will generate the key file in the modern format (PKCS#8), which PowerSchool doesn’t like. We need to manually convert the key file to traditional format (PKCS#1)

# traditional format (pkcs1)
$ cat pkcs1.pem
-----BEGIN RSA PRIVATE KEY-----
...
-----END RSA PRIVATE KEY-----
# modern format (pkcs8)
$ cat pkcs1.pem
-----BEGIN PRIVATE KEY-----
...
-----END PRIVATE KEY-----
# Convert from PKCS#8 to PKCS#1:
openssl pkey -in powerschool.local.school.key -traditional -out powerschool.local.school.rsa.key

The above code will generate a new key file (‘powerschool.local.school.rsa.key’) from your available modern key file (‘powerschool.local.school.key’)

Additional usefull commands

# Convert from PKCS#1 to PKCS#8:
openssl pkey -in pkcs1.pem -out pkcs8.pem

# Convert from PKCS#8 to PKCS#1:
openssl pkey -in pkcs8.pem -traditional -out pkcs1.pem

# RSA private key - To convert from PKCS#1 to PKCS#8:
openssl pkcs8 -topk8 -inform pem -in private_pkcs1.pem -outform pem -nocrypt \
 -out private_pkcs8.pem

# RSA private key - To convert from PKCS#8 to PKCS#1:
openssl rsa -in private_pkcs8.pem -out private_pkcs1.pem

# RSA public key - To convert from PKCS#8 to PKCS#1:
openssl rsa -pubin -in public_pkcs8.pem -RSAPublicKey_out -out public_pkcs1.pem

# RSA public key - To convert from PKCS#1 to PKCS#8:
openssl rsa -RSAPublicKey_in -in public_pkcs1.pem -pubout -out public_pkcs8.pem

# Create SSL identity file in PKCS12
openssl pkcs12 -export -in example.com.crt -inkey example.com.key -out example.com.pfx

# Convert P12 into PEM
openssl pkcs12 -in powerschool.local.school.pfx -nocerts -nodes -out powerschool.local.school.key
openssl pkcs12 -in powerschool.local.school.pfx -clcerts -nokeys -out powerschool.local.school.crt
openssl pkey -in powerschool.local.school.key -traditional -out powerschool.local.school.rsa.key

Additional Readings