# Connect local MySQL Workbench to private RDS `uat-rds-db` via SSM port forwarding

Account `022067219328` · region `ap-south-1` · caller `arn:aws:iam::022067219328:user/Balaji`
All AWS facts below were verified with read-only `describe`/`get`/`simulate` calls on 2026-08-12.

---

## 0. Verified environment (why this procedure is the right one)

| Item | Verified value | How verified |
|---|---|---|
| RDS endpoint | `uat-rds-db.c5myq6iqolml.ap-south-1.rds.amazonaws.com:3306` | `rds describe-db-instances` |
| Engine | MySQL **8.4.9** | same |
| `PubliclyAccessible` | **`false`** | same |
| Master username | `admin` | same |
| RDS VPC | `vpc-0ba5292c6e629d49b` | same |
| RDS subnet group | `uat-rds-subnet-group` (`subnet-0a95301dc28869233`, `subnet-051ed076122989e7c`) | same |
| RDS security group | **`sg-0d31a06531cad03bb`** (`UAT-RDS-SG`) | same |
| Jump host | `i-04bed8d526c55e47d` = `10.0.1.128`, Ubuntu 24.04, `running` | `ec2 describe-instances` |
| Jump host VPC | **`vpc-0ba5292c6e629d49b` — same VPC as RDS** | same |
| Jump host subnet | `subnet-01388097a3537078c` | same |
| Jump host security group | **`sg-0100d3a20fb80baef`** (`Bastion Server-SG`) | same |
| Jump host IAM profile | `arn:aws:iam::022067219328:instance-profile/SSM` | same |
| SSM agent | `PingStatus: Online`, agent `3.3.4793.0` | `ssm describe-instance-information` |
| Secret | `arn:aws:secretsmanager:ap-south-1:022067219328:secret:b2g/uat/rds-master-Uel63l`, not scheduled for deletion, rotation off | `secretsmanager describe-secret` |

### The tunnel IS required
`PubliclyAccessible = false` is confirmed. There is no route from your laptop to the instance. The tunnel is not optional.

### The network path is explicitly allowed — verified rule
`UAT-RDS-SG` (`sg-0d31a06531cad03bb`) has exactly one ingress rule, and it names the bastion's security group directly:

```
IpProtocol tcp, FromPort 3306, ToPort 3306
  UserIdGroupPairs:
    - GroupId: sg-0100d3a20fb80baef      <-- Bastion Server-SG = i-04bed8d526c55e47d
    - GroupId: sg-0148e52978f27d5a6      ("B2G Fargate tasks")
  IpRanges:
    - 223.181.117.177/32
```

So the path is a first-class, explicit SG-to-SG rule — not an accident and not dependent on anything implicit. `Bastion Server-SG` egress is `-1` (all protocols) to `0.0.0.0/0`, so nothing blocks the outbound leg either.

Note on `223.181.117.177/32`: that is a public IP allowed on 3306, but because `PubliclyAccessible = false` the instance has no public interface, so this rule cannot be used from the internet. Do not treat it as a direct-connect option.

### The right SSM document — verified present
```
Name:          AWS-StartPortForwardingSessionToRemoteHost
Owner:         Amazon        Type: Session       Status: Active
DocumentVersion: 1           SchemaVersion: 1.0
Parameters:    host (default "localhost"), portNumber (default "80"), localPortNumber (default "0")
```

**This is the document you need.** It accepts a `host` parameter, so the EC2 acts as a jump box and forwards to a *remote* host (the RDS endpoint).

Do **not** use `AWS-StartPortForwardingSession` — that document has no `host` parameter and can only forward to a port listening *on the instance itself*. Nothing listens on 3306 on the bastion, so it would fail or connect to nothing.

### IAM — verified sufficient, nothing to add
`user/Balaji` has **`arn:aws:iam::aws:policy/AdministratorAccess`** attached (plus Textract/SNS/S3/Bedrock policies and `IAMUserChangePassword`); no inline user policies; one group, `dev-team-repo-access`.

