How do I connect to SQL Server via sqlalchemy using Windows Authentication?

sqlalchemy, a db connection module for Python, uses SQL Authentication (database-defined user accounts) by default. If you want to use your Windows (domain or local) credentials to authenticate to the SQL Server, the connection string must be changed.

By default, as defined by sqlalchemy, the connection string to connect to the SQL Server is as follows:

sqlalchemy.create_engine('mssql://*username*:*password*@*server_name*/*database_name*')

This, if used using your Windows credentials, would throw an error similar to this:

sqlalchemy.exc.DBAPIError: (Error) ('28000', "[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for us
er '***S\\username'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for us
er '***S\\username'. (18456)") None None

In this error message, the code 18456 identifies the error message thrown by the SQL Server itself. This error signifies that the credentials are incorrect.

Solution 1:

In order to use Windows Authentication with sqlalchemy and mssql, the following connection string is required:

ODBC Driver:

engine = sqlalchemy.create_engine('mssql://*server_name*/*database_name*?trusted_connection=yes')

SQL Express Instance:

engine = sqlalchemy.create_engine('mssql://*server_name*\\SQLEXPRESS/*database_name*?trusted_connection=yes')

Solution 2:

If you're using a trusted connection/AD and not using username/password, or otherwise see the following:

SAWarning: No driver name specified; this is expected by PyODBC when using >DSN-less connections "No driver name specified; "

Then this method should work:

from sqlalchemy import create_engine

server = <your_server_name>

database = <your_database_name>

engine = create_engine('mssql+pyodbc://' + server + '/' + database + '?trusted_connection=yes&driver=ODBC+Driver+13+for+SQL+Server')

Solution 3:

A more recent response if you want to connect to the MSSQL DB from a different user than the one you're logged with on Windows. It works as well if you are connecting from a Linux machine with FreeTDS installed.

The following worked for me from both Windows 10 and Ubuntu 18.04 using Python 3.6 & 3.7:

import getpass
from sqlalchemy import create_engine
password = getpass.getpass()
eng_str = fr'mssql+pymssql://{domain}\{username}:{password}@{hostip}/{db}'
engine = create_engine(eng_str)

What changed was to add the Windows domain before \username. You'll need to install the pymssql package.

Solution 4:

pyodbc

I think that you need to put:

"+pyodbc" after mssql

try this:

from sqlalchemy import create_engine

engine = create_engine("mssql+pyodbc://user:password@host:port/databasename?driver=ODBC+Driver+17+for+SQL+Server")

cnxn = engine.connect()

It works for me

Luck!