MSSQL Kerberos how to

MSSQL-Kerberos.md

Connecting to MS SQL using Kerberos Authentication

FreeTDS is as set of libraries that allows programs to talk to MS SQL Server. It doesn't currently support Kerberos authentication, however, so you'll need to rebuild the freetds package to enable the flag.

Rebuilding FreeTDS

1. Pull down the existing recipe for the current conda packages for freetds located here: https://github.com/AnacondaRecipes/freetds-feedstock

git clone https://github.com/AnacondaRecipes/freetds-feedstock.git
cd freetds-feedstock/recipe

# Add the option to enable krb5
cat <<EOF > build.sh
#!/bin/bash

set -e

./configure \\
  --prefix=\$PREFIX \\
  --with-unixodbc=\$PREFIX \\
  --enable-krb5 \\
  --with-openssl=\$PREFIX \\
  --host=\$HOST \\
  --build=\$BUILD
make
# To run this check we need to have access to a mssql instance.
# make check
make install
EOF

2. Open the meta.yml file and turn on line numbers:

vi meta.yml
# Turn on line numbers
:set number

3. Update the meta.yml file as follows:

• Change the version by editing line 2 so it looks like this: {% set version = "1.1.4" %}

• Comment out line 3.

• Comment out lines 12-14.

• Add krb5 to the requirements:host section.

After making these changes, the updated meta.yaml file should look like the following:

{% set name = 'freetds' %}
{% set version = "1.1.4" %}

package:
  name: {{ name }}
  version: {{ version }}

source:
  fn: freetds-{{ version }}.tar.gz
  url: ftp://ftp.freetds.org/pub/freetds/stable/freetds-{{ version }}.tar.gz

build:
  number: 0
  run_exports:
    - {{ pin_subpackage('freetds') }}

requirements:
  build:
    - {{ compiler('c') }}
  host:
    - openssl
    - krb5
    - unixodbc  # [unix]
    - readline  # [not win]
    - make   # [not win]
    - cmake  # [win]
    - jom  # [win]
    - libiconv  # [osx]
  run:
    # Should be taken care of by run_exports
    # - unixodbc  # [unix]
    # - readline  # [not win]

test:
  commands:
    - tsql -C
    - conda inspect linkages freetds  # [linux or osx]
    - conda inspect objects freetds  # [osx]

about:
  home: http://www.freetds.org/
  license: GPL-2
  license_file: COPYING
  summary: FreeTDS is a free implementation of Sybases DB-Library, CT-Library, and ODBC libraries
  license_family: GPL2
  doc_url: http://www.freetds.org/docs.html
  dev_url: https://github.com/FreeTDS/freetds

extra:
  recipe-maintainers:
    - mariusvniekerk

4. Move up one directory so you are inside freetds-feedstock:

cd ..

5. Run the following command to build the package:

conda build recipe

If the build is successful, you'll have a valid package for freetds that can be uploaded to a channel in AE5. (You can also find the package here: https://anaconda.org/oldarmyc/freetds.)

On the AE5 master, you'll need to do the following:

1. Create a freetds.conf file for your organization that looks similar to the following example:

[SERVERNAME]
host = MSSQL-SERVER-DOMAIN i.e. win.dev.anaconda.com
port = 1433 # Port for MSSQL 1433 is the default
enable gssapi delegation = on
realm = KERBEROS-REALM i.e. DEV.ANACONDA.COM

2. Create an odbcinst.ini file that looks similar to the following example.

NOTE: This Anaconda project example uses the Python 3.6 kernel. The location of the driver might be different, depending on the kernel you are using in your project.

[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL
Driver=/opt/continuum/anaconda/envs/anaconda50_py36/lib/libtdsodbc.so
Setup=/opt/continuum/anaconda/envs/anaconda50_py36/lib/libtdsodbc.so
UsageCount=1

3. Create an odbc.ini file that looks similar to the following example:

[SERVERNAME]
Description         = Test to SQLServer
Driver              = FreeTDS
Servername          = SERVERNAME

You'll also need to have configured a krb5.conf file to use Kerberos authentication.

After you've created all of these files, you need to utilize the anaconda-enterprise-cli to ensure they are placed in every project. Assuming that all the files are in the current working directory, you can run the following command:

anaconda-enterprise-cli spark-config --config /opt/continuum/.freetds.conf freetds.conf --config /etc/odbc.ini odbc.ini --config /etc/odbcinst.ini odbcinst.ini --config /etc/krb5.conf krb5.conf

# Ensure you update the secret in kubernetes
sudo kubectl replace -f anaconda-config-files-secret.yaml

In AE5, create or open a project using the Python 3.6 template.

1. To ensure that the correct libraries are installed, open the terminal within the project and run the following command:

conda install freetds

2. When the install is complete, do the following to ensure everything is configured correctly:

# kinit and login through kerberos
kinit USERNAME

tsql -S SERVERNAME   # SERVERNAME is the name that you specified in [] in the freetds.conf file
isql SERVERNAME      # SERVERNAME is the name that was specified in the odbc.ini file and should be the same as in the previous command

If you were able to successfully authenticate through Kerberos, you can now use Python code to connect to MS SQL.

3. From within the project, open up an ipython notebook in the AE5 session and run the following code to test the connection:

import pyodbc
connection = pyodbc.connect('DSN=SERVERNAME')  # SERVERNAME is the same as the above commands for tsql and isql
sql = "SELECT Distinct TABLE_NAME FROM information_schema.TABLES"
rows = cursor.execute("select @@VERSION").fetchall()
print(rows)
cursor.close()
connection.close()

If everything is working as expected, you should see output from the print statement.