Superset cannot connect to my MSSQL database

Question

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. If you have an idea or if you already solved this problem, please just let me know. 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

Thank you !

Answer 1

What worked for me is the following:

mssql+pymssql://user:[email protected]/?charset=utf8

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

Answer 2

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://docs.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)

Answer 3

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