Installing MS SQL Server in Ubuntu 18.04

This has been an issue for sometime until now. I found the following link that help me install SQL Server on the latest Ubuntu 18.04:

https://askubuntu.com/questions/1032532/how-do-i-install-ms-sql-for-ubuntu-18-04-lts

But, there are few missing steps which can help ease the burden of errors. At the same time, the information is a little out-dated.

But, it works with the following adjustments.

Please Understand!!  This is NOT approved by Microsoft.  Use this method for Test Only!!

Create Your Installation

The following instructions help you download and get the dpkg package ready for you Ubuntu 18.04 SQL Server installation:

  • Create the folders to extract, and make changes to repackage the dpkg SQL Server installation:
cd ${HOME} && mkdir -p tmp/mssql/newpkg/DEBIAN/ && cd tmp/mssql
  • Download the latest version of SQL Server dpkg to the current folder location: (dpkg SQLServer date: 20-Jun-2018 18:03)
wget https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017/pool/main/m/mssql-server/mssql-server_14.0.3029.16-1_amd64.deb
  • Extract the dpkg package:
dpkg-deb -x mssql-server_14.0.3029.16-1_amd64.deb newpkg/
dpkg-deb -e mssql-server_14.0.3029.16-1_amd64.deb newpkg/DEBIAN/
  • Next step will change the OpenSSL version to avoid failure during SQL Server installation:
sed -i -e 's#openssl (<= 1.1.0)#openssl (<= 1.1.0g-2ubuntu4.1)#g' newpkg/DEBIAN/control
cat newpkg/DEBIAN/control | grep openssl
  • Next step it to Repackage the SQL Server installation:
sudo dpkg-deb -b newpkg/ 18.04-mssql-server_14.0.3029.16-1_amd64.deb

At this stage you could try to install SQL Server, but it might failed.  This is needed in order to check what dependencies are missing. Then, make the necessary dependencies installation.

Additional Steps

As of today, July 5th, I went thru a series of trial-and-error to get my SQL Server running on my Ubuntu 18.04.

After executing the following command:

sudo dpkg -i 18.04-mssql-server_14.0.3029.16-1_amd64.deb

But, I got errors:

The following is the list of all my missing dependencies on Ubuntu 18.04 for the SQL Server installation:

dpkg: dependency problems prevent configuration of mssql-server:
mssql-server depends on libjemalloc1; however:
Package libjemalloc1 is not installed.
mssql-server depends on libc++1; however:
Package libc++1 is not installed.
mssql-server depends on libcurl3; however:
Package libcurl3 is not installed.
mssql-server depends on openssl (<= 1.1.0); however: Version of openssl on system is 1.1.0g-2ubuntu4.1. mssql-server depends on python (>= 2.7.0); however:
Package python is not installed.
mssql-server depends on libsss-nss-idmap0; however:
Package libsss-nss-idmap0 is not installed.
mssql-server depends on gawk; however:
Package gawk is not installed.

Now, one thing to understand, if you execute the following command:

sudo apt install -f

It will clear/remove SQL Server installation components, but it also try to install some, but not all of the dependencies.

As is shown in the image, only two of the listed dependencies were installed: “gawk“, and “libsigsegv2” (this one might be from another package not for SQLServer).

So, identifying the missing dependencies can alleviate the burden of more fail attempts to install SQLServer.

The following command will install all on the listed failed dependencies, excluding OpenSSL because another version is already installed.

## - Adding the missing dependencies:
sudo apt install python libjemalloc1 libc++1 libcurl3 libsss-nss-idmap0

After all the missing dependencies are installed than I can proceed to rerun the re-package SQL Server installation for my Ubuntu 18.04. By the way, I already took care of the OpenSSL in the “Create Your Installation” step where I change the version number.

About Python Dependency?

Yes! In Ubuntu 18.04, Python version 3.6.5 is the one installed with the OS. So, Python 2.7 is not installed.

Try running the command: python –version, then python3 –version at the Terminal Console.

In order to install SQL Server in Linux, it need Python 2.7 installed in order for the installation to work. This is why I included Python in the “sudo apt install …” command to be installed with the other missing dependencies.

Finally Ready

