I am using pyodbc to connect to a MSSQL database with DB credentials that work great.
However for a PoC solution I need to connect to it as a user in a different domain.
I have created a credential dictionary that's being imported into the main script. Below is the snip of the dict with the problematic user:
creds = {
...
'amp': {'u': 'domain\\user' # double backslash to escape the \
...
}
And below are my parameters for mssql engine as an f string using values from the creds dictionary.
params = quote_plus(f"Driver={driver};Server={creds['amp']['s']};Database={creds['amp']['d']};uid={creds['amp']['u']};pwd={creds['amp']['p']}")
This returns the following error:
pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'domain\\user'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'domain\\user'. (18456)")
I have tried:
'domain\user' r'domain\user', hardcoding 'domain\user' in the params string
nothing produces the 'domain\user' to allow conection to the DB.
When I go through SSMS I can connect without any problems using same account.
PS. I have gone through numerous question on stack before posting, spoke to a python friend, no luck.
PS2. Stack seems to have assigned this question to sql server as opposed to python? Sorry!
Ok, I have made it work by using Trusted_Connection=yes in my params, but the question still remains. How to type domain\user strings to be able to use those?
params2 = quote_plus(f"Driver={driver};Server={creds['amp']['s']};Database={creds['amp']['d']};Trusted_Connection=yes")
ampengine = sql.create_engine(f'mssql+pyodbc:///?odbc_connect={params2}')