+44 (0)20 3051 3595 | info@coeo.com | Client portal login

Adventures with SQL Server 2019 on Linux

There are many posts out there on installing SQL Server 2019 on Linux and in fact the Microsoft tutorials do a pretty good job of taking you through the installation.

This blog post takes you through my experiences with those tutorials and down some of the rabbit holes that I stumbled into along the way.

My Linux distro of choice for this experience was Ubuntu 18.04 LTS. Over the years I have tried many distros and have settled on Ubuntu.

In order to try SQL Server on Linux I have access to an MSDN account that includes Azure credit, so this is where I begin.

First I deployed an Azure Virtual Machine with the Ubuntu 18.04 LTS Image deployed, which is straightforward and simple. Although 20.04 LTS is available there is no updated support statement on the SQL Server 2019 documentation to say that it is now supported. I might try this at a later date.

GOTCHA: Looking at the prerequisites for SQL Server on ubuntu states "You must have an Ubuntu 16.04 or 18.04 machine with at least 2 GB of memory." I chose a virtual machine B1ms (1 vCPU 2 GiB Ram 2 Data Disks). I obviously was not reading the spec correctly when I chose as 2GiB is not the same as 2GB and only gives 1905 MB of ram, which is not sufficient for mssql-conf to complete.

The next stage of the installation is getting SQL Server on the machine and installed, for this I followed the Quickstart: Install SQL Server and create a database on Ubuntu which can be found on the page Installation guidance for SQL Server on Linux.

After resizing my virtual machine to B2s all went well, SQL Server Installed, configured and ran. I could connect locally, create databases, query data and all was well with the world... UNTIL the last step Connecting from Windows.

GOTCHA: The Ubuntu image does not have UFW configured and the firewall is not configured but I could not connect from my local machine. The Aure VM deployed a Network Security Group (NSG) as default and this did not contain rules for:

  • SQL Server Port 1443
  • My IP Address (available from What is my IP)

Adding these and I was able to connect.

Great I have a working SQL Server that I can connect to from my local machine as SA... Hold on, I thought, I don't want to use SQL Authentication and Active Directory integration IS supported... How hard can it be?

OK first I need an Active Directory domain... HMM I will use Azure Active Directory Domain Services (AADDS), I thought, that should work.

So I followed Tutorial: Create and configure an Azure Active Directory Domain Services instance, created an AADDS instance, deployed a domain, set the DNS settings for the virtual network, created vnet peering between AADDS and my Linux server Vnet and all was good, I thought.

GOTCHA: Don't overlook the section Enable user accounts for Azure AD DS or your log ins will not work.

OK, so next we need to connect the Linux Server to Azure AD. For this I followed join an Ubuntu Linux virtual machine to an Azure AD Domain Services managed domain, which is straightforward and easy to follow and surprisingly worked well. At the end I could:

  • Log in to the Linux server using an active directory user
  • A home directory would be created for the user
  • If the user was a domain admin they would be able to run SUDO.

All good (or so I thought), so on to the next step connecting SQL Server to the Azure AD Domain. For this I used Tutorial: Use Active Directory authentication with SQL Server on Linux

I got as far as "Create AD user (or MSA) for SQL Server and set SPN" before I hit a problem. Creating the SPN using setspn.exe failed with permissions errors. Hold on, I'm using an account that is an AADDS Administrator so it should have permissions.

GOTCHA: After digging it appears that the attributes on the account are read only, in fact all of the attributes are read only! 

So I ended up deploying an Azure VM as a domain controller as a quick workaround and managed to get further.

NOTE: Pay attention to the Configure SQL Server service keytab section as it is easy to get confused as there is no worked example, take your time and replace each placeholder in turn.

OK, so I should now have an AD Integrated SQL Server. However I cannot connect from any other machines. I suspect my changing from one domain to another has broken things.

Time to tear it all down and rebuild it again following these instructions and see how far I get this time. 

So although SQL Server on Linux is easy to install, making it useable in a domain environment takes a little more work.

Subscribe to Email Updates

Back to top