--- name: bnna-infra-sqlserver-setup version: 1.0.0 description: Set up a shared infrastructure SQL Server CT on the global services network (vsvc174 / 172.24.0.0/24). Use when creating a SQL Server instance reachable from all tenant VLANs. Covers second NIC on vsvc174, switch VLAN trunking, gateway/routing, TLS via mssql-conf, cross-vnet connectivity verification, and tenant connection strings. Network is shared between tenants — TLS required. depends: [bnna-sqlserver-setup] --- # Infra SQL Server Setup (BNNA) Sets up a shared SQL Server CT on the global services network so all tenant VLANs can reach it via a private address. Requires `bnna-sqlserver-setup` for the base SQL Server install. ## Overview Shared infrastructure (Postgres, MariaDB, SQL Server, internal APIs) lives on the **global services network** (`vsvc174` / VLAN 174 / `172.24.0.0/24`). Tenant networks (`10.11.x.x`) route to `172.24.0.x` via OPNsense. There is no inter-tenant routing — only tenant → global services. MUST: The global services network is **shared between tenants** — it is NOT a private/trusted network. All SQL Server connections MUST use TLS. ## 1. Provision the CT Use `bn-create` with the bnna provisioning env (`~/.config/proxmox-sh/bnna-dev.env`). Use an **Ubuntu 22.04 or 24.04 LTS template** — SQL Server does not run on Alpine. The CT gets its primary NIC on the bnna account network (`v1110` / `10.11.10.x`). The global services NIC is added as a second interface after creation. ## 2. Run base SQL Server setup Follow the `bnna-sqlserver-setup` skill — run `scripts/sqlserver-setup-ubuntu.sh` as the `app` user. Save the SA password. ```sh scp scripts/sqlserver-setup-ubuntu.sh app@:~/sqlserver-setup-ubuntu.sh ssh app@ "sh ~/sqlserver-setup-ubuntu.sh" # VERIFY: SQL Server listening on 1433 ssh app@ "ss -tlnp | grep 1433" # VERIFY: tcpfwd forwarding 11433 → :1433 ssh app@ "ss -tlnp | grep 11433" ``` ## 3. Switch VLAN trunking + OPNsense firewall Follow `docs/new-vlan-checklist.md` steps 2-4. Key steps for vsvc174: 1. **MikroTik switches** — add VLAN 174 to trunk ports on sw1 and sw2 2. **OPNsense firewall** — add pass rules on the VLAN 174 interface (floating rules alone are NOT sufficient) 3. **TLS router** — trunk VLAN 174 to the TLS router's switch port if `tls-172-24-0-x` direct IP domains are needed NEVER: Skip the switch step. Proxmox SDN will show the bridge as UP but L2 frames won't reach the router. Symptoms: 100% packet loss. ## 4. Add second NIC on vsvc174 Add `net1` on the global services bridge (`vsvc174`) via the Proxmox API. The vsvc174 gateway (`172.24.0.1`) should be the **default gateway**. Remove the gateway from net0 (v1110) — it only needs local /24 reachability. ``` PUT /nodes/{node}/lxc/{vmid}/config net0=name=eth0,bridge=v1110,ip=10.11.10.{host}/24,type=veth net1=name=eth1,bridge=vsvc174,ip=172.24.0.{host}/24,gw=172.24.0.1,type=veth ``` - MUST: IP addresses start at `.21` (.1-.20 reserved) - MUST: Default gateway on net1 (vsvc174), NOT net0 (v1110) - MUST: Cold reboot after config change ### Expected route table ``` default via 172.24.0.1 dev eth1 metric 1 onlink 10.11.10.0/24 dev eth0 scope link src 10.11.10.{host} 172.24.0.0/24 dev eth1 scope link src 172.24.0.{host} ``` ### IP assignment | CT | VMID | vsvc174 IP | |----|------|------------| | pg-svc1 | 1110021 | 172.24.0.21 | | mariadb-svc1 | 1110022 | 172.24.0.22 | | sqlserver-svc1 | 1110023 | 172.24.0.23 | ### Static route for TLS router access After moving the default gateway to net1, add to `/etc/network/interfaces` under the eth0 stanza: ``` up ip route add 10.11.10.0/24 via 10.11.10.1 dev eth0 ``` MUST: Set up cross-CT SSH (to sqlserver-svc1 via 10.11.10.x LAN) BEFORE the cold reboot, since TLS router access may be broken until routes are configured. ## 5. OPNsense firewall rules OPNsense blocks all traffic on an interface by default. Add firewall rules on the VLAN 174 interface to allow inbound traffic from tenant VLANs. Floating rules alone are not sufficient. ## 6. Set up TLS SQL Server on the global services network MUST use TLS. TLS is configured via `mssql-conf` (not via a config file directly). ### Self-signed (initial setup) ```sh sudo mkdir -p /etc/mssql/tls sudo openssl req -new -x509 -days 3650 -nodes -text \ -out /etc/mssql/tls/server.crt \ -keyout /etc/mssql/tls/server.key \ -subj "/CN=sqlserver-svc1" sudo chown mssql:mssql /etc/mssql/tls/server.crt /etc/mssql/tls/server.key sudo chmod 600 /etc/mssql/tls/server.key sudo /opt/mssql/bin/mssql-conf set network.tlscert /etc/mssql/tls/server.crt sudo /opt/mssql/bin/mssql-conf set network.tlskey /etc/mssql/tls/server.key sudo /opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2 sudo /opt/mssql/bin/mssql-conf set network.forceencryption 1 sudo systemctl restart mssql-server ``` Verify TLS: ```sh sqlcmd -S 172.24.0.23,1433 -U sa -P '' \ -Q "SELECT SERVERPROPERTY('IsEncrypted')" \ -C # -C = trust server certificate (for self-signed) ``` ### ACME (production — replace self-signed) Use ACME with DNS-01 validation for a proper cert (no public reachability needed): ```sh acme.sh --issue --dns dns_cf -d sqlserver-svc1.m.bnna.net ``` Update `network.tlscert` / `network.tlskey` in mssql-conf to point at the ACME cert, then restart mssql-server. acme.sh handles renewal via cron. MUST: Keep `network.forceencryption = 1` — the network is shared between tenants. ## 7. Create admin login Create an admin login for remote management: ```sh sqlcmd -S 172.24.0.23,1433 -U sa -P '' -C << SQL CREATE LOGIN [bnna_admin] WITH PASSWORD = '', CHECK_POLICY = ON; ALTER SERVER ROLE sysadmin ADD MEMBER [bnna_admin]; GO SQL ``` The credentials become the `admin_dsn` for this SQL Server instance in bnna. When `forceencryption = 1`, the Go driver DSN needs `TrustServerCertificate=true` (self-signed) or proper cert verification (ACME): ``` sqlserver://bnna_admin:@172.24.0.23:1433?TrustServerCertificate=true ``` ## 8. Verify cross-vnet connectivity From a tenant CT on a different VLAN (e.g. `10.11.8.x` or `10.11.4.x`): ```sh # Ping the global services address ping -c 3 172.24.0.23 # Test SQL Server connection (port 11433 forwarded to :1433) sqlcmd -S 172.24.0.23,11433 -U -P '' -Q "SELECT 1 AS ok" ``` If ping fails, check in order: 1. Switch VLAN trunking (step 3) 2. OPNsense firewall rules (step 5) 3. OPNsense routing — tenant VLANs must route to `172.24.0.0/24` via their gateway NEVER: Assume connectivity works without testing from an actual tenant CT. ## 9. Drop SQLSERVER.md for production tenant instances When provisioning a production-facing instance for a tenant: ```sh scp scripts/SQLSERVER.md app@:~/SQLSERVER.md ``` This drops the licensing steps where the tenant will find them when they SSH in. The file explains how to switch to Express Edition (free) or enter a purchased key. ## 10. Tenant connection strings Tenants connect via the global services address, not the primary NIC. Via tcpfwd on 11433 (plain TDS from tenant's CT): ``` sqlserver://:@172.24.0.23:11433?database= ``` External access (dev/admin) via TLS router SSH tunnel: ``` sqlserver://:@localhost:21433?database= ``` See `connect-to-sqlserver` for tunnel setup from a dev machine. ## Related Skills - `bnna-sqlserver-setup` — base SQL Server install (prerequisite) - `connect-to-sqlserver` — dev machine access via SSH tunnel - `bnna-infra-mikrotik-vlan` — MikroTik switch VLAN trunking - `bnna-tls-router` — TLS router port/ALPN reference - `use-bnna-api` — CT lifecycle via the bnna API - `docs/new-vlan-checklist.md` — full new-VLAN checklist