Skip to main content
    • Knowledge Base/
    How To Set Up Ch On Docker Odbc Connect Mssql

    How to set up ClickHouse on Docker with ODBC to connect to a Microsoft SQL Server (MSSQL) database

    How to set up ClickHouse on Docker with ODBC to connect to a Microsoft SQL Server (MSSQL) database

    Question​

    How do I set up ClickHouse with a Docker image to connect to Microsoft SQL Server?

    Answer​

    Notes on this example

    • Uses the ClickHouse Docker Ubuntu image
    • Uses the FreeTDS Driver
    • Uses MSSQL Server 2012R2
    • Windows hostname for this example is MARSDB2.marsnet2.local at IP: 192.168.1.133 (update with your hostname and/or IP)
    • MSSQL Instance name MARSDB2
    • MSSQL Login and datbase users are sql_user

    Example setup in MSSQL for testing​

    Database and table created in MSSQL:

    Screenshot 2024-01-01 at 8 25 50 PM

    MSSQL Login User, sql_user:

    Screenshot 2024-01-01 at 8 27 11 PM

    Database membership roles for sql_user:

    Screenshot 2024-01-01 at 8 27 35 PM

    Database User with Login:

    Screenshot 2024-01-01 at 8 35 34 PM

    Configuring ClickHouse with ODBC​

    Create a working directory:

    Create an odbc.ini file:

    Add the following entries to update the name of the DSN and IP:

    Create an odbcinst.ini file:

    Add the following entries (trace is optional but helps with debugging):

    Configure a Dockerfile to download the image and add the TDS and required ODBC libraries​

    Create the Dockerfile:

    Add the contents of the Dockerfile:

    Build the new docker image:

    Create a docker-compose.yml file:

    Add the following contents to the YAML:

    Start the container:

    After you start the container, you should see something like this:

    Check to ensure the container is running:

    Test ODBC connection​

    Login with the ClickHouse client:

    Test the SELECT using the odbc table function to the remote MSSQL Database table:

    You can also create a remote table using the odbc table engine:

    Use a SELECT query to test the new remote table:

    For more information, please see:

    · 3 min read