C3P0 failed to connect to SQL Server on Docker

Environments Linux
,
1

Hello,

I’m used to connect to multi tenants databases MySQL, H2 and PostgreSQL on Travis CI through connection pool C3P0 with Hibernate.

My test suites are stable and I have encountered no problem with these databases, popping them on Docker using standard ports and accessing them concurrently via JDBC.

Now I want to access an additional database, SQL Server. I tested this locally and it works fine:

FROM mcr.microsoft.com/mssql/server:2017-latest
EXPOSE 1433

  - docker run --name jsql-sqlserver 
    --publish 3308:1433 
    -e ACCEPT_EULA=Y 
    -e SA_PASSWORD=my-secret-pw
    -d sqlserver

But I’m trying to integrate it since yesterday and I don’t manage to make it work on Travis CI,

  • I tried with all databases disabled except SQL Server
  • I tried to run on single JDK instead of five
  • also tried on non default database port 3308 instead of 1433

The jobs are failing with network errors Connection refused:

Do you know if there are limitations on using SQL Server on Travis CI?
Is there a configuration allowed to run this database on the platform?

[logback.xml] 2020-03-15 19:11:36 [C3P0PooledConnectionPoolManager[identityToken->z8kflta81q19z49dz0zfl|10af5f22]-HelperThread-#14] WARN c.m.v.resourcepool.BasicResourcePool - com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@1f87ea97 – Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 127.0.0.1, port 3308 has failed. Error: "Connection refused (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.".

2

I also tried to connect directly to the container:

sudo docker exec -it jsql-sqlserver /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "my-secret-pw" -Q 'select @@version'

It works on local, it fails on server with error:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
3

You can run docker logs <container> to get any output from a background container – which should contain any error messages.

4

Thanks @native-api, I already activated logs yesterday without finding new material, until you also suggested it all the same today :grinning:

And I found this in logs, meaning it’s listening also on port 1434 for IP 127.0.0.1, not only on default port 1433.

2020-03-17 12:28:53.89 spid18s     Server is listening on [ 'any' <ipv6> 1433].
2020-03-17 12:28:53.91 spid18s     Server is listening on [ 'any' <ipv4> 1433].
2020-03-17 12:28:53.94 Server      Server is listening on [ ::1 <ipv6> 1434].
2020-03-17 12:28:53.94 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].

So publishing port 1434:1434 made sqlcmd to work:

$ sudo docker exec -it jsql-sqlserver /opt/mssql-tools/bin/sqlcmd -S "tcp:127.0.0.1,1434" -U SA -P "yourStrong(!)Password" -Q "select @@version"    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) 
	Jan 23 2020 21:00:04 
	Copyright (C) 2017 Microsoft Corporation
	Developer Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS)                                                                                                            
(1 rows affected)

But C3P0 continued to fail on 1433 :thinking:

And after a lot of try and errors I managed to stabilize connection also on port 1433:

  • Use image 2019-GDR1-ubuntu-16.04 instead of 2017-latest
  • EXPOSE 1434 1433
  • Add sleep 10s before testing first connection with sqlcmd
  • Use password that complies with sql server password policy

Thank you for your support!