How do I connect to SQL Server via sqlalchemy using Windows Authentication?
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')
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_engineserver = <your_server_name>database = <your_database_name>engine = create_engine('mssql+pyodbc://' + server + '/' + database + '?trusted_connection=yes&driver=ODBC+Driver+13+for+SQL+Server')
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 getpassfrom sqlalchemy import create_enginepassword = 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.