Trying to connect to MSSQL DB with pyodbc and SQLAlchemy using code (from another SO post) like
import json
import urllib
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
CONF = json.load(open(f"{PROJECT_HOME}/configs/configs.json"))
assert isinstance(CONF, ty.Dict)
params = urllib.parse.quote_plus(f"DRIVER={{ODBC Driver 17 for SQL Server}};"
f"SERVER={CONF['db']['db_ip']};"
f"DATABASE={CONF['db']['dest_db']};"
f"UID={CONF['db']['username']};"
f"PWD={CONF['db']['password']}")
engine = create_engine(f"mssql+pyodbc:///?odbc_connect={params}")
print(params)
sql_conn = engine.raw_connection()
cursor = sql_conn.cursor()
and getting error
sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'myuser'. (18456) (SQLDriverConnect)")
Looking at the URL that gets generated, it looks like
mssql+pyodbc:///?odbc_connect=DRIVER%3D%7BODBC+Driver+17+for+SQL+Server%7D%3BSERVER%3D172.12.3.45%3BDATABASE%3Dmy_db%3BUID%3Dmyuser%3BPWD%3Dpass%2Bword10
even though 1) I can connect to the same DB via mssql-tools (eg. via bcp) using the same credentials I'm using here and 2) this same code snippet does work for another script that accesses a different DB (though was a slightly older version of SQLAlchemy (v1.3.15), but same ODBC DSN).
Any ideas what could be going wrong in this case?
The configs I'm using in the code above look like
{
"db":
{
"db_dsn": "MyMSSQLServer",
"db_ip": "172.12.3.45",
"dest_db": "my_db",
"dest_table": "my_table",
"username": "myuser",
"password": "pass+word10"
}
}
Note that my password here is "pass+word10" w/ a plus sign. When I look at the docs for urllib.parse.quote_plus, it seems like the plus sign should not be an issue, but I may be misinterpreting that and I can't think of what else it could be (since, again this general snippet has already worked in the past).
My various package versions are...
(venv) $ pip freeze | grep odbc
pyodbc==4.0.30
(venv) $ pip freeze | grep SQL
Flask-SQLAlchemy==2.5.1
SQLAlchemy==1.4.7
SQLAlchemy-JSONField==1.0.0
SQLAlchemy-Utils==0.37.0
Have also tried
engine = create_engine("mssql+pyodbc:///?odbc_connect="
"DRIVER={ODBC Driver 17 for SQL Server};"
f"SERVER={CONF['db']['db_ip']};"
f"DATABASE={CONF['db']['dest_db']};"
f"UID={CONF['db']['username']};"
f"PWD={CONF['db']['password']}")
(w/ and w/out "+"s between the driver words) showing the engine string as
Engine(mssql+pyodbc:///?DATABASE=mydb&PWD=mypassword&SERVER=172.12.3.45&UID=myuser&odbc_connect=DRIVER%3D%7BODBC+Driver+17+for+SQL+Server%7D)
and gotten the error
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Neither DSN nor SERVER keyword supplied (0) (SQLDriverConnect)')
Even though I can see a DSN in my odbc.ini like...
$cat /etc/odbc.ini
[MyMSSQLServer]
Driver=ODBC Driver 17 for SQL Server
Description=My MS SQL Server
Trace=No
Server=172.12.3.45
Anyone with more experience with this know what could be going on here? Any debugging stuff I could try or info that could help this post?
* I know this post title is not super informative for what the problem seems to be, but I don't really have a great idea of what the problem is even related to atm, so would need to get more info on the problem before being able to edit to make a more relevant title.
