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 installed.

You will need a local database client, you can use a CLI, or a GUI like dbbeaver.

You can install dbbeaver with:

brew install --cask dbbeaver-community`

Now, you need to ensure you have sqlpackage installed.

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.

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.

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.

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:

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:

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.

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.

~/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.