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.

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!

Solution 5

Create Your SqlAlchemy Connection URL
     From Your pyodbc Connection String
     OR Your Known Connection Parameters

I found all the other answers to be educational, and I found the SqlAlchemy Docs on connection strings helpful too, but I kept failing to connect to MS SQL Server Express 19 where I was using no username or password and trusted_connection='yes' (just doing development at this point).

Then I found THIS method in the SqlAlchemy Docs on Connection URLs built from a pyodbc connection string (or just a connection string), which is also built from known connection parameters (i.e. this can simply be thought of as a connection string that is not necessarily used in pyodbc). Since I knew my pyodbc connection string was working, this seemed like it would work for me, and it did!

This method takes the guesswork out of creating the correct format for what you feed to the SqlAlchemy create_engine method. If you know your connection parameters, you put those into a simple string per the documentation exemplified by the code below, and the create method in the URL class of the sqlalchemy.engine module does the correct formatting for you.

The example code below runs as is and assumes a database named master and an existing table named table_one with the schema shown below. Also, I am using pandas to import my table data. Otherwise, we'd want to use a context manager to manage connecting to the database and then closing the connection like HERE in the SqlAlchemy docs.

import pandas as pd
import sqlalchemy
from sqlalchemy.engine import URL

# table_one dictionary:
table_one = {'name': 'table_one',
    'columns': ['ident int IDENTITY(1,1) PRIMARY KEY',
        'value_1 int NOT NULL',
        'value_2 int NOT NULL']}

# pyodbc stuff for MS SQL Server Express
driver='{SQL Server}'
server='localhost\SQLEXPRESS'
database='master'
trusted_connection='yes'

# pyodbc connection string
connection_string = f'DRIVER={driver};SERVER={server};'
connection_string += f'DATABASE={database};'
connection_string += f'TRUSTED_CONNECTION={trusted_connection}'

# create sqlalchemy engine connection URL
connection_url = URL.create(
    "mssql+pyodbc", query={"odbc_connect": connection_string})

""" more code not shown that uses pyodbc without sqlalchemy """

engine = sqlalchemy.create_engine(connection_url)

d = {'value_1': [1, 2], 'value_2': [3, 4]}
df = pd.DataFrame(data=d)

df.to_sql('table_one', engine, if_exists="append", index=False)

Update

Let's say you've installed SQL Server Express on your linux machine. You can use the following commands to make sure you're using the correct strings for the following:

1. For the driver: odbcinst -q -d
2. For the server: sqlcmd -S localhost -U <username> -P <password> -Q 'select @@SERVERNAME'

View more solutions

Comments

[Ravi Chandra]

The above connection strings might be useful if we are running the script from the machine where we logged in using windows credentials. what if I need to run the script from the some other host?

[Didier Jean Charles]

This is exactly what I am dealing with now - thus far, I am running into errors.

[steadyfish]

@RaviChandra, If you are running the scripts in interactive mode using an IDE, you could open the IDE as a different user.

[CutePoison]

What if it is used using pymssql ? Tried just adding +pyssmql after mssql but that does not work

[DoloMike]

Unfortunately this didn't work for me, but it led me to the right path. This is what I had to use: sqlcon = create_engine('mssql+pyodbc://@' + servername + '/' + dbname + '?trusted_connection=yes&driver=ODBC+Driver+13+for+SQL+Serve‌​r')

[user8128167]

This doesn't answer the question about using "Windows Authentication" though it is correct you need the pyodbc as part of the connection string.

[NoName]

@DoloMike Yeah, this answer is outdated.

[Chicken Sandwich No Pickles]

@DoloMike I wish I could upvote your post 10,000 more times.

[brian_ds]

To explain your URL.create function, it might be worthwhile to add some more details. For instance you can use the following:url = URL.create("mssql+pyodbc" , host='servername' , database="database Name" , query={"Trusted_Connection":'yes',"driver":"ODBC Driver 17 for SQL Server"})

[brian_ds]

note that the query works for everything after the ? in the url. The named parameters work with the required info before that.