Latest Docker image missing /opt/mssql-tools directory

[vickism]

Tried on Ubuntu and Ubuntu on WSL.
When using the image mcr.microsoft.com/mssql/server:2022-latest, we are unable to call /opt/mssql-tools/bin/sqlcmd

Container: mcr.microsoft.com/mssql/server:2022-latest
Ubuntu on WLS: Ubuntu 22.04.3 LTS

Further inspection show that mssql-tools has been replaced by mssql-tools18, in the image we have.

Steps to Replicate:
docker run --restart=unless-stopped -e \"ACCEPT_EULA=Y\" -e \"MSSQL_SA_PASSWORD=****\" -p 1433:1433 -d --name MyServer mcr.microsoft.com/mssql/server:2022-latest
docker exec MyServer /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P \"*****\" -Q \"CREATE DATABASE myDatabase\"

The above command is part of our CI pipeline and started failing today.

[sonocoscott]

Our CI Pipeline has started doing this:

[testcontainers.org 00:00:23.12] Docker image mcr.microsoft.com/mssql/server:2022-latest created
[testcontainers.org 00:00:23.29] Docker container bec9e0ab1f03 created
[testcontainers.org 00:00:23.29] Start Docker container bec9e0ab1f03
[testcontainers.org 00:00:23.51] Wait for Docker container bec9e0ab1f03 to complete readiness checks
[testcontainers.org 00:00:23.52] Execute "/opt/mssql-tools/bin/sqlcmd -Q SELECT 1;" at Docker container bec9e0ab1f03
[testcontainers.org 00:00:24.60] Execute "/opt/mssql-tools/bin/sqlcmd -Q SELECT 1;" at Docker container bec9e0ab1f03
[testcontainers.org 00:00:25.68] Execute "/opt/mssql-tools/bin/sqlcmd -Q SELECT 1;" at Docker container bec9e0ab1f03
[testcontainers.org 00:00:26.77] Execute "/opt/mssql-tools/bin/sqlcmd -Q SELECT 1;" at Docker container bec9e0ab1f03
[testcontainers.org 00:00:27.87] Execute "/opt/mssql-tools/bin/sqlcmd -Q SELECT 1;" at Docker container bec9e0ab1f03
[testcontainers.org 00:00:28.99] Execute "/opt/mssql-tools/bin/sqlcmd -Q SELECT 1;" at Docker container bec9e0ab1f03
[testcontainers.org 00:00:30.15] Execute "/opt/mssql-tools/bin/sqlcmd -Q SELECT 1;" at Docker container bec9e0ab1f03
(repeat forever)

This was working yesterday, and changing it from latest to mcr.microsoft.com/mssql/server:2022-CU13-ubuntu-22.04 fixes the issue

[sputnik-adam]

Our CI pipeline is suddenly doing the same thing as of today. Will revert to the previous build until Microsoft remember what testing is.

[cmbodley]

I am getting this as well all our ci/cd builds fail. Trying an earlier image like SQL server 2017 works if that is an option in the mean time.

[NicosNet]

I fixed the problem by changing my healthcheck path to /opt/mssql-tools18/bin/sqlcmd

And i needed to add the -C to trust the certificate

healthcheck:
      test: /opt/mssql-tools18/bin/sqlcmd -C -S localhost -U sa -P "$${SA_PASSWORD}" -Q "SELECT 1" -b -o /dev/null
      interval: 10s
      timeout: 3s
      retries: 10
      start_period: 10s

[netdragonboberb]

Thank you all for the workaround. I imagine I was not alone being up part of the night unblocking QA by temporarily disabling CI tests as a build gate so QA could move forward.. Which would have never been a permanent solution, and we still would have been unable to release without the CI tests working and only having manual testing. These workarounds are great and we are confident implementing these will allow us to move forward with the release.

[benkemt]

As a workaround i use server:2022-CU13-ubuntu-20.04 instead of server:2022-latest

[adrinr]

As a workaround i use server:2022-CU13-ubuntu-20.04 instead of server:2022-latest

Same. This is a breaking change and I assume it should be fixed, so changing the sqlcmd path might break again if they change the path back to revert the breaking change.
This new path did not exist on previous images:

[rwestMSFT]

We have updated our documentation to say the following:

In SQL Server 2022 (16.x) CU 14 and later versions, container images include the new mssql-tools18 package. The previous directory /opt/mssql-tools/bin is being phased out. The new directory for Microsoft ODBC 18 tools is /opt/mssql-tools18/bin, aligning with the latest tools offering. For more information about changes and security enhancements, see ODBC Driver 18.0 for SQL Server Released.

