How to connect to Data Gateway from within Linux (or WSL)

gabrielius · May 19, 2025, 5:30pm

Introduction

There are cases in life that make you connect to APIs via ZappySys Data Gateway from within Linux. Let’s say you have the Data Gateway (part of ODBC PowerPack) installed on a Windows machine and you have Apache Airflow installed on Linux to orchestrate ETL processes. So, you are developing a DAG – an ETL task – and now you want to connect to the Data Gateway to retrieve data from a REST API and save it somewhere else, but you don’t know how to… Let’s not waste the time, dive in and find out how to do it!

Steps

Find the address of the Data Gateway machine

The first thing you need to do is to find out the hostname or the IP address of the Windows machine where the Data Gateway resides. Here is how you can do it for different scenarios:

Data Gateway and WSL run on the same machine

If you are hosting WSL v2 (Windows Subsystem for Linux) on the same Windows machine where the Data Gateway is installed, run this command on the WSL machine to get Windows host machine’s IP address:
```
ip route show | awk '{ print $3}' | head -1
```
You can also run this command on the Windows host machine from within PowerShell:
```
Get-NetIPAddress -InterfaceAlias "vEthernet (WSL)" -AddressFamily IPv4 | Select-Object -ExpandProperty IPAddress -First 1
```
Most likely, you will get an IP equal to 172.17.176.1 or 192.168.65.254 or anything in-between. But let’s be honest, it can be anything!
Data Gateway and WSL Docker container run on the same machine

If you are hosting Linux in a Docker container on the same Windows machine where the Data Gateway is installed, simply use this specialized hostname to connect to the Windows host machine:
```
host.docker.internal
```
Data Gateway runs on a different machine than WSL

Just use the IP of your Windows machine, where the ODBC PowerPack is installed and the Data Gateway is running.

NOTE: Make sure, you have configured the firewall between the two machines correctly.
You are accessing Data Gateway from standalone Linux

Just use the IP of your Windows machine, where the ODBC PowerPack is installed and the Data Gateway is running.

NOTE: Make sure, you have configured the firewall between the two machines correctly.

Connect to the Data Gateway

To connect to the Data Gateway from within Linux OS you will need to use Microsoft SQL Server drivers. In our example we will use a Python script:

Go to the WSL or Linux machine.
In the shell command line download Python library if you don’t have one installed:
```
sudo apt install python3
```
Then install the Microsoft SQL Server Python driver, if it’s missing:
```
sudo apt install python3-pymssql
```

Create Python script data-gateway.py:

import pymssql
import sys

# Replace placeholders with your actual connection details below

server = '172.17.176.1'
#server = 'host.docker.internal' # Use when WSL is running in Docker
port = 5000      # Port number for ZappySys Data Gateway
database = 'OData'    # Data source name in ZappySys Data Gateway
username = 'admin'    # Username, configured in ZappySys Data Gateway
password = '12345'    # Password, configured in ZappySys Data Gateway

conn = None

print(f"--- Connecting to Data Gateway ---")
print(f"Server:   {server}")
print(f"Database: {database}")
print(f"Username: {username}")
print("-" * 40)

try:
    # Establish connection
    print("Attempting connection...")
    conn = pymssql.connect(server=server,
                            user=username,
                            password=password,
                            database=database,
                            port=port,
                            timeout=30,
                            as_dict=False,
                            autocommit=True)      # !!! CRUCIAL OPTION !!!!!

    print("Connection successful!")
    cursor = conn.cursor()

    # --- Execute SELECT query ---
    sql_query = "SELECT * FROM Customers;"
    print(f"\nExecuting query: {sql_query}")
    cursor.execute(sql_query)

    # --- Fetch and print results ---
    print("\n--- Query Results ---")
    rows = cursor.fetchall()
    if not rows:
        print("No rows found in the table.")
    else:        
        # Print rows
        for row in rows:
            print(row)
    print("-" * 40)
    cursor.close()

except Exception as e:
    print(f"\nERROR: An unexpected error occurred: {e}")
    sys.exit(1)

finally:
    if conn:
        conn.close()
        print("\nConnection closed.")
    else:
        print("\nConnection was not established.")

print("Script finished.")

NOTE: Make sure autocommit property is set to True in pymssql.connect(...) method.

Run the script and get the results back:

python3 data-gateway.py

Results:

('RICAR', 'Ricardo Adocicados', 'Janete Limeira', 'Assistant Sales Agent')
('RICSU', 'Richter Supermarkt', 'Michael Holz', 'Sales Manager')
('ROMEY', 'Romero y tomillo', 'Alejandra Camino', 'Accounting Manager')
('SANTG', 'Santé Gourmet', 'Jonas Bergulfsen', 'Owner')
('SAVEA', 'Save-a-lot Markets', 'Jose Pavarotti', 'Sales Representative')
('SEVES', 'Seven Seas Imports', 'Hari Kumar', 'Sales Manager')
('SIMOB', 'Simons bistro', 'Jytte Petersen', 'Owner')
('SPECD', 'Spécialités du monde', 'Dominique Perrier', 'Marketing Manager')

Conclusion

In this article we learned how to connect to ZappySys Data Gateway from Linux, WSL or WSL within Docker container. In the demonstration, we used Python programming language and the native Microsoft SQL Server driver for Python.

But above all, it was quite fun, after all! Wasn’t it?

So, if you don’t have already, go ahead, download and install ODBC PowerPack and start connecting to REST APIs from within Linux right away!

References

Contact us

If you encounter any challenges or have specific use cases, please contact our support team via chat or ticket.