search

apache / superset

Apache Superset is a Data Visualization and Data Exploration Platform
https://superset.apache.org/
Apache License 2.0
61.78k stars 13.52k forks source link

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

Closed Zhiyuancheng closed 1 year ago

Zhiyuancheng commented 3 years ago

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

image

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):



### 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
![image](https://user-images.githubusercontent.com/16459956/93841970-d7f3ec00-fcc7-11ea-9857-f8ca0b72f2c1.png)
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] commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 .

eg : image

image

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] commented 3 years ago

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.

GolamRashed commented 2 years ago

I have the same issue with the latest Superset versions and Python 3.8:

vmrc_2021-10-30_22-12-58

stale[bot] commented 2 years ago

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.

GiovanniThysMichel commented 2 years ago

Hi I have the same issue. Can someone please help?

sfirke commented 1 year ago

@logicwic or @GolamRashed are you still having this issue? I'm able to connect Superset to SQL Server data sources just fine using Superset 2.0.0 or 2.0.1.

kamransuleymanli commented 1 year ago

@logicwic or @GolamRashed are you still having this issue? I'm able to connect Superset to SQL Server data sources just fine using Superset 2.0.0 or 2.0.1.

Hey @sfirke I got same issue, can you guide me ?

sfirke commented 1 year ago

Can you provide more details @kamransuleymanli ? Superset version, install method (docker?), what you have tried, what's happening. I'm using a docker setup so added a file /superset/docker/requirements-local.txt that contains the line pymssql so that pymssql package gets installed when Superset starts with docker-compose up.

kamransuleymanli commented 1 year ago

Can you provide more details @kamransuleymanli ? Superset version, install method (docker?), what you have tried, what's happening. I'm using a docker setup so added a file /superset/docker/requirements-local.txt that contains the line pymssql so that pymssql package gets installed when Superset starts with docker-compose up.

Thanks for quick response friend. Yes its docker. But Unfortunately I am not the person who do this stuffs. So I am like newbie on this one. So please explain simple as possible.

My colleague use this one mssql+pymssql://Username>:<Password>@<Host://?Encrypt=yes and when we try it, this happen : ERROR: Could not load database driver: MssqlEngineSpec

You said

" I'm using a docker setup so added a file /superset/docker/requirements-local.txt that contains the line pymssql so that pymssql package gets installed when Superset starts with docker-compose up "

So we should add requirements-local.txt file to docker folder ? and pymssql line into it ? btw pymssql line meaning is "mssql+pymssql://:.. " this ?

sfirke commented 1 year ago

Yes, that error indicates that Superset doesn't have the SQL Server driver needed. So correct, you'll add a requirements-local.txt file to the docker directory and add that one word of text: pymssql. Then when you run docker-compose up, that Python package will get installed and your Superset instance will have the driver. That's described here: https://superset.apache.org/docs/databases/docker-add-drivers Ignore the part about building your own image, use the Docker Compose part.

Then in Superset, use the connection string starting with pymssql from here: https://superset.apache.org/docs/databases/sql-server/ :

mssql+pymssql://<Username>:<Password>@<Host>:<Port-default:1433>/<Database Name>

kamransuleymanli commented 1 year ago

Thanks for trying help; we did steps like you explained;

But after "docker-compose up" step we got an error like this:

FATAL: could not write lock file "postmaster.pid": No space left on device superset_db exited with code 1 superset_cache exited with code 137

Got any idea how to move on ?

sfirke commented 1 year ago

Looks like that's related to your Docker installation needing more disk space, it's not a problem particular to Superset: https://stackoverflow.com/questions/43354778/docker-fatal-could-not-write-lock-file-postmaster-pid-no-space-left-on-devic

kamransuleymanli commented 1 year ago

Thanks a lot @sfirke Finally we solved problem

With every try Docker up command, files was not overwriteing existed one. So that lead as Disk Space problem. But even with more disk space Docker up command didn't gave error but didnt gave solution either.

How we did: We delete superset, start build from scratch. https://superset.apache.org/docs/installation/installing-superset-using-docker-compose/

When we has to docker-compose -f docker-compose-non-dev.yml pull docker-compose -f docker-compose-non-dev.yml up

begin your way, add a requirements-local.txt file to the docker directory and add that one word of text inside it: pymssql

Then docker-compose -f docker-compose-non-dev.yml pull docker-compose -f docker-compose-non-dev.yml up

Again Thanks a lot. Have a nice weekend =)

sfirke commented 1 year ago

Excellent, glad to hear it! Thanks for posting back what worked.

rusackas commented 1 year ago

Thanks @sfirke! Problem solved, so the issue is hereby resolved.