Error on SQL Server Integration

Server
1

Hi,

I just tried to connect a legacy mssql server 2008 R2 database to a docker container version of LinceBI on another LAN VM and received
ConnectionServiceImpl.ERROR_0009

lincebi_sqlserver_error

I installed the ODBC Drivers 17 for unix systems from microsoft on the vm which worked on a direct connection from vm to database host

Right now I do not know how to troubeshoot this issue
Does one of you know how to fix it or can help out?

Thanks in advance

2

According to the compatibility matrix the latest JDBC version you can use for your environment is 7.2.2, however with LinceBI we currently include the 9.2.1 version.

That may be the cause of your problem, anyway check in the Tomcat logs if you see a more detailed error.

In your case you have two options, extend our Docker image and apply the appropriate changes or use the init system provided by our image to replace the JDBC driver during startup.

To use the init system you can mount in the container a shell script in /etc/biserver.init.d/30_mssql-jdbc.sh with the following content (remember to give execution permission to the file):

#!/bin/sh

set -eu
export LC_ALL=C

RELEASE='7.2.2.jre8'
URL="https://github.com/microsoft/mssql-jdbc/releases/download/v${RELEASE%.jre*}/mssql-jdbc-${RELEASE:?}.jar"

cd "${CATALINA_BASE:?}"/lib/

rm -f ./mssql-jdbc-*.jar
curl -fsSLO "${URL:?}"
3

Yes, I have encountered compatibility issues with the version once in the past, so that will probably be it.

Thanks again for the help.

If either way doesn’t work I’ll write again,
otherwise have a nice day :+1:

4

Hi,

I am afraid I have to ask another time.

I went for your suggestion and included the install script to the container image.
The script is executable and is also triggered after a restart, as expected.

The tomcat logs revealed, that there isno sutiable driver (see screenshot).
lincebi_sql_no_driver

Then I tried to install the drivers on the container itself again which results in an error when trying to install the dependencies

I can only post one picture per post so her the package I wanted to install and its dependencies:

unixodbc - odbcinst1debian2 libodbc1

Any idea on how to proceed?

ps: I logged into the container as user “biserver” and root with the same result

5

It looks like you are using Impala to create the connection and that JDBC is also not included by default. Could you share a screenshot of how you are creating the connection inside LinceBI?

6

The screenshot is taken from
/var/lib/biserver/tomcat/logs/pentaho.log
inside the docker container

Could you give me a hand with finding the right log if this is not it?

I did not install anything differing from the default docker image I downloaded initially except for the shell script that you gave me above

Regarding linceBI I tried JDBC, image following

lincebi_frontend_database_integration
lincebi_frontend_database_integration702×650 29.1 KB

7

I can’t reproduce it in a local environment but LinceBI seems to be trying to connect via Impala.

mssql
mssql700×645 34 KB

Try choosing the generic database type and entering jdbc:sqlserver://SERVER_IP;databaseName=DATABASE_NAME as connection URL and com.microsoft.sqlserver.jdbc.SQLServerDriver as driver class name.

generic
generic693×636 42.8 KB

8

Hi,

thanks for the suggestion. This worked.

Another problem occured while trying this approach, which was that the Servers TLS Version (1) was not accepted by the Java module with Java 1.8 and higher.

For anyone facing similar issues:
I fixed this by deleting TLS V1 from the “disabledAlgorithms” in Java.security like suggested in

You can find the file in /usr/lib/jvm/zulu8-ca-amd64/jre/lib/security
Remember to log in as root

The other option was to update the servers TLS Version, which would obviously be the better solution, so this will probably be the approach, if the tool will be exposed to the internet.