How to Build a Free Linux Microsoft SQL Server

This covers how to create a virtual linux server running Microsoft SQL Server.

 

First, create a virtual server with the following requirements in mind.

  • Ubuntu 14.02 LTS (Server or Desktop)
  • At least two CPUs
  • At least 4gb RAM
  • At least 10GB HDD for the operating system
  • PLUS at least double the amount of space your databases will use

When you install Ubuntu, make sure to enable updates and third-party software, unless you’re the real DIY-type.

First, update the default installation packages;

sudo apt-get update && sudo apt-get upgrade

Now we need to install a few tools before we can get started;

sudo apt-get install cifs-utils curl

Install Microsoft SQL Server

Run these commands to install Microsoft SQL Server and its tools;

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –

curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list

sudo apt-get update && sudo apt-get install -y mssql-server

sudo /opt/mssql/bin/mssql-conf setup

You will be prompted to create an SA or Server Administrator password. Use something with high entropy!

Now install tools;

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –

curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

sudo apt-get update && sudo apt-get install mssql-tools unixodbc-dev

echo ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bash_profile

echo ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bashrc

source ~/.bashrc

Now, copy over your backup file and put it in /var/opt/mssql/data/

I have not gotten the cli tools to work for importing the backups. They seem to look for Windows paths. You will need to use MS SQL Studio to import the backup.

How To Create a Local Storage Repository on XenServer

I was working with a XenServer in a complex corporate network environment, and it was not possible for this server to access any samba shares, such as my laptop. I needed to put some ISOs on it, so I decided to create a local storage repository. This way, I would be able to simply -wget an ISO from the web, and then use it locally.

 

First, SSH into the XenServer and create a directory for the repository;

mkdir -p /var/opt/xen/LocalRepo

Then, tell Xen to create a XenServer Storage Repository at that directory;

xe sr-create name-label=LocalRepo type=iso device-config:location=/var/opt/xen/LocalRepo device-config:legacy_mode=true content-type=iso

Now move to the directory and then wget whatever ISOs you need…

cd /var/opt/xen/LocalRepo

 

wget http://releases.ubuntu.com/16.04.2/ubuntu-16.04.2-desktop-amd64.iso

Now you’re cooking with gas!

 

PS. Make sure to check your free space and make sure your ISOs will fit. This partition is not very big by default.

df -H

Raspberry Pi 3 NAS: Debian, Apache, Samba, BitTorrent Sync, BitTorrent [In-Progress]

NAS stands for Network Accessible Storage, but in this post we are taking it to the next level!

I will show you how to build a Raspberry Pi 3 NAS with a few extra features:

  • Using Debian as the OS will be easy to secure and light on resources, but also Debian runs on anything, so almost all of these steps will work on other hardware as well. If you do not want to use a Pi, skip to the “Setting up Networking” step.
  • Apache will share files over the web, and secure them with a password.
  • Samba shares files over the local network.
  • BitTorrent Sync automatically backs up of all my devices in real time.
  • BitTorrent will allow me to remotely download files to my NAS and then access them. This means I can download large or slow files from my phone, or while my laptop is off.

The Hardware

Debian famously runs on anything, so probably any hardware will work, but I wanted to do this with a Raspberry Pi 3 so I picked one up along with a few accessories;

  • Raspberry Pi 3 Model B ($39 on Amazon)
  • Clear acrylic case This is probably unnecessary, but it seemed like a good precaution to protect the Pi and the surfaces it sits on. ($6.95 on Amazon)
  • Touchscreen for the Pi ($21.99 on Amazon) This was kind of a nightmare to set up. I would skip this unless you really want it.

I already had these other three items, but here they are if you want the same ones!

Installing the OS

Debian runs on anything, but it needs to know how. The Raspberry Pi uses a lot of unusual hardware which is not supported by mainstream Debian, so Raspberry Pi have come out with several customized versions which have support for all their hardware.

