SQL Server DB connection only displaying system tables on SQL Lab

cesar-loadsmart commented 1 year ago

I've followed this doc to connect Superset with a Microsoft SQL Server Database, but using the recommended connection string mssql+pymssql://<Username>:<Password>@<Host>:<Port-default:1433>/<Database Name>/?Encrypt=yes did not work:

Logs are also unclear:

 SupersetErrorsException
Traceback (most recent call last):
  File "/app/superset/databases/commands/test_connection.py", line 120, in run
    raise DBAPIError(None, None, None)
sqlalchemy.exc.DBAPIError: (builtins.NoneType) None
(Background on this error at: http://sqlalche.me/e/13/dbapi)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1499, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1485, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 89, in wraps
    return f(self, *args, **kwargs)
  File "/app/superset/views/base_api.py", line 113, in wraps
    raise ex
  File "/app/superset/views/base_api.py", line 110, in wraps
    duration, response = time_function(f, self, *args, **kwargs)
  File "/app/superset/utils/core.py", line 1507, in time_function
    response = func(*args, **kwargs)
  File "/app/superset/utils/log.py", line 245, in wrapper
    value = f(*args, **kwargs)
  File "/app/superset/views/base_api.py", line 83, in wraps
    return f(self, *args, **kwargs)
  File "/app/superset/databases/api.py", line 709, in test_connection
    TestConnectionDatabaseCommand(g.user, item).run()
  File "/app/superset/databases/commands/test_connection.py", line 145, in run
    raise DatabaseTestConnectionFailedError(errors) from ex
superset.databases.commands.exceptions.DatabaseTestConnectionFailedError: [SupersetError(message='(builtins.NoneType) None\n(Background on this error at: http://sqlalche.me/e/13/dbapi)', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Azure Synapse', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]
2023-03-01 13:09:04,232:WARNING:superset.views.base:SupersetErrorsException
Traceback (most recent call last):
  File "/app/superset/databases/commands/test_connection.py", line 120, in run
    raise DBAPIError(None, None, None)
sqlalchemy.exc.DBAPIError: (builtins.NoneType) None
(Background on this error at: http://sqlalche.me/e/13/dbapi)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1499, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1485, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 89, in wraps
    return f(self, *args, **kwargs)
  File "/app/superset/views/base_api.py", line 113, in wraps
    raise ex
  File "/app/superset/views/base_api.py", line 110, in wraps
    duration, response = time_function(f, self, *args, **kwargs)
  File "/app/superset/utils/core.py", line 1507, in time_function
    response = func(*args, **kwargs)
  File "/app/superset/utils/log.py", line 245, in wrapper
    value = f(*args, **kwargs)
  File "/app/superset/views/base_api.py", line 83, in wraps
    return f(self, *args, **kwargs)
  File "/app/superset/databases/api.py", line 709, in test_connection
    TestConnectionDatabaseCommand(g.user, item).run()
  File "/app/superset/databases/commands/test_connection.py", line 145, in run
    raise DatabaseTestConnectionFailedError(errors) from ex
superset.databases.commands.exceptions.DatabaseTestConnectionFailedError: [SupersetError(message='(builtins.NoneType) None\n(Background on this error at: http://sqlalche.me/e/13/dbapi)', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Azure Synapse', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]

So, I tested some different connections string, the following two worked:

mssql+pymssql://<user>:<password>@<host>/?charset=utf8

mssql+pymssql://<user>:<password>@<host>:<port>

However, when checking the Database schema on SQL Lab, it only shows the system tables:

It's not a permissions problem, because it works on Dbeaver! Queries also work, by specifying the complete path such as: select * from db_name.dbo.table_name;

The only problem is the fact that it is only displaying system tables and views.

How to reproduce the bug

Go to Databases tab
Create a new database connection using pymssql
By using the default connection string, you should receive an error message.
See error
Try using my suggested connection string, it may work, but you will only see system tables at the SQL Lab.

Expected results

Show all tables inside the database

Actual results

Only shows system tables, such as:

Environment

browser type and version: Chrome Version 110.0.5481.100 (Official Build) (x86_64), but the same happens for all browsers
superset version: 2.0.0
lib version: pymssql==2.2.7
python version: 3.8
node.js version: node 16
any feature flags active: ALERT_REPORTS, DASHBOARD_NATIVE_FILTERS, ENABLE_TEMPLATE_PROCESSING
My Superset is running via Kubernetes

Checklist

Make sure to follow these steps before submitting your issue - thank you!

[X] I have checked the superset logs for python stacktraces and included it here as text if there are any.
[ ] I have reproduced the issue with at least the latest released version of superset.
[X] I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

This is not a database we own, so some tests would require getting in contact with the external provider, something We wouldn't like to do at this moment.

cesar-loadsmart commented 1 year ago

Hey folks!

This stackoverflow entry, makes me believe the real problem is not specifying the Database: https://stackoverflow.com/questions/29151478/how-does-a-table-not-being-in-information-schema-or-systables-happen

I believe that making the connection string work properly will fix my issue.

kikolouro commented 1 year ago

Hey!

I'm having the same problems connecting to the database server, the only difference is that I can't connect even without database on the connection string.

Might add that I'm using the latest version in a built docker image and the host I'm installing superset on doesn't have internet connection.

Are you using windows authentication?

cesar-loadsmart commented 1 year ago

Here I'm using database authentication,

I'm also waiting for my local machine to be whitelisted once again so that I can test a built docker image at the latest version connecting to the Database.

I believe that by using windows authentication, your connection string should look different, not sure though.

kikolouro commented 1 year ago

What I've seen was you only needed to mention the user like "Domain\User" so the string would be something like: "mssql+pymssql://Domain>\<user>:<password>@<host:/". I've also tried with double back slashes because character escaping is a thing.

kikolouro commented 1 year ago

Any updates on this topic?

RobLucchi commented 1 year ago

@cesar-loadsmart I think the issue is when you are using: mssql+pymssql://<user>:<password>@<host>/?charset=utf8 or mssql+pymssql://<user>:<password>@<host>:<port> it connects to the master database, not your actual database which I tested by making it like this: mssql+pymssql://<user>:<password>@<host>:<port>/master it worked perfectly but when changing the DB to the required one I got the same error

kikolouro commented 1 year ago

Didn't work. I've tested with another environment and it worked, the only difference is that the user i am connecting. For the environment that is not working the user is from a domain controller and the other is a local windows user.

RobLucchi commented 1 year ago

@kikolouro My user connects perfectly through DBeaver but not sqlalchemy URL. can you please explain to me the difference between the two users? and if possible how to create it. Aslo, are you connecting to the Azure SQL server or Azure Synapse Analytics?

kikolouro commented 1 year ago

@RobLucchi

So I have two scenarios:

Scenario A

machine A - has superset and other services, linux machine and superset running on docker machine B - has SQL Server in a domain.

When connection superset to the SQL Server using a Domain User gives me the error.

Scenario B machine A - has superset and other services, linux machine and superset running on docker machine B - has SQL Server and it is NOT in a domain

When connecting superset to the SQL using a local user (since it isn't in a domain) does not give me a error.

Might add that machine A from scenario A and B are in different networks and the Scenario A is from a Client so a I don't have permission to do some things like local users, SQL users etc and they wont provide me.

When selecting a database type I choose "Microsoft SQL Server".

RobLucchi commented 1 year ago

well it worked with me after adding autocommit=true to the pyodbc driver URL mssql+pyodbc://USER:PASSWORD@HOST:PORT/Database?driver=ODBC+Driver+17+for+SQL+Server&autocommit=true

JohnDietrich-Pepper commented 1 year ago

Yep same issue. Synapse Serverless works great with no issues. The second you try and connect to a dedicated pool well then you can only connect to the master database as described above. Queries don't work for me because the dedicated pool doesn't support full path cross database queries.

pamelascr commented 10 months ago

Hello all,

Did any of you find a solution to this problem? I tried every solution in this thread but I still was not able to connect to it. If I specify the driver I get an error that the URL has an unexpected "driver".

Please let me know if you find any solution for this... Stuck in this problem for some time.

rusackas commented 6 months ago

This issue seems to have gone stale... is anyone still facing this? We might need someone to recapitulate the issue as it pertains to currently supported versions (3.1/4.0). If it's a superset bug, we can leave it open. If it's a config issue, it should be a GitHub discussion instead (I can migrate it). If nobody's worried about it anymore, we can certainly close it. We also warmly welcome documentation contributions if anyone knows a solution and wants to help others in the future.

apache / superset