Azure Function App in Docker Desktop does not have access to local sql server database on host

Question

I have my mssql server on my local windows desktop, which hold the development database for my application. I have a Azure function app that is written in python which has a couple endpoints that query the database.

If I run this function app in VSCode is connects to the database file. It uses the following env variables:

    "DB_HOST": "HOME\\SQLEXPRESS",
    "DB_NAME": "devdb.local",
    "DB_USERNAME": "user",
    "DB_PASS": "...",

I am trying to run my function app in docker and docker desktop in Linux containers so I can run multiple function app at the same time.

I have the following Dockerfile:

FROM python:3.10-slim-buster

# Set a non-root user and create the working directory.
WORKDIR /app

# Install system dependencies
RUN apt-get update && apt-get install -y --no-install-recommends \
    gcc \
    g++ \
    wget \
    gnupg \
    apt-transport-https \
    lsb-release \
    # Add Microsoft package signing key and repository
    && wget -q https://packages.microsoft.com/keys/microsoft.asc -O- | apt-key add - \
    && wget -q https://packages.microsoft.com/config/debian/10/prod.list -O /etc/apt/sources.list.d/mssql-release.list \
    && apt-get update \
    # Install ODBC Driver for SQL Server
    && ACCEPT_EULA=Y apt-get install -y msodbcsql17 \
    # Other dependencies
    && apt-get install -y unixodbc-dev \
    && apt-get install -y libicu-dev \
    && apt-get install -y azure-functions-core-tools-4 \
    && rm -rf /var/lib/apt/lists/* /packages-microsoft-prod.deb

# Copy only the requirements file and install Python dependencies.
COPY requirements.txt ./
RUN pip install --no-cache-dir -r requirements.txt

# Copy the rest of the application code.
COPY . .

# Expose the port on which the app will run.
EXPOSE 7071

# Start the function app.
CMD ["func", "start", "--python"]

Docker Compose File:

version: '3.8'

services:
  app:
    image: func-app:latest
    ports:
      - "8001:7071"
    environment:
      - AzureWebJobsStorage=UseDevelopmentStorage=true
      - AzureWebJobsFeatureFlags=EnableWorkerIndexing
      - FUNCTIONS_WORKER_RUNTIME=python
      - ENVIRONMENT=dev
      - DB_HOST=HOME\\SQLEXPRESS
      - DB_NAME=devdb.local
      - DB_USERNAME=user
      - DB_PASS=...
      - AZURE_STORAGE_ACCOUNT_CONNECTIONSTRING=...
      - AZURE_CONTAINER=media
      - CARD_MARKET_APP_SECRET=...
      - CARD_MARKET_APP_TOKEN=...
      - DEV_SHOP=...
      - SETS_PROCESSED=100
    command: ["func", "start", "--python"]

When I run this in docker desktop that app runs as expected but I get the following error:

{
  "error": "Database connection failed: Failed to connect to database: ('01000', \"[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.5.1' : file not found (0) (SQLDriverConnect)\")"
}

in my requirements.txt I have the packages for database connection:

mysql-connector-python==8.2.0
pyodbc==5.0.1

I cant seem to figure out why my function app in a docker contain does have access to my database on the host.

@Ross- Make sure you have installed ODBC Driver in the docker container- Dockerfile: RUN apt-get update && apt-get install -y --no-install-recommends \ unixodbc \ unixodbc-dev \ libgssapi-krb5-2 \ odbcinst \ libodbc1 \ && rm -rf /var/lib/apt/lists/* — SiddheshDesai
– SiddheshDesai, Commented Jan 10, 2024 at 11:04
After installing the ODBC driver, you can configure it by adding the following lines to your Dockerfile: COPY odbcinst.ini /etc/ COPY odbc.ini /etc/ — SiddheshDesai
– SiddheshDesai, Commented Jan 10, 2024 at 11:04
You will also need to create the odbcinst.ini and odbc.ini files in the same directory as your Dockerfile. These files should contain the configuration information for the ODBC driver. Here is an example odbcinst.ini file: [ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1 UsageCount=1 And here is an example odbc.ini file: [mydsn] Driver=ODBC Driver 17 for SQL Server Server=HOME\\SQLEXPRESS Database=devdb.local — SiddheshDesai
– SiddheshDesai, Commented Jan 10, 2024 at 11:04
Then update volume in your dockercompose file like this :- version: '3.8' services: app: image: func-app:latest ports: - "8001:7071" environment: - AzureWebJobsStorage=UseDevelopmentStorage=true - AzureWebJobsFeatureFlags=EnableWorkerIndexing - FUNCTIONS_WORKER_RUNTIME=python - ENVIRONMENT=dev - AZURE_STORAGE_ACCOUNT_CONNECTIONSTRING=... - AZURE_CONTAINER=media - CARD_MARKET_APP_SECRET=... - CARD_MARKET_APP_TOKEN=... - DEV_SHOP=... - SETS_PROCESSED=100 — SiddheshDesai
– SiddheshDesai, Commented Jan 10, 2024 at 11:05
volumes: - ./odbcinst.ini:/etc/odbcinst.ini - ./odbc.ini:/etc/odbc.ini command: ["func", "start", "--python"] — SiddheshDesai
– SiddheshDesai, Commented Jan 10, 2024 at 11:05

SiddheshDesai · Accepted Answer · 2024-01-10 11:41:21Z

1

Posting my comments as an answer for the benefit of the community:-

You need to install ODBC Driver in your Docker container too for the driver to connect to SQL:-

You can install the ODBC driver by using this command:-

RUN apt-get update && apt-get install -y --no-install-recommends \
    unixodbc \
    unixodbc-dev \
    libgssapi-krb5-2 \
    odbcinst \
    libodbc1 \
    && rm -rf /var/lib/apt/lists/*

Once the ODBC driver is installed, use the below lines into your Dockerfile to set up its configuration:

COPY odbcinst.ini /etc/
COPY odbc.ini /etc/

Additionally, it's necessary to generate the odbcinst.ini and odbc.ini files within the same location as your Dockerfile. These files must hold the setup details for the ODBC driver.

odbcinst.ini file:-

[ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1 UsageCount=1 And here is an example odbc.ini file: [mydsn] Driver=ODBC Driver 17 for SQL Server Server=HOME\\SQLEXPRESS Database=devdb.local

odbc.ini:-

[mydsn] 
Driver=ODBC Driver 17 for SQL 
Server Server=HOME\\SQLEXPRESS 
Database=devdb.local

Docker compose File:-

version: '3.8'

services:
  app:
    image: func-app:latest
    ports:
      - "8001:7071"
    environment:
      - AzureWebJobsStorage=UseDevelopmentStorage=true
      - AzureWebJobsFeatureFlags=EnableWorkerIndexing
      - FUNCTIONS_WORKER_RUNTIME=python
      - ENVIRONMENT=dev
      - AZURE_STORAGE_ACCOUNT_CONNECTIONSTRING=...
      - AZURE_CONTAINER=media
      - CARD_MARKET_APP_SECRET=...
      - CARD_MARKET_APP_TOKEN=...
      - DEV_SHOP=...
      - SETS_PROCESSED=100
    volumes:
      - ./odbcinst.ini:/etc/odbcinst.ini
      - ./odbc.ini:/etc/odbc.ini
    command: ["func", "start", "--python"]

Complete DockerFile:-

FROM python:3.10-slim-buster

# Install system dependencies
RUN apt-get update && apt-get install -y --no-install-recommends \
    gcc \
    g++ \
    wget \
    gnupg \
    apt-transport-https \
    lsb-release \
    # Add Microsoft package signing key and repository
    && wget -q https://packages.microsoft.com/keys/microsoft.asc -O- | apt-key add - \
    && wget -q https://packages.microsoft.com/config/debian/10/prod.list -O /etc/apt/sources.list.d/mssql-release.list \
    && apt-get update \
    # Install ODBC Driver for SQL Server
    && ACCEPT_EULA=Y apt-get install -y msodbcsql17 \
    # Other dependencies
    && apt-get install -y unixodbc-dev \
    && apt-get install -y libicu-dev \
    && apt-get install -y azure-functions-core-tools-4 \
    && rm -rf /var/lib/apt/lists/* /packages-microsoft-prod.deb

# Set a non-root user and create the working directory.
WORKDIR /app

# Copy only the requirements file and install Python dependencies.
COPY requirements.txt ./
RUN pip install --no-cache-dir -r requirements.txt

# Copy the rest of the application code.
COPY . .

# Expose the port on which the app will run.
EXPOSE 7071

# Set environment variables.
ENV DB_HOST=HOME\\SQLEXPRESS \
    DB_NAME=devdb.local \
    DB_USERNAME=user \
    DB_PASS=...

# Copy the ODBC configuration files.
COPY odbcinst.ini /etc/
COPY odbc.ini /etc/

# Start the function app.
CMD ["func", "start", "--python"]

Reference:-

dockerfile - Connect docker python to SQL server with pyodbc - Stack Overflow

answered Jan 10, 2024 at 11:41

SiddheshDesai

8,5321 gold badge4 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ross Over a year ago

Unfortunately I am still getting:

"Database connection failed: Failed to connect to database: ('01000', \"[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)\")"

I have added the idbcinst.ini and odbc.ini files. with the correct location to the driver in the opt/microsoft/... location.

SiddheshDesai Over a year ago

Try to run this:- RUN apt-get update && \ apt-get install -y --no-install-recommends \ curl \ sudo \ apt-transport-https \ gnupg \ unixodbc-dev \ libgssapi-krb5-2 && \ rm -rf /var/lib/apt/lists/* # Download and add Microsoft GPG key RUN curl packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc # Choose the appropriate package for the OS version (Debian 11 in this case) RUN curl packages.microsoft.com/config/debian/11/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list

SiddheshDesai Over a year ago

# Update package list and install Microsoft ODBC Driver 17 RUN apt-get update && \ ACCEPT_EULA=Y apt-get install -y --no-install-recommends msodbcsql17 && \ rm -rf /var/lib/apt/lists/*

SiddheshDesai Over a year ago

Looks like you seem to have resolved the ODBC issue here- stackoverflow.com/questions/77795735/…

Collectives™ on Stack Overflow

Azure Function App in Docker Desktop does not have access to local sql server database on host

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related