---
title: Connect SQL Server
description: Connect Microsoft SQL Server or Azure SQL - the two addresses to allow in your firewall, the read-only login, and where this engine is narrower.
updated: 2026-09-22
tier: pro
engines: [sqlserver]
---

Microsoft SQL Server or Azure SQL. Read
[Opening your database to Dashies](#1-open-your-database-to-dashies) before you plan
anything else: on this engine the network step is the one most likely to stop you, and
it is a firewall rule naming two addresses rather than a port open to the internet.

## Before you start

- **Dashies connects OUT to your database** to test the connection and to run each
  refresh, on **port 1433**. Any other port is refused with
  `The port must be the SQL Server port (1433).` The Postgres ports are rejected here
  and 1433 is rejected there; the two sets never mix.
- **Every connection comes from two fixed addresses**, one IPv4 and one IPv6, set by the
  region your workspace runs in. Section 1 names where to find them and the rule to add.
- **You must create a read-only login.** On this engine that is a requirement, not a
  recommendation: the connection test refuses a login that can write, and on a
  refreshing dashboard that login is what stands between the addresses you allow and
  your data.
- **TLS verifies by default and is not configurable on this engine.** What that means
  per provider is in [Certificates](#certificates).

## 1. Open your database to Dashies

> Dashies connects to your database only from the addresses listed for your workspace in
> its settings: the IPv4 and IPv6 addresses for the region your workspace runs in. Allow
> those addresses on port 1433, and nothing wider. Every connection Dashies makes to your
> database comes from them: the connection test, reading your schema, checking a query,
> and every refresh.

**Find your two addresses** in Settings, on your workspace's **General** page, in the
**Region** card, or on [IP addresses](/reference/ip-addresses), which lists every region.
Every workspace in a region uses the same two.

:::warning{title="Allow the published addresses, not one you read out of an error message"}
Azure's blocked-client error names the one address it saw, on one connection and over
one family. It is a useful diagnostic and a poor source for a rule: the page
[IP addresses](/reference/ip-addresses) names both addresses for your region and gives
30 days' notice before either changes, which an error message cannot.
:::

### Azure SQL Database

Two ways in, and the second is the one most customers should use. Azure SQL firewall
rules take IPv4 addresses, so each rule below names your region's IPv4 address, written
here as `<IPv4 address>`, as both its start and its end.

At the **server** level, in the portal under Networking, or with the Azure CLI:

```bash
az sql server firewall-rule create -g <resource-group> -s <server-name> \
  -n dashies --start-ip-address <IPv4 address> --end-ip-address <IPv4 address>
```

At the **database** level, which is narrower and needs no Azure portal access at all,
only `CONTROL` on the one database you are sharing. Run it against that database:

```sql
EXECUTE sp_set_database_firewall_rule N'dashies', '<IPv4 address>', '<IPv4 address>';
```

**Prefer the database-level rule.** It admits us to one database instead of every
database on the server, it does not require an Azure control-plane change or a role
your DBA may not have, and on a contained database with no failover partner it takes
effect immediately instead of after five minutes.

**Do not use the "Allow Azure services and resources to access this server" toggle.**
It looks like the generous option and it will not work at any setting: that toggle
admits Azure-internal addresses only, and Dashies does not run inside Azure. It would
also admit every other Azure customer's services, which is worse than what we are
asking for.

**The five-minute delay is real and it applies in both directions.** Microsoft's own
message says "It may take up to five minutes for this change to take effect", and that
covers a rule you delete as well as one you add. So a refresh that fails minutes after
you added the right server-level rule is not evidence the rule is wrong.

### Amazon RDS for SQL Server

Set the instance to publicly accessible, and add inbound rules to its security group:
type Custom TCP, port 1433, one with source `<IPv4 address>/32` and, if the instance has
an IPv6 address, one with source `<IPv6 address>/128`. For the instance to be reachable
at all, every subnet in its DB subnet group must be public with an internet gateway and
a `0.0.0.0/0` route, and the VPC must have DNS hostnames and DNS resolution enabled.

**If the instance is in a private subnet, leave it there.** Dashies cannot reach a
private RDS instance without a VPN or Direct Connect, and moving a production database
to a public subnet to accommodate a reporting tool is not a trade we would recommend.

### SQL Server you run yourself

Allow TCP 1433 inbound from your region's two addresses on the host firewall and on
anything in front of it. This is the case where we would most encourage you to point us
at a read replica or a restored copy rather than at the production instance.

### What that rule exposes, stated plainly

> This rule lets Dashies reach your database's login prompt. It does not give Dashies
> your data, and the two addresses are shared by every Dashies workspace in your region,
> so it admits Dashies rather than your workspace alone. What stands between the login
> prompt and your data is:
>
> - **the login you create for us**, which has `SELECT` on the schemas you name and
>   explicit `DENY` on insert, update, delete and schema changes. Dashies refuses a
>   login that can write, so this is enforced rather than recommended.
> - **the strength of that login's password.**
> - **TLS**, which Dashies verifies, so the connection is encrypted and the server is
>   authenticated.
> - whatever your server already does about failed logins and auditing.

## 2. Create the read-only login

Run this on your SQL Server. It is the exact script the connect form shows, and the
form shows it always rather than behind a toggle, because the connection test refuses a
login that can write.

```sql
-- in master:
CREATE LOGIN dashies_ro WITH PASSWORD = '<a strong password>';
-- in your database:
CREATE USER dashies_ro FOR LOGIN dashies_ro;
GRANT SELECT ON SCHEMA::dbo TO dashies_ro;
DENY INSERT, UPDATE, DELETE, ALTER ON SCHEMA::dbo TO dashies_ro;
DENY CREATE TABLE TO dashies_ro;
-- repeat the GRANT/DENY for each schema you import if it is not dbo (e.g. SCHEMA::sales).
```

**Schema-scoped `GRANT SELECT` rather than `db_datareader`, deliberately.**
`db_datareader` is exactly `GRANT SELECT ON DATABASE::<db>`, and Microsoft's own
guidance argues against it: it grants read access to every table in the database, which
is more than is strictly necessary. Ours grants only the schemas you name.

**On Azure SQL, a contained database user is the easier route.** `CREATE LOGIN`
requires connecting to `master` as the server admin or a member of `loginmanager`,
which many teams cannot arrange quickly. A contained user needs only `CONTROL DATABASE`
on the one database, pairs with the database-level firewall rule above, and has no
firewall propagation delay to wait out.

**On Amazon RDS, two things differ.** The master user is **not** `sysadmin` there - it
is a member of `processadmin`, `public` and `setupadmin`, and `sysadmin`,
`securityadmin` and `dbcreator` do not exist on RDS - so nothing here asks for
`sysadmin` and no error text should send you looking for it. And **logins are not
synchronised to a read replica**: the symptom is a login failure against the replica
while the identical credentials work against the primary, which is a genuinely
confusing shape.

:::danger{title="What the privilege probe can and cannot see"}
The probe counts over-privileged signals on the login: `sysadmin`, and membership of
`db_owner`, `db_datawriter`, `db_ddladmin`, `db_securityadmin`, `db_accessadmin`, or
`db_backupoperator`, plus any database permission among INSERT, UPDATE, DELETE, ALTER,
CONTROL, and CREATE TABLE.

**It is DATABASE-scoped.** A login holding a write grant at the OBJECT or SCHEMA level
only - `GRANT INSERT ON dbo.orders`, say - scores zero violations and is accepted. If
you build the login from the script above it has no such grant; if you reuse an
existing login, check it yourself.
:::

## 3. Turn on row versioning, so each dataset is one snapshot

**Where your database allows it, Dashies reads a dataset inside a snapshot transaction,
so that dataset is one consistent instant however long the read takes and whatever else
is writing while it runs.** Azure SQL Database ships with both settings on and needs
nothing from you. A SQL Server you run yourself, and Amazon RDS, ship with both **off**.

**What this does NOT promise, said plainly because no vendor on this market says it at
all:** a dashboard's datasets are read SEPARATELY, one per read, so two datasets are two
snapshots taken moments apart rather than one instant across the whole dashboard. If two
numbers on a page must agree to the row, put them in one dataset.

Run these against the database you are sharing:

```sql
ALTER DATABASE <your database> SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE <your database> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
```

The second statement takes an exclusive lock for an instant;
`WITH ROLLBACK IMMEDIATE` is what stops it waiting behind open transactions. Neither is
a permission a read-only login has, so these are run by somebody who administers the
database, once.

**What Dashies does without them.** It reads the two settings at the start of every read
and takes the best path the database actually permits, then **reports which one it used**
on the run. With neither setting on, the read is an ordinary read-committed one, which
means it can wait behind an uncommitted writer and the rows it returns are not a single
instant.

**Dashies never uses `NOLOCK`.** It does not block, and that is exactly the trade being
refused: it admits rows that were never committed and rows read twice or not at all.
Dashies ships the only copy of your numbers and there is no second query to correct a
double-counted row, so a read that cannot block is not worth a number that can be
wrong.

## 4. Add the data source

Open [dashies.ai/app/connections](https://dashies.ai/app/connections), click to add a
data source, and pick **SQL Server**.

| Field | Required | Example | Notes |
|---|---|---|---|
| Host | yes | `sql.example.com` | |
| Port | yes | `1433` | 1433 only. |
| Database | yes | `analytics` | |
| User | yes | `dashies_ro` | The read-only SQL login. |
| Password | yes | | Up to 256 characters. |
| Schemas to import | yes | `dbo` | Comma or newline separated. |
| Display name | no | `Analytics warehouse` | Up to 120 characters. |

Schema limits: at most **50 schemas**, each at most **128 characters**, each matching
`[A-Za-z_][A-Za-z0-9_]*`. Those are looser than the Postgres limits.

The password may contain any printable ASCII character including a space, and `;`, `{`,
and `}` are all allowed. A pasted private key or service-account JSON is rejected with
`that looks like a key or key file, not a SQL Server password`.

The host field refuses `\ ( ) , ; = { } ' "` and whitespace, which closes the
`host\instance` form and the `(local)` shorthand. Dashies assembles the connection
string itself from the validated fields and never accepts one you write.

## 5. Test it

SQL Server is a two-step connect, like Postgres. Creating the data source provisions the
connection and leaves the status **pending**. Click **Test** to run the probes and move
it to **active**.

The test signs in, reads back, and runs the privilege check in section 2. Any
over-privileged signal refuses the connection with:

```bash
That SQL login can write to the database. Dashies requires a read-only login. Grant it SELECT only (use the setup script above) and connect again.
```

Connecting as an admin login will not work, however convenient it is for testing.

Other failures:

| Message | Meaning |
|---|---|
| `SQL Server rejected the sign-in. Check the login name and password.` | Authentication. |
| `The secure connection to SQL Server could not be established.` | TLS. See [Certificates](#certificates). |
| `We couldn't reach SQL Server. Check the host and that it accepts connections.` | The host does not resolve, or refuses the connection. A firewall rule that does not name your region's addresses is refused here. |
| `The test failed. Check the details and try again.` | Anything else. |

:::note{title="A green test is a green firewall for every refresh"}
The test and every refresh come from the same addresses, so a connection that reads
**active** has passed the same firewall rule every refresh will meet.
:::

### Certificates

Dashies verifies your server's certificate, and there is no opt-out on the SQL Server
connect form. What that means depends on who runs the server:

| Your server | What happens |
|---|---|
| Azure SQL Database | Verifies with nothing on your side. |
| Amazon RDS for SQL Server | Verifies. RDS presents a certificate that chains to a private, per-region Amazon root, and Dashies ships those roots, so nothing is needed from you. |
| SQL Server you run yourself | Verifies only if somebody installed a real certificate on it. |

**The self-managed default cannot be made to verify by adding a certificate authority.**
A fresh SQL Server presents a self-signed certificate whose subject is the literal
string `SSL_Self_Signed_Fallback`, so it fails the NAME check as well as the chain
check, and no authority you could upload fixes a subject that matches nothing a client
would dial. Install a certificate whose name matches the host you gave Dashies.

**Three different situations produce the same refusal and want different fixes:** a
self-signed leaf, a chain whose root Dashies does not trust, and a certificate whose name
does not match the host you typed. Dashies reports all three as a certificate it could not
verify, so work through them in that order: is the certificate self-signed, is its issuer
a private authority, and does its name match the host you entered.

### Azure serverless auto-pause

If your Azure SQL database is serverless, it pauses after an idle period, 60 minutes by
default. The first connection to a paused database fails and starts the resume. Dashies
waits for it: a resume generally completes in under a minute and the refresh then
succeeds, so a refresh that lands on a paused database takes about a minute longer than
usual and still works.

**A refusal that comes back in about a second is not an auto-pause resume.** A resume
takes about a minute and ends in success. Something that fails immediately is a
different problem wearing the same clothes - a firewall rule, a wrong password, or a
subscription that has been disabled.

## What is different here

**SQL Server dashboards are served.** A dashboard reading SQL Server keeps its data
with Dashies rather than inside the published file: each refresh streams your cube over
one connection, writes it to Dashies' own storage, and the page asks for what it needs
when a reader opens it. That is the same path Postgres, BigQuery, Snowflake and
Databricks are on.

Three consequences worth knowing before you design a cube:

- **The refresh is asynchronous.** A newly published dashboard reads "Updating" until
  the first refresh lands, rather than carrying numbers from the moment you published.
- **Every refresh reads the whole statement.** There is no incremental mode on this
  engine yet, so bound the window in your own SQL and anchor it to the data's own
  latest complete period.
- **A few column types are refused rather than carried**, named at publish, with the
  T-SQL to select instead. See [Dialect notes](#dialect-notes).

**Letter case: your server and Dashies group text differently, and neither answer is
wrong.** Every SQL Server we have measured defaults to
`SQL_Latin1_General_CP1_CI_AS`, under which `acme` and `ACME` are the same value.
Dashies compares text by its bytes, under which they are two. So one column gives two
different answers depending on where the grouping happens:

| Where the grouping happens | `acme` and `ACME` |
|---|---|
| a `GROUP BY` in your own SQL, run by your server | one group |
| the same `GROUP BY` with a binary collation forced on the key | two groups |
| the rows come to Dashies and Dashies groups them | two groups |

:::warning{title="Moving an existing cube here can change a count, with nothing raising"}
A statement that does its own `GROUP BY` on a text key and one that returns the rows for
Dashies to group answer differently over the same source, one group against two, and
neither is an error. If you are porting a dashboard whose numbers you already know, expect
that difference rather than reading it as a fault.
:::

The difference is a row COUNT rather than an ordering, and the merge happens inside your
server before Dashies sees anything, so once two values have become one row nothing
afterwards can recover them: the refresh succeeds, the row count is simply smaller than
the number of distinct values in your source, and nothing anywhere raises.

**Pick the one you want and write it into the statement.** To keep case apart where your
server does the grouping, put the collation on the key:

```sql
select region collate Latin1_General_100_BIN2 as [region], sum(amount) as [revenue]
from dbo.orders
group by region collate Latin1_General_100_BIN2
```

It changes no schema and nothing is stored differently, and it can cost an index seek on
a large keyed column, so use it where the distinction matters rather than everywhere. To
fold case together where Dashies does the grouping, normalise the key instead, with
`upper(region)` or `lower(region)` in the projection.

## Caps

The first two bound one result read through this engine's **in-database** executor, which
is the path an inline dataset takes. **They are not what bounds a served dashboard's own
data**: that is streamed by the refresh rather than assembled by the executor. The third
is engine-independent.

| Limit | Value | What happens past it |
|---|---|---|
| Rows per inline dataset result | **5,000** | Hard error: `execute_ro: result exceeds 5000 rows; aggregate further` |
| Bytes per inline dataset result | **2,000,000** | Hard error: `execute_ro: result exceeds 2000000 bytes; aggregate further` |
| Compiled dashboard body | 5,242,880 bytes | Publish is refused. |

The 2,000,000 is SQL Server's own, and the comparison is not uniform across the other
five engines. Postgres and the built-in `self` connection are the only engines with an
execution-time byte cap besides SQL Server, and theirs is 8,000,000. BigQuery, Snowflake,
Redshift and Databricks have no execution-time byte cap at all.

How these ceilings relate to each other, and which one binds first, is in
[sizes and ceilings](/concepts/dataset-modes#sizes-and-ceilings).

## Dialect notes

Cube SQL is T-SQL.

- Quote identifiers with `[brackets]`. SQL Server preserves an unquoted output alias
  exactly as written.
- Bucket a date with `cast(ts as date)` or
  `datefromparts(year(ts), month(ts), 1)`. A relative window is
  `dateadd(month, -12, sysutcdatetime())`.

  ```sql
  select datefromparts(year(ordered_at), month(ordered_at), 1) as [month],
         sum(amount) as [revenue]
  from dbo.orders
  where ordered_at >= dateadd(month, -12, sysutcdatetime())
  group by datefromparts(year(ordered_at), month(ordered_at), 1)
  order by 1
  ```

- **Do not start a cube with a `WITH` clause.** Dashies bounds your statement by
  wrapping it as a derived table, and T-SQL does not allow a common table expression
  inside one, so a CTE-leading statement is refused at publish. Inline each one as a
  derived table in the `FROM` clause: `with r as (<body>) select ... from r` becomes
  `select ... from (<body>) as r`. The query is unchanged apart from where the subquery
  is written.
- **Do not end a cube with `ORDER BY ... OFFSET ... FETCH`.** Same wrap, same reason,
  and removing the paging would move the window Dashies samples off the window the
  dashboard shows, so it is refused rather than rewritten.
- **Four types cannot be carried at all**, and the refusal at publish names the column
  and what to select instead: `sql_variant` (cast it to a concrete type), `geography`
  and `geometry` (`<column>.STAsText()`), and `hierarchyid` (`<column>.ToString()`). A
  CLR user-defined type is refused the same way. Cast or drop the column.
- **A `decimal` or `numeric` column whose precision OR scale the server does not report
  is refused** rather than given a width Dashies guessed. Both are checked, because an
  absent scale is not the same as a scale of zero and `decimal(38,0)` is a real column.
  Declare the width in your own SQL if you hit it.
- **Exact numerics are preserved.** `decimal`, `numeric`, `money` and `smallmoney` are
  converted to text by your server, inside a projection Dashies writes around your
  statement, so a `decimal(38,10)` arrives with all its digits. You do not need to cast
  money to integer cents.
- **Time zones use WINDOWS zone names**, such as `Pacific Standard Time`, and that is
  true on a Linux-hosted SQL Server too: an IANA name such as `America/Los_Angeles` is
  rejected with `The time zone parameter ... provided to AT TIME ZONE clause is
  invalid.` Check `select name from sys.time_zone_info` for what your server takes.
  Where you can, return the UTC instant and let the dashboard bucket it rather than
  converting in T-SQL.
- **`datetimeoffset` keeps its instant and loses its offset.** If the originating
  offset matters, select it as a column of its own:
  `datepart(tzoffset, ts) as [ts_offset_minutes]`.
- `datetime` and `smalldatetime` are stored at their own granularity by SQL Server,
  before Dashies reads them - `datetime` to increments of 3.33 ms - so that is the
  source's precision rather than anything the pipeline did.
- Only the schemas you allowlisted are readable, plus the `sys` and `INFORMATION_SCHEMA`
  catalogs.

:::note{title="The read-only login is the real boundary"}
Dashies checks that your cube SQL is a single read-only `SELECT`, but on T-SQL that
check is defence in depth only: T-SQL statement terminators are optional, so no parser
can reliably bound how many statements a string contains. What actually protects your
database is the read-only login the connection test verified, which is re-checked at the
start of every refresh. That is why the check is a requirement here and a recommendation
elsewhere.
:::

## Rotating the password

Edit the data source and use the **Password** field, hinted `Leave blank to keep
the current password.`

## Check it worked

1. The data source reads **active** on
   [dashies.ai/app/connections](https://dashies.ai/app/connections).
2. Ask your AI tool to introspect it and run one query:

   > Introspect my SQL Server data source, then validate this cube SQL against it:
   > `select 1 as ok`

   Introspection should list the tables of the schemas you imported. **An empty
   schema list is a failure, not an empty database**: it means the login reached
   the server but cannot see your tables. Re-check the `GRANT SELECT` for each
   schema you imported.

3. Then [author a dashboard against it](/guides/author-a-dashboard).