Issues to execute queries via SQL Lab

[sprudel79]

Hi everyone,
I have a K8s cluster where I have installed the latest helm chart of superset from http://apache.github.io/superset/ (which is 3.1.0).
I need to access several MS SQL databases in my dashboards. For the creation of the database connections I use this kind of syntax for the SQLAlchemy uri: "mssql+pymssql://:@:/
When clicking the "Test connection" button, I get informed that everything's fine.
Now to the issue:
Next thing is to go to the SQL Lab to verify the DB connections. I can browse the available ones in the "DATABASE" drop-down, I am able to see the correct "SCHEMA" options as well as the "TABLE SCHEMA" options. SQL Lab shows also nicely all the available columns, so in my opinion the DB connection settings should be fine.
However, when I try to execute a query (I tried even with a super simple one like "SELECT 1 AS A") I always get the error:

Microsoft SQL Server Error
Authentication required.
This may be triggered by:
Issue 1002 - The database returned an unexpected error.

In the logs of the superset pod I can see this:

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1823, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1799, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/usr/local/lib/python3.9/site-packages/flask_appbuilder/security/decorators.py", line 95, in wraps
    return f(self, *args, **kwargs)
  File "/app/superset/views/base_api.py", line 127, in wraps
    raise ex
  File "/app/superset/views/base_api.py", line 121, in wraps
    duration, response = time_function(f, self, *args, **kwargs)
  File "/app/superset/utils/core.py", line 1454, in time_function
    response = func(*args, **kwargs)
  File "/app/superset/views/base_api.py", line 93, in wraps
    return f(self, *args, **kwargs)
  File "/app/superset/utils/log.py", line 255, in wrapper
    value = f(*args, **kwargs)
  File "/app/superset/sqllab/api.py", line 407, in execute_sql_query
    command_result: CommandResult = command.run()
  File "/app/superset/commands/sql_lab/execute.py", line 121, in run
    raise ex
  File "/app/superset/commands/sql_lab/execute.py", line 103, in run
    status = self._run_sql_json_exec_from_scratch()
  File "/app/superset/commands/sql_lab/execute.py", line 161, in _run_sql_json_exec_from_scratch
    raise ex
  File "/app/superset/commands/sql_lab/execute.py", line 156, in _run_sql_json_exec_from_scratch
    return self._sql_json_executor.execute(
  File "/app/superset/sqllab/sql_json_executer.py", line 111, in execute
    raise SupersetErrorsException(
superset.exceptions.SupersetErrorsException: [SupersetError(message='Authentication required.', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Microsoft SQL Server', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]

But now again another weird thing is that I can use the same DB connection without any problem in a chart, so it seems only SQL Lab has issues. Is there anything I did wrong?

I am aware that the error is described over here:
https://superset.apache.org/docs/miscellaneous/issue-codes/#issue-1002

But I have my doubts there's a problem with the DB itself since the connection works in general and even in the charts.

Many thanks in advance and kind regards,
Sprudel

[craunas]

@sprudel79 Did you find any way around this?

We're experiencing the same problem but with Snowflake as data source.
Looks like the query gets down to the DB but no results are shown in sql lab.

We've got the same error message as in the original post but have gotten this one as well:
Protocol error: unauthenticated bulk length

which seem to be related to Redis?

[sprudel79]

Hi @craunas , unfortunately I haven't found any solution to this issue. It cannot be a general issue with the connection itself because I can use it in SQL Lab and browse the tables and columns. Only when executing the query I get the error. Next step for me would be to check the source code directly.

[craunas]

Hi!
Sounds exactly like the problem we had. We solved it by using an in-cluster Redis setup instead of a Azure managed one.
Not sure why it solved it but one of our devops guys thought it would be because Azure only offers version 6 of Redis while the one we deployed in cluster ran version 6.

Let me know if I can help out in any way @sprudel79

[sprudel79]

Thanks @craunas , indeed we've been using an external Redis instance in our setup instead the default in-cluster one. So as I understand I should simply try the in-cluster setup to solve this issue?

[craunas]

It solved our issues at least, so it could be worth a try at least.
In our case we decided that since its only used for caching its not as critical as the backend db's fore example, so no risk having it in-cluster instead. @sprudel79

[Samoth69]

Make sure your RESULT_BACKEND or RedisCache config in superset_config.py is working. In our case we missed a password field in RedisCache object.

[dimashenkov]

and you had message='Authentication required.', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR ?

[sprudel79]

Thanks @dimashenkov for asking.
I indeed get this error:

I have tried already a few things regarding Redis (avoid using external Redis and rather using the instance which will get deployed automatically along with the Helm chart as it was suggested in this thread) but no luck so far.
We have considered removing the SQLLab from the menu which as I understand is a matter of setting the right permissions.

[dimashenkov]

@sprudel79 I fixed the ERR with this change in helpers.tpl , its a bug and I would create an issue from that discussion .

CELERY_CONFIG = CeleryConfig
RESULTS_BACKEND = RedisCache(
      host=env('REDIS_HOST'),
{{- if or .Values.redis.auth.enabled .Values.supersetNode.connections.redis_password }}
      password=env('REDIS_PASSWORD'),
{{- end }}
      port=env('REDIS_PORT'),

[sprudel79]

Hi @dimashenkov , awesome, many thanks for having shared that fix!
I can confirm it's working on my side, I have simply applied the following code in my configOverrides section at the very end:

RESULTS_BACKEND = RedisCache(
      host=env('REDIS_HOST'),
      password=env('REDIS_PASSWORD'),
      port=env('REDIS_PORT'),
      key_prefix='superset_results',      
    )

And yes, that did the trick! Once you have the issue created we can close this one.
Thanks again!