Help: I can't connect with my SQL Server database

[rscarrasco]

I'm trying to connect to a SQL Server database, but SchemaSpy attempts to connect fail.

My config file is as follows:

schemaspy.t=mssql
schemaspy.dp=/home/foo/bin/jars/mssql-jdbc-7.0.0.jre8.jar
schemaspy.host=dbms.foo.com
schemaspy.port=1433
schemaspy.db=FooBar
schemaspy.u=foo
schemaspy.p=pass!
schemaspy.o=/home/foo/gen/schemaspy/
schemaspy.schemas=dbo, AnotherSchema

Expected Behavior

I am expecting that SchemaSpy will generate a set of HTML pages at /home/foo/gen/schemaspy/, concerning both dbo and AnotherSchema, from FooBar database.

Current Behavior

It fails to connect:

WARN  - Connection Failure
org.schemaspy.model.ConnectionFailure: Failed to connect to database URL [jdbc:microsoft:sqlserver://dbms.foo.com:1433;databaseName=FooBar] Cannot connect to 'jdbc:microsoft:sqlserver://dbms.foo.com:1433;databaseName=FooBar' with driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver,com.microsoft.jdbc.sqlserver.SQLServerDriver'
    at org.schemaspy.DbDriverLoader.getConnection(DbDriverLoader.java:101)
    at org.schemaspy.DbDriverLoader.getConnection(DbDriverLoader.java:75)
    at org.schemaspy.SchemaAnalyzer.getConnection(SchemaAnalyzer.java:421)
    at org.schemaspy.SchemaAnalyzer.analyzeMultipleSchemas(SchemaAnalyzer.java:121)
    at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:102)
    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:498)
    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)
Caused by: org.schemaspy.model.ConnectionFailure: Cannot connect to 'jdbc:microsoft:sqlserver://dbms.foo.com:1433;databaseName=FooBar' with driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver,com.microsoft.jdbc.sqlserver.SQLServerDriver'
    at org.schemaspy.DbDriverLoader.getConnection(DbDriverLoader.java:96)
    ... 15 common frames omitted

Possible Solution

I noticed that the connection string starts with jdbc:microsoft:sqlserver:, but in every other tool that I use, I just type jdbc:sqlserver:, without the microsoft bit. Maybe this is the problem? I tried inserting schemaspy.t=sqlserver instead of schemaspy.t=mssql at the beginning of the configuration file, but to no avail.

[npetzall]

@rscarrasco What version of MSSQL Server are you trying to connect to?

-t mssql uses jdbc:microsoft:sqlserver:// this is for older mssql server and older drivers. There are also -t mssql05 and mssql08 they both use jdbc:sqlserver://

[npetzall]

Since you're using mssql-jdbc-7.0.0.jre8.jar I'm guessing your connecting to 2008R2 or newer and then -t mssql08 should solve your problem.

[npetzall]

@rscarrasco did it work with -t mssql08

[rscarrasco]

@npetzall sorry for taking too long to respond. I was on a extended weekend. I tried using -t mssql08, and it worked like a charm. Thanks! If I may suggest, maybe the docs could be updated to list all available types, and their SGBDs. I've found the DBMS supported list, but could infer little from that.

[npetzall]

Do you refer to -dbhelp for supported list or is that in the docs.

[rscarrasco]

I refer to the -dbhelp, althought I have not found this information at the docs either.

[npetzall]

No, it's not documented. But -dbhelp should lista available. Should we add something to description. I could look at automatically create a list or something when docks are generated.

[robjens]

May I suggest clearly adding the key value to be used for -t? I was using the JTDS driver for MSSQL and I really couldn't figure out what string to use on that parameter. After digging through this source, I figured out it just needed to be -t jtds but this used to be a different name (mssql05-jtds, mssql-jtds) with earlier versions of schemaspy

[npetzall]

@robjens do you mean in the output of -dbhelp, that the value for -t should be clear? Maybe as example?

I started to think about writing out the hierarchy since most extends something else or group them by dbms. This mostly effect mssql since there is jtds. But there are version differences that effects the custom queries.

[RomainPoirmeur]

