SQL Server on Ubuntu 16.04

Install and configure mssql

https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-linux-ver15

Create and mount mssql volume

Create a Digital Ocean volume. In the following examples it appears as volume_sfo2_03 or ``volume-sfo2-03`.

sudo su -

mkdir -p /mnt/mssql
mount -o discard,defaults,noatime /dev/disk/by-id/scsi-0DO_Volume_volume-sfo2-03 /mnt/mssql
echo '/dev/disk/by-id/scsi-0DO_Volume_volume-sfo2-03 /mnt/mssql ext4 defaults,nofail,discard 0 0' | sudo tee -a /etc/fstab

Configure mssql default directories

sudo su -

# Backup to the mounted drive
mkdir -p /mnt/mssql/backup
chown -R mssql:mssql /mnt/mssql/backup
chmod 770 /mnt/mssql/backup
/opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /mnt/mssql/backup

# OPTIONALLY, store data in a mounted drive
mkdir -p /mnt/mssql/data
chown -R mssql:mssql /mnt/mssql/data
chmod 770 /mnt/mssql/data
/opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /mnt/mssql/data

# OPTIONALLY, log to a mounted drive
mkdir -p /mnt/mssql/log
chown -R mssql:mssql /mnt/mssql/log
chmod 770 /mnt/mssql/log
/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /mnt/mssql/log

systemctl restart mssql-server.service

Use NGINX and certbot to generate certificates

sudo su -

# Install NGINX
apt update
apt install nginx

# Create NGINX stub site
nano /etc/nginx/sites-available/data1.flipforms.com

Paste this into nano and save.

server {
    listen 80;
    listen [::]:80;
    server_name data1.flipforms.com;

    return 404 'ignore mode';
}

sudo su -

# Enable the stub site
ln -s /etc/nginx/sites-available/data1.flipforms.com /etc/nginx/sites-enabled/data1.flipforms.com
# Disable the default site
rm /etc/nginx/sites-enabled/default
# Test the config and restart NGINX to apply the changes
nginx -t
systemctl restart nginx.service

# Install Certbot - https://certbot.eff.org/lets-encrypt/ubuntuxenial-nginx
apt-get update
apt-get install software-properties-common
add-apt-repository universe
add-apt-repository ppa:certbot/certbot
# press ENTER to agree
apt-get update

apt-get install certbot python-certbot-nginx

# Configure certbot and NGINX to work together
certbot --nginx

# Create a script that copies the Let's Encrypt certificates for MSSQL to use
nano /opt/flipforms-scripts/copy-mssql-letsencrypt-certs.sh

Paste this into nano and save.

cp /etc/letsencrypt/live/data1.flipforms.com/fullchain.pem /etc/letsencrypt/live/data1.flipforms.com/privkey.pem /var/opt/mssql/secrets/
chown mssql:mssql /var/opt/mssql/secrets/fullchain.pem /var/opt/mssql/secrets/privkey.pem
chmod 400 /var/opt/mssql/secrets/fullchain.pem /var/opt/mssql/secrets/privkey.pem
systemctl restart mssql-server

sudo su -

chmod 770 /opt/flipforms-scripts/copy-mssql-letsencrypt-certs.sh
# Run it and make sure there are no errors
/opt/flipforms-scripts/copy-mssql-letsencrypt-certs.sh

# Have root run it weekly
crontab -e
0 0 * * 1  /opt/flipforms-scripts/copy-mssql-letsencrypt-certs.sh

# Update mssql TLS settings
cat /var/opt/mssql/mssql.conf
/opt/mssql/bin/mssql-conf set network.tlscert /var/opt/mssql/secrets/fullchain.pem
/opt/mssql/bin/mssql-conf set network.tlskey /var/opt/mssql/secrets/privkey.pem
/opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2
/opt/mssql/bin/mssql-conf set network.forceencryption 1

systemctl restart mssql-server.service