[SOLVED] connecting SchemaSpy to Microsoft SQL Server on Linux for Docker Engine fails with Connection Failure

connecting SchemaSpy to Microsoft SQL Server on Linux for Docker Engine fails with Connection Failure

I am running my database locally in Microsoft SQL Server on Linux for Docker Engine I can successfully connect to it using mssql-cli -U username -P password -d dbname however I am struggling to connect to it using SchemaSpy e.g.

$ java -jar schemaspy-6.0.0.jar -t mssql -u username -p password -o . -host localhost -port 1433 -db dbname
INFO  - Started Main in 1.183 seconds (JVM running for 1.556)
INFO  - Configuration file not found
INFO  - Starting schema analysis
INFO  - Failed to validate png renderer ':cairo'.  Reverting to default renderer for png.
WARN  - Connection Failure

Any idea what I can do to fix the Connection Failure?

Solution

I started SQL server with this:

docker run --name sqlserver -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=BadPassword1' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest-ubuntu

I'd check that you've given your Docker Desktop enough RAM. When I first started SQL Server with Docker configured for < 2GB RAM, the container started, but I couldn't connect, when I looked in the logs:

docker logs sqlserver

(sqlserver is the name of the exited Docker container)

I saw:

sqlservr: This program requires a machine with at least 2000 megabytes of memory.
/opt/mssql/bin/sqlservr: This program requires a machine with at least 2000 megabytes of memory.

Once I'd updated my Docker Desktop to have 2GB of RAM again (I'd dropped it to 1GB a while ago), it came up fine.

One that was up, I could use nmap localhost -p 1433 to validate that the port was open.

I then checked that SQL Server was up-and-running by starting an interactive shell on the running container:

docker exec -it --rm sqlserver /bin/bash

Inside the interactive shell I could use /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P BadPassword1 to access the SQL server shell. In the mssql shell you have to type what you want, then input GO on its own line to execute everything you've entered.

So, to list the dbs:

SELECT name FROM master.sys.databases;
GO

To list the tables:

SELECT DISTINCT TABLE_NAME FROM information_schema.TABLES;
GO

I then created a DB and a table to run SchemaSpy against:

CREATE DATABASE SchemaSpyTest;
GO;
USE SchemaSpyTest;
GO;
CREATE TABLE TableTest1(id int);
GO

Then, I ran the parameters you listed on SchemaSpy and it didn't work for me either.

Once I'd used the -debug flag, I saw that the mssql setting wasn't enough:

org.schemaspy.model.ConnectionFailure: Failed to connect to database URL [jdbc:microsoft:sqlserver://localhost:1433;databaseName=dbname] Failed to create any of 'com.microsoft.sqlserver.jdbc.SQLServerDriver, com.microsoft.jdbc.sqlserver.SQLServerDriver' driver from driverPath 'C:/Program Files/Microsoft SQL Server 2000 Driver for JDBC/lib/msbase.jar;C:/Program Files/Microsoft SQL Server 2000 Driver for JDBC/lib/mssqlserver.jar;C:/Program Files/Microsoft SQL Server 2000 Driver for JDBC/lib/msutil.jar' with sibling jars no.
Resulting in classpath: empty
There were missing paths in driverPath:
        C
        /Program Files/Microsoft SQL Server 2000 Driver for JDBC/lib/msbase.jar;C
        /Program Files/Microsoft SQL Server 2000 Driver for JDBC/lib/mssqlserver.jar;C
        /Program Files/Microsoft SQL Server 2000 Driver for JDBC/lib/msutil.jar
Use commandline option '-dp' to specify driver location.
If you need to load sibling jars used '-loadjars'
        at org.schemaspy.DbDriverLoader.getConnection(DbDriverLoader.java:101)
        at org.schemaspy.DbDriverLoader.getConnection(DbDriverLoader.java:75)
        at org.schemaspy.service.SqlService.connect(SqlService.java:68)
        at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:186)
        at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:107)
        at org.schemaspy.cli.SchemaSpyRunner.runAnalyzer(SchemaSpyRunner.java:97)
        at org.schemaspy.cli.SchemaSpyRunner.run(SchemaSpyRunner.java:86)
        at org.schemaspy.Main.main(Main.java:48)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:48)
        at org.springframework.boot.loader.Launcher.launch(Launcher.java:87)
        at org.springframework.boot.loader.Launcher.launch(Launcher.java:50)
        at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:51)

So, I downloaded the JDBC driver from https://www.microsoft.com/en-us/download/details.aspx?id=57782 and unarchived it in the same folder as my other scripts.

I was then able to work out the right combination of parameters based on the docs:

java -jar schemaspy-6.0.0.jar -t mssql05 -u sa -p BadPassword1 -o . -host localhost -port 1433 -db SchemaSpyTest -debug -dp ./sqljdbc_7.2/enu/mssql-jdbc-7.2.2.jre8.jar

The dp flag links to the JAR file within the Microsoft SQL Server JDBC driver.

After that, it worked perfectly.