I decided to use the most lightweight version they offer, Raspbian Jessie Lite. Ideally, I would like to star with just pure Debian, but that would involve building a kernel and that doesn’t sound like fun.

Head over to the official site or click here to get the torrent for Raspbian Jessie.

Now head over to Sourceforge and download Win32DiskImager.

Use that to write the image from the torrent onto the SD card.

If You Decided To Get That Touchscreen…

Like I said, setting it up is a nightmare. I got the “Kuman 3.5 Inch 480×320 TFT Touch Screen” and eventually figured out how to set it up by reading through dozens of angry Amazon comments until I pieced this together.

Open the sd card you wrote the image to. Edit the file “/boot/cmdline.txt” and change its contents to… (All one line)

dwc_otg.lpm_enable=0 console=tty1 console=ttyAMA0,115200 root=/dev/mmcblk0p2 rootfstype=ext4 elevator=deadline rootwait fbcon=map:10 fbcon=font:ProFont6x11 logo.nologo

Now, edit “/boot/config.txt” and add onto the end…

dtparam=audio=on
dtparam=spi=on
dtoverlay=ads7846,penirq=25,penirq_pull=2,xohms=150,swapxy=1,xmin=300,ymin=700,xmax=3800,ymax=3400,pmax=255
dtoverlay=waveshare35a

Go to this GitHub and download the file “waveshare35a.dtb.”

Rename it to “waveshare35a.dtbo” and put it in the directory “/boot/overlays”

That’s all it takes! ;P

Setting Up Networking

Now you will need to eject the sd card and boot up the pi with the screen attached, as well as a usb keyboard.

The default username is pi, and the default password is raspberry.

In order to connect the Pi to Wifi, type “sudo nano /etc/wpa_supplicant/wpa_supplicant.conf” and add the following…

network={
  ssid="YOUR SSID"
  psk="YOUR PSK"
}

Save that and exit.

Now lets set a static IP.

Select an IP outside the range of your router’s DHCP and then type “sudo nano /etc/network/interfaces”. Find the line that says “iface wlan0 inet manual” and replace it like this…

iface wlan0 inet static
        address 192.168.1.200
        netmask 255.255.255.0
        gateway 192.168.1.1

Leave everything before and after that line we replaced, but all all this stuff in place of that line.

Last but not least, run “sudo raspi-config”.

Under boot options, set Desktop/CLI to “console autologin”

Under advanced options, enable ssh server.

Now exit raspi-config and reboot with “sudo reboot.” You can disconnect the keyboard. We will no longer need it. This is a good time to move the Pi to its eventual destination if you would like.

Remote Access

Install Putty or another SSH client and use it to connect to the static IP you just set up.

In order to use SSL, we will need to add another repository to the package manager. Type “sudo nano /etc/apt/sources.list” and add…

deb http://ftp.debian.org/debian jessie-backports main

Now we need to make sure everything is up to date with “sudo apt-get update && sudo apt-get upgrade”

Now let’s install all the things we will need:

sudo apt-get install fail2ban apache2 screenfetch htop nload python-certbot-apache -t jessie-backports && sudo a2enmod rewrite && sudo service apache2 restart

This will install the following:

  • Fail2Ban: Prevents bruteforce ssh attacks.
  • Apache2: Shares files over the web, securely.
  • Screenfetch: Let’s us share sweet screenshots.
  • Htop: Shows resource usage in case there is a problem.
  • Nload: Shows a graphical representation of real-time network usage. Good ambient display.
  • Certbot: Gives us free SSL through LetsEncrypt.

Set Up The Web Server

Change directory to the new webroot with “cd /var/www” and remove the default files with “rmdir html –ignore-fail-on-non-empty”

Edit the default VirtualHost configuration file, “sudo nano /etc/apache2/sites-available/000-default.conf”.

Change the line “DocumentRoot /var/www/html” to “DocumentRoot /var/www” and save the file.

 

Wifi Pentesting on a Raspberry Pi [In-Progress]

