Accessing Microsoft SQL Server using Windows authentication on a Mac in Python

Question

I am having trouble figuring out the configuration steps and code that I need to access a SQL Server database via Windows Authentication from a Mac.

On the Windows machine I can use the following code:

import getpass

import pypyodbc

cnxn_string = (r"DRIVER={ODBC Driver 13 for SQL Server};"
               r"SERVER=[Host]\[Instance];"
               r"DATABASE=[Database]"
               r"UID=[Domain]\[Username];"
               r"Trusted_Connection=yes;"% (getpass.getuser()))

cnxn = pypyodbc.connect(cnxn_string)

But on a Mac I believe I need to re-enter my username and password.

How can I create a similar python connection to the SQL Server, via Windows Authentication, on my mac? I am open to any python modules (it does not have to be pypyodbc).

Answer 1

Using FreeTDS + pymssql it's pretty trivial to connect.

Note: I would recommend using FreeTDS 1.0+ and pymssql 2.2.0+ because there are compatibility issues with FreeTDS 1.0 and pymssql 2.1.3.

Here's an example using pymssql:

import pymssql
pymssql.connect(server="myserver\\myinstance",
                user="mydomain\\myuser",
                password="mypassword",
                database="mydatabase")

Note: the instance is parsed from the server using the backslash character, but we need to escape this because this is also the escape character. The same can be said for parsing the Active Directory domain from the user.