`iam simulate-principal-policy` against the real resource ARNs returned **`allowed`** for all of:

| Action | Resource simulated | Decision |
|---|---|---|
| `ssm:StartSession` | `arn:aws:ec2:ap-south-1:022067219328:instance/i-04bed8d526c55e47d` | allowed |
| `ssm:StartSession` | `arn:aws:ssm:ap-south-1::document/AWS-StartPortForwardingSessionToRemoteHost` | allowed |
| `ssm:TerminateSession`, `ssm:DescribeSessions`, `ssm:ResumeSession` | — | allowed |
| `secretsmanager:GetSecretValue` | the `b2g/uat/rds-master-Uel63l` ARN | allowed |

**No IAM change is needed, and nothing was attached or modified.** (If you ever run this as a *different*, non-admin principal, the minimum policy is in the troubleshooting section — it is provided as reference JSON only.)

### Local machine — verified
- AWS CLI `aws-cli/2.29.1` ✓
- `session-manager-plugin.exe` present at `C:\Program Files\Amazon\SessionManagerPlugin\bin\session-manager-plugin.exe` **and resolvable on `PATH`** ✓
- Shell is **Windows PowerShell 5.1** (`5.1.26100.8875`) — this matters for quoting, see step 2
- **Local TCP 3306 is already in use**: a listener on `[::]:3306` owned by PID 7260, process name **`mysqld`** (your local MySQL/XAMPP). This is why the runbook uses **3307**.
- **Local TCP 3307 is free** ✓
- MySQL Workbench **8.0.28** installed at `C:\Program Files\MySQL\MySQL Workbench 8.0 CE\` — see the version caveat in step 4.

---

## 1. Pre-flight (30 seconds)

```powershell
aws sts get-caller-identity --output table
aws ssm describe-instance-information --region ap-south-1 `
  --filters "Key=InstanceIds,Values=i-04bed8d526c55e47d" `
  --query "InstanceInformationList[0].{Id:InstanceId,Ping:PingStatus,Agent:AgentVersion}" `
  --output table
```

Require `Ping = Online`. If it says anything else, stop and go to troubleshooting → `TargetNotConnected`.

---

## 2. Open the tunnel

> **Leave this terminal window open for the whole session.** The tunnel lives inside this process. Closing the window, pressing `Ctrl+C`, or letting the machine sleep drops the tunnel and every Workbench query will fail mid-flight. Use a *second* terminal / Workbench for actual work.

### 2a. RECOMMENDED — `key=value` shorthand (simplest form that survives PowerShell 5.1)

```powershell
aws ssm start-session `
  --region ap-south-1 `
  --target i-04bed8d526c55e47d `
  --document-name AWS-StartPortForwardingSessionToRemoteHost `
  --parameters host=uat-rds-db.c5myq6iqolml.ap-south-1.rds.amazonaws.com,portNumber=3306,localPortNumber=3307
```

No quotes are needed at all in this form, which is exactly why it is safe.

Expected output:
```
Starting session with SessionId: Balaji-xxxxxxxxxxxxxxxxx
Port 3307 opened for sessionId Balaji-xxxxxxxxxxxxxxxxx.
Waiting for connections...
```
When Workbench connects you will additionally see `Connection accepted for session [...]`.

### 2b. MOST ROBUST — parameters in a JSON file

Immune to all shell quoting. Create the file once and reuse it.

```powershell
$tunnel = "$env:USERPROFILE\.aws\rds-uat-tunnel.json"
@'
{
    "Target": "i-04bed8d526c55e47d",
    "DocumentName": "AWS-StartPortForwardingSessionToRemoteHost",
    "Parameters": {
        "host": ["uat-rds-db.c5myq6iqolml.ap-south-1.rds.amazonaws.com"],
        "portNumber": ["3306"],
        "localPortNumber": ["3307"]
    }
}
'@ | Out-File -FilePath $tunnel -Encoding utf8

aws ssm start-session --region ap-south-1 --cli-input-json "file://$tunnel"
```

