Search code examples
python-3.xsql-serverodbcpyodbc

Setup pyodbc connection without password in the connection string

I am trying to set up pyodbc connection using only the DSN name in the connection string (primary goal is to NOT have the password in the connection string) like so:

import pyodbc
cxxn = pyodbc.connect("DSN=HARSHIT_CON")

Here is my .odbc.ini file:

[HARSHIT_CON]
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.1
Description=python
Database=my_db
Server=someserver,2500
user=harshitjindal
password=penguincontrolgroup
QuotedId=Yes
AnsiNPW=Yes

Expected result: for the connection to be setup because the username and password are defined in the .odbc.ini file, but I get the following error:

Traceback (most recent call last):
  File "hjind1/testdb.py", line 2, in <module>
    cxxn = pyodbc.connect("DSN=HARSHIT_CON")
pyodbc.InterfaceError: ('28000', "[28000] [unixODBC][Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user ''. (18456) (SQLDriverConnect)")

What I have tried so far:

  1. To check if the correct .odbc.ini file is being read, I tried removing the .odbc.ini file, and the traceback shows that the DSN was not found. This leads me to believe the ini file is being read.
  2. I noticed that the error says Login failed for user ''.. I thought that the user_id is not being detected, so I added the following lines to the ini file (hoping UID instead of user and PWD instead of password might help):
UID=harshitjindal
PWD=penguincontrolgroup
  1. To try and solve the issue of user not being detected, I also tried to manually pass it in the connection string like so: cxxn = pyodbc.connect("DSN=HARSHIT_CON;UID=harshitjindal"). This time the error message shows that the user is harshitjindal but connection failed. If I also pass the password in the connection string like cxxn = pyodbc.connect("DSN=HARSHIT_CON;UID=harshitjindal;PWD=penguincontrolgroup"), then it works, but the entire idea is to remove the password from the connection string when it's already defined in the ini file.

How can I setup my connection without explicitely passing in the password (read it from the DSN config)?

Solution

  • The trick is to realise that the .odbc.ini file needs to be treated like a config file. This was not intuitive because other parameters such as Driver, Server, Database were being read from the ini file implicitly upon calling pyodbc.connect(), but user and password (or UID and PWD) need to be read explicitly.

    This can be done as follows:

    import pyodbc
from ConfigParser import ConfigParser

config = ConfigParser()
config.read("/Users/harshitjindal/.odbc.ini")   # odbc file path
DSN = "HARSHIT_CON"
cxxn = pyodbc.connect("DSN={0};UID={1};PWD={2}".format(DSN, config.get(DSN, "user"), config.get(DSN, "password")))