In this post I cover the 0A000086 and “command not found” errors that you might encounter with the new version of SQL Server command-line tools, namely sqlcmd and bcp, for Linux in both VMs and containers.
While the latest version of SQL Server command-line tools, based on Microsoft ODBC 18, brings improvements, it also brings some gotchas that can break your automations.
The binaries are not found because they’re in another path
If you look over the installation steps for the SQL Server command-line tools for Linux, you’ll notice that the path where the binaries are located is different in the Microsoft ODBC 18 based command-line tools.
Meaning that instead of finding sqlcmd and bcp in /opt/mssql-tools/bin, they will now be in /opt/mssql-tools18/bin.
This will lead to a “command not found” error or, if you use PowerShell for Linux, you’ll get the following messages:
sqlcmd: The term ‘sqlcmd’ is not recognized as a name of a cmdlet, function, script file, or executable program.
Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
bcp: The term ‘bcp’ is not recognized as a name of a cmdlet, function, script file, or executable program.
Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
The fix
While you do have the option of using the full path of the two binaries, there are more elegant ways of doing this that don’t need you updating all of your scripts.
For Linux VMs
Fortunately, if you follow the installation steps provided by Microsoft, you shouldn’t run into this error on Linux VMs.
Or you can create symbolic links to point to binaries’ new location like so:
| 1 2 | sudo ln -s /opt/mssql-tools18/bin/sqlcmd /usr/local/bin/sqlcmd sudo ln -s /opt/mssql-tools18/bin/bcp /usr/local/bin/bcp |
For SQL Server containers
When using SQL Server containers starting with versions 2019 CU28 and 2022 CU14.
Connect to the container as root.
- Docker
| 1 | docker exec -u 0 -it [YourContainerName] "bash" |
- Podman
| 1 | podman exec -u 0 -it [YourContainerName] "bash" |
And create the two symlinks.
| 1 2 | ln -s /opt/mssql-tools18/bin/sqlcmd /usr/local/bin/sqlcmd ln -s /opt/mssql-tools18/bin/bcp /usr/local/bin/bcp |
The TLS police is out for blood – error:0A000086:SSL
Look, I’m all for security, but if an attacker is already in a position to intercept the traffic between your client application and your SQL Server instance, then you have bigger issues to worry about.
As with the newer releases of dbatools, and the new Go-based sqlcmd, both Microsoft ODBC 18 based sqlcmd and bcp now expect you to have your instances set up for encrypted connections with certificates signed by a CA (in this economy?).
If you’re trying to connect to instances that are not properly set up for TLS, then you’ll get the 0A000086:SSL error.
- This is how the full error message looks like for sqlcmd:
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate].
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722.
- And to this error for bcp:
SQLState = 08001, NativeError = -1
Error = [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate]
SQLState = 08001, NativeError = -1
Error = [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722
The fix
This applies to both SQL Server command-line tools on Linux VMs as well as the one running on the new SQL Server containers.
If you don’t want to configure TLS with a valid CA-signed certificate at the moment, you can add some optional switches to sqlcmd and bcp so that they work with self-signed certificates.
In sqlcmd‘s case, the switch is -C (that’s uppercase C).
Here’s an example:
- original sqlcmd command
| 1 | sqlcmd -S localhost -U sa -P S0MeP4sS! |
- new sqlcmd command
| 1 | sqlcmd -S localhost -U sa -P S0MeP4sS! -C |
In bcp‘s case, it’s -u (lowercase u).
Here’s an example:
- original bcp command
| 1 | bcp sys.databases out /tmp/databases.csv -w -t"|" -S localhost -U sa -P S0MeP4sS! -d master -k |
- new bcp command
| 1 | bcp sys.databases out /tmp/databases.csv -w -t"|" -S localhost -U sa -P S0MeP4sS! -d master -k -u |
Conclusion
Updated tools are always welcome, but, in this case, the update to mssql-tools for Linux brings a few changes that end up causing 0A000086 and “command not found” errors.
The information in this post should help you sort out both 0A000086 and “command not found” errors that come with the new mssql-tools for Linux