Today, I’m looking at a small part of a large project. The overall idea is to automate the busywork of my job as much as possible. This system needs a way to store data. As MPN members, we have Azure credits, and I decided to take MSSQL out for a spin. The first step is connecting it to StackStorm, the backbone of the project. That’s what I’m going over today.
These are the steps for integrate an MSSQL server in Azure with a local StackStorm instance. At first glance, this seems like it should be trivial. Install StackStorm, then install the MSSQL pack. The catch is that Azure requires OpenSSL authentication, so the pre-built pack won’t work. If you’re here for just the commands, they’re below. A more detailed explanation follows.
apt-get install -y git autoconf libtool gettext pkg-config libssl-dev curl git clone https://github.com/FreeTDS/freetds.git ~/freetds/autogen.sh ~/freetds/configure --with-openssl make make install curl -sSL https://stackstorm.com/packages/install.sh | bash -s -- --user=st2admin --password='Ch@ngeMe' st2 pack install mssql cp /opt/stackstorm/packs/mssql/mssql.yaml.example /opt/stackstorm/configs/mssql.yaml /opt/stackstorm/virtualenvs/mssql/bin/pip uninstall -y pymssql /opt/stackstorm/virtualenvs/mssql/bin/pip install git+https://github.com/pymssql/pymssql.git st2ctl reload --register-configs
Understanding why something that seems this simple ends up being complicated requires an understanding of the basic way the MSSQL pack communicates with servers. A quick glance (link) at the source code shows that it uses the pymssql library via the _mssql library. That, in turn, uses libraries provided by a FreeTDS installation (either developer or binary, named freetds-dev and freetds-bin in Ubuntu). StackStorm installs pymssql in a virtualenv directory (/opt/stackstorm/virtualenvs/mssql) when you install the MSSQL pack, and documentation for the pack instructs you to install freetds-dev. When you run a query from StackStorm, a few things happen.
- StackStorm checks for the existence of /opt/stackstorm/configs/mssql.yaml, which determines the default settings for a given query. If you don’t have one, StackStorm will give you an error. You can copy it from /opt/stackstorm/packs/mssql/mssql.yaml.example or write your own.
- StackStorm takes the arguments you specify plus the defaults from mssql.yaml and uses them to call _mssql.connect() in /opt/stackstorm/packs/mssql/lib/mssql_action.py. Note: This is an oversimplification, but it’s what ended up being important. It actually calls mssql_runner.py which inherits a method from mssql_action.py which returns the value of _mssql.connect().
- connect() checks for the FreeTDS libraries it needs and loads yet more defaults from /etc/freetds/freetds.conf
- The FreeTDS library is run and attempts a connection with the database specified. This is where things break down.
When FreeTDS tries to connect to an Azure database, it fails, because the pre-built FreeTDS package doesn’t have support for OpenSSL. As I understand it, they are licensed differently, so FreeTDS can’t or won’t package OpenSSL in its prebuilt binaries. You’ll need to build it yourself and use the –with-openssl flag to include the appropriate libraries. First clone the repository, then run the autogen.sh script. The catch is that autogen.sh may fail on your system if you don’t have FreeTDS’s dependencies installed. You’ll know that’s the case if autogen.sh returns something along these lines:
root@stackstorm:~# git clone https://github.com/FreeTDS/freetds.git root@stackstorm:~# cd freetds root@stackstorm:~/freetds# ./autogen.sh error: possibly undefined macro: AC_DISABLE_SHARED If this token and others are legitimate, please use m4_pattern_allow. See the Autoconf documentation.
Which ones you’re missing will depend on what OS you’re running and what’s already installed on your system. The solution is to install a few libraries manually. I’ve listed the errors I had along with the packages that fixed them.
AC_DISABLE_SHARED: apt-get install autoconf libtool
AM_ICONV: apt-get install gettext
AC_DEFINE: apt-get install pkg-config
Or just install all of them to be safe. You also may need the OpenSSL library. You’ll know you do if you see the line “checking for OPENSSL… no” in autogen.sh’s output. Alternatively, if tsql -Cshows “No” after you’ve finished installation, OpenSSL isn’t configured. The package names are different for different OS’s, but Ubuntu calls it libssl-dev. Once autogen.sh finishes, run:
root@stackstorm:~/freetds# ./configure --with-openssl root@stackstorm:~/freetds# make root@stackstorm:~/freetds# make install
And you’re done! You’ve built your own version of FreeTDS. Runtsql -Cto confirm that OpenSSL is enabled, then confirm that you can connect to your database using tsql -H HOSTNAME -p PORT -U USERNAME. It’ll prompt you for your password, then give you a live connection to your database. Run a few queries to test it out.
root@stackstorm:~# tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v1.1.dev.20170926
freetds.conf directory: /usr/local/etc
MS db-lib source compatibility: no
Sybase binary compatibility: no
Thread safety: yes
iconv library: yes
TDS version: auto
iODBC: no
unixodbc: no
SSPI "trusted" logins: no
Kerberos: no
OpenSSL: yes
GnuTLS: no
MARS: yes
root@stackstorm:~# tsql -H mydb.database.windows.net -p 1433 -U dbuser
Password: *******
locale is "C"
locale charset is "ANSI_X3.4-1968"
using default charset "ISO-8859-1"
1>
Now you just have to configure StackStorm to use your newly built FreeTDS. Install StackStorm with the command specified here. Once it’s finished, install the MSSQL pack. That’ll download pymssql and create the virtualenv to hold it. Now, create myssql.yaml with your information or copy the example.
root@stackstorm:~# curl -sSL https://stackstorm.com/packages/install.sh | bash -s -- --user=st2admin --password='Ch@ngeMe'\ root@stackstorm:~# st2 pack install mssql root@stackstorm:~# cp /opt/stackstorm/packs/mssql/mssql.yaml.example /opt/stackstorm/configs/mssql.yaml
Unfortunately, we’re still not done. Running an action from the MSSQL pack will most likely give you _mssql.MSSQLDatabaseException: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (datawhse.database.windows.net:1433)\n'). As of writing this, MSSQL grabs pymssql version 2.1.3. That’s no good because (to quote Wayne from Stack Overflow, pymssql wheel does not have SSL bindings. I ended up using the instructions here, but Wayne’s may work as well. You can confirm that this works with these commands:
root@stackstorm:~# apt-get install -y python-pip
root@stackstorm:~# pip install git+https://github.com/pymssql/pymssql.git
root@stackstorm:~# python
Python 2.7.12 (default, Nov 19 2016, 06:48:10)
[GCC 5.4.0 20160609] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import _mssql
>>> conn = _mssql.connect(server='mydb.database.windows.net', user='dbuser@mydb', password='dbpass', port='1433', tds_version='8.0', database="exampledb"))
>>> conn.execute_row("YOUR QUERY HERE")
>>> conn.close()
Errors during this process indicate that something’s wrong with your pymssql build. However, we don’t want to install pymssql on our general system, we want it in the virtualenv StackStorm will look to. We can do that by using the virtualenv’s pip. The second command may take some time, but it should finish looking like this:
root@stackstorm:/opt/stackstorm/virtualenvs/mssql/bin# ./pip uninstall -y pymssql
Uninstalling pymssql-2.1.3:
/opt/stackstorm/virtualenvs/mssql/lib/python
/opt/stackstorm/virtualenvs/mssql/lib/python2.7/site-packages/_mssql.so
/opt/stackstorm/virtualenvs/mssql/lib/python2.7/site-packages/pymssql-2.1.3.dist-info/DESCRIPTION.rst
/opt/stackstorm/virtualenvs/mssql/lib/python2.7/site-packages/pymssql-2.1.3.dist-info/INSTALLER
/opt/stackstorm/virtualenvs/mssql/lib/python2.7/site-packages/pymssql-2.1.3.dist-info/METADATA
/opt/stackstorm/virtualenvs/mssql/lib/python2.7/site-packages/pymssql-2.1.3.dist-info/RECORD
/opt/stackstorm/virtualenvs/mssql/lib/python2.7/site-packages/pymssql-2.1.3.dist-info/WHEEL
/opt/stackstorm/virtualenvs/mssql/lib/python2.7/site-packages/pymssql-2.1.3.dist-info/metadata.json
/opt/stackstorm/virtualenvs/mssql/lib/python2.7/site-packages/pymssql-2.1.3.dist-info/top_level.txt
/opt/stackstorm/virtualenvs/mssql/lib/python2.7/site-packages/pymssql.so
Proceed (y/n)? y
Successfully uninstalled pymssql-2.1.3
root@stackstorm:/opt/stackstorm/virtualenvs/mssql/bin# ./pip install git+https://github.com/pymssql/pymssql.git
Collecting git+https://github.com/pymssql/pymssql.git
Cloning https://github.com/pymssql/pymssql.git to /tmp/pip-y4MXcs-build
Installing collected packages: pymssql
Running setup.py install for pymssql ... done
Successfully installed pymssql-2.2.0.dev0
We’re in the home stretch. Run st2ctl reload --register-configs, and StackStorm will load the mssql.yaml file you created. You should then be able to run queries on your Azure database instance from StackStorm.
Closing out- this went way deeper than I thought it would. I learned a ton. I had only worked with compiling from source once before, and had only heard of virtualenvs- never actually used them. I still know very little on both subjects, but I at least now know the bare minimum to solve this. I actually ended up editing and recompiling mssql_actions.py a few times to try to get information out, which was also new.
I originally decided to have the database on Azure because the “server” I’m running everything from is already overtaxed, I wanted to know how Azure worked, like, at all, and (obviously most importantly, but last on my list) it’s way more reliable than my Frankenstein of a box. I very seriously considered abandoning it altogether in favor of Microsoft’s MSSQL bindings, but I really wanted to use the built-in MSSQL pack instead of rebuilding it myself or running it all through local commands.
The server I have set up uses ZFS, and I have a Rundeck container. That combination was extremely helpful for testing. I took a snapshot of the StackStorm VM before installing anything, then slowly built up a Rundeck playbook containing the above plus a few other settings. Being able to roll back in literally seconds and restart the busywork of configuration without having to manually type everything in made this a much quicker and less painful process.
Useful references:
- FreeTDS build options: https://www.freetds.org/userguide/config.html
- FreeTDS error code examples: https://www.freetds.org/userguide/ConfirmInstall.html
- MSSQL pack Readme: https://github.com/StackStorm-Exchange/stackstorm-mssql/blob/master/README.md
- Pymssql/_mssql examples: https://www.pymssql.org/en/stable/_mssql_examples.html
