admin Let’s go ahead and admit it: I just have a thing for open source software. FreeBSD and Ubuntu Linux really tickle my fancy. Free, lightweight, fully customizable, versatile, yet world-renowned for stability. This “thing” used to be totally useless when your daily work is all about Microsoft. Recently, things have changed… Because Microsoft released SQL Server 2017 for Linux!
Probably, you’re already wondering why I wrote this article, or even why I tried running SQL Server on Linux. Yes, I know, who cares about on premise installations since we have Azure and Docker?
Don’t ask me “why”, but I just had to give it a go: A Dynamics NAV 2018 database, on Microsoft SQL Server, on Linux. Two (or three?) worlds working together in peace. It’s happening! And if this isn’t enough motivation, well, then there’s always this little beauty:
- Because we can! *evil laugh*
Step 1: Setting up the Linux Server
If your world is entirely made up out of Microsoft, setting up a Linux Server doesn’t seem the most straightforward thing to do. But actually, it is, so here’s a (detailed but quick) crash course for installing a Ubuntu Linux Server:
- Go to this link to download Ubuntu Server. Unless you need cutting edge functionality, download the LTS version. Like with Windows Server editions, this release has Long Term Support (five years), so if you keep this Linux Server running you’ll be happy until 21.04 (for the 16.04 version).
- If you’re planning to run Ubuntu on a physical machine, google “unetbootin” (it’s somewhere on SourceForge) to quickly copy the image you just downloaded to a USB stick + make it bootable.
- If you’re running a hypervisor (Hyper-V or VMWare for example), just mount the Ubuntu image on a new system and run it. Concerning resources, I’d start with 3GB RAM and 2 cores if you’re limited – Ubuntu Server doesn’t have a GUI and is very lightweight out of the box.
- You’ll find the setup is very straightforward. By the way, I usually skip keyboard detection and just select “US International”.
- Go easy on yourself: Install “SSH” right from the setup tool. This is your “Remote Desktop Connection” for Linux. Slight difference: Your Linux has no GUI, so this is a tool to access the command prompt. If you already skipped through the screen that gives you this option: No problem, you can do it afterwards from the command prompt.
So, probably you’re looking at an installed Linux Server with a login-prompt now. After you login it’ll probably tell you it needs some updates. You can easily install these updates by running the tool to do so as administrator with the following command:
sudo apt-get upgrade
After entering this, you will have to enter your password again. “sudo” means “superuser do”, which is equal to running stuff as administrator in Windows. The rest is not important now. Fun fact: Unless you’re very unlucky, you won’t have to reboot the machine after the updates have installed…
If you forgot to install SSH earlier, just run this command to get that settled:
sudo apt-get install -y openssh-server
Last time I did this manually is a while ago but it should work right after installation.
Step 2: Tools to access the machine from your Windows box
To make our Linux-box with SSH useable from outside, we need two tools:
- PuTTY: Use this to access the command prompt from your Windows machine. You can run ifconfig (with an F instead of a P) from the command prompt to see the IP address of your Linux machine.
- WinSCP: Convenient tool which looks like a mix between Windows Explorer and good old Norton Commander, but allows you to access files on your Linux machine over the SSH connection. (yes there’s also Bitvise SSH, this is personal preference)
Step 3: Installing Microsoft SQL Server on Linux
This is where the mixing starts. Let’s first tell Ubuntu where to get SQL Server, by running the following commands (either through PuTTY or directly on the prompt):
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –
sudo add-apt-repository “$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)”
sudo apt-get update
Background info: Ubuntu Linux has a “Store”, just like Windows. What we did here is: add the Microsoft encryption key to the system (so it trusts Microsoft’s repository), add the repository to the list of repositories in Ubuntu, and then get information on available packages for the system. Next commands:
sudo apt-get install -y mssql-server
sudo /opt/mssql/bin/mssql-conf setup
These last steps will look different than usual, but probably be very familiar once you see what is being asked. Let’s keep it simple, and also open up port 1433 for all incoming connections in the firewall:
sudo ufw allow 1433/tcp
Congratulations: You’ve just installed and setup SQL Server on your Linux machine!
Step 4: Preparing your database
If all went well, you should now have a running SQL Server, and you should be able to login to the server from your workstation using Microsoft SQL Server Management Studio.
The folder structure is a little different in Linux, but if you copy an SQL backup to your server through WinSCP, you can locate it in SSMS:
Once the database is there, you should be able to connect through the classic “Development Environment”.
Step 5: Connecting the Microsoft Dynamics NAV Service Tier
But suddenly, setting up the Dynamics NAV Service Tier is not so straightforward.
Yes, you probably already noticed this in step 3 and 4: SQL Server Authentication is back. Not a good thing in my opinion, but the Linux machine is simply not a domain member and we’ll have to live with this for now.
The difficulty on using SQL Server Authentication in the Dynamics NAV service tier is in the fact that you’ll need an encryption key to secure traffic between the service tier and the database server: Normally this is handled by the domain. For production environments I’d always use a certificate provided by an official authority, but if you’re simply testing stuff I’d say use makecert.exe to generate one yourself.
After generating the certificate, you’ll need to install this to the service tier. Ashwini Tripathi has written a very helpful blog on how to get this running. Here’s another Dynamics NAV Administration (Power)shell shortcut:
$Credential = (New-Object PSCredential -ArgumentList ‘sa’,(ConvertTo-SecureString -AsPlainText -Force ‘MYPASSWORD’))
Import-NAVEncryptionKey -ApplicationDatabaseServer LINUXSERVERHOSTNAME -ApplicationDatabaseCredentials $Credential -ApplicationDatabaseName DATABASENAME -KeyPath ‘KEYPATH.key’ -Force
Where all the caps need to replaced with your values, of course.
Step 6: Enjoy!
After completing these steps, your Dynamics NAV clients and even the classic development environment should now be able to start on the Linux database server:
For now, I’ll be using this database server to do some development on 2018 and possibly also on 2013; if I have any news on this, you’ll read it here.
If you have any issues, questions or remarks, let me know!