The key names are **PascalCase** (`Target`, `DocumentName`, `Parameters`) in `--cli-input-json`, and every parameter value is a **list of strings**. This shape was confirmed with `aws ssm start-session --generate-cli-skeleton`. The `file://` prefix passes through PowerShell untouched (verified).

### 2c. The quoting trap — measured, not guessed

I dumped the actual `argv` a native `.exe` receives from Windows PowerShell 5.1 for each candidate form. Results:

| Form you type | What the CLI actually receives | Verdict |
|---|---|---|
| `--parameters host=...,portNumber=3306,localPortNumber=3307` | `host=...,portNumber=3306,localPortNumber=3307` | ✅ **clean** |
| `--parameters '{"host":["..."],"portNumber":["3306"]}'` | `{host:[...],portNumber:[3306]}` — **all `"` stripped** | ❌ `Invalid JSON received` |
| `--parameters '{\"host\":[\"...\"]}'` (single quotes + backslash-escaped `"`) | `{"host":["..."]}` — intact | ✅ works |
| `--parameters "{\"host\":[\"...\"]}"` (double quotes + backslash) | split into **2 arguments**, literal `\` kept | ❌ badly mangled |
| `--parameters '{""host"":[""...""]}'` (doubled quotes) | `{host:[...]}` — quotes stripped | ❌ invalid JSON |
| `--cli-input-json file://C:/path/params.json` | passed through verbatim | ✅ **clean** |

**Takeaway:** the intuitive form — single-quoted JSON — is the one that silently breaks, because PowerShell 5.1 strips the inner double quotes before the exe ever sees them. Use **2a** or **2b**. If you must inline JSON, the *only* correct form is single quotes with backslash-escaped double quotes:

```powershell
aws ssm start-session --region ap-south-1 --target i-04bed8d526c55e47d `
  --document-name AWS-StartPortForwardingSessionToRemoteHost `
  --parameters '{\"host\":[\"uat-rds-db.c5myq6iqolml.ap-south-1.rds.amazonaws.com\"],\"portNumber\":[\"3306\"],\"localPortNumber\":[\"3307\"]}'
```

In **Git Bash** the plain single-quoted JSON *does* work correctly — the trap is PowerShell-specific. Don't carry a working Git Bash command into PowerShell unchanged.

---

## 3. Get the password from Secrets Manager

Never hardcode it. Fetch only the password field into a variable:

```powershell
$sec = aws secretsmanager get-secret-value `
  --secret-id b2g/uat/rds-master `
  --region ap-south-1 `
  --query SecretString --output text | ConvertFrom-Json

