Installing the Microsoft ODBC Driver for SQL Server on Debian Linux with Saltstack

Introduction

Microsoft offers several open source utilities for quite some time now. One is the Microsoft ODBC Driver for SQL Server on Linux which can be leveraged with unixodbc. In my case, I need this driver to query Microsoft SQL Server for my Zabbix ODBC monitoring, to execute native SQL queries for monitoring purposes. As I have to take control over 30+ Zabbix Proxies, I’m currently transforming all of them into Salt minions. My goal here is to achieve 100% automation, as soon as I install the Salt minion on a fresh Debian Linux.

The problem

In Saltstack the state declaration to install a package through the default package manager is straight forward:

msodbcsql17:
  pkg.installed:
    - require:
    - sls: microsoft.repo

Sadly the SQL driver requires an EULA to be accepted, which you can see when installing manually via: apt install msodbcsql17

To squeeze this into configuration management automation via Saltstack, Chef, Puppet or Ansible, there also is a request at Microsoft, which hasn’t been solved yet:
https://feedback.azure.com/forums/908035-sql-server/suggestions/32901982-ability-to-automate-installation-for-linux-driver

To give this a short summary, the “problem” is, that there is a standard for “answer-files” for deb packages called debconf. This answers can be made visible AFTER the setup, so you can automate future setups:

apt install msodbcsql17
debconf-get-selections | grep -i mso
sodbcsql17 msodbcsql/accept_eula boolean true

To convert this debconf selection into reusable code for future deployments, you could write something like this:

Accept Microsoft Eula:
  debconf.set:
    - name: msodbcsql17
    - data:
        'msodbcsql/accept_eula': {'type': 'boolean', 'value': True }

Sadly the msodbcsql17 deb package ignores this settings, which is the reason Markus Kuhn and Jan Katins posted updated GitHub code Gists which would solve this issue.

The solution

As this wasn’t implemented by Microsoft just yet, I dug into the original files and searched for the install behavior. In the Microsoft repository in the msodbcsql17 deb file archive is a file called “preinst”. It has a function which starts with the following IF function:

#!/bin/bash -e

. /usr/share/debconf/confmodule

check_eula_acceptance()
{
    if [ "$ACCEPT_EULA" != "y" ] && [ "$ACCEPT_EULA" != "Y" ]; then
        db_set msodbcsql/accept_eula false
        db_fset msodbcsql/accept_eula seen false
        db_input high msodbcsql/accept_eula	|| true
        db_go

        db_get msodbcsql/accept_eula

        if [ "$RET" != "true" ]; then
            echo "ERROR: The EULA was not accepted. Installation aborted." >&2
            exit 1
        fi
    fi

    db_set msodbcsql/accept_eula true
}

As you can see, nobody (seems to) care about debconf, but checks the value of a BASH variable. This led me to the conclusion, that an environment variable managed by saltstack could solve the issue:

Accept Microsoft Eula:
  environ.setenv:
    - name: ACCEPT_EULA
    - value: Y

Which results in the following unattended package install state for saltstack:

msodbcsql17:
  pkg.installed:
    - require:
      - sls: microsoft.repo
      - environ: Accept Microsoft Eula

Result of state.apply:

root@salt:~# salt 'FQDN' state.apply
FQDN:
----------
          ID: microsoft_repo
    Function: pkgrepo.managed
        Name: deb [arch=amd64] https://packages.microsoft.com/repos/microsoft-debian-stretch-prod stretch main
      Result: True
     Comment: Package repo 'deb [arch=amd64] https://packages.microsoft.com/repos/microsoft-debian-stretch-prod stretch main' already configured
     Started: 11:44:37.049133
    Duration: 24.016 ms
     Changes:
----------
          ID: Accept Microsoft Eula
    Function: environ.setenv
        Name: ACCEPT_EULA
      Result: True
     Comment: Environ values were set
     Started: 11:44:39.616346
    Duration: 0.551 ms
     Changes:
              ----------
              ACCEPT_EULA:
                  Y
----------
          ID: microsoft_software
    Function: pkg.installed
      Result: True
     Comment: The following packages were installed/updated: msodbcsql17
              The following packages were already installed: tree, ncdu, nmap, bc, powershell, tcpdump, unixodbc, curl, apt-transport-https
     Started: 11:44:39.617206
    Duration: 4394.078 ms
     Changes:
              ----------
              msodbcsql17:
                  ----------
                  new:
                      17.2.0.1-1
                  old:

Summary for FQDN
------------
Succeeded: 5 (changed=2)
Failed:    0
------------
Total states run:     5
Total run time:   4.639 s

Appendix: The Microsoft repository

As I use a saltstack state to manage the Microsoft repository in my example code, it might be interesting for others to see my state declaration for it:

{% if salt['grains.get']('os_family') == 'Debian' -%}
microsoft_repo:
  pkgrepo.managed:
    - humanname: Microsoft repository
    - name: deb [arch=amd64] https://packages.microsoft.com/repos/microsoft-{{ salt['grains.get']('os')|lower }}-{{ salt['grains.get']('oscodename')|lower }}-prod {{ salt['grains.get']('oscodename')|lower }} main
    - file: /etc/apt/sources.list.d/microsoft.list
    - key_url: https://packages.microsoft.com/keys/microsoft.asc
    - architectures: amd64
    - clean_file: True
{% endif -%}