So, finally all the dependencies have been installed. Now, I can rerun the SQL Server installation:

sudo dpkg -i 18.04-mssql-server_14.0.3029.16-1_amd64.deb

This time the installation completes without any error.

To verify that SQL Server is running, execute to following command:

sudo service mssql-server status

Next, verify from your Windows client and open SQL Server Management Studio to verify that the Ubuntu 18.04 SQL Server is accessible.

What’s Next?

Well, if you got PowerShell Core installed, then get the SQLServer Module and start to play around working with both the available cmdlets and/or start coding SMO (SQL Server Management Object) PowerShell Core scripts.

Be creative!  Check out my previous blog post “PSCore6 – SQLServer Module Expanding The Barrier Cross-Platform” for more information.

In Summary

This is a hacking technique to be able to install SQL Server in Ubuntu 18.04.  This is not supported by Microsoft, but you will be able to make it work. Basically, is a matter of installing all the missing dependencies, and change the package required OpenSSL version number to the one installed in Ubuntu 18.04.  Then, repackaging the SQL Server installation dpkg file will allow the installation to work.

Special Thanks to the contributor in the UbuntuAsk forum, as without it I won’t have figured out, and made it work

 

Listing SQL Server 2017 Installed Anaconda Packages Using PowerShell

SQL Server 2017 comes with the option to include Python (Anaconda) during SQL Server installation. It will install Anaconda with a small set of python packages for the purpose of creating Data Science solution that sre executed within T-SQL statement(s). Unfortunately, there’s no documentation of what Anaconda packages are installed with SQL Server.

Much Easier with Full Installation

Doing the full Anaconda installation, gives the necessary commands to query what has been installed in your system. This makes it much easier to list all existing installed packages.

In the full installation of Anaconda, done separate from SQL Server, you can use the following command to list all packages installed:

conda info

But, with SQL Server 2017 is a different story.

Where’s my SQL Server Anaconda packages?

These packages are found in the default installation location: “C:\Program Files\Microsoft SQL Server\”YourSQLServerInstanceName”\PYTHON_SERVICES\conda-meta

All packages are of file type *json. Each Anaconda package will named with: the package name, package version, and python version number. But, this makes it hard to view using “File Explorer“.

So, solution to list the SQL Server Anaconda packages in a proper format will be needed.

PowerShell To The Rescue

So, here’s a PowerShell function that will list all installed Anaconda packages in SQL Server 2017. This will required to enter some parameters, such as: SQL Server Installation Location, and SQL Server Instance name.

function Get-SQLServerAnacondaPkgList
{
[CmdletBinding()]
Param (
[string]
$SQLServerInstallationDrive = 'C:',
[string]
$SQLServerInstanceName
)

$SQLServerInstallationLocation = "$($SQLServerInstallationDrive)\Program Files\Microsoft SQL Server\MSSQL14.$($SQLServerInstanceName)\PYTHON_SERVICES\conda-meta"
$SqlAnaconda = Get-ChildItem $SQLServerInstallationLocation -File *.json;

[array]$global:SqlCondaPkgList = $null;
[array]$global:SqlCondaPkgList = foreach ($Pkg in $SqlAnaconda.name)
{
## - Build PSCustomObject:
[PSCustomObject]$PkgList = New-Object PSObject -Property @{
PackageName = $Pkg.Split('-')[0];
PackageVersion = $Pkg.Split('-')[1];
PackageLocation = $SQLServerInstallationLocation;
}; $PkgList;
};
$global:SqlCondaPkgList;
}

## To execute function:
$SQLServerInstallationDrive = 'C:'
$SQLServerInstanceName = "MSQL2K17A"