$sec.username          # expected: admin
# Copy to clipboard WITHOUT printing it:
$sec.password | Set-Clipboard
```

Paste into the Workbench password prompt, then clear the clipboard and the variable:

```powershell
Set-Clipboard -Value ""
Remove-Variable sec
```

> **Do not** echo `$sec.password` to the terminal, paste it into chat/tickets/Slack, or write it into any file in `d:\Projects\modernization\b2g_demo`. Terminal scrollback and shell history both persist. `admin` is the RDS **master** user — prefer the lower-privilege `b2gapp` account for day-to-day querying if you have its credentials.

---

## 4. Configure the MySQL Workbench connection

MySQL Workbench → **Database ▸ Manage Connections… ▸ New**:

| Field | Value |
|---|---|
| Connection Name | `UAT RDS (via SSM tunnel)` |
| **Connection Method** | **Standard (TCP/IP)** |
| Hostname | **`127.0.0.1`** |
| Port | **`3307`** |
| Username | `admin` (or `b2gapp`) |
| Password | click **Store in Vault …** and paste; leave the field itself blank |
| Default Schema | `bgi_b2g_reg_dev` |

### Do NOT pick "Standard TCP/IP over SSH"
This is the most common mistake with this setup. Session Manager has **already built the tunnel** — Workbench's SSH option would try to build a *second*, independent SSH tunnel and would need an SSH host, user and private key that you do not have and do not need (the bastion is reached over SSM, not SSH; port 22 is not part of this path). Choose **Standard (TCP/IP)** and point it at `127.0.0.1:3307`. From Workbench's point of view the database is simply running on your own machine.

### Advanced tab — raise the timeouts
Tunnelled connections add latency and Workbench's stock timeouts are tight. Under **Advanced** (or *Edit ▸ Preferences ▸ SQL Editor ▸ MySQL Session*):

- **Connection Timeout**: `10` → **`60`** seconds
- **DBMS connection read timeout**: `30` → **`600`** seconds

### SSL — important given `127.0.0.1`
Server-side TLS is **available but not forced**: the parameter group `b2g-uat-mysql84` has exactly one user-modified parameter (`log_bin_trust_function_creators = 1`), so `require_secure_transport` is at its RDS default of **off**.

On the **SSL** tab set **SSL Mode = `Require`** (or leave `If available`). Do **not** choose `Verify CA` or `Verify Identity`: you are connecting to the literal name `127.0.0.1`, which will never match the RDS server certificate's hostname, so identity verification fails by construction even though the connection is perfectly fine. Keeping TLS enabled also matters because MySQL 8.4 defaults to `caching_sha2_password`, whose first-time (uncached) authentication needs either TLS or an RSA key exchange.

### Workbench version caveat — this one is likely to bite
Your Workbench is **8.0.28** (released early 2022); the server is **MySQL 8.4.9**. Expect an *"Incompatible/nonstandard server version"* or *"unsupported server version"* warning on connect. For running queries and browsing data you can usually dismiss it and continue, but 8.0.28 predates MySQL 8.4 entirely and some Workbench features that read server metadata (schema inspector, table/user editors, Server Status, data export) can misbehave against an 8.4 server.

If you hit odd metadata errors, upgrade Workbench — either the latest **8.0.4x** or **MySQL Workbench 8.4 LTS** to match the server major version. Alternatively verify the tunnel with a plain client, which is unaffected:

```powershell
mysql -h 127.0.0.1 -P 3307 -u admin -p
```

---

## 5. Verify, in order

**5a. Local port is listening** (run in a *second* terminal, tunnel still open in the first):

```powershell
Get-NetTCPConnection -LocalPort 3307 -State Listen |
  Select-Object LocalAddress,LocalPort,State,OwningProcess
```
Expect one row on `127.0.0.1:3307`. Confirm it belongs to the plugin:
```powershell
Get-Process -Id (Get-NetTCPConnection -LocalPort 3307 -State Listen).OwningProcess |
  Select-Object Id,ProcessName
```
Expect `session-manager-plugin`. **No row at all** ⇒ the tunnel never came up; re-read step 2's output for an error.

**5b. TCP reaches the database:**
```powershell
Test-NetConnection 127.0.0.1 -Port 3307
```
Expect `TcpTestSucceeded : True`.

**5c. Session is registered server-side:**
```powershell
aws ssm describe-sessions --state Active --region ap-south-1 `
  --query "Sessions[*].{Id:SessionId,Target:Target,Doc:DocumentName}" --output table
```

**5d. Workbench:** click **Test Connection**, then open the connection and run:
```sql
SELECT @@hostname, @@version, DATABASE(), CURRENT_USER();
SHOW DATABASES;
```
`@@version` should report **8.4.9** — that confirms you reached RDS and not your local `mysqld` on 3306.

**5e. Close down.** `Ctrl+C` in the tunnel terminal, or:
```powershell
aws ssm terminate-session --session-id <SessionId> --region ap-south-1
```

---

## 6. Which schema to open

Verified against the repo's own config files:

