SQLAlchemy trusted connection with different windows username/password than local user

I am trying to connect to a sql server with a different username/password combination than on my local account. I know it is valid credentials, since i am able to login through SSMS.

I've tried the following:

connection_string = "DRIVER={SQL Server};SERVER=server;DATABASE=db;UID=username;PWD=password"
connection_url = URL.create("mssql", query={"odbc_connect": connection_string})

Where i get the login failed response: [SQL Server]Login failed for user 'username'. (18456)

I am trying to connect to a microsoft sql server (on the same network)

CodePudding user response：

You get the error, because the Windows account don't have permissions to logon to SQL Server. Note: Windows admin <> SQL admin, it has it's own permission system. Grant logon and other required permissions for the Windows logon.

According to your error code I guess. "Using Windows login name with SQL Server authentication"

CodePudding user response：

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

Try