Path to sqlcmd binary executable has changed

[rapolas-oag]

For container setup we used /opt/mssql-tools/bin/sqlcmd to create needed Server Objects, like Linked server as part of pre-deployment.
After changing version to FROM mcr.microsoft.com/mssql/server:2019-latest this binary executable is not accessible in that location and all pre-deployment and those scripts were left not executed which led to failing deployments.

Checked the latest version and binary executable is under ```/opt/mssql-tools18/bin/sqlcmd``

Once reverted to FROM mcr.microsoft.com/mssql/server:2019-CU27-ubuntu-20.04 same setup worked without issues and binary executable was in expected folder.

[Epimetheus89]

I have the same issue after upgrading to mcr.microsoft.com/mssql/server:2022-latest.

[rapolas-oag]

@Epimetheus89 , think this is the new default path, according Docker HUB page:

Note Starting with SQL Server 2022 CU 14, we are updating SQL Server 2022 container images to include the new mssql-tools18 package. With the introduction of SQL Server 2022 CU 14, and in all future container images, the previous directory /opt/mssql-tools/bin will be phased out. The new directory for Microsoft ODBC 18 tools will be '/opt/mssql-tools18/bin', aligning with the latest tools offering. For additional details on all the changes and security enhancements, please refer to the blog post [ODBC Driver 18.0 for SQL Server Released - Microsoft Community Hub⁠](https://techcommunity.microsoft.com/t5/sql-server-blog/odbc-driver-18-0-for-sql-server-released/ba-p/3169228). This new iteration, ODBC driver version 18, is designed with an ‘encryption-first’ approach, ensuring that utilities like sqlcmd and bcp that utilize the Microsoft ODBC driver will operate under the ‘secure by default’ principle. Users who wish to disable encryption will need to do so explicitly.

For example when trying to connect using the sqlcmd tool, the -N option is available with [s|m|o] parameters, where ‘s’ stands for strict, ‘m’ for mandatory, and ‘o’ for optional. The default setting is mandatory. For more information refer : [Connecting with sqlcmd - ODBC Driver for SQL Server⁠](https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/connecting-with-sqlcmd?view=sql-server-ver16) .

Although they are talking about SQL 2022, but seems that's the way now. Saw that TestContainers SQL Server updated their lib as well to search for sqlcmd using wildcards instead of fixed path.

[sharpjs]

This nasty surprise could have been avoided if the image had a /usr/bin/sqlcmd symbolic link. Then the actual path of the binary wouldn't matter.

[cezarlamann]

@Epimetheus89 and @rapolas-oag, if you'd like, 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.

[rapolas-oag]

@cezarlamann thanks, but as my project is flexible at the moment for this kind of changes, I amended scripts to new path.