Docs › Your data
Connect a database
A data source connects a workspace to one of your own databases (SQL Server, PostgreSQL, MySQL / MariaDB or Oracle) so a dataset can be refreshed from it, by hand or on a schedule. Reports read the stored copy of the data, never the live database, so opening or sharing a report doesn't touch your server unless a dataset is set to read it live. A data source sealed for writing can also take the edits people save in the app, through write-back, which is off until its owner turns it on.
Plan availability Data sources are the Data sources add-on: $30 a month per workspace, on the Plus and Ultimate plans. The workspace owner adds it on Plan & billing, the same way as extra users.
Before you start#
- The add-on. Once it is added, the workspace shows "Setting up your secure vault" for a few minutes: each workspace gets its own vault, identity and key service, so credentials are never shared with another workspace.
- An authenticator app for everyone. Before the first data source, the workspace must require an authenticator app of every member, and the requirement must be in effect (any grace period over). Turn it on under Security. While any data source or gateway exists, it stays locked on. See Use an authenticator app.
- Owners and admins add and change data sources, and every change asks for a code from your authenticator app. Anyone else can use the data sources they are given.
- A dedicated, read-only database account. Make one account per data source with only
SELECTon the tables and views you need (and, on SQL Server,EXECUTEon the named stored procedures). The wizard shows the exactGRANTscript for your database. To let the app write edits back, the recommended set-up keeps this data source read-only and adds a second one, with its own account, for writing (see Write-back on a data source).
Add a data source#
- Open Data sources and choose Add data source.
- Name it and choose the database type.
- Choose how to reach it: Direct (a database reachable from the internet, with a password), Cloud database without a password (Azure, AWS or Google Cloud, see Cloud databases without passwords), or Through a gateway (see Install and pair a gateway).
- Enter the server, port and database. For Azure SQL, PostgreSQL or MySQL flexible server you can choose Azure Private Link instead of a public endpoint.
- List the objects that may be read, each with its name (
schema.name) and type (table, view, or on SQL Server stored procedure). Only these can ever be queried. Readers and report authors never type SQL; only the data source's managers can, as query texts. - Under What this data source may do, choose Read only (recommended: datasets and live reads), Read and write ("also runs dataset write actions on the objects you approve for writing") or Write only ("a separate write data source with its own account, for write actions"). The hint says: "Writing needs an account that can change data, so the owner confirms it after the test ("this account can change data"). The safest set-up keeps this data source read-only and adds a second one, with its own account, for writing."
- Choose how the server's certificate is trusted, enter the account and its password, and choose Seal and test.
Your password goes straight from your browser to the workspace's sealed vault. It is never stored by the app or shown again.
Firewalls and certificates#
- A direct connection comes from our published addresses (listed in the app). Add them to your database firewall: an Azure SQL firewall rule, an RDS security group or Cloud SQL authorized networks.
- TLS is always on and always checked. A server with a public certificate needs nothing more. With your own certificate authority, upload its certificate (on SQL Server, upload the server's own certificate). For a self-signed certificate, the test shows its subject and SHA-256 thumbprint, and you can pin that exact certificate. Pinning is recorded in the activity log.
Read the test results#
The test connects, checks the account's rights and reads each object's columns:
- Administrative rights (for example
sysadmin,db_owneror a PostgreSQL superuser) always block the data source. Use a dedicated account. - Write rights on a read-only data source block it too, unless the workspace owner accepts this as an audited exception ("I accept that this account can change data").
- Write rights on a data source sealed for writing are expected: "This account can change data, as a data source sealed for writing needs. Write actions run only on the objects approved for writing below, and every change is logged." Only the workspace owner can confirm it, by ticking "This account can change data, and write actions may change data through it (recorded in the activity log as the owner's decision)." Anyone else sees "Only the workspace owner can accept an account that can change data."
- Objects the account can't see are listed, and are not approved.
- Stored procedures show their parameters, and a warning when their definition writes data. A procedure read always runs inside a transaction that is rolled back, so anything it changes is discarded; only its first result set is loaded.
On a data source sealed for writing, each ticked object also has Approved for: Read, Read and write or Write (a view is read only; a write-only data source offers Write alone). Only tables and procedures approved for write can be the target of a write action. The GRANT script then adds INSERT, UPDATE, DELETE on the tables approved for writing and EXECUTE on the procedures.
A failed test still shows what it found, so a certificate that wasn't trusted can be pinned from the result. Untick objects you don't want before confirming: only the ticked ones can be queried, with no need to seal the credential again. The others stay listed, and you can approve them later after another passing test. To add an object the test didn't find, replace the credential with it listed. When it looks right, choose Confirm and activate.
Create a dataset from it#
- On Datasets, choose New dataset from a data source (or New dataset from it on the data source).
- Pick the table, view or procedure, and the columns to load. A column added in the database later is reported, not loaded, until someone adds it here.
- Add filters if you need them: a column, an operator (equals, between, in a list, is blank, starts with, contains and others) and a value. Values are typed to match the column, and a date can be relative, such as Start of this month or 30 days ago, worked out in the time zone you choose.
- For a stored procedure, give each required parameter a value. Optional parameters can use their default.
- Choose whether each refresh replaces every row or adds a new snapshot, and the schedule: only when asked, every 15 minutes to 24 hours, daily or weekly.
- Choose Create and refresh now.
A dataset filled from a data source is a read-only copy: uploading a file to it is refused, and its rows can be edited only through write-back: key fields and write actions that write the edits into the database (on a table, Make this table editable adds them in one click). To make it an ordinary dataset again, open its Source and choose Stop filling this dataset.
Write-back on a data source#
Write-back lets the edits people save in a report grid or on a dataset's page be written into this database, through a dataset's write actions: a generated statement on a table, a stored procedure, or a write query text. It needs a data source sealed for writing (see Add a data source), and its owner or an admin turning it on.
The data source's page has a Write-back section for owners and admins. It says what the data source is sealed for ("Sealed for: reading only", "reading and writing" or "writing only"). On one sealed for reading only, it explains how to get a write source: add a second data source with its own account, or Replace credential, choose "Read and write", and confirm "this account can change data" after the test.
On a data source sealed for writing:
- Tick Dataset editors may run write actions. It is off by default. While on, anyone who may edit a dataset with a write action on this data source can save edits that run it; every change is logged on the dataset with who saved it. Each action runs as the data source's account, in one transaction per save.
- Set the Limits:
Limit Default Allowed Most changed rows per save (up to 1,000) 1,000 1 to 1,000 Most data per save (MB) 4 1 to 4 Time limit (seconds) 60 5 to 300 Saves per person per minute 10 1 to 60 Saves per day, everyone 5,000 1 to 100,000 (a UTC day) - Click Save write-back, and enter a code from your authenticator app. Undo changes puts the form back.
The page then says "Write-back is on: dataset editors may now run this data source's write actions, within these limits.", "Write-back is off: saves that need this data source are refused." or "Write-back limits saved." Turning it on needs the Data sources add-on; turning it off or changing a limit doesn't. Sealing the data source again for reading only turns write-back off.
Things to know:
- A save past a limit is refused before anything is written ("That's more edited rows than one save may send to the data source.", "You're saving too often. Try again in a minute.").
- After 5 failed write runs in 5 minutes, write runs pause for 10 minutes and the owner is emailed once. The section shows "Write runs are paused until time after 5 failed write runs. The owner was notified."
- Consecutive actions of one save on this data source run in one transaction, committed at the end or rolled back whole. The first run of a new action is a dry run unless its author turns that off.
- Nothing is read back from the database after a write: a dataset filled from this data source is refreshed after a save that wrote to it, and a live dataset reads again.
- Every dataset editor may run the actions once write-back is on; there is no separate list of people. Defining an action on this data source needs Manage on it (or being the owner or an admin).
- Through a gateway, the gateway must also allow write-back for this source and approve each write action and query text locally: "The gateway must also allow write-back for this source and approve each write action locally." See Write-back through a gateway.
The Limits form checks each value against the ranges the server allows and names the one that is out of range, for example "Time limit (seconds): a whole number from 5 to 300."
Query texts#
A query text is a statement typed by a manager of the data source, with typed parameters, where a query would otherwise name a table, view or procedure. A read text can fill a dataset or be read live, like a procedure; a write text can be the target of a dataset's write action. Readers and report authors never type SQL.
The data source's page lists its texts under Query texts, with what each does ("Reads" or "Writes"), what the test read it as, its number of parameters, when it changed and its review state. Everyone allowed to use the data source sees the list; managers (members with Manage, the owner and admins) click New read text, New write text (only on a data source sealed for writing: "Write texts need a data source sealed for writing.") or Edit.
Writing a query text#
The dialog, "New query text" or "Query text: name", reminds you: "A text runs with everything the data source's account may do; a read-only account is the protection for reads. Every value is a bound parameter written as @Name: never put a value into the text itself. One statement per text."
- Name it, and under It choose reads rows (a dataset or a live read) or writes rows (a dataset's write action).
- Type the Statement. A read text is "One SELECT (WITH allowed) or one EXEC of a procedure. Runs in a transaction that is always rolled back (read-only elsewhere)." A write text is "One INSERT, UPDATE, DELETE, MERGE or EXEC (CALL on other engines)."; for an edit, NULL in a parameter usually means "unchanged", so write
COALESCE(@Qty, Qty), and a clear flag for a field that may be emptied (see Patch and clear). - Each
@Namein the statement becomes a row under Parameters:Column What it sets Type Text, Whole number, Decimal number, Date, Date and time or True / false List Takes several values. On PostgreSQL it is "Sent as an array: compare with = ANY(@Name)."; elsewhere it is sent as one value joined with the delimiter you set ( ,by default), and the text splits it, for example on SQL ServerIN (SELECT value FROM STRING_SPLIT(@Name, ',')).Required It must be given a value Default Tick A default and give a value; a date may be relative, such as the start of this month - Click Save and test. The text is classified, scanned for injection patterns and described on the data source; the dialog follows the test.
Under Test and review the dialog shows the text's state, what it was read as ("Read as: …"), the columns a read returns, and any findings:
| State | Meaning |
|---|---|
| Clear | "The test found nothing to review: the text can run." |
| Waiting for review | "The scanner found patterns an admin must review. Until the workspace owner or another admin approves it, the text can't run anywhere." |
| Approved | "An admin reviewed the findings and approved this version: it can run." |
| Rejected | "An admin rejected this version. It can't run; change the text to have it scanned and tested again." |
| Refused | "The text was refused: it isn't a single statement of the kind chosen, or it uses a form that is never allowed. Rewrite it." |
| Not tested | "Not tested yet. A text can't run until its test has passed." |
Each finding shows its severity (Refused, Needs review or Warning), its line and what the pattern means, with the statement below and the flagged lines marked. Test again reruns the test; Delete removes the text. Changing an approved text clears its approval and scans it again.
What the scanner looks for:
| Pattern | What it means |
|---|---|
stackedStatement | A second statement after a semicolon, even inside a comment. One text is one statement. |
commentTrick | A comment that cuts off the rest of the statement, a closing */ with no opener, or nested block comments. |
dynamicSql | SQL built at run time (EXEC of an expression, sp_executesql, EXECUTE IMMEDIATE, PREPARE, or text joined to a parameter), which bound parameters can't protect. |
tautology | A condition that is always true, such as OR 1=1 or OR @p = @p, which can make a filter match every row. |
unionProbe | A UNION SELECT reading the database's catalog (sys., information_schema, pg_catalog …) or a NULL, NULL column list. |
systemAccess | Reaching outside the database: xp_cmdshell, OPENROWSET, OPENQUERY, BULK INSERT, file reads, network calls, or another database or linked server. |
timing | A deliberate delay (WAITFOR DELAY, pg_sleep, SLEEP, BENCHMARK). |
obfuscation | Hidden keywords: CHAR() sequences, hex literals in place of text, or a keyword split by comments. |
unparameterisedUserValue | A literal compared where a declared parameter was probably meant (a warning only). |
writeInRead | A read text that writes: SELECT … INTO, or a call to a procedure or function approved as a writer. |
Some of these (systemAccess, timing, obfuscation, and forms the classifier never allows) refuse the text outright. Others make it wait for review. False positives are expected (WHERE 1=1 is common, and a reporting query may read information_schema on purpose), which is what the review is for.
A read text that is Clear or Approved appears under Query texts when you pick the object of a dataset from this data source ("Table, view, procedure or query text"), and runs like a procedure read. A write text that is Clear or Approved is offered in a write action's builder. A text that waits for review, or was rejected or refused, can't run anywhere: a save through its action is refused ("An action's query text can't run until its review is done.").
Reviewing query texts#
The workspace owner and admins review flagged texts on Data sources, Query text review (with a count of texts waiting, also shown beside Data sources in the workspace menu). The page lists each text with its data source, its author, its version, the findings explained and the whole text with the flagged lines marked.
- Approve as safe approves this version: it may run until it is changed. Reject keeps it from ever running; the author sees why. Add an optional Note for the author first. Both ask for a code from your authenticator app.
- The owner and admins can review texts they wrote themselves too.
- The page then says "name is approved: this version may run. Changing it scans it again." or "name is rejected: it won't run, and its author sees why." Every decision is in the activity log.
Through a gateway, an approved text also needs the gateway's own approval, and the gateway scans it again; see Write-back through a gateway.
Runs and activity#
Each data source lists its runs: who or which schedule started each one, the rows and size loaded, how long it took, and what went wrong in plain words when it failed (for example the sign-in was refused, or the database couldn't be reached). Owners and admins also see the Activity log: every change, test, refresh and refused access, with who did it and from where. It can't be edited, and it can be exported as CSV.
Public reports#
By default a report that uses a dataset filled from a data source can't be made public. An owner or admin can turn on Allow public reports on the data source. Making such a report public then warns which data source it publishes and how often it refreshes. Visitors still see only the stored copy, in the public setting's row access group.
Use your own key#
On Data sources, Customer key, the workspace owner can have its stored credentials encrypted under a key held in your own Azure Key Vault, AWS KMS or Google Cloud KMS. The page shows the workspace's issuer and key subject and generates the setup for your cloud with them filled in. Run it, then enter the key id and what it printed:
- Azure: the tenant ID and the client ID of the identity the key trusts.
- AWS: the role ARN, the region, and the audience of your IAM OIDC provider (
sts.amazonaws.comunless you changed it). - Google Cloud: the workload identity provider (
//iam.googleapis.com/projects/…/providers/…) and the service account the key is shared with.
The key is checked within minutes. Disabling the key stops every cloud refresh from the next run on, and each use of the key appears in your own cloud's key logs.
Stop or remove access#
- Disable a data source to stop it at once; Enable brings it back.
- Replace credential seals a new password or connection. It doesn't change the password in your database or end sessions already open: create the new database password, replace it here, test, then drop the old one.
- Delete stops the data source and removes its credentials; its datasets stay as ordinary datasets.
- If the add-on is removed, data sources are disabled at the end of the paid period and deleted 30 days later unless it is added again.
Plan limits summary#
| Feature | Free | Mini | Plus | Ultimate |
|---|---|---|---|---|
| Data sources add-on ($30 a month per workspace) | No | No | Yes | Yes |
| Direct, cloud identity and gateway connections | No | No | With the add-on | With the add-on |
| Scheduled refreshes into datasets | No | No | With the add-on | With the add-on |
| Your own encryption key (customer key) | No | No | With the add-on | With the add-on |
| Data sources sealed for writing, and write-back from datasets | No | No | With the add-on | With the add-on |
| Query texts, and their review | No | No | With the add-on | With the add-on |