Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Error when connecting to SQL Server in docker container

Tags:

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.
like image 646
Justin Lessard Avatar asked Apr 01 '19 20:04

Justin Lessard


2 Answers

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.

like image 119
Mr Moose Avatar answered Oct 15 '22 09:10

Mr Moose


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;

like image 29
SilentTremor Avatar answered Oct 15 '22 11:10

SilentTremor