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 https://github.com/microsoft/mssql-jdbc/issues/963. 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.