Question 253771 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Not able to connect to Sql Server in Docker container

I have to develop a Laravel application based on Microsoft Sql Server, so I started configuring a Docker ambient with an Sql Server on Linux container. Actually I’m working on my windows 10 laptop where I installed Docker Desktop.

Here my Dockerfile content; I modified it to be able to install ms sql server odbc driver and php driver for sql server

FROM ubuntu:22.04

LABEL maintainer="Taylor Otwell"

ARG WWWGROUP
ARG NODE_VERSION=18
ARG POSTGRES_VERSION=15

WORKDIR /var/www/html

ENV DEBIAN_FRONTEND noninteractive
ENV TZ=UTC

RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone

RUN apt-get update 
    && mkdir -p /etc/apt/keyrings 
    && apt-get install -y gnupg gosu curl ca-certificates zip unzip git supervisor sqlite3 libcap2-bin libpng-dev python2 dnsutils librsvg2-bin fswatch 
    && curl -sS 'https://keyserver.ubuntu.com/pks/lookup?op=get&search=0x14aa40ec0831756756d7f66c4f4ea0aae5267a6c' | gpg --dearmor | tee /etc/apt/keyrings/ppa_ondrej_php.gpg > /dev/null 
    && echo "deb [signed-by=/etc/apt/keyrings/ppa_ondrej_php.gpg] https://ppa.launchpadcontent.net/ondrej/php/ubuntu jammy main" > /etc/apt/sources.list.d/ppa_ondrej_php.list 
    && apt-get update 
    && apt-get install -y php8.2-cli php8.2-dev 
       php8.2-pgsql php8.2-sqlite3 php8.2-gd php8.2-imagick 
       php8.2-curl 
       php8.2-imap php8.2-mysql php8.2-mbstring 
       php8.2-xml php8.2-zip php8.2-bcmath php8.2-soap 
       php8.2-intl php8.2-readline 
       php8.2-ldap 
       php8.2-msgpack php8.2-igbinary php8.2-redis php8.2-swoole 
       php8.2-memcached php8.2-pcov php8.2-xdebug 
    && curl -sLS https://getcomposer.org/installer | php -- --install-dir=/usr/bin/ --filename=composer 
    && curl -fsSL https://deb.nodesource.com/gpgkey/nodesource-repo.gpg.key | gpg --dearmor -o /etc/apt/keyrings/nodesource.gpg 
    && echo "deb [signed-by=/etc/apt/keyrings/nodesource.gpg] https://deb.nodesource.com/node_$NODE_VERSION.x nodistro main" > /etc/apt/sources.list.d/nodesource.list 
    && apt-get update 
    && apt-get install -y nodejs 
    && npm install -g npm 
    && npm install -g pnpm 
    && curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | gpg --dearmor | tee /etc/apt/keyrings/yarn.gpg >/dev/null 
    && echo "deb [signed-by=/etc/apt/keyrings/yarn.gpg] https://dl.yarnpkg.com/debian/ stable main" > /etc/apt/sources.list.d/yarn.list 
    && curl -sS https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | tee /etc/apt/keyrings/pgdg.gpg >/dev/null 
    && echo "deb [signed-by=/etc/apt/keyrings/pgdg.gpg] http://apt.postgresql.org/pub/repos/apt jammy-pgdg main" > /etc/apt/sources.list.d/pgdg.list 
    && apt-get update 
    && apt-get install -y yarn 
    && apt-get install -y mysql-client 
    && apt-get install -y postgresql-client-$POSTGRES_VERSION 
    && apt-get -y autoremove 
    && apt-get clean 
    && rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/*


RUN curl https://packages.microsoft.com/keys/microsoft.asc | tee /etc/apt/trusted.gpg.d/microsoft.asc
RUN curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | tee /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql18
RUN apt-get install -y unixodbc-dev

RUN pecl channel-update pecl.php.net 
RUN pecl install sqlsrv
RUN pecl install pdo_sqlsrv
RUN printf "; priority=20nextension=sqlsrv.son" > /etc/php/8.2/mods-available/sqlsrv.ini
RUN printf "; priority=30nextension=pdo_sqlsrv.son" > /etc/php/8.2/mods-available/pdo_sqlsrv.ini
RUN phpenmod -v 8.2 sqlsrv pdo_sqlsrv

RUN echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bash_profile
RUN echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
# RUN source ~/.bashrc

# Configure firewall to allow TCP port 1433:
# RUN echo Configuring UFW to allow traffic on port 1433 and 5434...
# RUN ufw allow 1433/tcp
# RUN ufw allow 5434/tcp
# RUN ufw reload
# Restart SQL Server after installing:
# RUN echo Restarting SQL Server...
# RUN systemctl restart mssql-server

RUN setcap "cap_net_bind_service=+ep" /usr/bin/php8.2

RUN groupadd --force -g $WWWGROUP sail
RUN useradd -ms /bin/bash --no-user-group -g $WWWGROUP -u 1337 sail

COPY start-container /usr/local/bin/start-container
COPY supervisord.conf /etc/supervisor/conf.d/supervisord.conf
COPY php.ini /etc/php/8.2/cli/conf.d/99-sail.ini
RUN chmod +x /usr/local/bin/start-container

EXPOSE 8000

ENTRYPOINT ["start-container"]

Here also my docker-compose.yml file where I defined also my sql server docker container

services:
    laravel.test:
        build:
            context: ./docker/8.2
            dockerfile: Dockerfile
            args:
                WWWGROUP: '${WWWGROUP}'
        image: sail-8.2/app
        extra_hosts:
            - 'host.docker.internal:host-gateway'
        ports:
            - '${APP_PORT:-80}:80'
            - '${VITE_PORT:-5173}:${VITE_PORT:-5173}'
        environment:
            WWWUSER: '${WWWUSER}'
            LARAVEL_SAIL: 1
            XDEBUG_MODE: '${SAIL_XDEBUG_MODE:-off}'
            XDEBUG_CONFIG: '${SAIL_XDEBUG_CONFIG:-client_host=host.docker.internal}'
            IGNITION_LOCAL_SITES_PATH: '${PWD}'
        volumes:
            - '.:/var/www/html'
        networks:
            - sail
        depends_on:
            - mailpit
            - sql-server-db
    mailpit:
        image: 'axllent/mailpit:latest'
        ports:
            - '${FORWARD_MAILPIT_PORT:-1025}:1025'
            - '${FORWARD_MAILPIT_DASHBOARD_PORT:-8025}:8025'
        networks:
            - sail
    sql-server-db:
        container_name: sql-server-db
        image: mcr.microsoft.com/mssql/server:2019-latest
        environment:
          - SA_PASSWORD=Pass@word
          - ACCEPT_EULA=Y
          - MSSQL_PID=Express
        ports:
          - "5434:1433"
        networks:
          - sail
networks:
    sail:
        driver: bridge

If I run "sail up" all the containers starts running correctly.

Then I tried to connect to the sql server instance:
first directly from inside the container:

docker exec -it f353 'bash'

and then from the bash shell

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Pass@word'

and it works. I was able for example to list all the databases.

Then I tried the same from the inside of the laravel.test container.
First I installed sql tools and then I tried the following two commands where 172.23.0.2 is the IP assigned to the sql server container.

/opt/mssql-tools18/bin/sqlcmd -S 172.23.0.2:5434 -U SA -P 'Pass@word'
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : TCP Provider: Error code 0x2AF9.
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to 172.23.0.2:5434. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
# /opt/mssql-tools18/bin/sqlcmd -S 172.23.0.2:1433 -U SA -P 'Pass@word'
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : TCP Provider: Error code 0x2AF9.
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to 172.23.0.2:1433. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

I also tried to connect to sql server from the SQL Server Management Studio I have on my win10 pc.

In the server name I settted 172.23.0.2,5434
users = sa
password = Pass@word

also in this case I got a similar error

Not possible to connect to 172.23.0.2,5434
Server not found. Check the instance name and if the SQL Server is configured to accept remote connections (provider: TCP Provider, error: 0 - Timeout error.) (Microsoft SQL Server, error: 258)

I’m not able to find a solution.
Any help?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I was able to connect to the Sql Server in this way. First I started an interactive bash shell inside my running container of the web server.

    docker exec -it 523 'bash'

    Then first I installed mssql-tools (refer to this doc)

    sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18

    Finally I connected to the Sql Server instance using the following command

    /opt/mssql-tools18/bin/sqlcmd -S 172.23.0.2,1433 -U SA -P 'Pass@word' -No

    where 172.23.0.2 is the db internal IP assigned in Docker and 1433 is the internal port. I had to use the -No option because the db container has a self signed certificate and without that option it gives me this error:

    Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate].

    In Laravel, for the same reason, I added 2 connection properties in the .env file

    DB_ENCRYPT=no
DB_TRUST_SERVER_CERTIFICATE=true

    and also in the database.php file

    'sqlsrv' => [
    'driver' => 'sqlsrv',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', 'localhost'),
    'port' => env('DB_PORT', '1433'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'prefix_indexes' => true,
    'encrypt' => env('DB_ENCRYPT', 'yes'),
    'trust_server_certificate' => env('DB_TRUST_SERVER_CERTIFICATE', 'false'),
],

    Finally in Sql Server Management Studio, after starting my containers, I was able to connect using the following parameters in the connection panel

    • server name = localhost,5434
    • disable encryption
    • trust server certificate

    where 5434 is the port exposed out of the db container see docker-compose file

    sql-server-db:
    container_name: sql-server-db
    image: mcr.microsoft.com/mssql/server:2019-latest
    environment:
      - SA_PASSWORD=Pass@word
      - ACCEPT_EULA=Y
      - MSSQL_PID=Express
    ports:
      - "5434:1433"
    networks:
      - sail

  2. 0

    Try SQL_SA_PASSWORD instead.

    This is how I run SQL Server:

    docker run -tid 
-p 1433:1433 
--name mssql 
--env-file ./mssql.env 
mcr.microsoft.com/mssql/server:2022-latest

[jgomez@mercury ds]$ cat mssql.env
SQL_SA_PASSWORD=xxxxxxxxx
SA_PASSWORD=xxxxxxxxx
ACCEPT_EULA=Y
MSSQL_PID=Express
[jgomez@mercury ds]$
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search