I want to expand on these resources to create a good post about wifi pentestint on Raspberry Pi. I will also include Wifite which has made the process really easy for me.

 

http://www.catonrug.net/2014/05/prepare-reaver-for-raspberry-pi.html

http://lifehacker.com/5873407/how-to-crack-a-wi-fi-networks-wpa-password-with-reaver

http://askubuntu.com/questions/362146/how-to-install-cowpatty

Building a Debian Application Server with PHP 7 + MySQL 5.7 + MS SQL

One of the applications I am building integrates with a legacy software platform including MS SQL Server. In past versions of PHP, there was a simple and free tool called FreeTDS which enabled PHP to connect to MS SQL Server, but this has been deprecated as of PHP 7.

In order to get all the new features, performance increases, and security improvements that come with PHP 7, we need to find an alternative to FreeTDS. Oddly enough, Microsoft has released an official replacement! 😀
First, I created a droplet with Digital Ocean (Referral Link) and give it 1GB RAM.

Now, the default apt list needs to be expanded;

nano /etc/apt/sources.list

Add these sources…

deb http://httpredir.debian.org/debian jessie main contrib non-free
deb-src http://httpredir.debian.org/debian jessie main contrib non-free

deb http://httpredir.debian.org/debian jessie-updates main contrib non-free
deb-src http://httpredir.debian.org/debian jessie-updates main contrib non-free

deb http://ftp.debian.org/debian jessie-backports main

deb http://repo.mysql.com/apt/debian/ jessie mysql-apt-config
deb http://repo.mysql.com/apt/debian/ jessie mysql-5.7
deb http://repo.mysql.com/apt/debian/ jessie mysql-tools
deb-src http://repo.mysql.com/apt/debian/ jessie mysql-5.7

deb http://packages.dotdeb.org jessie all
deb-src http://packages.dotdeb.org jessie all

Now lets install the GPG key for DotDeb and MySQL so we can install their packages…

wget https://www.dotdeb.org/dotdeb.gpg && apt-key add dotdeb.gpg &&
gpg --keyserver pgpkeys.mit.edu --recv-key  8C718D3B5072E1F5 &&
gpg -a --export 8C718D3B5072E1F5 | apt-key add -

Update our sources and run any available upgrades;

apt-get update && apt-get upgrade

Add a firewall rule to prevent unwanted sending of outbound mail.

iptables -A INPUT -i eth0 -j REJECT -p tcp --dport 25

Now run this command to install PHP7 and MySQL 5.7;

apt-get -y install fail2ban apache2 && apt-get install php7.0 php7.0-fpm php-pear php7.0-dev php7.0-mysql mcrypt php7.0-mcrypt php-mbstring php7.0-mbstring libapache2-mod-php7.0 php7.0-curl php7.0-xml screenfetch htop nload curl git unzip ntp mcrypt postfix mailutils php7.0-memcached mysql-server apt-transport-https && apt-get install python-certbot-apache -t jessie-backports && a2enmod rewrite && service apache2 restart && mysql_secure_installation

You will be prompted to create a new root password for the mysql installation, and then give that password to mysql_secure_installation so it can run.

Setup Postfix Mail Server

Now edit the config files and change the interface to loopback-only like so. We already set up a firewall rule to block connections to port 25, but those rules can be changed by mistake, so this will be a good second line of defense to prevent public access to sending mail through our server, while allowing us to still use it locally.

nano /etc/postfix/main.cf

Find this line;

inet_interfaces = all

And change to;

inet_interfaces = 127.0.0.1

Now edit the email aliases;

nano /etc/aliases

At the end of the file, make sure there is a line that starts with root and ends with your email, like so;

root: email@domain.com

Save the file and exit. Then run newaliases to let Postfix apply the changes. Restarting Postfix is not enough because we changed the interfaces line in the config file. We need to stop and start it like so;

newaliases && postfix stop && postfix start

 

Creating Two VirtualHosts

First, we need to forward an A-Record from our DNS provider over to the public IP of our new server.