- **`bgi_b2g_reg_dev` — set this as the Default Schema.** It is the primary transactional schema: `b2g_usermgmt/config/db-local.php`, `b2g_profilemgmt/config/db-local.php`, `b2g_confmgmt/config/db-local.php` and `b2g_mail/config/db-local.php` all point their DSN at `dbname=bgi_b2g_reg_dev`, and `b2g_digitall/config/params-*.php` sets `regDbSchema => bgi_b2g_reg_dev`.
- **`bgi_b2g_master_dev`** — reference/master tables (`basemodulemst_tbl`, `accessmst_tbl`, etc.). Code comments across `b2g_usermgmt` and `b2g_profilemgmt` are explicit that these are resolved via the master service and **never cross-DB joined**, so you will open it for lookups rather than as your default.
- **`bgi_b2g_gmarket_dev`** — gMarket only (`b2g_gmarketapi/config/db-local.php`). Note `README.md:195` flags that `b2g_gmarketapi/config/db-dev.php` points at `bgi_b2g_master_dev` as a known copy-paste bug; the correct schema for that service is `bgi_b2g_gmarket_dev`. Don't let that confuse you when comparing config to reality.

Because all three live on the same instance, one connection reaches them all — fully-qualify names (`bgi_b2g_master_dev.accessmst_tbl`) when you need to reach outside the default schema.

---

## 7. Troubleshooting

### `TargetNotConnected` — "… is not connected"
The SSM agent isn't reachable. Check:
```powershell
aws ssm describe-instance-information --region ap-south-1 `
  --filters "Key=InstanceIds,Values=i-04bed8d526c55e47d" `
  --query "InstanceInformationList[0].{Ping:PingStatus,Agent:AgentVersion,LastPing:LastPingDateTime}" --output table
aws ec2 describe-instance-status --instance-ids i-04bed8d526c55e47d --region ap-south-1 --output table
```
If `PingStatus` is `ConnectionLost` or the instance is stopped, that is an infrastructure fix (start the instance / restart `amazon-ssm-agent`), not a client-side one. The agent was `Online` at v`3.3.4793.0` when verified; `IsLatestVersion` is `false`, which is fine and not the cause of a failure. **Fallback:** `i-0e259c00d05375899` is also Online, but it sits in a **different VPC** (`ip-172-31-43-73` is a `172.31.x` default-VPC address, whereas RDS is in `vpc-0ba5292c6e629d49b` / `10.0.x`) — so it is **not** a usable substitute jump box. Only `i-04bed8d526c55e47d` is confirmed to have the network path.

### `AccessDeniedException` on `StartSession`
Should not occur — `AdministratorAccess` is attached and `simulate-principal-policy` returned `allowed` for both the instance and the document ARN. If you nonetheless see it, you are almost certainly authenticated as a *different* identity than expected. Check first:
```powershell
aws sts get-caller-identity
aws configure list
$env:AWS_PROFILE
```
Expired SSO/STS credentials surface as `ExpiredToken`/`InvalidClientTokenId` rather than `AccessDenied`.

For reference only — the least-privilege policy a non-admin principal would need. **Do not attach this now; it is not required for `user/Balaji`.**
```json
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "StartPortForwardToRemoteHost",
      "Effect": "Allow",
      "Action": "ssm:StartSession",
      "Resource": [
        "arn:aws:ec2:ap-south-1:022067219328:instance/i-04bed8d526c55e47d",
        "arn:aws:ssm:ap-south-1::document/AWS-StartPortForwardingSessionToRemoteHost"
      ]
    },
    {
      "Sid": "ManageOwnSessions",
      "Effect": "Allow",
      "Action": ["ssm:TerminateSession", "ssm:ResumeSession"],
      "Resource": "arn:aws:ssm:ap-south-1:022067219328:session/${aws:username}-*"
    },
    {
      "Sid": "DescribeSessions",
      "Effect": "Allow",
      "Action": "ssm:DescribeSessions",
      "Resource": "*"
    },
    {
      "Sid": "ReadDbSecret",
      "Effect": "Allow",
      "Action": "secretsmanager:GetSecretValue",
      "Resource": "arn:aws:secretsmanager:ap-south-1:022067219328:secret:b2g/uat/rds-master-Uel63l"
    }
  ]
}
```