Get-SQLServerAnacondaPkgList -SQLServerInstallationDrive $SQLServerInstallationDrive `
-SQLServerInstancename $SQLServerInstanceName;

## - Or, after executing the function, go back to use
## - the existing global variable:
$global:SqlCondaPkgList | Select-Object PackageName, PackageVersion

Bottom line

Executing Anaconda within T-SQL seems only available on Windows version. But, you can still create the Python code and do some testing on Linux.

The total number of packages provided with Microsoft SQL Server 2017 is about 146. Now, in the full version of Anaconda, there is a total of about 217 python packages.

Full listing of all Anaconda Packages installed for SQL Server 2017 (See below):

PackageName PackageVersion
----------- --------------
alabaster 0.7.10
babel 2.4.0
blaze 0.10.1
bleach 1.5.0
bokeh 0.12.5
bottleneck 1.2.0
bzip2 1.0.6
cffi 1.9.1
chest 0.2.3
click 6.7
cloudpickle 0.2.2
colorama 0.3.7
conda 4.3.22
conda env
configobj 5.0.6
console_shortcut 0.1.1
cryptography 1.7.1
curl 7.52.1
cycler 0.10.0
cython 0.25.2
cytoolz 0.8.2
dask 0.14.1
datashape 0.5.4
decorator 4.0.11
dill 0.2.5
docutils 0.13.1
entrypoints 0.2.2
et_xmlfile 1.0.1
flask 0.12.1
flask cors
freetype 2.5.5
h5py 2.7.0
hdf5 1.8.15.1
heapdict 1.0.0
html5lib 0.999
icu 57.1
idna 2.2
imagesize 0.7.1
ipykernel 4.6.0
ipython_genutils 0.2.0
ipython 5.3.0
ipywidgets 6.0.0
itsdangerous 0.24
jdcal 1.3
jinja2 2.9.6
jpeg 9b
jsonschema 2.5.1
jupyter_client 5.0.1
jupyter_console 5.1.0
jupyter_core 4.3.0
jupyter_kernel_gateway 2.0.0
jupyter 1.0.0
libpng 1.6.27
libtiff 4.0.6
llvmlite 0.16.0
locket 0.2.0
lxml 3.7.3
markupsafe 0.23
matplotlib 2.0.0
menuinst 1.4.2
mistune 0.7.4
mkl 2017.0.1
mkl service
mpmath 0.19
multipledispatch 0.4.9
nbconvert 5.1.1
nbformat 4.3.0
networkx 1.11
nltk 3.2.2
notebook 5.0.0
numba 0.31.0
numexpr 2.6.2
numpy 1.12.1
numpydoc 0.6.0
odo 0.5.0
olefile 0.44
openpyxl 2.4.1
openssl 1.0.2k
pandas 0.19.2
pandas datareader
pandasql 0.7.3
pandocfilters 1.4.1
partd 0.3.7
path.py 10.1
pathlib2 2.2.1
patsy 0.4.1
pickleshare 0.7.4
pillow 4.1.0
pip 9.0.1
prompt_toolkit 1.0.14
psutil 5.2.1
py 1.4.33
pyasn1 0.2.3
pycosat 0.6.1
pycparser 2.17
pycrypto 2.6.1
pycurl 7.43.0
pygments 2.2.0
pyodbc 4.0.16
pyopenssl 16.2.0
pyparsing 2.1.4
pyqt 5.6.0
pytables 3.2.2
pytest 3.0.7
python 3.5.2
python dateutil
pytz 2017.2
pywavelets 0.5.2
pywin32 220
pyyaml 3.12
pyzmq 16.0.2
qt 5.6.2
qtconsole 4.3.0
requests 2.13.0
requests file
ruamel_yaml 0.11.14
scikit image
scikit learn
scipy 0.19.0
seaborn 0.7.1
setuptools 27.2.0
simplegeneric 0.8.1
sip 4.18
six 1.10.0
snowballstemmer 1.2.1
sphinx 1.5.4
sqlalchemy 1.1.9
sqlparse 0.1.19
statsmodels 0.8.0
sympy 1.0
testpath 0.3
tk 8.5.18
toolz 0.8.2
tornado 4.4.2
traitlets 4.3.2
unicodecsv 0.14.1
vs2015_runtime 14.0.25123
wcwidth 0.1.7
werkzeug 0.12.1
wheel 0.29.0
widgetsnbextension 2.0.0
win_unicode_console 0.5
xlrd 1.0.0
xlsxwriter 0.9.6
xlwt 1.2.0
zlib 1.2.8

So, there’s plenty of room to learn with Python Data Science and SQL Server 2017.

Be Bold! Learn PowerShell Core!