Update:
This issue seems to be mssql2016 related. We have connected to a 2019 box and run dbal queries with no problems. We can also run queries on a dockerised 2017 instance. We still can't completely rule out a config issue though
Attempts to connect with Python and nodeJS work. We can also connect from php outside of docker.
From the experiments below, it looks like we can find the database, but it isn't responding. If we try and connect to a nonesnse name we get a network error database not found. If we use the ip address we get a timeout from an unresponsive server.
Connections to a sqlserver database in another docker container in the same stack work fine.
We can resolve the server name (You will need to install dnsutils in the container to try this)
running a dbal query from a bare bones symfony app inside a docker container (doctrine installed)
using this docker-compose.yml config:
php:
container_name: <container_name>
build: # we are specifying the version in the Dockerfile
context: .
target: php
dockerfile: Dockerfile
# network: host
volumes:
- .:/srv/ioda
working_dir: /srv/ioda
ports:
- "8000:8000"
# extra_hosts:
# - "nihrdw:192.168.170.29"
with doctrine installed
and this connection url (.env):
DATABASE_URL="sqlsrv://<username>:<password>@192.168.170.29:1433/INFONIHR_DW?serverVersion=13.00.6430.9&charset=UTF-8&driverOptions[TrustServerCertificate]=1"
produces this response:
docker exec <container_name> symfony console dbal:run-sql 'select 1'
[critical] Error thrown while running command "dbal:run-sql 'select 1'". Message: "An exception occurred in the driver: SQLSTATE [08001, 10054]: [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2746
SQLSTATE [08001, 10054]: [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection"
In ExceptionConverter.php line 67:
An exception occurred in the driver: SQLSTATE [08001, 10054]: [Microsoft][O
DBC Driver 18 for SQL Server]TCP Provider: Error code 0x2746
SQLSTATE [08001, 10054]: [Microsoft][ODBC Driver 18 for SQL Server]Client u
nable to establish connection
In Error.php line 42:
SQLSTATE [08001, 10054]: [Microsoft][ODBC Driver 18 for SQL Server]TCP Prov
ider: Error code 0x2746
SQLSTATE [08001, 10054]: [Microsoft][ODBC Driver 18 for SQL Server]Client u
nable to establish connection
dbal:run-sql [--connection CONNECTION] [--depth DEPTH] [--force-fetch] [--] <sql>
using the below URL with a gibberish database name produces a different result
DATABASE_URL="sqlsrv://<username>:<password>@wibble:1433/INFONIHR_DW?serverVersion=13.00.6430.9&charset=UTF-8&driverOptions[TrustServerCertificate]=1"
produces a different result: the TCP Provider: Error code is now '0x2AFA':
$ docker exec <container name> symfony console dbal:run-sql 'select 1'
SQLSTATE [08001, 11002]: [Microsoft][ODBC Driver 18 for SQL Server]A networ
k-related or instance-specific error has occurred while establishing a conn
ection to wibble,1433. Server is not found or not accessible. Check if inst
ance name is correct and if SQL Server is configured to allow remote connec
tions. For more information see SQL Server Books Online.
In Error.php line 42:
SQLSTATE [HYT00, 0]: [Microsoft][ODBC Driver 18 for SQL Server]Login timeou
t expired
SQLSTATE [08001, 11002]: [Microsoft][ODBC Driver 18 for SQL Server]TCP Prov
ider: Error code 0x2AFA
SQLSTATE [08001, 11002]: [Microsoft][ODBC Driver 18 for SQL Server]A networ
k-related or instance-specific error has occurred while establishing a conn
ection to wibble,1433. Server is not found or not accessible. Check if inst
ance name is correct and if SQL Server is configured to allow remote connec
tions. For more information see SQL Server Books Online.
Error 0x2AFA means the host is unreachable
Error 0x2746 means the host was reached but the client timed out due to non-response
also tried switching msodbcsql18 to msodbcsql17
still 0x2746
attempts to connect using isql
set up the datasource by adding the floowing to etc/odbc.ini:
[MSSQLServer]
Driver = ODBC Driver 18 for SQL Server
Trace = No
Server = 192.168.170.29
Port = 1433
User = <usename>
Password = <password>
run this:
$ docker exec <container name> isql -v MSSQLServer
which produces:
[ISQL]ERROR: Could not SQLConnect
[08001][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2746
[08001][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwl
ink/?linkid=2226722
Error code 0x2746 means we found a server on the network but timed out...
changing the server option above to an arbitrary string produced a different response:
docker exec <container name> isql -v MSSQLServer
gives us this:
[S1T00][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired
[08001][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2AF9
[08001][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to the moon. Server is no
t 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.
[ISQL]ERROR: Could not SQLConnect
This error code means we couldn't find the destination on the network
We've established this was due to an unsupported SQL Server server version and the client not indicating this in its error handling. The SQL Server instance in question will be upgraded.
Update: This issue seems to be mssql2016 related. We have connected to a 2019 box and run dbal queries with no problems. We can also run queries on a dockerised 2017 instance. We still can't completely rule out a config issue though
Attempts to connect with Python and nodeJS work. We can also connect from php outside of docker. From the experiments below, it looks like we can find the database, but it isn't responding. If we try and connect to a nonesnse name we get a network error database not found. If we use the ip address we get a timeout from an unresponsive server.
Connections to a sqlserver database in another docker container in the same stack work fine.
We can resolve the server name (You will need to install dnsutils in the container to try this)
$ docker exec nslookup srv01647
Server: 127.0.0.11
Address: 127.0.0.11#53
Non-authoritative answer:
Name: srv01647.soton.ac.uk Address: 192.168.170.29
We can also ping it.
running a dbal query from a bare bones symfony app inside a docker container (doctrine installed)
using this docker-compose.yml config:
with doctrine installed
and this connection url (.env):
DATABASE_URL="sqlsrv://<username>:<password>@192.168.170.29:1433/INFONIHR_DW?serverVersion=13.00.6430.9&charset=UTF-8&driverOptions[TrustServerCertificate]=1"
produces this response:
using the below URL with a gibberish database name produces a different result
DATABASE_URL="sqlsrv://<username>:<password>@wibble:1433/INFONIHR_DW?serverVersion=13.00.6430.9&charset=UTF-8&driverOptions[TrustServerCertificate]=1"
produces a different result: the TCP Provider: Error code is now '0x2AFA':
Error 0x2AFA means the host is unreachable Error 0x2746 means the host was reached but the client timed out due to non-response
installed openssl as per this https://stackoverflow.com/questions/74708033/error-code-0x2746-10054-when-trying-to-connect-to-sql-server-2014-via-odbc-fro
also tried switching msodbcsql18 to msodbcsql17 still 0x2746
attempts to connect using isql set up the datasource by adding the floowing to etc/odbc.ini:
run this:
$ docker exec <container name> isql -v MSSQLServer
which produces:Error code 0x2746 means we found a server on the network but timed out... changing the server option above to an arbitrary string produced a different response:
docker exec <container name> isql -v MSSQLServer
gives us this:This error code means we couldn't find the destination on the network