DEV Community

sys-ronin
sys-ronin

Posted on

AdventureWorks SQL Server with DBeaver Keystore Helper

#sqlserver #dbeaver #adventureworks #devops

I needed a local SQL Server with the AdventureWorks2022 sample database. Sounds like a 10-minute job, right? Run a container, restore a .bak, connect with DBeaver. Done.

It took two hours. Here's what actually happened, what broke, and the setup that finally worked.

The Goal

  • SQL Server 2022 running in Docker on Debian
  • Auto-restore AdventureWorks2022.bak on first boot
  • Connect from DBeaver without certificate errors
  • Survive restarts without re-restoring the database

What I Built

A Docker Compose setup with a custom entrypoint.sh that:

  1. Starts SQL Server in the background
  2. Polls sqlcmd every 2 seconds until the engine is ready
  3. Checks if AdventureWorks2022 already exists (idempotent)
  4. If not, runs RESTORE DATABASE ... WITH MOVE
  5. Keeps the container alive

The database files live in a named Docker volume, so stopping and restarting the container doesn't wipe your data.

The Bugs That Weren't in the Tutorial

1. Permission denied on entrypoint.sh

I store my projects on Koofr (cloud-synced). chmod +x works on the host, but Docker bind-mounts the file without the execute bit. The container kept restarting with Permission denied.

Fix: Run the script through /bin/bash instead of relying on the shebang:

entrypoint: ["/bin/bash", "/usr/config/entrypoint.sh"]
Enter fullscreen mode Exit fullscreen mode

2. "Access is denied" on master.mdf

SQL Server's default mssql user couldn't write to the Docker volume.

Fix: Added user: root to the compose service. Fine for local dev. Don't do this in production.

3. sqlcmd v18 won't connect without -C

SQL Server 2022 bundles sqlcmd in /opt/mssql-tools18/bin/. This version refuses to connect to localhost without explicitly trusting the self-signed certificate, even though it's the same machine.

Fix: Every sqlcmd call gets the -C flag. The healthcheck too.

4. DBeaver's JDBC driver demanded a keystore

I kept getting:

"keyStoreAuthentication" connection string keyword must be specified, if "keyStoreSecret" is specified

Clearing the properties in one connection didn't help — the driver had cached defaults somewhere. I spent 20 minutes in DBeaver preferences before giving up and creating a dummy Java keystore.

Fix: One keytool command to generate a fake .jks file, then four driver properties in DBeaver:

Property Value
keyStoreAuthentication JavaKeyStorePassword
keyStoreLocation /absolute/path/to/dummy.jks
keyStoreSecret dummy123
trustServerCertificate true

It shouldn't be this hard to connect to a local database. But here we are.

DBeaver Connection — The Exact Settings

After the container is running, here's how to connect DBeaver without any certificate drama.

Main Tab

Setting Value
Host localhost
Port 1433 (or whatever you set in .env)
Database AdventureWorks2022
Authentication SQL Server Authentication
User sa
Password your .env password

Driver Properties Tab

These are the properties that actually matter. Add or edit them here:

Property Value Why
trustServerCertificate true Tells the JDBC driver to accept the self-signed cert SQL Server generates on first boot. Without this, you get SSL handshake failures even on localhost.
keyStoreAuthentication JavaKeyStorePassword Tells the driver you're using a local JKS file, not Azure Key Vault.
keyStoreLocation /absolute/path/to/dummy.jks Points to the dummy keystore file. Use the absolute path — DBeaver doesn't resolve ~ here.
keyStoreSecret dummy123 The password for the dummy keystore.

How to get there: Edit Connection → Driver Properties → scroll down or use the search box. If a property doesn't exist, right-click in the list and choose Add New Property.

Click Test Connection. It should say "Connected".

If You Don't Want the Keystore Hack

You can try resetting the driver completely:

  1. Window → Preferences → Connections → Drivers → Microsoft JDBC Driver for SQL Server
  2. Click Reset to Defaults (or delete and re-download)
  3. In your connection: Driver Properties → gear icon → Reset to Defaults
  4. Add back only: trustServerCertificate = true

Sometimes this works. Sometimes the keystore properties come back anyway. The dummy keystore is faster and more reliable.

The Repo

Everything is here: docker-compose.yml, entrypoint.sh, the keystore generator, and a full setup guide.

github.com/sjyotis/adventureworks2022-docker

Quick Start 

git clone https://github.com/sjyotis/adventureworks2022-docker.git
cd adventureworks2022-docker
mv /path/to/AdventureWorks2022.bak backups/
docker compose up -d
Enter fullscreen mode Exit fullscreen mode

Wait for the logs to show Restore completed successfully!, then connect DBeaver using the settings above.

Repo Structure 

adventureworks2022-docker/
├── docker-compose.yml              # Main SQL Server container
├── .env                            # Password and port config
├── init/
│   └── entrypoint.sh              # Auto-restore on first boot
├── backups/
│   └── AdventureWorks2022.bak    # Drop your .bak here
├── dbeaver-keystore-fix.md        # Full keystore workaround guide
├── adventureworks-docker-setup.md # Complete implementation docs
└── keystore-generator/
    ├── docker-compose.yml         # One-shot keystore generator
    └── generate_keystore.py       # Python wrapper if you prefer
Enter fullscreen mode Exit fullscreen mode

What This Actually Demonstrates

  • Docker Compose orchestration with health checks and named volumes
  • Bash scripting for service initialization and idempotency
  • SQL Server administration (RESTORE FILELISTONLY, WITH MOVE)
  • SSL/TLS debugging in client drivers
  • Cross-platform filesystem edge cases (cloud-synced storage stripping permissions)

It's not just "I ran a database in Docker." It's a complete local dev infrastructure setup that handles provisioning, initialization, persistence, and client connectivity across the full stack.

The Files

  • docker-compose.yml — SQL Server 2022 container with volume mounts and healthcheck
  • init/entrypoint.sh — Auto-restore script with startup polling
  • .env — Configurable SA password and port
  • backups/ — Drop your .bak here
  • dbeaver-keystore-fix.md — Standalone guide for the DBeaver certificate workaround

If you've ever fought with DBeaver certificates, SQL Server Docker permissions, or restore scripts that assume the database is already running, this repo is for you.

Pull requests welcome. Issues too — especially if you hit a new edge case I haven't seen yet.

Top comments (0)