No tables retrieved for a schema

[sstano]

I'm trying to export a schema from MSSQL database using SchemaCrawler & jTDS driver (version 1.3.1) with following command:

./schemacrawler.sh \
  -server=sqlserver \
  -password= \
  -command=schema \
  -outputformat=png \
  -outputfile=./output/result.png \
  -infolevel=standard \
  -schemas=.*DATABASE.*DOMAIN.user.* \
  -tabletypes=TABLE \
  -tables=.* \
  -routinetypes= \
  -loglevel=ALL \
  -url=jdbc:jtds:sqlserver://server.com:1433/DATABASE\;instance=dbinstance\;useNTLMv2=TRUE\;domain=DOMAIN\;user=user\;password=pwd

• The DB server (should) be Microsoft Windows Server 2012 R2 Standard.
• DB tables are under a default schema named after the user (incl. Windows domain) "DOMAIN\user".
• The connection URL and regex to match the schema work fine - when I launch the Database Manager with the same connection string, I can see all the tables listed as DOMAIN\user.tableName
• However, when I run the script from a Docker container, I'm getting:

  Mar 14, 2017 8:53:18 PM schemacrawler.crawl.SchemaCrawler crawlTables
  INFO: Crawling tables
  Mar 14, 2017 8:53:18 PM schemacrawler.crawl.TableRetriever retrieveTables
  INFO: Retrieving tables for schema: "DATABASE"."DOMAIN\user"
  Mar 14, 2017 8:53:18 PM schemacrawler.crawl.TableRetriever retrieveTables
  FINER: Retrieving table types: [TABLE]
  Mar 14, 2017 8:53:18 PM schemacrawler.crawl.MetadataResultSet close
  INFO: Processed 0 rows for <retrieveTables>
  Mar 14, 2017 8:53:18 PM schemacrawler.crawl.SchemaCrawler crawlTables
  INFO: Retrieved 0 tables

It would be helpful if I could somehow get the SQL query it's executing to list the tables.

[schemacrawler]

SchemaCrawler leverages the JDBC driver's built-in functionality to retrieve tables, so I cannot say what query jTDS is using.

1. How many schemas were retrieved, and what was the name of the schema that you are interested in? Was that schema filtered out?
2. Please try this command-line out:

   ./schemacrawler.sh \
   -server=sqlserver \
   -password= \
   -command=schema \
   -outputformat=png \
   -outputfile=./output/result.png \
   -infolevel=standard \
   -schemas=.*user\.dbo.* \
   -routines= \
   -loglevel=ALL \
   -url=jdbc:jtds:sqlserver://server.com:1433/DATABASE\;instance=dbinstance\;useNTLMv2=TRUE\;domain=DOMAIN\;user=user\;password=pwd

3. See if there a different result using the official Microsoft JDBC Driver for SQL Server, which is in the latest SchemaCrawler Docker image.
4. If you are still not getting the expected results, please email me the logs at sualeh@hotmail.com

[schemacrawler]

Internally, both the jTDS JDBC driver and official Microsoft JDBC driver use sp_tables.

[sstano]

Hi, thanks a lot for digging into this.

Regarding your 1st point, quite a lot schemas were found in the DB, actually. I tried to play around with it and it seems that the problem is because of the backslash in the schema name. I got the desired schema included by the filter when I used .(dot) in the schema regex where the backslash is:

-schemas=\"TESTDB-D1\"\.\"DOMAIN.user\" \
FINE: Including <"TESTDB-D1"."DOMAIN\user"> since it matches /"TESTDB-D1"."DOMAIN.user"/

However, no tables are found.

If I don't want to use the dot, the schema is only matched when I use following regex with 4 backslashes (2 backslashes won't match the schema name):

-schemas=\"TESTDB-D1\"\.\"DOMAIN\\\\user\" \
FINE: Including <"TESTDB-D1"."DOMAIN\user"> since it matches /"TESTDB-D1"."DOMAIN\\user"/

