4

After installing Superset (open source software from Airbnb) on my virtual machine (RHEL, Linux 7.2-11), I cannot add my MSSQL database in the configuration page.

Menu->Sources->Databases->Add

In the SQLAlchemy URI field, I entered :

mssql+pymssql://user:password@host:port/database

Obviously, with my user, password, host, port and database name.

But when I click "Test Connection" I have the following error :

ERROR: {"error": "Connection failed!
The error message returned was:
(pymssql.OperationalError) (18456, 'DB-Lib error message 20018, severity 14:\
General SQL Server error: Check messages from the SQL Server\
DB-Lib error message 20002, severity 9:\
Adaptive Server connection failed (host:port)')"}

I already installed the pymssql package and I do not really know where this error could come from.

I tried to follow these tutorials but, maybe I'm doing it wrong:

http://airbnb.io/superset/installation.html

http://airbnb.io/superset/tutorial.html#connecting-to-a-new-database

TylerH
  • 20,799
  • 66
  • 75
  • 101
LaPalme
  • 339
  • 5
  • 16

4 Answers4

4

What worked for me is the following:

mssql+pymssql://user:pass@address.of.db/?charset=utf8

However, I didn't manage to define a database using this syntax, and in turn couldn't define/find the available tables.

Dror
  • 12,174
  • 21
  • 90
  • 160
  • Thank you Dror, but it also failed. I am sure there is a problem of driver. I need an equivalent of jTDS driver for python to connect to SQL Server with Windows authentication, from my RHEL virtual machine. Maybe you've heard about such a driver ? – LaPalme Apr 21 '17 at 08:26
  • I don't know... What OS are you using? – Dror Apr 21 '17 at 11:02
  • I am using Red Hat Enterprise Linux 2.7-11 – LaPalme Apr 21 '17 at 13:06
  • Sorry, I don't think I can further help :( – Dror Apr 21 '17 at 14:32
0

I was also having problem to connect with MSSQL. I was on macOS Catalina. I took the following steps, and it worked:

  1. brew install msodbcsql17 mssql-tools (see: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver15)
  2. pip install pyodbc
  3. mssql+pyodbc://UserName:Password@HostIP,Port/DBName?driver=ODBC Driver 17 for SQL Server

Mentioning of 'driver=ODBC Driver 17 for SQL Server' in the end is important. (See: https://stackoverflow.com/a/51266453/13150101)

Muhammad Abu ul Fazal
  • 149
  • 8
0

I found I had to install the MSSQL driver first, which I did with:

docker exec -it superset_app bash -c 'pip install pymssql'

In the Superset UI, I was then able to connect to the database by choosing the "Microsoft SQL Server" database and setting the SQLALCHEMY URI:

mssql+pymssql://username:password@host:port/dbname
Peter Howe
  • 1,403
  • 2
  • 16
  • 30
-1

Please try mssql://user:password@host:port/database

Wendy
  • 640
  • 1
  • 4
  • 8
  • Hi thank you for your answer. I tried it and I have this error now : "ERROR:{"error":"Connection failed! The error message returned was : (pyodbc.Error)('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')"}" Previously I installed the ODBC Driver for SQL Server on Linux (https://learn.microsoft.com/en-us/sql/connect/odbc/linux/installing-the-microsoft-odbc-driver-for-sql-server-on-linux). And I also installed pyodbc. Thank you again :) – LaPalme Apr 11 '17 at 07:21
  • 1
    You need to specify the driver name in the connection string. See http://docs.sqlalchemy.org/en/latest/dialects/mssql.html#hostname-connections – wchatx Oct 04 '17 at 12:12