SQLSTATE[HYT00]: [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired on docker php:8.1-apache

[h1meS]

Working on a task with 2 containers(each build with Dockerfile) and compose file, try to test connection with php script but throws the error on title. I'm using vagrant for VM (Ubuntu 20.04)

First Container (sqlsrvr)

FROM mcr.microsoft.com/mssql/server:2022-latest
USER root
ENV ACCEPT_EULA="Y"
ENV MSSQL_SA_PASSWORD=Mypass9809
CMD ["/opt/mssql/bin/sqlservr"]

Second Container (php-apache)

FROM php:8.1-apache
RUN apt-get update && apt-get install -y gnupg2
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql18 mssql-tools18
RUN apt-get update && apt-get install -y unixodbc-dev
RUN pecl install pdo_sqlsrv sqlsrv
RUN docker-php-ext-enable pdo_sqlsrv sqlsrv
COPY src/ /var/www/html/
CMD ["apache2-foreground"]

Compose file

version: "3.9"
services:
    web:
        build:
            context: app
        depends_on:
            - db
        ports:
            - 80:80
        volumes:
            - ./app/src:/var/www/html
    db:
        build:
            context: db
        environment:
            - ACCEPT_EULA=Y
            - MSSQL_SA_PASSWORD=Mypass9809
        restart: always
        ports:
            - 1433:1433
        healthcheck:
            test: ["CMD-SHELL", "/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Mypass9809 -Q 'SELECT 1' || exit 1"]
            interval: 10s
            retries: 10
            start_period: 10s
            timeout: 3s
        volumes:
            - ./data:/var/opt/mssql/data

My php script for test

final class QuickDbTest
{
private const host = 'localhost';
private const db = 'db_name';
private const user = 'sa';
private const pass = 'Mypass9809'

private function connectToDatabase() {
        try {
            $dsn = "sqlsrv:server=".self::host.";Database=".self::db;
            $connection = new PDO($dsn, self::user, self::pass);
            $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        }
        catch(PDOException $e)
        {
            $this->quickLog(['conf' => [self::host, self::db, self::user],'sqlsrv_errors' => $e->getMessage()], 'DB failed to connect.');
        }
        return $connection;
    }
}

After the initial startup everything seems ok and running looking on the logs. I can connect to database with docker exec with my password . I can access into index.php page through apache and confirm drivers are working. But switching to DbTest.php gets the error.

So far I try these and still no solution:

1. I try other versions for php ,pdo or sqlsrv driver also ODBC17, it didnt work
2. My hostname is in script localhost. I thought thats the issue so I changed the service db to localhost in compose file.
3. Some other research refers me using links parameter in compose.yaml. So I tried to use like links: - "db:localhost" .
4. Another thing that I tried for compose.yaml is hostname parameter since I'm suspicious about the parameter host
5. Read and did some other solutions yet no solution so far.

[h1meS]

So I did add network_mode: host reference to both services for localhost usage but now I'm getting another error:

[sqlsrv_errors] => SQLSTATE[08001]: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate]

I try to add CMD ["sqlcmd -S localhost -U SA -P 'Un!q@to2023' -C"] command to dockerfile but it didnt work.
I also learned that there is a parameter for config file which is TrustServerCertificate=yes but I couldnt figure out where to put it.

[h1meS]

I manage to add TrustServerCertificate=true for pdo connection but it leeds me to another error this time:

[sqlsrv_errors] => SQLSTATE[42000]: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Cannot open database "db_name" requested by the login. The login failed.

[v-makouz]

This seems like the server doesn't have a database "db_name", rather than an issue with the driver. What happens if you don't specify the database?

[h1meS]

Yes I realize that and fixed now It's working and I can connect to through php script Thx.

[martin-kaboja]

Hello @h1meS im running through the same problem can you explain what steps you took to make it work

[basdas79]

@martin-kaboja
for me it was that I had in the .env DB_CONNECTION=localhost instead of the docker image name DB_CONNECTION=sqlsrv