pyodbc connection string sql server authentication

Question

I know this question has been asked many times on the web resulting in many different solutions none which have worked for me.

For my scenario I'm attempting to do a simple connection to a MS Sql database connecting just with Service account username and password using windows authentication isn't an option for my task.

This is the connection string that I am providing:

databaseConnection = 'DRIVER={SQL Server}; SERVER=ServerName; Database=DatbaseName; UID=UserId; PWD=password;'

This is the error I receive when trying to run the script:

    dbConnection = pyodbc.connect(DATABASE.databaseConnection)
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver]
[SQL Server]Cannot open database "XXXX" requested by the login. The login failed.
(4060) (SQLDriverConnect); [42000] [Microsoft][ODBC SQL Server Driver]
[SQL Server]Cannot open database "XXXX" requested by the login. The login failed. (4060)')

Is there anything incorrect about this connection string? I have double checked the username and password by copy and pasting it into SQL Server Management Studio and logging into the database from there and it is successful.

Answer 1

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.

Answer 2

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