ODBC connection through dsn error

Question

I have an issue trying to call my uid and password through pyodbc.connect

Here is my odbc.ini:

[my_dsn]
Driver= SQL Server
Server=my_server
User=uid_reader
Password=password_reader
MultiSubnetFailover=Yes
Database=master

When I hard code, it works perfectly and I can connect

test_uid = 'uid_reader'
test_password = 'password_reader'

conn = pyodbc.connect(r'DSN=my_dsn;UID={a};PWD={b}'.format(a=test_uid,b=test_password))

When I call my dsn variables from my odbc.ini, it doesn't work

conn = pyodbc.connect(r'DSN=my_dsn;UID=User;PWD=Password')

Error: ('28000', "[28000] [unixODBC][Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'User'. (18456) (SQLDriverConnect)")

I would like to hide the password in my odbc.ini so it doesn't appear when I call pyodbc.connect

The error message shows that User is being used as the value for UID. What happens if you do not specify the user and password in the connect string? — mhawke
I runned conn = pyodbc.connect(r'DSN=my_dsn'') and it returns Error: ('28000', "[28000] [unixODBC][Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user ''. (18456) (SQLDriverConnect)") — antoinem
How are you loading your config? The line conn = pyodbc.connect(r'DSN=my_dsn;UID=User;PWD=Password') seems like it's supposed to use the literal string value of my_dsn, User and Password. My understanding is you should be doing string formatting like you did in your hardcode version, but instead of pointing to the variable, point it to the loaded config. — r.ook

r.ook r.ook · Accepted Answer · 2018-01-19T23:52:33

The line conn = pyodbc.connect(r'DSN=my_dsn;UID=User;PWD=Password') seems to be using the literal string values of my_dsn, User and Password. You can try using keywords like the documentation here, or do a similar string formatting as you did for your hard coded solution but replace it with the loaded config data.

From the documentation it seems you should be able to load the DSN info directly from the ini, but I haven't had much success on my own either. The caveat is the document only said you can possibly use the login credential, so maybe it depends on the driver?

Either way, below is a version using configparser that should work. It might be considered a hack for some.

import pyodbc
from configparser import ConfigParser

config = ConfigParser()
config.read(configfile)   # your odbc.ini file path and name
dsn = config['my_dsn']

#If you want to use Keywords:

conn = connect(driver=dsn['driver'], server=dsn['server'], database=dsn['database'], uid=dsn['user'], pwd=dsn['password'])

#If you want to use string formatting with list comprehension instead:
conn = pyodbc.connect(''.join(['{0}={1}; '.format(k, l) for k, l in d.items()])

ODBC connection through dsn error

2 Answers