---
name: bnna-sqlserver-setup
version: 1.0.0
description: Set up SQL Server 2022 Developer Edition on an Ubuntu 22.04 LXC via Microsoft apt repo. Use when installing SQL Server on a bnna Ubuntu CT, configuring port 1433 with tcpfwd forwarding 11433 (TLS router), registering sqlserver-fwd via serviceman, or creating tenant databases. Covers apt install, mssql-conf init, serviceman, connection strings, and production licensing paths.
---

# Setup SQL Server (BNNA Ubuntu 22.04)

## Ports

- SQL Server listens on its default port **1433** (TDS protocol).
- **tcpfwd** forwards port **11433** (= 10000 + 1433) to `<service-ip>:1433` for the TLS router.

## Prerequisites

1. Ubuntu 22.04 LTS LXC container (SQL Server is NOT supported on Alpine/musl)
2. tcpfwd binary available — installed via webi during the setup script
3. serviceman available — installed via webi during the setup script

## Quick Start

Run the setup script as the `app` user (bnna template default):

```sh
scp scripts/sqlserver-setup-ubuntu.sh app@<host>:~/sqlserver-setup-ubuntu.sh
ssh app@<host> "sh ~/sqlserver-setup-ubuntu.sh"
```

MUST: Save the SA password printed by the script — it is shown once.

```sh
# VERIFY: SQL Server listening on 1433
ssh app@<host> "ss -tlnp | grep 1433"

# VERIFY: tcpfwd forwarding 11433 → <service-ip>:1433
ssh app@<host> "ss -tlnp | grep 11433"

# VERIFY: connect as sa
ssh app@<host> "sqlcmd -S localhost,1433 -U sa -P '<sa_password>' -Q \"SELECT SERVERPROPERTY('Edition')\""
# Expected: Developer Edition (64-bit)
```

## What the Script Does

1. `webi tcpfwd serviceman` — installs tcpfwd and serviceman if not present
2. Adds Microsoft GPG key + SQL Server 2022 apt repo, `apt-get install mssql-server`
3. Adds Microsoft prod apt repo, `ACCEPT_EULA=Y apt-get install mssql-tools18`
4. Adds `/opt/mssql-tools18/bin` to PATH via `/etc/profile.d/mssql-tools18.sh` and envman
5. `ACCEPT_EULA=Y MSSQL_PID=Developer mssql-conf -n setup` — initializes DB non-interactively
6. `systemctl enable --now mssql-server` — starts and enables via the apt-provided unit
7. `serviceman add --name sqlserver-fwd -- tcpfwd 11433:<service-ip>:1433` — registers tcpfwd

## Key Differences from PostgreSQL / MariaDB Setup

| Aspect | PostgreSQL (Alpine) | MariaDB (Alpine) | SQL Server (Ubuntu) |
|--------|--------------------|-----------------|--------------------|
| Install method | `webi postgres` (user) | `apk add mariadb` (system) | Microsoft apt repo (system) |
| OS | Alpine (musl) | Alpine (musl) | Ubuntu 22.04 LTS (glibc) |
| Service manager | serviceman | OpenRC (apk) | systemctl (apt unit) + serviceman (tcpfwd) |
| Native port | 5432 | 3306 | 1433 |
| tcpfwd port | 15432 | 13306 | 11433 |
| Config tool | postgresql.conf | /etc/my.cnf.d/ | mssql-conf |
| Data directory | ~/.local/share/postgres/var | /var/lib/mysql | /var/opt/mssql/data |
| Admin user | postgres (socket auth) | root (unix_socket) | sa (password auth) |
| sqlcmd syntax | `psql -p 5432` | `mariadb -P 3306` | `sqlcmd -S host,1433` (comma, not colon) |

## Licensing

### Developer Edition (what the script installs)

Free, full Enterprise feature set. Licensed for **development and testing only**.
May NOT be used for production traffic (end-users of a deployed application).

EULA: https://aka.ms/sqlserver-linux-eula

The script accepts the EULA on your behalf by setting `ACCEPT_EULA=Y`. You are
responsible for ensuring your use is within license terms.

### Production Paths (tenant's responsibility)

