[microsoft_sqlserver] Failing test daily: system test: errorlog in microsoft_sqlserver.log

[mrodm]

• Stack version: 8.16.0-SNAPSHOT
• Package: microsoft_sqlserver
• Failing test: system test: errorlog (variant MSSQL_2019-latest)
• DataStream: log
• Owners:
  • @elastic/obs-infraobs-integrations

Failure:

Error: error running package system tests: could not complete test run: could not setup service: service is unhealthy: timeout waiting for healthy container

Running docker inspect with the mssql container, the healthcheck for that container fails with this error:

                    {
                        "Start": "2024-08-26T18:42:09.028716844+02:00",
                        "End": "2024-08-26T18:42:09.063568887+02:00",
                        "ExitCode": 127,
                        "Output": "/bin/sh: 1: /opt/mssql-tools/bin/sqlcmd: not found\n"
                    }

Some of the latest builds where it failed:

• https://buildkite.com/elastic/integrations/builds/15011
• https://buildkite.com/elastic/integrations/builds/14993

[shmsr]

Thanks for sharing. I'll fix this one!

Reason for failure: See this.

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

But this note is also outdated in DockerHub as it just mentions that SQL Server 2022 is affected where the path has changed from /opt/mssql-tools/bin to /opt/mssql-tools18/bin. See this. The official doc says 2019-latest (we are currently using) is affected as well.

[shmsr]

Okay, the failure wasn't just this. After I fixed the issue, the HEALTHCHECK was still failing. In the end, I narrowed down the bug to sqlcmd.

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⁠ . To connect without encryption, the sample command would be:

sqlcmd -S <ip address,port> -U <login_name> -P -No

It is important to use -N with -o to make the HEALTHCHECK pass. There are 2 bugs, not just 1 here.

[anil-elastic]

LGTM!