So, a backslash needs to be escaped to be passed as a backslash, but it seems like the string is then passed further (to jTDS?) where it needs to be escaped again (jTDS bug?).

With 4 backslashes in the regex, the schema is found and included, but again, no tables are found.

Then I tried a schema that doesn't have any backslashes in it: -schemas=master\.dbo and actually got some tables printed, so I'm getting pretty convinced the backslash is the trouble-maker.

[schemacrawler]

Can you try with the latest Docker image? It is distributed with the official Microsoft JDBC driver. Also, please send me detailed instructions on how to set up a user and schema like the one you have, and I will try to reproduce on Amazon RDS.

[schemacrawler]

Ok, I have this (see screenshot), but I cannot connect using jdbc:jtds:sqlserver://localhost:1433;instance=SQLEXPRESS;useNTLMv2=true;domain=THOTH;user=Sualeh Fatehi;password=xxxxx Please help me connect, so I can reproduce your issue.

[schemacrawler]

Ok, I was able to connect, using jdbc:jtds:sqlserver://localhost:53665;useNTLMv2=true;domain=THOTH;user=Sualeh Fatehi;password=xxxxx - the instance parameter did not work for me.

I have reproduced the issue. Basically, when jTDS sees DOMAIN\user, it internally escapes it to DOMAIN[u]ser before calling sp_tables. I am looking to see how the Microsoft JDBC Driver for SQL Server escapes that schema before calling sp_tables.

[schemacrawler]

I confirmed that the Microsoft JDBC Driver for SQL Server that is distributed with the latest version of SchemaCrawler does not have same the escaping issue as jTDS. I was able to get output from the Microsoft JDBC Driver for SQL Server.

[adriens]

On the debian and rpm installers, i have switched to mssql-jdbc-6.1.0.jre8.jar and removed jtds . Did you take a look to the latest pre-release (https://github.com/Microsoft/mssql-jdbc/releases/tag/v6.1.5) ?

[schemacrawler]

Adrien, as always, thanks for keeping the Debian and RPM installers up to date. Yes, I have taken a look at the pre-release, and will upgrade once the release is final. Microsoft JDBC Driver for SQL Server v6.1.0 is working as expected with SchemaCrawler.

[adriens]

v6.1.0 is working as expected with SchemaCrawler. : that's all i wanted to hear ;-D

[sstano]

Is there any chance it could also be fixed for jTSD driver? I'm having issues connecting with the MS JDBC driver from my Mac. I need to use Windows authentication with a specific user domain and only jTDS was able to get it right.

[schemacrawler]

I can see three possible options to help you:

1. Patch jTDS - This is not a great option, since the source has not been updated since a few years. It may be risky to make a patch.
2. Fix the Microsoft JDBC Driver for SQL Server - The official driver should support conecting to Microsoft SQL Server. Please enter an issue on GitHub.
3. Use a SchemaCrawler workaround - I have re-opened this ticket to see if there is a workaround using the INFORMATION_SCHEMA.
4. Use another authentication method - Create a database user that can connect using SQL Server authentication, instead of Windows authentication.

[schemacrawler]

I have a fix ready for you, and I will release a new version of SchemaCrawler in a couple of days. I will also give you instructions on how to modify your SchemaCrawler configuration file to make this work. I had to work-around the jTDS driver schema name escaping issue by scanning all tables and columns in the database, and this will make SchemaCrawler a little slower, but hopefully not very much.

[schemacrawler]

Please use SchemaCrawler 14.15.03. Add the following lines to your schemacrawler.config.properties file in the config/ folder of the SchemaCrawler distribution, and re-run.

schemacrawler.schema.retrieval.strategy.tables=metadata_all
schemacrawler.schema.retrieval.strategy.tablecolumns=metadata_all

[sstano]

@sualeh Amazing! Thank you so much! I can see the tables in the logs now :) I'll check the generated image once there's a new Docker image available, but I believe it should be fine now. THANKS!!!

[schemacrawler]

The SchemaCrawler Docker images are available now.