pyodbc connection string sql server authentication

I had this problem myself and I fixed it by going to the Microsoft SQL Server management studio and manually connecting the database to the user account you login into the database with.

To do this:

• Open Microsoft SQL Server management studio and connect to your server.
• Go to login, right click on the correct user, and go to properties.
• On the top left there's a panel (titled "select a page"). Go to User mapping.
• On the table displayed, where it says user (second column), type in the name of the user next to the database you are using.

While you are at it, look at the panel at the bottom. Where it says Database role membership for:{database name}, make sure you have db_datareader and db_datawriter checked.

Here is a way to have a connection string using sqlalchemy (see https://www.sqlalchemy.org/):

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from db import Db

#Get localhost DB
engine = create_engine('mssql+pyodbc://myDbUser:myDbPassword@localhost:1433/MyDatabase?driver=SQL+Server+Native+Client+11.0?trusted_connection=no')

DBSession = sessionmaker(bind=engine)

session = DBSession()
db = Db(session)
print "the widget number is: "+db.widgetNumber

I faced the same issue and the problem was the database name.

I was putting the database name between brackets, like [DB_NAME], however as soon as I removed the brackets to DB_NAME it worked fine.

Please give it a try.

Configure the MSSQL server in the obdc datasources and give the name with the pyodbc connection as below.My userdsn name is MyConnection

import pyodbc

cnxn = pyodbc.connect('DSN=MyConnection')
cursor = cnxn.cursor()
cursor.execute("SELECT TOP (1000) [DepartmentID]      ,[Name]      ,[GroupName]      ,[ModifiedDate]  FROM ["
               "AdventureWorks2019].[HumanResources].[Department]")
rows = cursor.fetchall()
for row in rows:
    print(row.DepartmentID, row.Name)

It is working for me. Let me know if there are any other ways to do this.

The important parameter is where the “username and password” were set, I am replacing it with “Trusted_Connection=yes” so the user account logged into Windows is used instead.

import pyodbc

conn_str = ("Driver={SQL Server};"
                           "Server=<server-name>;"
                            "Database=<db-name>;"
                            "Trusted_Connection=yes")

conn = pyodbc.connect(conn_str)

cursor = conn.cursor()

I also faced a similar issue and the problem was the database name.

I was adding hierarchy in the database name , like "DB_NAME.dbo", however as soon as I removed it to to DB_NAME it worked fine.