Document a database with SchemaSpy using integrated

Document a database with SchemaSpy using integrated

SchemaSpy is a great free open source software for database documentation. You can get a first glimpse of this awesome tool by viewing the sample output. This blog post shows how to configure SchemaSpy to connect to a database using integrated authentication with MS AD  (your windows login) in a Windows environment.

Please follow the steps outlined in my previous blog post to setup SchemaSpy. I would also recommend testing to connect with a SQL username and password first in order to make sure that the driver and SchemaSpy works as planned.

SchemaSpy documentation has a rather short notice regarding integrated authentication or as they call it “SSO”.

Follow these instructions to connect with integrated authentication:

  1. Open the download of the SQL Server JDBC driver and navigate to the auth-directory. There you’ll find two files (in different subfolders) called mssql-jdbc-auth-version-x64.dll and mssql-jdbc-auth-version-x32.dll. An example for a full file name is mssql-jdbc_auth-12.4.0.x64.dll
  2. Copy both files (the x32 and x64 one) to the folder where you unzipped java in the bin-directory (for example C:\SQL\schemaSpy\installation\openjdk-20\jdk-20.0.2\bin).
    1. To be honest this is a trick I am not too proud of. In theory you should be able to name the dll file using the –dp switch of java but it never worked for me and this is the working solution I have found at StackOverflow.
  3. Now run SchemSpy using the following additional parameters
    1. -connprops = ‘integratedSecurity\=true’ to tell SQL Server to use the integrated authentication with your AD credentials
    1. -sso to tell SchemaSpy that no username and password are supplied
    1. Here’s an example with the complete command:
      java.exe -jar ‘C:\SQL\schemaSpy\installation\schemaspy-6.2.4.jar’ -o ‘C:\sql\schemaSpy\output\’ -vizjs -t mssql17 -dp ‘C:\SQL\schemaSpy\installation\mssql-jdbc-12.4.0.jre11.jar’ -connprops ‘trustServerCertificate\=true;integratedSecurity\=true’ -db ‘StackOverflowDocumentation’ -host ‘DESKTOP-RGO4OCI’ -port 1433 –sso
    1. I have added trustServerCertificate=true in the example which is just a workaround for encrypting a SQL Server Connection with a self-signed certificate. However this is nothing you would typically need to do.

Unfortunately there is one gotcha related to the SQL Server JDBC driver on Windows. This applies if you configure the SQL Server Network Protocols to support ExtendedProtection at the server side. Extended Protection protects against Man-in-the-middle-Attacks. The issue is already logged with Microsoft at Unfortunately this is waiting on a modification to be done to the Java JDK itself which seems to be very unlikely to happen. For now you will have to set ExtendedProtection to Off to successfully connect with integrated authentication.

2 thoughts on “Document a database with SchemaSpy using integrated

  1. Hi, really nice post. Sorry about the short notice on the SSO.

    I wonder if you could help med make it clearer regarding the short notice.

    A bit of background. The dll supplied by Microsoft need to be natively loaded by the JVM.

    Hence the usage of `-Djava.library.path=` which must be supplied before the `-jar` argument.

    I’m just guessing here but jre/jdk used would be 64-bit and such `mssql-jdbc_auth-12.4.0.x64.dll` would be of interest.

    If that is placed in `C:\SQL\mssql-jdbc_auth\`

    Then you should be able to use `java -Djava.library.path=C:\SQL\mssql-jdbc_auth -jar schemaspy-6.2.4.jar (etc etc)`

    Any suggestions to the documentation in general is very much appreciated.

    1. Hi Nils,
      thanks for stopping by.
      Interesting…I will give your solution a try and will update this blog post accordingly.
      I am very grateful for the amazing work you and your collaborators have put in to SchemaSpy.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.