All paths are in-place — no reinstall required:

| Path | Command | Notes |
|------|---------|-------|
| **Express (free)** | `sudo /opt/mssql/bin/mssql-conf setpid express` + restart | Max 50 GB DB / 4 cores / 1.4 GB buffer. No SQL Agent (use cron). |
| **Standard/Enterprise (paid)** | `sudo /opt/mssql/bin/mssql-conf setpid <product-key>` + restart | Tenant provides purchased license key. |
| **Stay Developer** | (no change) | Valid only for dev/test — no end-user production traffic. |

Note: "No SQL Agent" (Express) means no built-in job scheduler. Go clients connecting
over TDS/port 1433 are **not** affected — SQL Agent is an internal scheduler only.

Drop `scripts/SQLSERVER.md` in the tenant's home directory on production instances:
```sh
scp scripts/SQLSERVER.md app@<host>:~/SQLSERVER.md
```

## Creating Databases

Deploy `sqlserver-createdb.sh` to the CT, then run:

```sh
scp scripts/sqlserver-createdb.sh app@<host>:~/sqlserver-createdb
ssh app@<host> "chmod +x ~/sqlserver-createdb && \
    ~/sqlserver-createdb <dbname> localhost '<sa_password>'"
```

This creates:
- Login `<dbname>_<6hex>` with a generated password
- Database `<dbname>_<6hex>` with the login as `db_owner`

Uses underscores, not hyphens (e.g. `foo_app` not `foo-app`). Save the password — shown once.

## Connection String Format

Direct connection (within the same VLAN, from another CT):
```
sqlserver://<login>:<password>@<container-ip>:1433?database=<dbname>
```

Via SSH tunnel to tcpfwd port (from dev machine — see `connect-to-sqlserver`):
```
sqlserver://<login>:<password>@localhost:21433?database=<dbname>
```

Go driver (`github.com/microsoft/go-mssqldb`) uses the same URL format.

sqlcmd (note: comma between host and port, not colon):
```sh
sqlcmd -S <ip>,1433 -U <login> -P '<password>' -d <dbname>
```

## Security Model

SQL Server's security model differs from MariaDB in one important way:
**SQL Server logins have no host-based restrictions.** A valid login can connect
from any IP, unlike MariaDB's `user@'172.24.0.%'` pattern.

This means network-level controls are the only gate:

| Layer | Control |
|-------|---------|
| **Transport** | TLS Router terminates external TLS; internal path is plain TDS on controlled VLAN |
| **Network** | OPNsense firewall rules restrict which subnets can reach port 1433/11433 |
| **SQL Server** | `forceencryption = 0` — plain TDS from tcpfwd is accepted (TLS handled externally) |
| **Login** | Server-level logins (no host restriction). Treat credentials as the only auth factor. |

### tcpfwd binding

tcpfwd binds on the first routable IP (`172.24.x.x` if present, else first global IP).
This ensures SQL Server sees real source IPs in audit logs rather than localhost.
Override with `TCPFWD_BIND=<ip>` env var for infra 2-NIC setups:

```sh
TCPFWD_BIND=172.24.0.23 sh sqlserver-setup-ubuntu.sh
```

### Operator access

Use SSH to the CT then `sqlcmd -S localhost,1433 -U sa` — no TCP exposure to operators
via localhost-only tricks (unlike MariaDB's unix_socket for root, SQL Server has no socket auth).
Use OPNsense to block direct port 1433 access from untrusted subnets.

### Contrast with MariaDB

| Aspect | MariaDB | SQL Server |
|--------|---------|-----------|
| Host restrictions | `user@'172.24.0.%'` grants | None — login-level only |
| Root access | unix_socket (no TCP) | SA via TCP (restrict via firewall) |
| Encryption enforcement | `require_secure_transport = OFF` | `forceencryption = 0` |
| Access control layer | MariaDB grants + network | Network (firewall) only |

## Related Skills

- `connect-to-sqlserver` — SSH tunnel and Proxmox SSH options for dev machine access
- `bnna-infra-sqlserver-setup` — shared infra SQL Server on vsvc174 (TLS required)
- `bnna-tls-router` — TLS router port/ALPN reference