We will need to do the following steps twice: once for the fqdn of the machine, and once for the fqdn of the application we are serving. I like to set the machine’s virtualhost to use /var/www and then put the other virtualhosts in directories inside there, to make them easy to access.

First disable the default VirtualHost.

a2dissite 000-default.conf

Create a directory for our new FQDN.

mkdir /var/www/[fqdn]/

Create a new VirtualHost for our new FQDN.

cp /etc/apache2/sites-available/000-default.conf /etc/apache2/sites-available/[fqdn].conf

Edit the virtual host and make sure it has all of this;

nano /etc/apache2/sites-available/[fqdn].conf
ServerName [fqdn]

ServerAdmin your_email@website.com
DocumentRoot /var/www/[fqdn]/

ErrorLog ${APACHE_LOG_DIR}/error.log
CustomLog ${APACHE_LOG_DIR}/access.log combined

Now enable the virtualhost and restart apache.

a2ensite [fqdn] && service apache2 restart

Securing the Machine’s FQDN VirtualHost

Create a password file for the VirtualHost. Pick a high entropy username and password.

htpasswd -c /etc/apache2/.htpasswd [username]

Now add this to the VirtualHost for the machine’s FQDN.

<Directory "/var/www/">
      AuthType Basic
      AuthName "Restricted Content"
      AuthUserFile /etc/apache2/.htpasswd
      Require valid-user
  </Directory>

Restart Apache to make the changes take effect.

service apache2 restart

PHPMyAdmin

Navigate to the machine’s webroot.

cd /var/www/[FQDN]

Download PHPMyAdmimn.

wget https://files.phpmyadmin.net/phpMyAdmin/[version]/phpMyAdmin-[version]-all-languages.zip

Unzip it into a new directory in the current directory.

unzip phpMyAdmin-[version]-all-languages.zip -d .

Troubleshooting

For basic troubleshooting and performance monitoring, I wrote a simple tool to see the output of a few simple cli tools. It also includes a directory listing. So it’s essentially just a better index file for the vps. Try it out if you like.

wget https://raw.githubusercontent.com/cjtrowbridge/vps-home/master/index.php

Using LetsEncrypt for Free SSL

We already added the repository we need, and we installed the Certbot to take care of our certificates, so now let’s run Certbot to setup SSL for our VirtualHosts.

certbot --apache

Installing MS SQL For PHP

First install the tools.

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt-get update
ACCEPT_EULA=Y apt-get install msodbcsql mssql-tools unixodbc-dev

Then install the drivers

pecl install sqlsrv
pecl install pdo_sqlsrv

Add the newly installed tools into the PHP configuration file.

echo "extension=/usr/lib/php/20151012/sqlsrv.so" >> /etc/php/7.0/apache2/php.ini
echo "extension=/usr/lib/php/20151012/pdo_sqlsrv.so" >> /etc/php/7.0/apache2/php.ini
echo "extension=/usr/lib/php/20151012/sqlsrv.so" >> /etc/php/7.0/cli/php.ini
echo "extension=/usr/lib/php/20151012/pdo_sqlsrv.so" >> /etc/php/7.0/cli/php.ini

Restart Apache and you’re ready to go!

service apache2 restart

Installing PHP 7 on a Debian Server

PHP 7 has reached the level of maturity where I want to start moving my development and production environments over to it.

Setup is a little more complicated than with previous versions, so the purpose of this post is to journal for myself on how I did it, as well as to help others who may need help doing this.

I am using Digital Ocean (Referral Link) to host this VPS. First, I create a new Debian server and update their default apt sources…

nano /etc/apt/sources.list

Make sure all of these are there;

deb http://httpredir.debian.org/debian jessie main contrib non-free
deb-src http://httpredir.debian.org/debian jessie main contrib non-free

deb http://httpredir.debian.org/debian jessie-updates main contrib non-free
deb-src http://httpredir.debian.org/debian jessie-updates main contrib non-free

