Incubator-superset: Superset connection MS SQL Server failed.(WARNING:superset.views.core:Connection failed (pymssql.OperationalError))

[Zhiyuancheng]

A clear and concise description of what the bug is.

Expected results

I want to connection mssql use pymssql model(Install: pip3 install pymssql)

Actual results

but it doesn't work.

my connection link is :
'mssql+pymssql://username:password@db_host:1433/database'

the error logs :

WARNING:superset.views.core:Connection failed (pymssql.OperationalError) (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (10.xx.xx.xx)\n')
(Background on this error at: http://sqlalche.me/e/13/e3q8)
INFO:werkzeug:10.xx.xx.xx0 - - [22/Sep/2020 11:10:56] "POST /superset/testconn HTTP/1.1" 400 -
Connection failed (pymssql.OperationalError) (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (10.xx.xx.xx)\n')
(Background on this error at: http://sqlalche.me/e/13/e3q8)
WARNING:superset.views.core:Connection failed (pymssql.OperationalError) (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (10.xx.xx.xx)\n')
(Background on this error at: http://sqlalche.me/e/13/e3q8)
INFO:werkzeug:10.xx.xx.xx- - [22/Sep/2020 11:16:57] "POST /superset/testconn HTTP/1.1" 400 -

Screenshots

How to reproduce the bug

1. Go to 'source--> databases-->add a new record -->Database [mssql] --> SQLAlchemy URI -->mssql+pymssql://username:password@db_host:1433/database'
2. Click on 'TEST CONNECTION'
3. Scroll down to ‘You will see some error'
4. See error:ERROR: Connection failed, please check your connection settings.

Environment

(please complete the following information):

• superset version: superset version
Superset 0.37.1
• python version: python --version
Python 3.6.8
• node.js version: node -v
• npm version: npm -v
• pip3 list

(venv) [root@operation ~]# pip3 list 
DEPRECATION: The default format will switch to columns in the future. You can use --format=(legacy|columns) (or define a format=(legacy|columns) in your pip.conf under the [list] section) to disable this warning.
aiohttp (3.6.2)
alembic (1.4.3)
amqp (2.6.1)
apache-superset (0.37.1)
apispec (3.3.2)
async-timeout (3.0.1)
attrs (20.2.0)
Babel (2.8.0)
backoff (1.10.0)
billiard (3.6.3.0)
bitarray (1.5.3)
bleach (3.2.0)
Brotli (1.0.9)
cachelib (0.1.1)
celery (4.4.7)
cffi (1.14.3)
chardet (3.0.4)
click (7.1.2)
colorama (0.4.3)
contextlib2 (0.6.0.post1)
croniter (0.3.34)
cryptography (3.1)
dataclasses (0.6)
decorator (4.4.2)
defusedxml (0.6.0)
dnspython (2.0.0)
email-validator (1.1.1)
Flask (1.1.2)
Flask-AppBuilder (3.0.1)
Flask-Babel (1.0.0)
Flask-Caching (1.9.0)
Flask-Compress (1.5.0)
Flask-JWT-Extended (3.24.1)
Flask-Login (0.4.1)
Flask-Migrate (2.5.3)
Flask-OpenID (1.2.5)
Flask-SQLAlchemy (2.4.4)
flask-talisman (0.7.0)
Flask-WTF (0.14.3)
future (0.18.2)
geographiclib (1.50)
geopy (2.0.0)
gunicorn (20.0.4)
humanize (2.6.0)
idna (2.10)
idna-ssl (1.1.0)
impala (0.2)
importlib-metadata (1.7.0)
impyla (0.16.2)
isodate (0.6.0)
itsdangerous (1.1.0)
Jinja2 (2.11.2)
jsonschema (3.2.0)
kombu (4.6.11)
Mako (1.1.3)
Markdown (3.2.2)
MarkupSafe (1.1.1)
marshmallow (3.8.0)
marshmallow-enum (1.5.1)
marshmallow-sqlalchemy (0.23.1)
msgpack (1.0.0)
multidict (4.7.6)
mysqlclient (2.0.1)
natsort (7.0.1)
numpy (1.19.2)
packaging (20.4)
pandas (1.0.5)
parsedatetime (2.6)
pathlib2 (2.3.5)
pip (9.0.3)
ply (3.11)
polyline (1.4.0)
prison (0.1.3)
py (1.9.0)
pyarrow (0.17.1)
pycparser (2.20)
PyHive (0.6.3)
PyJWT (1.7.1)
pymssql (2.1.5)
pyodbc (4.0.30)
pyparsing (2.4.7)
pyrsistent (0.17.3)
python-dateutil (2.8.1)
python-dotenv (0.14.0)
python-editor (1.0.4)
python-geohash (0.8.5)
python3-openid (3.2.0)
pytz (2020.1)
PyYAML (5.3.1)
retry (0.9.2)
sasl (0.2.1)
selenium (3.141.0)
setuptools (39.2.0)
simplejson (3.17.2)
six (1.15.0)
slackclient (2.5.0)
SQLAlchemy (1.3.19)
sqlalchemy-exasol (2.2.0)
SQLAlchemy-Utils (0.36.8)
sqlparse (0.3.0)
thrift (0.13.0)
thrift-sasl (0.4.2)
thriftpy2 (0.4.11)
typing-extensions (3.7.4.3)
urllib3 (1.25.10)
vine (1.3.0)
webencodings (0.5.1)
Werkzeug (1.0.1)
WTForms (2.3.3)
WTForms-JSON (0.3.3)
yarl (1.5.1)
zipp (3.1.0)

Checklist

Make sure these boxes are checked before submitting your issue - thank you!

• [* ] 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.
• [ *] I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

I found the superset Documentation suggestion to use pymssql too :
https://superset.incubator.apache.org/docs/databases/sql-server

I used the suggestion and test but it still doesn't work:
mssql+pymssql://UserName@DB:Password@DB_Host:1433/TestSchema

Please help me how to resolve this problem ? Have any suggestion pleast just let me know , thanks.

[issue-label-bot[bot]]

Issue-Label Bot is automatically applying the label #bug to this issue, with a confidence of 0.65. Please mark this comment with :thumbsup: or :thumbsdown: to give our bot feedback!

Links: app homepage, dashboard and code for this bot.

[eugeniamz]

Hi, I have a similar issue when I was trying MSSQL in Azure in Preset, and I documented what worked, ignore the Preset IP and see if the extra parameters help you.

[Zhiyuancheng]

Hi, I have a similar issue when I was trying MSSQL in Azure in Preset, and I documented what worked, ignore the Preset IP and see if the extra parameters help you.

Thanks @eugeniamz I have solved it , use pyodbc instead of pymssql .

• apt-get install unixODBC
• pip install pyodbc

eg :

and you can refer to MS related link introduce:
https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-1-configure-development-environment-for-pyodbc-python-development?view=sql-server-ver15

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.