[eerhardt]

We have updated our documentation to say the following:

In SQL Server 2022 (16.x) CU 14 and later versions, container images include the new mssql-tools18 package. The previous directory /opt/mssql-tools/bin is being phased out. The new directory for Microsoft ODBC 18 tools is /opt/mssql-tools18/bin, aligning with the latest tools offering. For more information about changes and security enhancements, see ODBC Driver 18.0 for SQL Server Released.

Do you plan on fixing the examples in this repo?

mssql-docker/linux/preview/examples/mssql-customize/configure-db.sh

Lines 12 to 25 in 264d720

	while [[ $DBSTATUS -ne 0 ]] && [[ $i -lt 60 ]] && [[ $ERRCODE -ne 0 ]]; do
	i=$i+1
	DBSTATUS=$(/opt/mssql-tools/bin/sqlcmd -h -1 -t 1 -U sa -P $SA_PASSWORD -Q "SET NOCOUNT ON; Select SUM(state) from sys.databases")
	ERRCODE=$?
	sleep 1
	done
	if [ $DBSTATUS -ne 0 ] OR [ $ERRCODE -ne 0 ]; then
	echo "SQL Server took more than 60 seconds to start up or one or more databases are not in an ONLINE state"
	exit 1
	fi
	# Run the setup script to create the DB and the schema in the DB
	/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $SA_PASSWORD -d master -i setup.sql

[cloudshiftchris]

...theres this thing called a "symlink" that is useful to maintain compatibility, consistent with the "phasing out" position...

[richlander]

This looks like an (very) unnecessary break and doesn't fit with the cloud-native paradigm/principles. It should be reverted or apply a symlink as aptly suggested by @cloudshiftchris.

Also, was there an announcement for this break ahead of time? If not, that's something to consider for next time. Again, breaks should never be a surprise. If this break was accidental, then the comment by @eerhardt is quite relevant.

#892 (comment)

[engenb]

Is it sensible to put these commands on the bash path?

I agree. If sqlcmd was on the bash path, this wouldn't be an issue and no one would care what the physical location of the binary is. Is this an option? Presumably, we'll be right back here again when mssql-tools19 is released.

[BenjaminQuandt]

Hi,

i don't get that this should be the solution. Some ppl like me might have to test our software with a mssql server as our customers want to use them for some reason...

so i have to get a container ready and filled with data for my tests. to do this i have to use the mssql cmd.
why do you think it is cool to push out a breaking change like that, that his everyone who has to use theis software...

ist is not that hard to set a fallback from /opt/mssql-tools/ to the new /opt/mssql-tools18/

i don't careifs it is tools version 18, 19 or 20. you can easaly swap there to the newest version and let the option to take a specific one if someone need it.

and btw you still have this on dockerhub....

docker exec -it <container_id|container_name> /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <your_password>

[cyptus]

We have updated our documentation to say the following:

In SQL Server 2022 (16.x) CU 14 and later versions, container images include the new mssql-tools18 package. The previous directory /opt/mssql-tools/bin is being phased out. The new directory for Microsoft ODBC 18 tools is /opt/mssql-tools18/bin, aligning with the latest tools offering. For more information about changes and security enhancements, see ODBC Driver 18.0 for SQL Server Released.

It's not just the versions after 2022 that are affected. 2019-latest has also been changed to mssql-tools18.

beside that, the new tool in 2019-latest does not trust the localhost self signed cert by default, thats why you may need to add "-C" flag to the call, like:

docker exec MyServer /opt/mssql-tools18/bin/sqlcmd -C -S localhost ...

see https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility?view=sql-server-ver16&tabs=go%2Cwindows&pivots=cs1-bash#breaking-changes-from-sqlcmd-odbc

"Breaking changes from sqlcmd (ODBC)": If -N and -C aren't provided, sqlcmd negotiates authentication with the server without validating the server certificate.

this change did break all our CI runs

[cezarlamann]

Well, as a workaround, for anyone who would like to have this symlink to /usr/bin being done for you, and not to worry when the "19", "20", or "N" versions come around, you can add sqlcmd (and bcp as well, if needed) manually to /usr/bin using something like this:

ln -sf "$(find / -iname "sqlcmd" -exec realpath {} \; -quit)" /usr/bin/sqlcmd

I'm doing something similar here.