deb http://security.debian.org/ jessie/updates main contrib non-free
deb-src http://security.debian.org/ jessie/updates main contrib non-free

deb http://packages.dotdeb.org jessie all

deb http://ftp.debian.org/debian jessie-backports main

Add the key for DotDeb so we can install their packages…

wget https://www.dotdeb.org/dotdeb.gpg && apt-key add dotdeb.gpg

Next, update the package manager and upgrade any installed packages;

apt-get update && apt-get upgrade

Now let’s block smtp so people can’t use our server to send emails…

iptables -A INPUT -i eth0 -j REJECT -p tcp --dport 25

Now let’s install all the packages we will need;

apt-get -y install fail2ban apache2 && apt-get install php7.0 php-pear php7.0-mysql php7.0-mcrypt php7.0-mbstring libapache2-mod-php7.0 php7.0-curl screenfetch htop nload curl git ntp mcrypt postfix mailutils php7.0-memcached mysql-server && apt-get install python-certbot-apache -t jessie-backports && a2enmod rewrite && service apache2 restart && mysql_secure_installation

PHPMyAdmin

I like to use PHPMyAdmin to administer my databases. At the time of this post, this is still a little jank to install from the package manager, and I could not get it working that way. The version it installs is trying to run in PHP5.

My recommendation is to setup a secure Apache Virtualhost with a high-entropy password and download the PMA directory into there. This will not receive important security updates because it is not installed through the package manager, but it will be protected from access by the secure Virtualhost.

I hesitate to give instruction on how to do this, because it is not an ideal solution, and once the setup process is more streamlined with PHP 7, I will certainly go back to installing it through the package manager.

Setup Postfix Mail Server

Now edit the config files and change the interface to loopback-only like so. We already set up a firewall rule to block connections to port 25, but those rules can be changed by mistake, so this will be a good second line of defense to prevent public access to sending mail through our server, while allowing us to still use it locally.

nano /etc/postfix/main.cf

Find this line;

inet_interfaces = all

And change to;

inet_interfaces = 127.0.0.1

Now edit the email aliases;

nano /etc/aliases

At the end of the file, make sure there is a line that starts with root and ends with your email, like so;

root: email@domain.com

Save the file and exit. Then run newaliases to let Postfix apply the changes.

newaliases

Restarting Postfix is not enough because we changed the interfaces line in the config file. We need to stop and start it like so;

postfix stop
postfix start

Creating  a VirtualHost

First, we need to forward an A-Record from our DNS provider over to the public IP of our new server.

Then, create a directory for our new FQDN.

mkdir /var/www/[fqdn]/

Create a new VirtualHost for our new FQDN.

cp /etc/apache2/sites-available/000-default.conf /etc/apache2/sites-available/[fqdn].conf

Edit the virtual host and make sure it has all of this;

nano /etc/apache2/sites-available/[fqdn].conf
ServerName [fqdn]

ServerAdmin your_email@website.com
DocumentRoot /var/www/[fqdn]/

ErrorLog ${APACHE_LOG_DIR}/error.log
CustomLog ${APACHE_LOG_DIR}/access.log combined

Using LetsEncrypt for Free SSL

We already added the repository we need, and we installed the Certbot to take care of our certificates, so now let’s run Certbot to setup SSL.

certbot --apache

Automated Backups

Create a directory in which to generate the backups.

mkdir /var/backups/[fqdn]

Add the following to crontab;

nano /etc/crontab
0 22 * * * root tar -cf /var/backups/[fqdn]/www-backup-$( date +'\%Y-\%m-\%d_\%H-\%M-\%S' ).gz /var/www/[fqdn]
find "/var/www/backups/[fqdn]" -type f -name "*.gz" -mtime +6 -delete

This will create a new backup of the document root each night, and delete any backups older than a week. These should automatically be moved to another device each day. I am partial to using BitTorrent Sync to do this, but there are any number of good solutions to this problem. Finding the best solution depends on your setup.