Hello, (First, I'm sorry if I should have opened a new issue) I have the same problem with MS SQL Server but this solution don't work for me, I use this command line : java -jar C:\SchemaSpy\schemaspy-6.0.0.jar -configFile C:\SchemaSpy\config.file

And my config.file contains : schemaspy.t=mssql08 schemaspy.dp=C:\SchemaSpy\mssql-jdbc-7.0.0.jre8.jar schemaspy.host=localhost\myinstance schemaspy.port=1433 schemaspy.db=mydb schemaspy.u=sa schemaspy.p=saPassword schemaspy.o=C:\SchemaSpy\result\ schemaspy.s=mySchema schemaspy.gv="C:\Program Files (x86)\Graphviz2.38"

Result : INFO - Starting Main v6.0.0 on myPC with PID 18008 (C:\SchemaSpy\schemaspy-6.0.0.jar started by myUser in c:\SchemaSpy) INFO - The following profiles are active: default INFO - Found configuration file: C:\SchemaSpy\config.file INFO - Started Main in 1.497 seconds (JVM running for 2.065) INFO - Starting schema analysis WARN - Connection Failure

Without more details. Any idea to resolve this issue between SQL Server 2016 and SchemaSpy 6.0.0 please? Thanks in advance

[npetzall]

You can get additional info with -debug but I believe the host argument is your problem.

Connecting to an instance you use -t mssql08-instance -instance [instance-name] and omitt /instance from host since host should be considered ip or host name.

[RomainPoirmeur]

Thanks, Sorry, I don't really understand your answer, I've tried : "[...] -t mssql08-instance -instance [instance-name] [...]" and I get : "Unable to resolve databaseType : mssql08-instance" I've tried : "[...] -t mssql08 -instance [instance-name] [...]" and I get : "Connection Failure [...] Failed to connect to database URL [jdbc:sqlserver://localhost;databasename=MYDB] ...

How I can create a connection string with [jdbc:sqlserver://localhost/MyInstance;databasename=MyDb] ?

[npetzall]

Oh, I'm sorry we only have instance config for jdts. I thought we had it for the mssql drivers.

I can look in to fixing that. I quick workaround is to create a new databaseType.

I can supply one later today.

[RomainPoirmeur]

That would be great ! Thanks in advance :)

[npetzall]

I thought of another thing that might work.

Just have host as localhost removing myInstance.

On the commandline add -connprops instanceName\\=myInstance

This is with your configfile and mssql08 instead of mssql08-instance since it's missing.

[RomainPoirmeur]

Thanks for this idea, unfortunately I've the same result org.schemaspy.model.ConnectionFailure: Failed to connect to database URL [jdbc:sqlserver://127.0.0.1:1433;databaseName=myDB]

[npetzall]

With the -debug you should get a stacktrace could you post it or at least the last 5 to 10 lines?

[npetzall]

So create a new file in the same folder as schemaspy-jarfile named mssql08-instance.properties that has the following contents

extends=mssql08
connectionSpec=jdbc:sqlserver://<host>\<instance>;databasename=<db>
instance=Instance to connect to

another possibility would be

extends=mssql08
connectionSpec=jdbc:sqlserver://<hostOptionalPort>;databaseName=<db>;instanceName=<instance>
instance=Instance to connect to

In both cases you need to add -instance myInstance to command line.

[xaviarnau]

Hi @npetzall

I am having the same problem connecting to a \. Do I have to edit the schemaspy-6.1.0.jar file? Could you upload the file with the required changes so I can use the -instance myInstance argument instead?

Thanks,

[npetzall]

@xaviarnau Which version of mssql are you using? Also which driver are you using? JTDS or Microsoft?

[xaviarnau]

@npetzall I am using the driver sqljdbc4-3.0.jar and SQL Server 2014. Could I use it for SQL Server 2019?

[npetzall]

@xaviarnau We have existing configuration for: mssql (2000) mssql05 (2005) mssql08 (2008) mssql17 (2017)

Most of the configuration is overrides of sql queries and as such it depends on which views and system catalogs exists.

For 2019 or Azure mssql17 should be used. It's available in the snapshot https://github.com/schemaspy/schemaspy#latest-build

You should be able to set instance using connection properties when connecting to sql server https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver15#named-and-multiple-sql-server-instances

And as such you should be able to use -t mssql08 -connprops instanceName\=[instanceName] where you replace [instanceName] with the correct instanceName, if its MSSQLSERVER it would be -t mssql08 -connprops instancName\=MSSQLSERVER https://schemaspy.readthedocs.io/en/latest/advanced_usage.html#started-connection-props

On windows and cmd it's key\=value and in git bash or linux it's key\\=value. But I'm always unsure so to be sure to test with both \\= and \= as separator between key and value.

[gustavorps]

The option for custom properties mentioned by @npetzall worked for me! -connprops instanceName\\=[instanceName], I use Linux so double backslash key\\=value.

How get the instanceName? Execute this query on the SQL Server:

SELECT @@SERVERNAME