Store Credentials for Excel Report Scripts Safely

Report scripts need credentials: the database the numbers come from, the mail server that sends the result, the API of the CRM or the file share. The quickest way to make a script work is to type the password into it, and that is how passwords end up in version control, in emailed copies of the script, in screenshots and in the logs of every failed run. The fix is not complicated, but it depends on where the script runs. This page, part of Testing and Packaging Excel Automation Scripts, walks through the options from a laptop to a cloud job, with code that runs as shown, and ends with the piece most often forgotten: keeping secrets out of logs.

Prerequisites

pip install python-dotenv keyring boto3
pip install "moto[secretsmanager]"      # only for the local test of the cloud example

The examples read a database password and an SMTP password. None of them is real; the cloud example runs against moto's in-memory imitation of AWS Secrets Manager.

Step 1 — Read secrets from the environment, and fail clearly

The environment is the lowest common denominator: every scheduler, container platform and CI system can set environment variables, and code that reads them works everywhere. Read them in one place and fail with a message that names every missing variable at once:

import os

REQUIRED = ["REPORT_DB_URL", "REPORT_DB_PASSWORD", "SMTP_PASSWORD"]

class ConfigError(RuntimeError):
    pass

def load_secrets(names=REQUIRED):
    missing = [n for n in names if not os.environ.get(n)]
    if missing:
        raise ConfigError(f"missing environment variables: {', '.join(missing)}")
    return {n: os.environ[n] for n in names}

try:
    load_secrets()
except ConfigError as err:
    print(err)

Checking every variable up front, before the script has spent ten minutes querying and formatting, turns a confusing None failure deep in the SMTP code into one line naming exactly what to set. It also makes the script’s needs explicit: the list at the top is the documentation of what it expects, and a new colleague setting the job up on another machine sees at once what to provide.

Step 2 — A .env file for development only

Typing export lines before every test run is tedious, so developers keep local values in a .env file that python-dotenv loads into the environment. The file must never be committed:

REPORT_DB_URL=postgresql://reports@db.internal:5432/sales
REPORT_DB_PASSWORD=dev-only-password
SMTP_PASSWORD=dev-smtp-password

from dotenv import load_dotenv

loaded = load_dotenv(".env", override=False)
secrets = load_secrets()
print(loaded, sorted(secrets))

printf '.env\n*.env\n' > .gitignore && git init -q . && git check-ignore -v .env && rm -rf .git

override=False means real environment variables win over the file, so the same code uses the server's values in production and the file's values on a laptop. The git check confirms .env is ignored before anything is ever committed; keep a .env.example with the variable names and dummy values in the repository instead, so new developers know what to set. A .env file on a production server is just a password in a file with an unusual name — use the next options there.

Step 3 — The operating system keyring on desktops

For a report scheduled on a Windows or Mac desktop, the operating system already has a secure store: Windows Credential Manager and the macOS Keychain, encrypted and tied to the user account. The keyring library reads and writes them with one API:

import keyring
from keyring.backend import KeyringBackend

class MemoryKeyring(KeyringBackend):
    priority = 1
    def __init__(self):
        self.store = {}
    def set_password(self, service, username, password):
        self.store[(service, username)] = password
    def get_password(self, service, username):
        return self.store.get((service, username))
    def delete_password(self, service, username):
        self.store.pop((service, username), None)

keyring.set_keyring(MemoryKeyring())          # stand-in for Credential Manager / Keychain in this test

keyring.set_password("weekly-sales-report", "reports@acme.example", "s3cret-smtp")
print(keyring.get_password("weekly-sales-report", "reports@acme.example"))
print(keyring.get_password("weekly-sales-report", "nobody"))

On a real desktop the set_keyring line is omitted: keyring picks the platform's store automatically, and the password is set once — interactively, keyring set weekly-sales-report reports@acme.example — then read by the script at every run. The secret is protected by the user's login; a scheduled task must run as that same user to read it. On headless Linux servers there is usually no keyring service, which is why servers use the next options.

Step 4 — A secret manager on servers and in the cloud

A secret manager — AWS Secrets Manager, Azure Key Vault, Google Secret Manager, HashiCorp Vault — stores secrets centrally, controls who may read each one, logs every access and supports rotation. The script fetches the secret at start-up with credentials it already has from its environment:

import json
import boto3
from moto import mock_aws

