Connecting to MS SQL using Kerberos authentication¶
Anaconda Enterprise enables you to use Kerberos authentication for trusted connections to an MS SQL server database. After you’ve authenticated, you’ll be able to access the data stored in it.
Before you can do so, you’ll need to rebuild the freetds conda package. FreeTDS is a set of libraries that allows programs to work with MS SQL Server. It doesn’t currently support Kerberos authentication, however, so you’ll need to enable that flag and rebuild the package.
Then you can configure Anaconda Enterprise so all projects will be able to use Kerberos to connect to an MS SQL database.
Note
You’ll need access to an MS SQL instance, to be able to verify the connection. You’ll also need to have configured a krb5.conf
file to use Kerberos authentication.
Rebuilding FreeTDS¶
Run the following command to pull down the existing recipe for the freetds conda package located here.:
Open the recipe used to build the package:
Run the following commands to open the recipe, and update it to enable krb5:
Open the
meta.yml
file included in the recipe, and turn on line numbers:Make the following changes to the
meta.yaml
file:
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 therequirements:host
section.
Your updated meta.yaml
file should look like this:
Move up a directory, to enter
freetds-feedstock
:Run the following command to build the package:
After the build processes completes successfully, you’ll have a freetds
package that supports Kerberos authentication. You can now upload this package to a channel, to use it in your projects and optionally share it with other users.
Configuring Anaconda Enterprise¶
Note
The following steps need to be performed on the master node of the Anaconda Enterprise cluster, so if you don’t have access to it, you’ll need to ask an Administrator to do the following:
On the master node, create a
freetds.conf
file for your organization that looks similar to the following (replacing placeholder values with actual values):On the master node, create an
odbcinst.ini
file that looks similar to the following:
Note
This example uses the Python 3.6 kernel. The location of the driver might be different if you are using a different kernel version.
On the master node, create an
odbc.ini
file that looks similar to the following:After you’ve created these files, use the
anaconda-enterprise-cli
to ensure they are placed in every project. If the files are all in the current working directory, you can do this by running the following command on the master node:Run this command to update the secret in Kubernetes:
Connecting to MS SQL from a project¶
Create or open a project that uses the Python 3.6 template.
To ensure that the correct libraries are installed, open the terminal within the project and run the following command:
NOTE: Any packages you install from the command line are available during the current session only. If you want them to persist, add them to the project’s anaconda-project.yml
file. For more information, see Developing a project.
When the install is complete, run the following command to verify whether you can successfuly authenticate using Kerberos, replacing
<USERNAME>
with your credentials:If you were able to authenticate, run these commands to ensure you can connect to the MS SQL server, replacing
<SERVERNAME>
with the value you specified in thefreetds.conf
andodbc.ini
files.:If you were able to successfuly authenticate using Kerberos, you can then use code such as this to connect to the MS SQL database from within a notebook session:
If everything is working as expected, you should see output from the print
statement.