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).

I believe so, because I am able to access the database from my mac using a database client (DBVisualizer), but in the client I need to re-enter my username and password and select the "Windows" authentication method. — mgoldwasser
– mgoldwasser, Commented Jun 1, 2017 at 13:51

mgoldwasser · Accepted Answer · 2017-06-06 17:48:34Z

3

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.

answered Jun 6, 2017 at 17:48

mgoldwasser

15.6k16 gold badges86 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mdegges Over a year ago

awesome. this doesn't work with pypyodbc as far as i can tell

CezarySzulc Over a year ago

@mgoldwasser You said that we should use pymssql 2.2.0+ version. I checked latest version: pymssql.org/en/stable/changelog.html and it's 2.1.4. This version is ok? I have problem during connection (pymssql 2.1.4, freeTDS 1.00.111). I get error during connection: Net-Lib error during Operation timed out (60)\n').

mgoldwasser Over a year ago

Hmm, I guess they removed the 2.2.0+ branch from the project GitHub. I'm not sure why...

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related