Connect SQL Server

Microsoft SQL Server or Azure SQL. This engine differs from the other five in kind rather than degree, so read What is different here before you plan around it.

Before you start

• The host must be reachable from the public internet, 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.
• TLS must validate. Dashies always connects with Encrypt=true and TrustServerCertificate=false, and that is not configurable. A self-signed server certificate cannot connect and surfaces as a TLS failure. Use a certificate from an authority the server's chain can validate.
• You must create a read-only login. On this engine that is a requirement, not a recommendation.

1. 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.

-- 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).

2. Add the data source

Open dashies.xyz/app/connections, click to add a data source, and pick SQL Server.

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.

3. 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 two probes and move it to active.

The two probes are:

1. A real connect: TLS, sign-in, and select 1.
2. A privilege check. This is the one that catches people.

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.

Other failures:

What is different here

SQL Server is the one engine where the database, rather than a Dashies worker, makes the outbound connection to your server. There is no HTTPS SQL API for any SQL Server variant, so the path is different, and three consequences follow.

• No row-level datasets and no Parquet offload. SQL Server supports the additive cube and the grain lattice, and nothing else. A row-level dataset or a Parquet-backed one is not available on this engine. If a dashboard needs row-level detail, it needs a different warehouse.
• The tightest caps of any engine, by a wide margin. See below.
• No row estimates and no Resync button. Both are Postgres-only.

If you read a claim anywhere in these docs that something works on every engine, SQL Server is the exception to check.

Caps

Both SQL Server byte figures are its own, and the comparison is not uniform across the other five.

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, so there is no 8,000,000 to compare against on those four.

The island ceiling is the number that applies everywhere: 8,388,608 bytes on the other five engines, against SQL Server's 2,097,152.

How these ceilings relate to each other, and which one binds first, is in 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()).

  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

• Time zones need the double AT TIME ZONE, cast back to datetime2. AT TIME ZONE returns a datetimeoffset, which the data island reader does not support, so convert and then cast:

  cast(ts at time zone 'UTC' at time zone 'Pacific Standard Time' as datetime2) as [ts_local]

  SQL Server uses Windows zone names such as Pacific Standard Time, not IANA names such as America/Los_Angeles. A Linux-hosted instance may accept IANA names; check select name from sys.time_zone_info for what your server takes.

• Cast tinyint measures and grouping flags to a wider integer. A tinyint above 127 fails the island read outright, so write cast(<measure> as int). A lattice's grouping flags need the same treatment: cast(grouping(region) as smallint) as [__g_region].

• Precision limits. decimal, numeric, and money are read through a floating-point hop good for about 15 to 16 significant digits, so cast money to integer cents if you need exactness. datetime and datetime2 truncate to whole seconds in the island, so bucket or format in SQL rather than relying on sub-second precision.

• Only the schemas you allowlisted are readable, plus the sys and INFORMATION_SCHEMA catalogs.

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.xyz/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.