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

Question

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

Answer 1

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

Answer 2

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