Skip to content
Fresco BIFresco BIFresco BI Docs

Search is only available in production builds. Try building and previewing the site to test it out locally.

Azure Synapse Analytics

Azure Synapse Analytics uses the Azure SQL Database guide below. What differs for Synapse SQL pools:

  • Import only: DirectQuery is not available for Synapse.
  • Host: a dedicated pool is <workspace>.sql.azuresynapse.net (or a legacy <server>.database.windows.net); a serverless pool is <workspace>-ondemand.sql.azuresynapse.net. Port 1433.
  • SSL is locked: always encrypted and verified. SSH tunnel and On-premises (via Gateway) are available.
  • External tables of a serverless pool are listed as tables.
  • Synapse has no snapshot isolation: each table is read on its own, not at one point in time. No row estimates or foreign keys are shown.
  • A paused dedicated pool shows No connection could be opened to this host and port. Check the host, the port, and that the firewall allows Fresco BI’s IP. Resume the pool, then test again.
  • In a serverless pool, the user also needs access to the files behind external tables; otherwise reads fail with The database user cannot read this object.
  • Add the firewall rule for Fresco BI’s outbound IP addresses (the setup guide beside the data source form lists them) under the Synapse workspace’s Networking page.

Connect Azure SQL Database or Azure SQL Managed Instance to Fresco BI. Fresco BI reads your tables and views read-only: it imports them into a Data Table, or queries them live with DirectQuery.

Feature Supported
Import Yes
DirectQuery Yes
SSL/TLS Always on and verified
SSH tunnel Yes
Cloud Yes
On-premises (via Gateway) Yes

Prerequisites

Section titled “Prerequisites”
  • SQL authentication (user name and password). Microsoft Entra ID sign-in is not supported.
  • The server name, for example <server>.database.windows.net, and the database name.
  • An admin account on the server (the server admin or a Microsoft Entra admin) to run Step 1.

Step 1: Create a read-only user

Section titled “Step 1: Create a read-only user”

Option A: contained database user (recommended). Connect to your database itself, not to master, and run:

CREATE USER fresco_reader WITH PASSWORD = '<strong-password>';
GRANT SELECT ON SCHEMA::<schema> TO fresco_reader;

Option B: server login. Connect to master:

CREATE LOGIN fresco_reader WITH PASSWORD = '<strong-password>';

Then connect to your database:

CREATE USER fresco_reader FROM LOGIN fresco_reader;
GRANT SELECT ON SCHEMA::<schema> TO fresco_reader;

A grant on the schema also covers tables and views created there later. Repeat the GRANT for each schema, or give read access to every schema:

ALTER ROLE db_datareader ADD MEMBER fresco_reader;

Do not give the user write rights or db_owner. Fresco BI only runs SELECT.

Step 2: Allow network access

Section titled “Step 2: Allow network access”

Cloud. In the Azure portal open the logical SQL server, then Networking. Under public access add a firewall rule for each address in Fresco BI’s outbound IP addresses (the setup guide beside the data source form lists them). Azure SQL Database listens on port 1433. A Managed Instance public endpoint uses port 3342: enter it in Port.

The same rule can be added with the Azure CLI:

Terminal window
az sql server firewall-rule create --resource-group <resource-group> --server <server> --name frescobi --start-ip-address <frescobi-ip> --end-ip-address <frescobi-ip>

SSH tunnel. If the database is reachable only from a bastion VM, turn on the SSH tunnel in the form. Allow Fresco BI’s outbound IP addresses (the setup guide beside the data source form lists them) on the bastion’s SSH port.

On-premises (via Gateway). For a database reachable only from a private network (for example through a private endpoint), choose On-premises (via Gateway) as the connection type. The panel then switches to the Gateway guide.

Step 3: Fill in the form

Section titled “Step 3: Fill in the form”
  • Connection type: Cloud, or On-premises (via Gateway) and then a Gateway cluster.
  • Display name: the name shown in Fresco BI.
  • Host: the server name, for example <server>.database.windows.net.
  • Port: 1433 by default (3342 for a Managed Instance public endpoint).
  • Database: the database to read.
  • User and Password: the user from Step 1.
  • SSL mode: fixed. This source always encrypts the connection and verifies the server certificate.
  • CA certificate (PEM): leave empty. Azure certificates chain to public roots in the system trust store.
  • Connect through an SSH tunnel (bastion): then fill SSH host, SSH port, SSH user, SSH private key (OpenSSH or PEM) and Key passphrase (if any). On the first test, compare the shown key with the fingerprint from the bastion’s administrator and click Trust this key.

Click Test connection. The result shows the server version and the Security state of the connection.

SSL modes

Section titled “SSL modes”

Azure SQL connections are always encrypted and verified (encrypted_verify_certificate); the mode cannot be changed.

Troubleshooting

Section titled “Troubleshooting”
  • The server rejected the user name or password. Check the user and password. For a contained user, the Database field must name the database where the user was created.
  • The database user cannot read this object. At the connection test this usually means the server firewall does not allow Fresco BI’s IP: add Fresco BI’s outbound IP addresses (the setup guide beside the data source form lists them) under Networking. When reading a table, grant SELECT on its schema (Step 1).
  • No connection could be opened to this host and port. Check the host, the port, and that the firewall allows Fresco BI’s IP. A serverless database may be paused or resuming: wait a minute and test again.
  • The server did not answer in time. Usually a firewall that drops packets, or a wrong host or port. Check the port (1433, or 3342 for a Managed Instance) and the firewall rules.
  • The host name could not be resolved (DNS). Check the Host field. Use the full server name ending in .database.windows.net.
  • The SSL connection could not be set up with the chosen mode. Check the SSL mode and the CA certificate. With a custom DNS name, the certificate does not match: use the server’s real name.
  • The server certificate was not issued for the host name entered. Use the name in the certificate or a mode that checks the CA only. Enter <server>.database.windows.net, not an alias or IP.
  • The database or object was not found on the server. Check the Database name.
  • A size or concurrent-connection limit was exceeded. The database reached its resource limits: try later or scale up its service tier.

© 2026 Fresco BI. All rights reserved.