Error when connecting to SQL Server in docker container

5
votes

I have an ASP.NET Core WebAPI running inside a Windows Docker container. I'm having trouble connecting to an on premises SQL instance from inside the app.

This is the error message:

(0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception (53): The network path was not found

I found this issue on github, but it doesn't really apply to my situation.

I can make everything work by replacing the instance name with the server local IP address like so:

"ConnectionString_ThrowingError": "Data Source=MySqlInstance; (...)"
"ConnectionString_WorkingFine":   "Data Source=192.168.0.123; (...)"

What is happening here? It seems .NET cannot resolve domain machine hostname.

Other details

  • Using .Net Core 2.2
  • Using Docker Windows container on Windows
  • Tried hosting on both Windows 10 and Windows Server 2019. Both had the exact same error.
  • I can ping the SQL instance from inside the container using either its hostname or local IP.
  • Everything works when I replace the instance name with the local IP address in the connection string.
  • Everything works normally if I run the app in the .NET CLI (not using Docker)
  • The SQL instance I'm trying to reach is not hosted on Docker. It's hosted on another Windows Server on the same local network.
  • I can reach others SQL instance hosted on Azure using DataSource=MyAzureServer.database.windows.net without any issues.
2
c#dockerasp.net-core.net-coredocker-for-windows
Does the DNS Services section of this link provide any guidance? I'm guessing that the server name isn't resolving from inside the container. I know you said you can ping the IP from the container, but maybe it is some combination of DNS and the need to possibly publish ports (1433). - Mr Moose
@MrMoose Not really. The container can resolve the hostname. I suspect there is a deeper issue at play. - Justin Lessard
I know I had issues connecting to named instances on my local machine from a container, but for the life of me, I can't recall what it was. I feel like I had to explicitly defined my connection with the port like <server name>\MyNamedInstance,1433; kind of like this link. If I get a chance, I'll check at home tonight and report back if it looks like it might help. - Mr Moose
Sorry @JustinLessard, my issue was specific to the fact that a named instance on my local machine is configured by default to use dynamic ports. I had to adjust the configuration to ensure it listens on a specific port and that isn't what you are seeing. - Mr Moose
Did you ever get this sorted. I've been thinking about it off and on. I wondered if SQL Server Name resolution was some how different, or whether you weren't using a fully qualified domain name, but I see from your update that you are. This link has some useful things to check around name resolution. It isn't docker specific, but could be useful. Failing that, I'd see what else you can find out about container networking and DNS debugging/investigations you can leverage. - Mr Moose

2 Answers

3
votes

As per my suggestion in the comments, if the server name isn't a fully qualified domain name (FQDN), then it might have issues resolving the server to an IP.

Try adjusting the servername to an FQDN and hopefully, that will resolve it.

Failing that, look at links such as this one to work out name resolution from a SQL Server perspective, and you'll get some other ideas as to how you can troubleshoot this type of issue.

1
votes

Check this docker-composer.yaml, networks and alias sections are important, above setup can be generated with docker commands instead of yaml.

    version: '3.4'

    services:
      some.api:
        image: ${DOCKER_REGISTRY-}someapi
        container_name: some.api
        build:
          context: .
          dockerfile: /Dockerfile
        ports:
          - "40080:80"
          - "44443:443"
        networks:
        - database
      some.db:
        image: microsoft/mssql-server-linux:2017-latest
        container_name: mssql1
        networks:
          database:
            aliases:
              - mssql1
        environment:
          - SA_PASSWORD=Pass@word
          - ACCEPT_EULA=Y
        ports:
          - "5454:1433"
    networks:
  database:
  • Networks are defined to access containers one from another.
  • Alias is used to have a named resource, for above snippet, connection string is:

Server=mssql1;Database=whatever_is_need;User Id=sa;Password=Pass@word;