SQL Server

Connect Microsoft SQL Server 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	3 modes (default encrypted_verify_certificate)
SSH tunnel	Yes
Cloud	Yes
On-premises (via Gateway)	Yes

Prerequisites

• SQL Server authentication (user name and password) enabled on the server. Windows authentication is not supported.
• The server listens on a fixed TCP port (default 1433). A named instance must have its own static port: host\instance is not accepted and SQL Browser is not used.
• A login that can create logins and users, to run Step 1.
• The host name, port and database name.

Step 1: Create a read-only user

Run as an administrator. Replace the placeholders.

-- On the server (master)
CREATE LOGIN fresco_reader WITH PASSWORD = '<strong-password>';

-- In the database you want to read
USE <database>;
CREATE USER fresco_reader FOR 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. To read every schema of the database instead:

ALTER ROLE db_datareader ADD MEMBER fresco_reader;

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

Optional: when snapshot isolation is allowed on the database (ALLOW_SNAPSHOT_ISOLATION ON), Fresco BI reads all tables of one load at the same point in time. Without it, each table is read on its own.

Step 2: Allow network access

Cloud. The server must be reachable from the internet. Allow inbound connections from Fresco BI’s outbound IP addresses (the setup guide beside the data source form lists them) on port 1433 (or your port). For example, in an AWS security group or Azure network security group add an inbound TCP rule for each IP; on Windows Firewall:

New-NetFirewallRule -DisplayName "Fresco BI" -Direction Inbound -Protocol TCP -LocalPort 1433 -RemoteAddress <frescobi-ip> -Action Allow

SSH tunnel. If the database is open only to a bastion host, turn on the SSH tunnel in the form. Fresco BI connects to the bastion with an SSH key, then to the database through it. 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 inside your own network, choose On-premises (via Gateway) as the connection type. The panel then switches to the Gateway guide.

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’s DNS name or public IP, without \instance.
• Port: 1433 by default.
• Database: the database to read.
• User and Password: the login from Step 1.
• SSL mode: see the table below. Keep the default unless you must change it.
• CA certificate (PEM): shown for encrypted_verify_certificate. Paste the CA that signed the server certificate, or the server’s own self-signed certificate. Empty uses the system trust store.
• Connect through an SSH tunnel (bastion): then fill SSH host, SSH port, SSH user and SSH private key (OpenSSH or PEM), plus 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

Mode	What it does
encrypted_verify_certificate (default)	Encrypts and verifies the server certificate and host name
encrypted_trust_server_certificate	Encrypts but trusts the certificate without checking (testing only)
unencrypted	No encryption

Only encrypted_verify_certificate shows the CA certificate (PEM) field. SQL Server uses a self-signed certificate when none is installed: paste that certificate into the CA field, or install a certificate from a trusted CA.

Troubleshooting

• The server rejected the user name or password. Check the user and password, that SQL Server authentication is enabled, and that the login is not locked or expired.
• The host name could not be resolved (DNS). Check the Host field. Use a public DNS name or IP, without \instance.
• The server refused the connection on this port: nothing listens there or a firewall rejects it. Check that TCP/IP is enabled in SQL Server Configuration Manager and the port is right.
• The server did not answer in time. Usually a firewall that drops packets, or a wrong host or port. Allow Fresco BI’s outbound IP addresses (the setup guide beside the data source form lists them) on the database port.
• The host is in a blocked range (private, loopback or metadata). A database inside a private network needs an On-premises connection through a Gateway. Choose On-premises (via Gateway).
• The server certificate is not signed by a trusted CA. Paste the right CA into the CA certificate field. For a self-signed certificate, paste the server’s certificate itself.
• 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 the host name the certificate is issued for.
• The server requires encryption but the chosen mode does not encrypt. Pick an encrypting SSL mode. The server forces encryption: do not use unencrypted.
• The database or object was not found on the server. Check the Database name and that the user from Step 1 exists in it.
• The database user cannot read this object. Grant SELECT on the schema (Step 1).