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

Installing the Microsoft ODBC Driver for SQL Server on Debian Linux (msodbcsql17) with Saltstack requires to pass ACCEPT_EULA=Y to the package manager. This blog post shows a possible solution.

Do you accept the license terms?

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

Do you accept the license terms?
Do you accept the license terms?

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 -%}

Author: Marco

Marco is an IT-System administrator and IT-Consultant with 10+ years experience. He is specialized in the delivery of virtual Apps and Desktops with Citrix solutions. In 2017 he has been awarded Citrix Technology Advocate by Citrix for his community work (#CTA). His second core area is availability & performance monitoring with Zabbix, a leading open-source solution. His employer is the German IT-Company ANAXCO, which is developing a Transport Management Software (TMS) based on Microsoft Dynamics AX. More about Marco

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.