unable to add MSSQL source on m1 mac

[scriptnull]

Version Information

Server Version: v2.1.0-beta.3 CLI Version (for CLI related issue): N/A

Environment

OSS

What is the expected behaviour?

Hasura should allow adding MSSQL data source on m1 mac ( linux/arm64 ) graphql-engine image.

Keywords

Microsoft SQL Server, MSSQL, m1, mac, arm64

What is the current behaviour?

Hasura fails with the following error

[unixODBC][Driver Manager]Can’t open lib ‘ODBC Driver 17 for SQL Server’ : file not found[unixODBC][Driver Manager]Can’t open lib ‘ODBC Driver 17 for SQL Server’ : file not found

Screenshots or Screencast

Any possible solutions?

This issue could only be fixed when Microsoft officially supports msodbcsql17 package for linux/arm64 (more specifically for debian). Some of the tracking issues for that are as follows:

• https://feedback.azure.com/d365community/idea/7bef10f9-9a4c-ec11-a81a-6045bd78b970
• https://feedback.azure.com/d365community/idea/38d01043-5825-ec11-b6e6-000d3a4f0da0
• https://github.com/MicrosoftDocs/sql-docs/issues/6494#issuecomment-985246219
• https://github.com/MicrosoftDocs/sql-docs/issues/6494#issuecomment-985246219

[27medkamal]

Is there an ETA or a workaround for this?

[terrymunro]

Apple M1 (ARM64) support was added starting with version 17.8. source

😭 (Nevermind, just re-read the OP and you already said you need it specifically for Debian)

[jmarbutt]

I am also looking for some help on this.

[deinspanjer]

MS has added support for arm64 (M1) support in the latest version of ODBC 17 and ODBC 18.

I was able to add the v18 driver to the graphql-engine container manually and connect to a sqlserver DB. The hack steps are to start it up, then get the container ID (docker ps) and run docker exec -it <container hash> bash then run the curls and the apt-get installs for msodbcsql18 from here https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16#ubuntu18

I'm suspicious that it might have been working for a while but because the ODBC drivers aren't in the Hasura graphql-engine images, it just wasn't working for anyone.

I have to get back to other work right now, but I will try to set up a Dockerfile that can be used to build a derivative image allowing other people to get up and running. No promises on when though.

[deinspanjer]

Okay, here are the results of what I had to do to get stuff running in Docker on an M1 Mac:

1. Install the ODBC 18 driver into the graphql-engine image so it is available to the server
2. Switch from the version of mssqlserver that was previously used to azure-sql-edge which has an image that is compatible with arm64 (M1).

To do the first, I created the following Dockerfile. It creates a build image to install the ODBC driver, then it copies the resulting driver files into a new version of the Hasura/graphql-engine image. I don't know why Hasura isn't including those files in their image anymore, but this works well enough.

Next, I modified the docker-compose.yml file to point at the built image and at azure-sql-edge.

If you copy these two files locally, you should be able to run docker compose up -d and get an Hasura server that can connect to the dockerized mssql or to an external mssql server using the driver connection string: Driver={ODBC Driver 18 for SQL Server};Server=tcp:mssql,1433;Database=tempdb;Uid=sa;Pwd={Password123};Encrypt=yes;TrustServerCertificate=yes;ConnectionTimeout=30;

Dockerfile

# syntax=docker/dockerfile:1

FROM hasura/graphql-engine:v2.12.0 AS odbc-builder

RUN  apt-get update && \
     apt-get install -y gnupg && \
     curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
     curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list && \
     apt-get update && \
     echo msodbcsql18 msodbcsql/ACCEPT_EULA boolean true | debconf-set-selections && \
     apt-get install -y msodbcsql18


FROM hasura/graphql-engine:v2.12.0 AS runtime

RUN mkdir -p /opt/microsoft/msodbcsql18/{etc,include,lib64,share/resources/en_US}
COPY --from=odbc-builder /usr/lib/libmsodbcsql-18.so /usr/lib/
COPY --from=odbc-builder /etc/odbcinst.ini /etc/
COPY --from=odbc-builder /opt/microsoft/msodbcsql18/etc/odbcinst.ini /opt/microsoft/msodbcsql18/etc/
COPY --from=odbc-builder /opt/microsoft/msodbcsql18/include/msodbcsql.h /opt/microsoft/msodbcsql18/include/
COPY --from=odbc-builder /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.1.1 /opt/microsoft/msodbcsql18/lib64/
COPY --from=odbc-builder /opt/microsoft/msodbcsql18/share/resources/en_US/msodbcsqlr18.rll /opt/microsoft/msodbcsql18/share/resources/en_US/

docker-compose.yml

version: '3.6'
services:
  postgres:
    image: postgres:14
    restart: always
    ports:
    - "15432:5432"
    volumes:
    - pg_data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: postgrespassword
  mssql:
    image: mcr.microsoft.com/azure-sql-edge
    environment:
      ACCEPT_EULA: 'Y'
      SA_PASSWORD: ${MSSQL_PASSWORD:-Password1}
      MSSQL_PID: Developer
      MSSQL_USER: SA
    ports:
    - "11433:1433"
    volumes:
      - mssql_data:/var/opt/mssql
  graphql-engine:
    build: .
    ports:
    - "8080:8080"
    depends_on:
    - "postgres"
    restart: always
    environment:
      ## postgres database to store Hasura metadata
      HASURA_GRAPHQL_METADATA_DATABASE_URL: postgres://postgres:postgrespassword@postgres:5432/postgres
      ## this env var can be used to add the above postgres database to Hasura as a data source. this can be removed/updated based on your needs
      PG_DATABASE_URL: postgres://postgres:postgrespassword@postgres:5432/postgres
      ## enable the console served by server
      HASURA_GRAPHQL_ENABLE_CONSOLE: "true" # set to "false" to disable console
      ## enable debugging mode. It is recommended to disable this in production
      HASURA_GRAPHQL_DEV_MODE: "true"
      HASURA_GRAPHQL_ENABLED_LOG_TYPES: startup, http-log, webhook-log, websocket-log, query-log
      ## uncomment next line to set an admin secret
      # HASURA_GRAPHQL_ADMIN_SECRET: myadminsecretkey
      #HASURA_GRAPHQL_DEFAULT_NAMING_CONVENTION: graphql-default
      #HASURA_GRAPHQL_EXPERIMENTAL_FEATURES: "naming_convention"
volumes:
  pg_data:
  mssql_data:

[SamirTalwar]

This is awesome. Thank you @deinspanjer!

I am working on getting msodbcsql18, with proper arm64 support, into the official Hasura Docker image. It's a bit more involved, and therefore taking a bit longer, because we also need to update our build and test infrastructure to ensure it doesn't break anything.

In the mean time, for anyone who can't wait that long, @deinspanjer's solution looks like a great workaround, though of course it's not officially supported.

[SamirTalwar]

This is now implemented in https://github.com/hasura/graphql-engine/commit/c3afa0fdd7eece8eb1bc708e4e241efe36c18618, and will be part of a subsequent release.