How Apache Superset connects to SQL Server through pymssql
A team that wants dashboards on SQL Server or Azure Synapse data should start by deciding which database the charts read from, and that database should not be the transactional system serving the application. Superset connects to Microsoft SQL Server through the pymssql driver, using a connection string in the form mssql+pymssql://{username}:{password}@{host}:{port}/{database} with port 1433 as the default. Azure Synapse uses the same pymssql driver, so the connection pattern is the same, while the host, database and credentials point at the Synapse endpoint. You add the connection in Superset's database catalog, the list of data sources it can query. The database catalog entry is what Superset uses to run queries, and it is separate from the metadata database that stores Superset's own dashboards, users, charts and saved queries.
Keep those two databases apart in your plan. The metadata database holds Superset's internal state and should be PostgreSQL or MySQL in production, as the Superset architecture documentation describes. SQL Server is only ever a data source in this design, so a SQL Server outage or a heavy report should not affect Superset's login or dashboard metadata. Confirm the exact driver version and any dialect requirements for your Superset release against the database catalog and the Superset documentation before you build the image, because driver packaging can change between releases. Confirm also whether your Superset image already includes pymssql or whether you must add it during the build.
Before you enter credentials, decide which SQL Server login Superset will use and what it may read. Use a dedicated read-only login mapped to the reporting schemas, not an administrative account or the application's own login. Store the password in your secrets manager and inject it at deploy time rather than writing it into a configuration file or a shared connection string. Test the connection from the Superset host's network segment, not only from your workstation, because firewall rules, DNS and TLS settings often differ between the two. The Superset-side settings that control how these queries run, such as timeouts and row limits, are covered in the performance and security sections of this page.
What the SQL Server connection needs before configuration
Most SQL Server connection problems come from decisions made before anyone opens the Superset database settings. The cards below cover the release, the driver, the login, the network path and the encryption settings that need agreement with your database team.
Confirm the Superset release and install method
Apache Superset 6.1.0 and the 7.0.0rc2 pre-release are the current release references in the project's published materials, but 7.0.0rc2 is a release candidate and should not be treated as production-ready. Confirm which version you will deploy against its release notes and the upgrade guidance, and record whether you run a container image, a PyPI install or the Kubernetes operator. The install method decides where the driver must be added, so settle it first.
Install the pymssql driver in the image
Superset's database catalog lists pymssql as the driver for Microsoft SQL Server, and Azure Synapse uses the same driver. The package must be installed into the Python environment that runs the Superset web server, Celery workers and any scheduled report jobs, or those processes will fail to connect. Ask whether your image build pipeline can add it reproducibly and how it will be pinned for each upgrade.
Create a read-only SQL login
Create a dedicated SQL Server login for Superset that has only SELECT rights on the schemas and views that dashboards need. Do not reuse an administrative account or an application login that can write. Ask the database team whether the login will be a SQL authentication account, since Superset's connection string carries a username and password, and how that password will be stored and rotated.
Confirm network reachability
Superset's web and worker containers must reach the SQL Server host on its listening port, which is 1433 unless your instance or listener uses another port. Check routing, DNS resolution, firewall rules and any network segmentation between the Superset hosts and the database, including the path from Kubernetes nodes if you run there. Test the path from the same runtime that will execute queries, not only from a workstation.
Agree on TLS settings with the database team
Ask the database team whether connections must be encrypted, which certificate authority issued the server certificate, and whether the Superset host must trust a private CA. Decide whether certificate validation is enforced or whether the server name must match the certificate. Record the answer before configuration, because encryption options differ between SQL Server, Azure Synapse and any proxy or listener in front of them, and changing them later can break dashboards.
Document the Windows and Azure identity limits
The standard pymssql connection string authenticates with a username and password, so integrated Windows authentication and Microsoft Entra sign-in are not covered by the basic configuration in the database catalog. Ask your identity and database teams whether they require either method and what authentication path they will accept. Treat any gap as a scoping item to resolve before the build starts.
How to configure the SQL Server database connection in Superset
This sequence takes a self-hosted Superset from a running instance to a verified connection to SQL Server or Azure Synapse, with a read-only login and a first chart that proves the data is reachable. Each step ends with a check so the team knows it can move on.
Install the pymssql driver into the Superset image
The platform engineer adds the pymssql package to the Superset image or the virtual environment that runs Superset, so the SQLAlchemy dialect for SQL Server is available to the web and worker processes. Install it in every environment that runs Superset, including the Celery workers if you use alerts, reports or async queries, because those processes also open database connections. Confirm the install by importing pymssql from the same Python environment Superset uses and restarting the services. The step is complete when the import succeeds in each container or host that runs Superset. Azure Synapse uses the same pymssql driver, so the same install covers both targets.
Create a read-only login on the SQL Server
A database administrator creates a dedicated SQL login or contained user for Superset and grants only SELECT on the schemas and views that dashboards need. Do not reuse an application account or a sysadmin login, and do not grant write or DDL permissions. Confirm the scope by connecting with the new login in a query tool and checking that a SELECT on an approved view works while an INSERT or CREATE statement is refused. The step is complete when the permission test behaves as designed and the credentials are stored in a secrets store your team controls. Ask your DBA to document which schemas the account can read so the access review is simple later.
Build the connection string from the catalog template
The engineer assembles the connection string from the SQL Server entry in the Superset database catalog, which uses the form mssql+pymssql with the username, password, host, port and database placeholders. The default port is 1433 unless your server listens elsewhere, and the password should be URL-encoded if it contains special characters. Keep the password out of the string in a shared document by supplying it through your secrets handling process at configuration time. The step is complete when the string is written with your host, port and database name and reviewed by a second person. Confirm the port and database name with the DBA before this step closes.
Add the database connection in Superset settings
A Superset administrator opens the database settings, adds a new database, selects Microsoft SQL Server, and pastes the connection string. Save the connection with the display name your analysts will see in the SQL Lab and dataset screens. Review the advanced options, including any extra parameters for encryption or timeouts, and record the choices in your configuration notes. The step is complete when the database appears in the list with the expected name and the connection is saved without errors.
Test the connection before saving
Use the test connection action in the database dialog before finalizing the record. A successful test confirms that the driver loads, the network path from the Superset host to the SQL Server is open, and the login is accepted. If the test fails, check the container logs for driver or authentication errors, then confirm firewall rules and the TCP port with your network team. The step is complete when the test reports success from the same host and environment that runs the web application. Repeat the test from a worker container if you use scheduled reports, since workers make their own connections.
Add a dataset from an approved view
An analyst or data owner opens SQL Lab or the dataset screen, selects the new database, and adds a dataset based on an approved table or view rather than a broad system catalog query. Prefer views that the DBA has shaped for reporting, because they keep dashboards stable when base tables change. Confirm the dataset loads its column list and that the data types match what the source contains. The step is complete when the dataset is saved and its columns are visible in the dataset panel.
Build a sample chart and check row counts
The analyst creates a simple chart, such as a count of records grouped by one dimension, and compares the result with a query the DBA runs directly against SQL Server. Matching totals confirm that the connection, the permissions and the dataset definition all point at the intended data. If the numbers differ, check the schema selected in the dataset, any filters, and time zone handling for date columns. The step is complete when the chart output matches the reference query for the same filter. Save the chart and record the reference query so future checks have a baseline.
Where Windows and Azure authentication stop working
The documented SQL Server connection string in the Superset database catalog uses a username and password: mssql+pymssql://{username}:{password}@{host}:{port}/{database}. That entry does not cover integrated Windows authentication, Kerberos, or Azure Active Directory token flows. Do not promise any of them to the business until the driver and your team have confirmed that the pymssql path supports them in your exact Superset image, and until someone has tested a connection end to end. The same limit applies to Azure Synapse, which the catalog also lists under the pymssql driver.
The practical default is a dedicated SQL login for Superset with a read-only role, granted only the schemas and views its dashboards need. Store the password outside the plain configuration file, for example in your container orchestrator's secret store or a vault, and inject it at runtime rather than writing it into the connection string in the Superset metadata. Plan a rotation schedule that includes a step to update the secret, restart the workers that hold connections, and confirm that alerts and scheduled reports still run afterward. Each rotation is recurring work, so count it in the operating model.
Ask your DBA to confirm four things before the build starts: whether SQL authentication is permitted on the target instance or Synapse workspace under your security baseline, which server-side login and database user the service account maps to, whether the login can be restricted by IP range or network path, and how failed logins and password changes are logged and monitored. Ask the team to confirm the pymssql version bundled in your image and whether any Azure-specific connection option is needed. Record the answers, because they decide whether this design is acceptable to your security reviewers.
How SQL Server schemas, logins and Superset objects map
This table compares SQL Server objects with the Superset objects that expose them, and shows which layer enforces each permission. Read it to see where access is decided: the SQL Server login sets what data can be read, while Superset roles only control what each dashboard user can open or change. Rows marked for confirmation need a test in your environment before you rely on them.
| SQL Server object | Superset object | What to know |
|---|---|---|
| Server host and port | Database connection | Set in the mssql+pymssql connection string with a host and port, default 1433. Azure Synapse uses the same driver. |
| SQL Server database | Database connection target | The database name in the connection string selects the catalog. Each additional database needs its own Superset connection. |
| Schema | Schema選択 in dataset creation | Superset lists only the schemas the connection login can see. Visibility follows SQL Server grants, not Superset roles. |
| Table or view | Dataset | Views often suit datasets better because joins and filters live in SQL Server. Confirm how your object types behave in Superset. |
| SQL Server login | Credential on the database connection | Queries run as this login by default, so Superset cannot see the individual end user. Confirm whether user impersonation is available for your setup. |
| GRANT SELECT or EXECUTE on schemas | Enforced by SQL Server, not Superset | These grants are the hard data boundary. Grant read access on named schemas and avoid db_owner or broad roles for the reporting login. |
| Superset role (Admin, Alpha, Gamma or custom) | Controls datasets, charts and dashboards a user can open or edit | Superset roles do not change what the SQL Server login can read. Map them to access to Superset content, not to database privileges. |
| Native SQL Server row-level security | Superset row-level security rules per role | Confirm whether SQL Server row-level security can use the Superset user identity. Without impersonation, Superset row-level filters are the layer to test. |
Securing the SQL Server connection and its credentials
The SQL Server connection is where Superset meets your most sensitive reporting data, so its controls span the database login, the transport, the secrets Superset stores and the people allowed to change connections. Work through these cards before the first dashboard reads production data, and record each decision so it can be tested later.
Read-only login scoped to reporting schemas
Create a dedicated SQL Server login for Superset that holds only SELECT on the schemas and views that dashboards need. Avoid db_owner and shared administrator accounts, and do not let the login write to or alter reporting objects. Superset runs whatever SQL its users send through that login, so the database permissions are the last line of defense if an application-level control fails. Confirm the grants with a test query against a table outside the approved schemas.
Encrypt the SQL Server connection
Require TLS between Superset and SQL Server, and confirm that the server certificate is validated rather than accepted blindly. Check the driver and server settings for your environment, since the pymssql connection options and the SQL Server encryption policy determine whether traffic is protected end to end. Traffic between Superset and the database should not cross untrusted networks, and the team should verify the encrypted session from the Superset host rather than assume it.
Protect the SECRET_KEY
Superset requires a user-specified SECRET_KEY to start, a requirement added in version 2.1.0, so the key is never a default you can leave in place. Store it in your secrets manager or an environment variable injected at runtime, not in a checked-in config file. The configuration documentation also supports rotation through PREVIOUS_SECRET_KEY, so plan a rotation procedure and test it in staging before you need it.
Store database credentials outside the UI
Superset keeps database connection details in its metadata database, so the SQL Server password is only as protected as that database and its backups. Decide who can see connection strings, and where the password is supplied from at deploy time. Confirm with your security team how the metadata database is encrypted at rest and who can read its backups, because a copy of the metadata database is a copy of the SQL Server credentials.
Limit who can add or edit connections
Creating or changing a database connection effectively decides what data Superset can reach, so restrict those permissions to a small platform group. Review which roles inherit the database management permissions in your Superset role model, and remove them from analyst and viewer roles. Changes to connections should go through the same review path as other infrastructure changes, with an audit trail of who changed the host, login or driver.
Control exports and SQL Lab access
SQL Lab lets users run ad hoc SQL against the SQL Server login, and chart exports can move large result sets out of Superset. Decide which roles may open SQL Lab, whether they can export results to CSV or Excel, and whether each database connection allows DML or query execution from SQL Lab. Confirm these settings in the Superset version you deploy, because export and SQL Lab options are governance decisions that should be tested before rollout.
Reporting from SQL Server without slowing transactional workloads
Dashboards that query the primary SQL Server instance compete with order processing for the same CPU, memory, locks and log throughput. Superset issues SQL on behalf of every viewer of every chart, and a dashboard that scans large tables at the start of the business day can slow the transactions that the business depends on. Before the connection is made, the database team should agree which workloads are protected and what load Superset may place on the primary. The options to evaluate are a read replica or secondary that the database team already runs, a separate reporting copy refreshed on a schedule, Superset caching so that repeated chart loads do not re-run the same query, scheduled queries and alerts that run in off-peak windows, and limits on dataset row counts and on the SQL that Superset may run.
Each option has a different cost and a different owner. A readable secondary or a reporting copy depends on the edition, replication features and licensing that the client already holds, and some of those features carry license conditions that the database team must confirm before anyone designs around them. A reporting copy also adds a refresh process, storage, a recovery plan and a data latency that business users must accept in writing. Caching reduces repeat load but serves stale results, so the cache lifetime for each dataset should be set by the data owner. Caching and scheduled jobs require the Superset caching and worker components, which must be sized as part of the same decision.
The right combination depends on the replication and licensing setup in the client's environment, and it must be confirmed with the database team and the license holder before the Superset connection is pointed at any SQL Server instance. As a first check, ask the database team to measure the current load of order processing at peak, identify the heaviest reporting queries, and confirm which replica or copy, if any, may serve Superset. Then set the connection to a read-only login on that target only, and test the heaviest dashboards against it with the database team watching wait statistics. Illustrative example: if a secondary is licensed for read access but lags behind the primary by several minutes, the business owner must agree that dashboards show data that old before the dashboards are published.
Why the SQL Server connection fails and how to fix it
This table lists the failures teams most often hit when connecting Superset to SQL Server or Azure Synapse, with the likely cause and the first check for each. Work down the rows in order, since network and driver problems hide behind what looks like a login error.
| Symptom | Likely cause | First check |
|---|---|---|
| Connection times out or is refused | Wrong port or a network rule blocks the path from the Superset host to SQL Server | Confirm the host and port (default 1433) from the Superset container with a basic TCP test, then review firewall and network security group rules |
| Error that the module or driver cannot be found | The pymssql package is missing from the Superset image, so the mssql+pymssql dialect cannot load | Run a Python import of pymssql inside the running Superset container and check the image build for the package |
| Login failed for user | Wrong username or password, an account without access to the target database, or a Windows-style account that pymssql cannot use | Test the same credentials with a separate SQL client from the Superset host and confirm the login maps to a SQL authentication principal |
| Cannot open database requested by login | The database name in the connection string does not match an existing database the login can reach | Compare the database segment of the connection string with the name shown on the server, and check the user's default database and permissions |
| TLS or certificate handshake error | Encryption is required by the server or Azure endpoint but the certificate chain is not trusted by the Superset container | Confirm the server certificate is trusted in the container's CA store and review the encryption settings your SQL Server or Azure endpoint requires |
| Dashboard or chart loads very slowly | Datasets point at large unfiltered tables, so each query scans far more rows than the chart needs | Open the query in SQL Server to check its execution plan, then add filters, aggregates or a reporting view before rebuilding the chart |
| Connection works in tests but not in Azure Synapse | Synapse endpoint, firewall allow list or user principal differs from the on-premises SQL Server setup | Confirm the Synapse workspace firewall rules include the Superset egress address and that the login exists in the target dedicated or serverless pool |
What teams ask before connecting Superset to SQL Server
What drives the cost of connecting Superset to SQL Server?
The main cost drivers are the number of databases and schemas Superset must reach, the authentication model, the replica design, and how much network change the connection requires. Each database adds work to define connections, roles and data access rules, and each one needs testing. A SQL login is usually simpler to configure than an integrated Windows or Entra ID setup, which can add identity and service configuration. Network changes, such as firewall rules or private routing between the Superset host and the SQL Server, often take the longest because other teams must approve them. Ask any partner for a quote that lists each database, the auth method, the source and target networks, and whether a read replica is in scope. Setup is usually one-time work, while patching the driver, rotating credentials and reviewing access recur.
How long does a SQL Server connection take to set up?
Duration depends on scope rather than on a single fixed figure. A single database on a network that already allows traffic from the Superset host is a much smaller job than several databases across segmented networks with Windows or Entra ID authentication. Time is usually spent on access approvals, DBA review of a read-only account, and testing dashboards against real data volumes. Confirm the exact timeline in writing with any partner after the scope is agreed, and ask which steps depend on other teams. Superset's own upgrade and configuration work is separate and should be scoped on its own.
What must our team provide before the work starts?
Plan to provide the following before the work starts:
- A read-only SQL Server login for each database Superset will query, or the service account details if you use integrated authentication.
- Network access from the Superset host to the SQL Server port, usually 1433 unless your instance uses another port.
- A named DBA or database owner who can review permissions, replica options and query impact.
- A staging Superset instance and a test copy of the reporting data where possible.
- Written decisions on which schemas and tables are in scope and who approves new dashboards.
Ask the partner to confirm this list against your environment before quoting.
Can our own team connect Superset to SQL Server without a partner?
Yes, a capable team can connect Superset to SQL Server alone if it has the skills to install the pymssql driver into the Superset image, write the connection string, and manage SQL Server permissions. The harder parts are usually the authentication model, network approvals, and designing read-only access that does not load the transactional system. Teams that lack a DBA or security engineer often find those areas take the most time. An external partner can work fixed-scope on the parts you choose, such as the connection and access design, while your team keeps operating the platform.
Does Azure Synapse need a different setup from SQL Server?
Azure Synapse uses the same pymssql driver and connection string pattern as SQL Server, so the core Superset setup is similar. The differences lie in the Synapse environment itself: the endpoint, network rules, authentication options and the workload you query. Confirm with the Superset database catalog and your Azure administrators which Synapse features and authentication methods you need, and test each one in staging before you rely on it. Plan for Synapse capacity and query cost as a separate concern from the connection.
How do we keep reporting from slowing the transactional database?
Point Superset at a read replica, a reporting database, or a copy of the data where possible, and give it a read-only account with no write permissions. Set query limits and caching so repeated dashboard loads do not rescan large tables. Ask the DBA to review the heaviest dashboard queries before go-live and to monitor load on the source system. Replica design is often a bigger cost driver than the Superset setup itself, so decide it early.
Which questions should we ask about the Superset side of this work?
Ask how Superset will store its own metadata, which is supported on PostgreSQL and MySQL for production, and where the SECRET_KEY is kept. Ask how role mapping will restrict which SQL Server schemas each user sees, and how upgrades will be tested against the SQL Server connection. Confirm whether alerts, reports or async queries need the caching layer and worker components, since those add infrastructure and operating work. Keep the metadata database separate from the SQL Server analytics source.
How DeployOpen scopes and delivers a SQL Server connection
DeployOpen delivers a SQL Server connection under a written, fixed scope agreed before work starts. The scope names every database Superset will query, the account model used for each (service accounts, read-only roles or Azure identities), the network paths from the Superset hosts to SQL Server or Azure Synapse, and the metadata database Superset uses for its own state. Writing these down first matters because the choices interact: a pymssql connection string, the authentication method, the firewall rules and the database roles all determine what Superset can reach and what a compromised dashboard could read.
The work is carried out inside your own infrastructure, whether that is your on-premises network, your cloud subscriptions or a segregated environment you control. DeployOpen configures the pymssql driver in the Superset image, builds the connection with the agreed credential handling, and tests each database against the read-only accounts and query limits in the scope. Testing covers connection behavior from every Superset node, failed-login and timeout paths, and confirmation that the accounts cannot write. Your team receives the configuration as code where possible, a runbook for rotating credentials, and handover documentation that records every decision and the reason for it.
Ask any partner, DeployOpen included, to confirm which items the quote covers and which stay with your team, such as firewall change approvals, Entra ID or Active Directory group ownership and access to the SQL Server instances. Ongoing support, upgrades of the driver and Superset releases, and periodic testing can be added through an optional retainer, so the connection is not left to drift after handover. DeployOpen is not a Microsoft or Apache Superset partner, reseller or certified provider, and the scope should not imply otherwise.
