Random connection error

[kKen94]

Sometimes docker-compose connection results successful, sometimes get " A network-related or instance-specific error occurred while establishing a connection to SQL Server", sometimes "A connection was successfully established with the server, but then an error occurred during the pre-login handshake", sometimes ""Login failed for user ‘sa’"

I run docker-compose up until it works.

This my docker-compose:

version: '3.7'

services:

  rabbitmq-api:
    image: rabbitmq:3-management-alpine
    container_name: rabbitmq-api
    environment:
      - RABBITMQ_DEFAULT_USER=rabbit_user
      - RABBITMQ_DEFAULT_PASS=Rabbit_123
    networks:
      - api-dev

  catalogdata-api:
    image: mcr.microsoft.com/mssql/server:2017-latest
    container_name: catalogdata-api
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=Catalog_123
    networks:
      - api-dev
    
  basketdata-api:
    image: mcr.microsoft.com/mssql/server:2017-latest
    container_name: basketdata-api
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=Basket_123
    networks:
      - api-dev
    
  userdata-api:
    image: mcr.microsoft.com/mssql/server:2017-latest
    container_name: userdata-api
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=User_123
    networks:
      - api-dev
    
  setupdata-api:
    image: mcr.microsoft.com/mssql/server:2017-latest
    container_name: setupdata-api
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=Setup_123
    networks:
      - api-dev
      
  web-gateway-api:
    image: ${REGISTRY:-tiger}/web.gateway:${PLATFORM:-linux}-${TAG:-latest}
    container_name: web-gateway-api
    environment:
      - ASPNETCORE_ENVIRONMENT=Development
    networks:
      - api-dev
    build:
      context: .
      dockerfile: src/gateways/web/Dockerfile
    ports:
      - "5001:5001"
    
  catalog-api:
    image: ${REGISTRY:-tiger}/catalog.api:${PLATFORM:-linux}-${TAG:-latest}
    container_name: catalog-api
    environment:
      - ASPNETCORE_ENVIRONMENT=Development
    networks:
      - api-dev
    build:
      context: .
      dockerfile: src/services/catalog.api/Dockerfile
    depends_on:
      - catalogdata-api
      - rabbitmq-api
      
  basket-api:
    image: ${REGISTRY:-tiger}/basket.api:${PLATFORM:-linux}-${TAG:-latest}
    container_name: basket-api
    environment:
      - ASPNETCORE_ENVIRONMENT=Development
    networks:
      - api-dev
    build:
      context: .
      dockerfile: src/services/basket.api/Dockerfile
    depends_on:
      - basketdata-api
      - rabbitmq-api
      
  user-api:
    image: ${REGISTRY:-tiger}/user.api:${PLATFORM:-linux}-${TAG:-latest}
    container_name: user-api
    environment:
      - ASPNETCORE_ENVIRONMENT=Development
    networks:
      - api-dev
    build:
      context: .
      dockerfile: src/services/user.api/Dockerfile
    depends_on:
      - userdata-api
      - rabbitmq-api
      
  setup-api:
    image: ${REGISTRY:-tiger}/setup.api:${PLATFORM:-linux}-${TAG:-latest}
    container_name: setup-api
    environment:
      - ASPNETCORE_ENVIRONMENT=Development
    networks:
      - api-dev
    build:
      context: .
      dockerfile: src/services/setup.api/Dockerfile
    depends_on:
      - setupdata-api
      - rabbitmq-api
  
networks:
  api-dev:

And this one of net core Dockerfiles:

FROM mcr.microsoft.com/dotnet/core/sdk:3.1 AS build

WORKDIR /app

COPY "src/utils/infrastructure/infrastructure.csproj" "src/utils/infrastructure/infrastructure.csproj"
COPY "src/utils/models/models.csproj" "src/utils/models/models.csproj"
COPY "src/utils/utilities/utilities.csproj" "src/utils/utilities/utilities.csproj"
COPY "src/services/user.api/user.api.csproj" "src/services/user.api/user.api.csproj"

RUN dotnet restore src/services/user.api/user.api.csproj

COPY . .
WORKDIR src/services/user.api

RUN dotnet publish /p:Configuration=Release -o /app --no-restore

FROM mcr.microsoft.com/dotnet/core/aspnet:3.1
WORKDIR /app
COPY --from=build /app .
ENTRYPOINT ["dotnet", "user.api.dll"]

[croblesm]

Hello there,

This issue could be caused by your containers taking some time to get SQL Server started. Of course, It depends of your Docker daemon resources and configuration. But as an example in my case, I had the same problem experiencing variations with the SQL Server startup time from 10 to 20 seconds.

I would suggest using some kind of "wait" flag, for your application to connect after X amount of seconds. Just to make sure your SQL Server containers are up at the time the app is connecting.

Cheers,

[kKen94]

Hi,

if you tell the truth it should be enough try with sqlOptions.EnableRetryOnFailure(10, TimeSpan.FromSeconds(5), null);

Anyway, I upgraded from sql-2017 to sql-2019

[croblesm]

EnableRetryOnFailure

Cool!! That's pretty much what I do with Flyway to perform database migrations 😄

[SolveIT-PNG]

if you tell the truth it should be enough try with sqlOptions.EnableRetryOnFailure(10, TimeSpan.FromSeconds(5), null);

I found that the this error (18456) was not included in the list of errors that get retried; I had to add it like this: EnableRetryOnFailure(10, TimeSpan.FromSeconds(5), new List<int> { 18456 })

ETA: Sorry, to clarify: this is for the "Login failed" error

[ghost]

Can confirm here aswell.

I am using testcontainers in nodejs to start up a MSSQL container before tests. Testcontainers declared the container ready before it was, and therefore the server's connection failed even though I was puzzling how 3 seconds after I was able to login with sqlcmd with the same credentials.

Just FYI for anyone using testcontainers for this purpose.

Solution is to use wait strategies

  beforeAll(async () => {
    container = await new GenericContainer('mcr.microsoft.com/mssql/server:2019-latest')
      .withEnv('ACCEPT_EULA', 'Y')
      .withEnv('MSSQL_SA_PASSWORD', 'solvency_SPIKE_234')
      .withExposedPorts(1433)
      .withWaitStrategy(Wait.forLogMessage('The tempdb database has 2 data file(s).'))
      .start()
    await connectionTest(container.getMappedPort(1433));
    httpServer = await server();
  });