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.

[toppanscott]

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?

https://github.com/microsoft/mssql-docker/blob/264d7201bc6301e31f91d2e9cf3c5ac261906389/linux/preview/examples/mssql-customize/configure-db.sh#L12-L25

[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.

https://github.com/microsoft/mssql-docker/issues/892#issuecomment-2250459631

[alysson-souza]

And I'm guessing this will happen again when version 19 of the driver is released?

[cloudshiftchris]

likely, as there doesn't appear to be a strategy here for deploying new versions, only a "hey, we updated the docs after-the-fact" statement.

[richlander]

The team should document how they will maintain compatibility.

[wilsonwaters]

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

[Anonynym3845937]

Hi, FYI, folks on another github had found this and a further issue related to this: https://github.com/elastic/apm-agent-nodejs/issues/4147

---

When we changed the path, we are hitting the second issue described in the ticket above. Any ideas?

[bblommers]

@Anonynym3845937 You can add a -C to explicitly trust the certificate, and get around the certificate verify failed:self-signed certificate error.

See the answer from @NicosNet in this thread: https://github.com/microsoft/mssql-docker/issues/892#issuecomment-2248045546

[Anonynym3845937]

Thanks @bblommers. We will wait until the next docker image release, to see if these changes are permanent, or will some be retracted.

[amitkh-msft]

Update: the CU 28 for SQL 2019 will also have this change; we are working on the upcoming CUs to see if we can have the path working or have both the tools installed side by side.

I thank you for the feedback and appreciate the suggestions provided here. I regret the inconvenience caused by the update of the mssql-tools package to mssql-tools18 with the release of CU 14 for SQL Server 2022, without prior notice.

To ensure clarity for all, here are the key changes with the tools package update:

1. The tools path has been modified from /opt/mssql-tools/bin to /opt/mssql-tools18/bin. If you’re deploying container images with the latest version, you’ll need to update the path to the new location. We’re exploring various options, including the use of symbolic links (symlinks) for future CU releases, though no final decision has been made yet.

2. The ODBC driver version 18 has been developed with an encryption-first methodology, making encryption mandatory by default. If you prefer the previous optional encryption setting, you can modify this as outlined in the document here ) by using the -No option. For instance, when using sqlcmd, you can connect with the encryption option set to optional with the command: sqlcmd -S <ip address,port> -U <login_name> -P -No

For further details, please consult the official documentation.

We are committed to continuously improving our products and services, and your feedback is crucial in this process. Thank you for your understanding and support.

[alysson-souza]

The documentation still refers to the old path on several steps. Also, Microsoft's example repository code on how to customize sqlserver images is now broken.

[netdragonboberb]

One thing we're doing now that may be helpful for others is that we are now controlling the docker image version we will be using with a configuration variable (and not "latest"). That way we can be the gate that controls when we update this and avoid surprises. This strategy may be helpful to others. There are obviously trade-offs with that, such as that now we have to actively ensure we don't fall too far behind on versions, but the tradeoff is worth it to protect from disruptions.

[sblackstone]

You really should have had the old binary in place for 6 months with a message to stderr warning that this was going to change rather than just pulling the rug on us.

Literally everybody else does this.

[amitkh-msft]

We are working on this to look at options to fixing this in upcoming CUs for both SQL 19 and SQL 22. For now, I want you all to be aware that the CU 28 for SQL 2019 will also have the tools package updated like CU 14 for SQL 22, and you will have to update the path and connection string in case you are not using the encrypted option.

[grieve54706]

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.

[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.

[belgattitude]

For those interested, here's a gist with latest version server:2022-CU16-ubuntu-22.04.

See: https://gist.github.com/belgattitude/9979e5501d72ffa90c9460597dee8dca

Here's a summarized version:

name: example-mssql

services:
  mssql:
    container_name: example-mssql
    image: mcr.microsoft.com/mssql/server:2022-CU16-ubuntu-22.04
    # Only when docker is running as "root".
    cap_add: ['SYS_PTRACE']
    env_file:
      - .env.mssql.development
    environment:
      - HOST_PORT=${MSSQL_DOCKER_HOST_PORT:-1433}
    ports:
      - "${HOST_PORT}:1433"
    networks:
      - example-mssql
    volumes:
      - example-mssql:/var/opt/mssql:rw
    restart: no
    healthcheck:
      test: /opt/mssql-tools18/bin/sqlcmd -S localhost -C -U sa -P "$$MSSQL_SA_PASSWORD" -Q "SELECT 1" -b -o /dev/null
      interval: 1s
      timeout: 30s
      retries: 30
      start_period: 5s

volumes:
  example-mssql:

networks:
  example-mssql:
    driver: bridge
    enable_ipv6: false

[DPIDNB]

For anyone still wondering, the fix is actually very simple. The root cause is that the sqlcmd directory has changed in the latest 2022 and 2019 version from /opt/mssql-tools/bin/sqlcmd to /opt/mssql-tools18/bin/sqlcmd. Referencing the new directory will fix the issue.

This is also mentioned in the microsoft docs at https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver17&tabs=cli&pivots=cs1-bash