Debian Question posted in
The oficial documentation can be found here.

How to create ODBC "DSN" for cross-platform testing? – Debian

I need a simple ODBC test scenario on WIN which I can configure very simply and be assured it is working in support of another question at Unix.SE.

In a nutshell I’m trying to setup a PyODBC/Python script connection from Debian 10 (192.168.1.2) to Windows 10 in KVM/QEMU virtual system (192.168.1.12).

First, on the Windows 10/KVM, I see the ODBC Data Source Administrator has a tab File DSN and Microsoft Text Driver. Can I use FileDSN to test Python PyODBC connection to ODBC using a simple CSV file in place of Server?? (My research with ODBC only finds running server instances).

Next, what I tried:

  • On Debian I installed ODBC Microsoft driver for Linux.

  • Shutdown the Windows 10 firewall, and I can ping in both directions:

    $nmap -p 22 192.168.1.12 # Deb to Win

    > Test-NetConnection 192.168.1.2 -p 22 # Win to Deb

  • On Windows 10/KVM I added a FileDSN with Microsoft Text Driver. I created a CSV file (odbc_test_01.csv) with simple header and one row of data (IE. {'ID' : 1, 'NAME' : 'FOO'})

  • Created a Jupyter Notebook to make testing easier. Here is my connection string and the results:

    cn = pyodbc.connect(r'Driver={ODBC Driver 17 for SQL Server};' # Driver installed above
                r'FILEDSN=odbc_test_01.csv;'  # my attempt at FileDSN
                r'SERVER=192.168.1.12;'  # KVM IP tested with ping
                r'Trusted_Connection=no;' # explicit; use UID/PWD
                r'UID=<username>;'   # Windows user name 
                r'PWD=<password>',  # Windows user password 
                autocommit=True)

OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

  • Tried isql from Debian command line with same string:

    isql -v -k ''Driver={ODBC Driver 17 for SQL Server};FILEDSN=odbc_test_01.csv;SERVER=192.168.1.12; Trusted_Connection=no;UID=<username>;PWD=<password>'

Similar pages here at SO:

Authenticate from Linux to Windows SQL Server with pyodbc

Python pyodbc connect to Sql Server using SQL Server Authentication

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I created an SQLite database. Then I added SQLite drivers for ODBC.


  2. 0

    An ODBC "File DSN" is not a driver for accessing data in a file. It is a way to specify a DSN (connection information for a target database) as values in a standalone file instead of in a standard configuration file on Linux (e.g., /etc/odbc.ini) or in the Windows registry.

    If you need to "clone" a Windows DSN entry for use in a Linux environment then you may find my dump_dsn utility helpful. It retrieves an ODBC DSN from the Windows registry and presents it in a form that you could use to recreate the DSN on Linux.

    For example, say I had a DSN named "mssql199" on Windows and when I ran dump_dsn.to_text("mssql199") on it I got

    [mssql199]
Driver=ODBC Driver 17 for SQL Server
Description=with UseFMTONLY
Server=192.168.0.199
Database=myDb
Encrypt=No
TrustServerCertificate=No
ClientCertificate=
KeystoreAuthentication=
KeystorePrincipalId=
KeystoreSecret=
KeystoreLocation=
UseFMTONLY=Yes
Trusted_Connection=No

    To use that same DSN on a Linux box I would have to

    1. copy that block into /etc/odbc.ini (or equivalent, for a "System DSN") or ~/.odbc.ini (for a "User DSN") to use DSN=mssql199, or
    2. save that block with an [ODBC] header instead of [mssql199] to a file, e.g., /home/gord/mssql199_file.dsn (for a "File DSN") and use FILEDSN=/home/gord/mssql199_file.dsn

    Edit re: "Can I use FileDSN to test Python PyODBC connection to ODBC using a simple CSV file in place of Server??"

    No. An ODBC DSN or FILEDSN on the Windows box will only be useful to connect from the Windows box to a data source (either locally, or on some other machine). We cannot connect from one machine (e.g., Linux) to an ODBC DSN entry on another machine

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search