os.environ.update(AWS_ACCESS_KEY_ID="testing", AWS_SECRET_ACCESS_KEY="testing", AWS_DEFAULT_REGION="eu-west-2")

def fetch_secret(client, name):
    value = client.get_secret_value(SecretId=name)["SecretString"]
    return json.loads(value)

with mock_aws():
    sm = boto3.client("secretsmanager")
    sm.create_secret(Name="reports/sales-db", SecretString=json.dumps({"username": "reports", "password": "rotated-2026-09"}))
    db = fetch_secret(sm, "reports/sales-db")
    print(sorted(db), "password length:", len(db["password"]))

The secret is JSON holding both the username and the password, so rotating both is one update. Fetch it once per run and keep it in memory, never in a file or a cache on disk. On AWS, the job's IAM role grants secretsmanager:GetSecretValue on that one secret; no access key is stored anywhere. Send an Excel Report with Amazon SES relies on the same role-based credentials.

Better still is a secret you do not have: Azure managed identities, Google service accounts attached to Cloud Run, AWS IAM roles and PostgreSQL IAM authentication let the job authenticate as itself, with short-lived tokens issued by the platform. When the database or API supports it, there is nothing to store and nothing to rotate.

Step 5 — Keep secrets out of logs

The most common leak is not the code but the log: a connection string printed in a traceback, an exception message containing a URL with a password, a debug line printing the config. A logging filter that masks known secret values catches them wherever they appear:

import logging

class RedactSecrets(logging.Filter):
    def __init__(self, secrets):
        super().__init__()
        self.secrets = [s for s in secrets if s and len(s) >= 6]
    def filter(self, record):
        message = record.getMessage()
        for s in self.secrets:
            message = message.replace(s, "***")
        record.msg, record.args = message, ()
        return True

log = logging.getLogger("weekly_report")
handler = logging.StreamHandler()
handler.setFormatter(logging.Formatter("%(levelname)s %(message)s"))
handler.addFilter(RedactSecrets(list(secrets.values()) + [db["password"]]))
log.addHandler(handler)
log.setLevel(logging.INFO)
log.info("connecting to %s with password %s", secrets["REPORT_DB_URL"], secrets["REPORT_DB_PASSWORD"])
log.error("SMTP login failed for reports@acme.example using %s", secrets["SMTP_PASSWORD"])

The filter replaces every configured secret value with *** before the message is written, whichever handler writes it. Values shorter than six characters are skipped, because masking a two-character string would mangle ordinary text. The same filter belongs on file handlers — Log Python Excel Script Output to a File — and on the handler that emails failure alerts.

Common pitfalls

• Passwords in the script "just for testing". They get committed; read them from the environment from day one.
• Committing .env. Add it to .gitignore before creating it, and scan history if it ever slipped in — a committed secret must be rotated, not just deleted.
• Secrets in command-line arguments. They are visible to other users in the process list; use the environment or a file readable only by the job.
• Printing the config for debugging. Log which variables are set, never their values.
• Keyrings on servers. Headless Linux usually has none; use the service's environment or a secret manager.

Conclusion

Read every secret through one function that checks the environment and names anything missing. Load a git-ignored .env file on developer machines only, use the operating system keyring for desktop jobs, and on servers take secrets from the service's environment or a secret manager — or avoid them entirely with a managed identity. Add a logging filter that masks secret values so no traceback or debug line can leak them.

Frequently asked questions

Where should I store the database password for a Python report script? Not in the script. Read it from an environment variable set by the scheduler or service, from the operating system keyring on a desktop, or from a secret manager on a server; locally, load it from a git-ignored .env file.

Is a .env file safe? It is fine for development if it is excluded from version control, but it is still a plain-text file. On servers, use the service manager's environment settings or a secret manager instead.

How do I use Windows Credential Manager or the macOS Keychain from Python? Use the keyring library: keyring.set_password(service, username, password) once, and keyring.get_password(service, username) in the script. It selects the platform's secure store automatically.

How do I stop passwords appearing in log files? Add a logging.Filter to every handler that replaces known secret values with a mask before the message is written, and avoid logging configuration objects or connection strings.

Related

• Up one level: Testing and Packaging Excel Automation Scripts — making report code dependable.
• Keep Excel Report Settings in a Config File — non-secret settings belong here.
• Authenticate to Microsoft Graph with MSAL for Excel Automation — app credentials for Microsoft 365.
• Set Up an Excel Automation Project with uv — where .env.example and .gitignore live.