### `SessionManagerPlugin is not found`
The plugin is installed and on `PATH` (both verified), so this points to a stale shell. Open a **new** PowerShell window — `PATH` changes don't propagate into already-running terminals. Then:
```powershell
(Get-Command session-manager-plugin).Source
session-manager-plugin --version   # expect 1.2.835.0
```
If still not found, prepend it for the current session only (non-persistent, changes nothing on disk):
```powershell
$env:PATH += ";C:\Program Files\Amazon\SessionManagerPlugin\bin"
```

### Local port already in use
`localPortNumber` 3307 was verified free, but **3306 is occupied by `mysqld` (PID 7260)** — which is precisely why we don't use it. Beyond the bind failure, using 3306 risks the far more confusing outcome of silently querying your *local* XAMPP database while believing you're on UAT. If 3307 is ever taken:
```powershell
Get-NetTCPConnection -State Listen -LocalPort 3307 |
  ForEach-Object { Get-Process -Id $_.OwningProcess | Select-Object Id,ProcessName }
```
Kill the stray process, or just switch to `localPortNumber=3308` and update Workbench's port to match. Symptom of a leftover tunnel: `Unable to start port forwarding: Local port 3307 is in use` — list active sessions with `aws ssm describe-sessions --state Active` and terminate the orphan.

### Connection refused / times out at the RDS end
The tunnel opens (you see `Waiting for connections...`) but Workbench reports `Can't connect to MySQL server on '127.0.0.1'` and the tunnel terminal logs an error when the connection is attempted. That means the bastion→RDS hop failed, not your laptop→bastion hop. Re-confirm the SG rule still names the bastion's group:
```powershell
aws ec2 describe-security-groups --group-ids sg-0d31a06531cad03bb --region ap-south-1 `
  --query "SecurityGroups[0].IpPermissions" --output json
```
You must still see `sg-0100d3a20fb80baef` under `UserIdGroupPairs` for port 3306. Also re-check `rds describe-db-instances` for `DBInstanceStatus` = `available` (it was `available`, AZ `ap-south-1a`). Confirm the endpoint spelling — a typo in `host` produces exactly this symptom, since the bastion's DNS lookup fails.

### Workbench hangs, then "Lost connection to MySQL server during query"
Two distinct causes:
1. **Timeouts too low** — apply the Advanced-tab values in step 4 (read timeout 600s). Long-running queries over a tunnel routinely exceed the 30s default.
2. **The tunnel died.** Check the tunnel terminal is still open and hasn't printed an error, and re-run step 5a. Session Manager also idles out; the account's idle-timeout is in the Session Manager preferences document. Just restart the tunnel and reconnect.

### Everything connects but the data looks wrong / like your local DB
Run `SELECT @@hostname, @@version;`. If `@@version` is not `8.4.9`, you are talking to your local `mysqld` — you almost certainly used port 3306 instead of 3307 in the Workbench connection.

---

## 8. Quick reference

```powershell
# 1. tunnel (leave running)
aws ssm start-session --region ap-south-1 --target i-04bed8d526c55e47d `
  --document-name AWS-StartPortForwardingSessionToRemoteHost `
  --parameters host=uat-rds-db.c5myq6iqolml.ap-south-1.rds.amazonaws.com,portNumber=3306,localPortNumber=3307

# 2. password to clipboard (second terminal)
((aws secretsmanager get-secret-value --secret-id b2g/uat/rds-master --region ap-south-1 `
  --query SecretString --output text) | ConvertFrom-Json).password | Set-Clipboard

# 3. Workbench: Standard (TCP/IP) | 127.0.0.1 | 3307 | admin | schema bgi_b2g_reg_dev
```