dosubot[bot]
commented
3 days ago @hmily3913 Hello there! I'm a bot here to help you with bug fixes, questions, and contributions. If you need any assistance, feel free to ask.
To resolve the error when creating a SQL Server database connection in Superset, given that the credentials are correct but the connection fails with a DBAPIError, you should check for custom errors extracted by the db_engine_spec of the database. The relevant code snippet shows that the DBAPIError is caught and custom errors are extracted and raised as SupersetErrorsException.
Here is the relevant part of the code:
except DBAPIError as ex:
event_logger.log_with_context(
action=get_log_connection_action(
"test_connection_error", ssh_tunnel, ex
),
engine=database.db_engine_spec.__name__,
)
# check for custom errors (wrong username, wrong password, etc)
errors = database.db_engine_spec.extract_errors(ex, self._context)
raise SupersetErrorsException(errors) from exTo resolve the error, you should:
- Check the custom errors extracted by
database.db_engine_spec.extract_errors(ex, self._context). - Address the specific issues indicated by these custom errors.
Superset defines various types of errors that can occur, including but not limited to:
CONNECTION_INVALID_USERNAME_ERRORCONNECTION_INVALID_PASSWORD_ERRORCONNECTION_INVALID_HOSTNAME_ERRORCONNECTION_PORT_CLOSED_ERRORCONNECTION_HOST_DOWN_ERRORCONNECTION_ACCESS_DENIED_ERRORCONNECTION_UNKNOWN_DATABASE_ERRORCONNECTION_DATABASE_PERMISSIONS_ERRORCONNECTION_MISSING_PARAMETERS_ERRORSYNTAX_ERRORCONNECTION_DATABASE_TIMEOUT
For example, if the custom errors indicate issues such as wrong username (CONNECTION_INVALID_USERNAME_ERROR), wrong password (CONNECTION_INVALID_PASSWORD_ERROR), or other connection-related problems, you will need to correct those specific issues in your connection configuration.
Additionally, Superset provides issue codes and messages for these errors, which can help you diagnose and resolve the problem more effectively. For instance:
- Issue code
1012: "The username provided when connecting to a database is not valid." - Issue code
1013: "The password provided when connecting to a database is not valid." - Issue code
1007: "The hostname provided can't be resolved." - Issue code
1008: "The port is closed." - Issue code
1009: "The host might be down, and can't be reached on the provided port."
In the case of SQL Server, the MssqlEngineSpec class in Superset defines specific regular expressions to catch custom errors:
CONNECTION_ACCESS_DENIED_REGEX = re.compile("Adaptive Server connection failed")
CONNECTION_INVALID_HOSTNAME_REGEX = re.compile(
r"Adaptive Server is unavailable or does not exist \((?P<hostname>.*?)\)"
"(?!.*Net-Lib error).*$"
)
CONNECTION_PORT_CLOSED_REGEX = re.compile(
r"Net-Lib error during Connection refused \(61\)"
)
CONNECTION_HOST_DOWN_REGEX = re.compile(
r"Net-Lib error during Operation timed out \(60\)"
)These regular expressions are used to match specific error messages and map them to Superset error types:
custom_errors: dict[Pattern[str], tuple[str, SupersetErrorType, dict[str, Any]]] = {
CONNECTION_ACCESS_DENIED_REGEX: (
__(
'Either the username "%(username)s", password, '
'or database name "%(database)s" is incorrect.'
),
SupersetErrorType.CONNECTION_ACCESS_DENIED_ERROR,
{},
),
CONNECTION_INVALID_HOSTNAME_REGEX: (
__('The hostname "%(hostname)s" cannot be resolved.'),
SupersetErrorType.CONNECTION_INVALID_HOSTNAME_ERROR,
{},
),
CONNECTION_PORT_CLOSED_REGEX: (
__('Port %(port)s on hostname "%(hostname)s" refused the connection.'),
SupersetErrorType.CONNECTION_PORT_CLOSED_ERROR,
{},
),
CONNECTION_HOST_DOWN_REGEX: (
__(
'The host "%(hostname)s" might be down, and can\'t be '
"reached on port %(port)s."
),
SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
{},
),
}By addressing the specific issues indicated by these custom errors and issue codes, you can resolve the DBAPIError and successfully create a SQL Server database connection in Superset [1][2][3].
To continue talking to Dosu, mention @dosu.
Habeeb556
hmily3913
Bug description
When creating a new database connection, fill in the correct URI, click Test Connection, and the username, password, and database error are displayed, which is actually correct.Error prompts such as pictures
How to reproduce the bug
pip install pymssql superset db upgrade superset init docker restart superset_app
Screenshots/recordings
Superset version
4.0.1
Python version
3.9
Node version
16
Browser
Chrome
Additional context
2024-07-05 06:20:49,729:DEBUG:superset.stats_logger:[stats_logger] (incr) DatabaseRestApi.test_connection.error 2024-07-05 06:20:49,730:WARNING:superset.views.base:SupersetErrorsException Traceback (most recent call last): File "/app/superset/commands/database/test_connection.py", line 171, in run raise DBAPIError(ex_str or None, None, None) sqlalchemy.exc.DBAPIError: (builtins.NoneType) None [SQL: (pymssql.exceptions.OperationalError) (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (10.0.0.7)\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (10.0.0.7)\n') (Background on this error at: https://sqlalche.me/e/14/e3q8)] (Background on this error at: https://sqlalche.me/e/14/dbapi)
The above exception was the direct cause of the following exception:
Traceback (most recent call last): File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1823, in full_dispatch_request rv = self.dispatch_request() File "/usr/local/lib/python3.10/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.10/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 1463, in time_function response = func(*args, kwargs) File "/app/superset/utils/log.py", line 255, in wrapper value = f(*args, *kwargs) File "/app/superset/views/base_api.py", line 93, in wraps return f(self, args, kwargs) File "/app/superset/databases/api.py", line 920, in test_connection TestConnectionDatabaseCommand(item).run() File "/app/superset/commands/database/test_connection.py", line 200, in run raise SupersetErrorsException(errors) from ex superset.exceptions.SupersetErrorsException: [SupersetError(message='Either the username "sa", password, or database name "xx" is incorrect.', error_type=<SupersetErrorType.CONNECTION_ACCESS_DENIED_ERROR: 'CONNECTION_ACCESS_DENIED_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Microsoft SQL Server', 'issue_codes': [{'code': 1014, 'message': 'Issue 1014 - Either the username or the password is wrong.'}, {'code': 1015, 'message': 'Issue 1015 - Either the database is spelled incorrectly or does not exist.'}]})]
Checklist