Issue connecting to SQL Server docker instance with ignition?

Ignition
1

I am new to docker so I am probably missing something silly.

Here is my compose file -

services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2019-latest
    container_name: sql_server
    ports:
      - "1435:1433"
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=SomeStrong!Passw0rd
    networks:
      - app-network
    volumes:
      - sql_data:/var/opt/mssql

  ignition:
    image: inductiveautomation/ignition:8.1.43
    container_name: ignition
    ports:
      - "8089:8088"
    depends_on: # Ensures SQL Server starts first
      - sqlserver
    environment:
      GATEWAY_ADMIN_PASSWORD: password
      IGNITION_EDITION: standard
      ACCEPT_IGNITION_EULA: "Y"
    networks:
      - app-network
    volumes:
      - gateway-data:/usr/local/bin/ignition/data

networks:
  app-network:
    driver: bridge

volumes:
  sql_data:
  gateway-data:

I am able to see and access my Ignition gateway.

I am able to login to this SQL Server instance via the SSMS on my local computer by going to 127.0.0.1,1435. I do need to trust server certificate but otherwise works.

When I try to connect to SQL Server via Ignition by making a database connection I keep getting
java.sql.SQLException: Cannot create PoolableConnectionFactory (The TCP/IP connection to the host localhost, port 1435 has failed. Error: "Connection refused. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".)

I tried adding the trustServerCertificate=true to the connection string but still cannot connect.

I don't know what else to try or do. Any ideas?

2

What host name are you using in your gateway's DB connection URL? (Should be the container name, not localhost.)

1 Like
3

I changed my container_name and added a hostname like

container_name: sqlserver
hostname: sqlserver

My JBDC conn string is
jdbc:sqlserver://sqlserver\MSSQLSERVER:1435

I am still getting the same error regarding TCP/IP.

I did notice through SSMS I need to do 127.0.0.1, 1435, trying sqlserver or sqlserver,1435 does not reach it so I am curious if it is a network thing. I have not configured the app-network at all outside of making it.

4

Not sure. But URLs are not supposed to have backslashes.

5

On ours we use

jdbc:sqlserver://localhost\MSSQLSERVER:1433

Yours should end with 1435

With the extra connection settings of

databaseName=NameOfDatabase;trustServerCertificate=true

1 Like
6

Try with only jdbc:sqlserver://sqlserver:1435

Make sure to configure the database name in the extra connection properties

1 Like
7

I figured it out. I stopped the SQL Service running on my local computer. I also have Ignition running locally and figured I could try connecting from that gateway. That worked - with a
jdbc:sqlserver://localhost\MSSQLSERVER:1435 and trustServerCertificate=true.

However the above was not working with my dockerized version of SQL Server. I know @pturmel and mentioned using the name, so I tried jdbc:sqlserver://sqlserver\MSSQLSERVER:1435 and jdbc:sqlserver://sqlserver:1435 but no dice.

I took a guess that this part

    ports:
      - "1435:1433"

meant from the outside I need to use 1435 but inside the docker network app-network I am using, I can use 1433 and that did - so it ended up being jdbc:sqlserver://sqlserver (without the \MSSQLSERVER - it does not work with this - thanks @pascal.fragnoud )

8

As a general rule with docker instances accessing other docker instances, I have been using the host.docker.internal instead of using localhost as the hostname. This essentially routes the network traffic as if you are querying the base machine for the resource, which then works as docker exposes the ports to the base machine as normal. This works when running MySQL on the same docker as Ignition, and hasn't broken in a couple of years.