Automatic Emails For Full Drives In Debian

I have a VPS which I use for storing backups and running some of my projects. This VPS recently filled up, leading to lots of problems. I went looking for a solution which would email automatically whenever a drive starts getting full.

 

I found a bash script online which I modified a bit; I added the hostname to the email is sends and set a lower threshold than the original script. Here is what I ended up with in ~/send_email_if_drive_full.sh

#!/bin/bash
CURRENT=$(df / | grep / | awk '{ print $5}' | sed 's/%//g')
THRESHOLD=75

if [ “$CURRENT” -gt “$THRESHOLD” ] ; then
mail -s ‘Disk Space Alert’ chris.j.trowbridge@gmail.com << EOF
vps1.cjtrowbridge.com
Your root partition remaining free space is critically low. Used: $CURRENT%
EOF
fi

 

Then I added this to the crontab;

0 0 * * 0       root    bash /root/send_email_if_drive_full.sh

Renewing Free LetsEncrypt SSL Certificates

A few days ago, I received an email from LetsEncrypt letting me know that it was time to renew my free SSL certificates.

I tried re-running the tool in order to renew the certificates which seemed to work, but then I received this email;

staging-expiry@letsencrypt.org <staging-expiry@letsencrypt.org> Sat, Feb 20, 2016 at 10:15 PM
To: chris.j.trowbridge@gmail.com

Hello,

[ Note: This message is from the Let’s Encrypt staging environment. It likely is not relevant to any live web site. ]

You issued a testing cert (not a live one) from Let’s Encrypt staging environment. This mail takes the place of what would normally be a renewal reminder, but instead is demonstrating delivery of renewal notices. Have a nice day!

Details:
DNS Names:
blog.cjtrowbridge.com
cjtrowbridge.com
j-ha.us
opennewsaggregator.us
Expiration Date: 02 Mar 16 03:36 +0000)
Days to Expiration: 9

For any questions or support, please visit https://community.letsencrypt.org/. Unfortunately, we can’t provide support by email.
If you are receiving this email in error, unsubscribe at [REMOVED]. (HTTP link, we know. We’re working on it!)

Regards,
The Let’s Encrypt Team

It seems my attempt to use the same tool to renew was not the correct way to go about it. I went looking for tutorials online and eventually found the command letsencrypt-auto renew. It seems too easy! It took just a few seconds to renew all the certs that were going to expire.

Official Documentation suggests using the following script to automate this process;
#!/bin/sh
if ! /path/to/letsencrypt-auto renew > /var/log/letsencrypt/renew.log 2>&1 ; then
echo Automated renewal failed:
cat /var/log/letsencrypt/renew.log
exit 1
fi
apachectl graceful

I created this bash script and added it to the crontab with 0 0 * * 0 root bash /root/letsencrypt/maybe_renew.sh

Now it should be checking automatically on a weekly basis!

Updated Comprehensive VPS Setup Documentation

Building a VPS requires lots of complex steps, and these steps change over time.

The time has come to create comprehensive, consolidated documentation for how I setup these machines. Many of these steps are optional.

  1. Deploy A New VPS With Digital Ocean
  2. FQDN DNS Setup With Godaddy
  3. Recommended Initial Installations
  4. Setup Email Server
  5. Create a VirtualHost
  6. Setup Free SSL With LetsEncrypt
  7. Automate Database Backups
  8. Install Webmin

And then you are golden!

VPS Setup: Automated Database Backups

This is a subpost of the larger post Updated Comprehensive VPS Setup Documentation.

 

Create a new directory for the backups;

mkdir /var/backups/mysql

 

I added the following line to /etc/crontab in order to facilitate automatic database backups;
0 22 * * * root /usr/bin/mysqldump -uroot -i[MySQL Root Password] [MySQL Database Name] | gzip > /var/backups/mysql/mysql-backup-$( date +'\%Y-\%m-\%d_\%H-\%M-\%S' ).sql.gz