SQL Server
Read SQL Server tables and views into a Strata flow, and write results to tables. Works with SQL Server 2017 to 2025 using SQL authentication.
Before you start
- A server that accepts TCP connections from outside your network, a database name and a SQL login. Windows authentication isn't supported.
- Connections come from Oplema's servers, not your browser. This also applies when the rest of your flow runs locally. A database behind a firewall or allowlist must accept them. Oplema doesn't promise a fixed IP address and there is no SSH tunnel or private link; contact us before you plan around an allowlist.
- TLS 1.2. Connections are always encrypted, using TDS 7.4. Servers that require strict TDS 8.0 or allow only TLS 1.3 aren't supported.
- A dedicated login for Oplema, with the permissions below. See GRANT in SQL Server.
-- Read
GRANT SELECT ON SCHEMA::dbo TO oplema;
-- Write also needs the Read grants
-- Write: Append rows
GRANT INSERT ON SCHEMA::dbo TO oplema;
-- Write: Replace also runs DELETE and reads table metadata
GRANT DELETE ON SCHEMA::dbo TO oplema;
GRANT VIEW DEFINITION TO oplema;
-- Write: Create new table
GRANT CREATE TABLE TO oplema;
GRANT ALTER ON SCHEMA::dbo TO oplema;Connect
- In Strata, select an Input block to read, or an Output block to write.
- Set Source to Connectors. On an Output block the same control is Destination.
- In the connector dropdown below it, choose SQL Server.
- Open the Connection dropdown and choose Create new connection.
- Fill in Server (host or host\instance), Port (default 1433; omit for named instances), Database, User and Password. The password is shown as you type. Select Trust unverified server certificate (local/self-signed only) only for a local or self-signed server; traffic stays encrypted but the certificate is not checked.
- Set Name (optional). Without a name, the connection is labeled
server/database. - Select Save connection.
The new connection is selected. Saving only checks the format of what you typed. It doesn't sign in, so wrong details first show up as an error when the pickers below load.
A saved connection can't be edited. To change a password, token or any other field, open the Connection dropdown, choose Delete (the ×) on its row, and create the connection again. Blocks that used the deleted connection must then select the new one.
Read
| Setting | What it does |
|---|---|
| Schema | Default dbo. |
| Table or view | The table or view to read. |
| Columns | All columns or Selected columns. |
| Date column, From and To | Optional. These are inclusive whole days. DATETIMEOFFSET uses UTC. |
Select Refresh data to read again. A read uses one streaming query with no row limit. DATETIME, DATETIMEOFFSET and high-precision times arrive as text. Unsupported column types fail by name.
Write
Pick Schema, then Table. Only ordinary disk-based tables can be written. Memory-optimized, temporal, external and ledger tables aren't listed.
- Create new table: enter a Table name, default
output. - An existing table: set Write to Replace or Append rows. Replace deletes all rows under a table lock and inserts the new ones in one transaction.
Output writes when you run the flow. Choosing tables in the panel never writes. If you pick Create new table and a table with that name already exists, the run fails.
Limits and billing
- Reads: the first 50 MB of each download is free. After that, each download costs $0.01 per GB from your usage. Failed reads aren't charged, and a read stops when your usage runs out.
- Writes: the first 50 MB of each write is free. After that, each write costs $0.01 per GB from your usage, and a write stops when your usage runs out.
See Billing for who pays and how usage works.
Troubleshooting
| Message | What to do |
|---|---|
SQL Server: select at least one column | Choose All columns, or select at least one column. |
SQL Server: column <name> is unavailable; refresh available columns | The table changed. Refresh the list and select the columns again. |
SQL Server: choose a date column before setting dates | Pick a Date column, or clear From and To. |
SQL Server: From must not be after To | Fix the date range. |
SQL Server: enter a host, with the TCP port in the separate Port field | Remove the port or tcp:// from the server field. |
SQL Server: use host\instance without a port, or host and an explicit TCP port | Use one or the other. |
SQL Server: could not verify a supported server version (2017 or newer required) | Upgrade the server, or check the login can read the version. |
SQL Server: unsupported column <name> (<type>); select supported columns | Use Selected columns and leave that column out. |
SQL Server: selected table is unavailable; refresh and check permissions | Check the table exists and the login has SELECT. |
SQL Server: Replace requires database VIEW DEFINITION and a table without enabled row-level security policies | Grant VIEW DEFINITION, or use Append rows. |
SQL Server: destination must be an ordinary disk-based table | Choose another table. |
SQL Server: <name> would change when converted to <type>; convert it explicitly in Strata | Convert the column before Output. |
SQL Server: empty replacement is blocked; existing data was preserved | The incoming table has no rows. Check the flow upstream. |
SQL Server: write completion could not be confirmed. Check the destination before running again. | The connection dropped during the write. Look at the table first, because a retry could write the rows twice. |
This transfer exceeds the free 50 MB and the available usage credits | The read or write passed 50 MB and your usage ran out. Add usage, or move less data. |
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.