# SSH

This presumes an SSH box has been created, and a client has deposited into it a `.bacpac` file -- which is a Microsoft SQL Server backup file.

This is intended to be followed by a client strategist to gain access to the data for data validation.

## One time setup

Ensure you have [docker](https://docs.docker.com/desktop/setup/install/mac-install/) installed.

You will need a local database client, you can use a CLI, or a GUI like [dbbeaver](https://dbeaver.io/).

You can install `dbbeaver` with:

```bash
brew install --cask dbbeaver-community`
```

Now, you need to ensure you have `sqlpackage` installed.

```bash
cd
curl -L -o sqlpackage.zip https://aka.ms/sqlpackage-macos
unzip sqlpackage.zip -d ~/sqlpackage
chmod +x ~/sqlpackage/sqlpackage
```

## Getting the file from the EC2 instance

First, get the SSH secret from AWS.

```bash
fsh login
```

Please select the correct customer.

That will give you an output like:

```
╭─rodda@Roddas-MacBook-Pro ~
╰─$ fsh login
Select an environment: ulster
Ready. customers/ulster as Admin:

    export AWS_PROFILE=fsh-ulster-Admin

Tip: eval "$(fsh login ulster --role Admin --export)"
```

We need to remember the `fsh-ulster-Admin`.

Then, get the secret.

```bash
aws secretsmanager get-secret-value --profile fsh-ulster-Admin \
    --secret-id ulster-ingest/ssh-private-key \
    --query SecretString --output text > ulster-ingest.pem
```

Replace `fsh-ulster-Admin` with whatever it is for you.  `ulster-ingest.pem` will be the filename for the certificate, you can change this to whatever you want.

Then, change the permissions of the SSH key.

```bash
chmod 600 ulster-ingest.pem
```

Now, you can `ssh` into the box to see what's there, as well as `scp` files off the box.

To `ssh`:
```bash
ssh -i ulster-ingest.pem dataclient@52.15.209.190
```

```{warning}
The IP address will change depending on the box you're connecting to
```

To `scp`:
```bash
scp -i ulster-ingest.pem dataclient@52.15.209.190:/home/dataclient/'Ulster County'/ULSTER_35.bacpac ~/ulster.bacpac
```

This will copy the file at `'Ulster County'/ULSTER_35.pacpac` to your local `'~/ulster.bacpac'`.  You're now ready to load the DB.

## Loading the `.bacpac` to a local database

First, we need to start a local SQLServer database.

```bash
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=YourStrong-Pass1' \
  -p 1433:1433 --name sqlserver \
  -d mcr.microsoft.com/azure-sql-edge:latest
```

The first time run the docker image, it will download the image, which may take a few minutes.

```{warning}
The SQL Server instance will fail to start if the password does not meet complexity requirements, I encourage you to preserve the password as indicated above.
```

Now, load the `.bacpac` file into your database.

```bash
~/sqlpackage/sqlpackage \
  /Action:Import \
  /SourceFile:"<FILEPATH>" \
  /TargetServerName:"localhost,1433" \
  /TargetDatabaseName:"MyDatabase" \
  /TargetUser:"sa" \
  /TargetPassword:"YourStrong-Pass1" \
  /TargetTrustServerCertificate:True
```

Voila!

You can now access the database via any tool that connects to a SQLServer database.

DB name: `MyDatabase`
Username: `sa`
Password: `YourStrong-Pass1`

```{warning}
If you ever need to delete and pull in a new `bacpac` file, please run `docker rm sqlserver` and start from the `docker run` command again.
```