pyodbc DSN - connection to SQL Server without UID and PWD in connection string

Viewed 6147

I am looking to establish a connection to MS SQL through a DSN using pyodbc. what I am seeing is that I cannot connect to database unless I specify the username (PID) and password (PWD) in the connection string like this:

conn_str = 'DSN=MYMSSQL;UID=sa;PWD=password'

so if I use PID and PWD it works but if I put the PID and PWD in my DSN configuration (MYMSSQL) and remove these two attributes from conn_str then it doesn't work, below is the DSN configuration:

[MYMSSQL]
Description         = Test to SQLServer
Driver              = FreeTDS
Servername          = MYMSSQL
UID                 = sa
PWD                 = password
Database            = tempdb

Observation from the pyodbc API docs, apprently no way to do it without UID and PWD

def connect(p_str, autocommit=False, ansi=False, timeout=0, **kwargs): # real signature unknown; restored from __doc__
    """
    connect(str, autocommit=False, ansi=False, timeout=0, **kwargs) --> Connection

    Accepts an ODBC connection string and returns a new Connection object.

    **The connection string will be passed to SQLDriverConnect, so a DSN connection
    can be created using:**

      **cnxn = pyodbc.connect('DSN=DataSourceName;UID=user;PWD=password')**

    To connect without requiring a DSN, specify the driver and connection
    information:

      DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=user;PWD=password

    Note the use of braces when a value contains spaces.  Refer to SQLDriverConnect
    documentation or the documentation of your ODBC driver for details.

    The connection string can be passed as the string `str`, as a list of keywords,
    or a combination of the two.  Any keywords except autocommit, ansi, and timeout
    (see below) are simply added to the connection string.

      connect('server=localhost;user=me')
      connect(server='localhost', user='me')
      connect('server=localhost', user='me')

    The DB API recommends the keywords 'user', 'password', and 'host', but these
    are not valid ODBC keywords, so these will be converted to 'uid', 'pwd', and
    'server'.

    pass
1 Answers

The ODBC driver managers that I've dealt with (Windows' built-in DM, and unixODBC on Linux) silently ignore UID= and PWD= entries in "System DSN" and "User DSN" definitions. They do respect those entries in "File DSN" definitions, so you could create a file named "mymssql.dsn" in a secure location containing

[ODBC]
Description         = Test to SQLServer
Driver              = FreeTDS
Servername          = MYMSSQL
UID                 = sa
PWD                 = password
Database            = tempdb

and then use

conn_str = 'FILEDSN=/path/to/mymssql.dsn'
Related