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

Question

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.

Answer 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')