MS SQL Server adapter

The MS SQL Server adapter works with:

• SQL Server 2012+
• Azure SQL Database / Azure SQL Managed Instance
• AWS RDS SQL Server
• Google Cloud SQL SQL Server
• Self-hosted SQL Server

Config shape

Connection string form:

{
  "type": "mssql",
  "config": {
    "connectionString": "Server=tcp:sqlsrv.example.com,1433;Database=prod;User Id=spelo_ro;Password=***;Encrypt=true;"
  },
  "collections": {
    "Orders": {
      "source": "dbo.Orders",
      "searchable_fields": ["OrderNumber", "CustomerName"],
      "filterable_fields": ["Status", "TotalAmount", "CreatedAt"],
      "display_fields": ["OrderId", "OrderNumber", "Status", "TotalAmount"]
    }
  }
}

Discrete form:

{
  "type": "mssql",
  "config": {
    "server": "sqlsrv.example.com",
    "port": 1433,
    "database": "production",
    "user": "spelo_ro",
    "password": "${MSSQL_PASSWORD}",
    "encrypt": true,
    "trustServerCertificate": false
  }
}

Setup

1. Create a read-only login + user

   -- Server-level login
   CREATE LOGIN spelo_ro WITH PASSWORD = 'StrongRandom123!';
   
   -- Database-level user
   USE production;
   CREATE USER spelo_ro FOR LOGIN spelo_ro;
   
   -- Grant SELECT on the tables you want searchable
   GRANT SELECT ON dbo.Orders TO spelo_ro;
   GRANT SELECT ON dbo.Products TO spelo_ro;
   
   -- Or all tables in the schema (looser):
   GRANT SELECT ON SCHEMA::dbo TO spelo_ro;

2. Azure SQL specifics

   Azure SQL requires the Encrypt=true flag and will reject plaintext connections. Make sure your firewall rules allow Spelo’s egress IPs (Azure Portal → SQL Server → Networking → Firewall rules).

3. Self-signed certs

   If your SQL Server uses a self-signed cert (common for on-prem), set trustServerCertificate: true. Only do this on private networks.

4. Paste connection in the dashboard

   Dashboard → Data → MS SQL Server → Test connection.

5. Map collections with source = dbo.TableName (schema-qualified).

Pagination

SQL Server uses OFFSET ... FETCH NEXT ... ROWS ONLY (T-SQL 2012+). This requires an ORDER BY clause, so the adapter always appends one:

• If you specified sort_by, it uses that column.
• Otherwise it falls back to the first display_field as an order key.

Set sort_by explicitly in the collection config to control this.

Field type support

SQL Server type	Filter operators
int, bigint, decimal, float, real, money	eq, neq, gt, gte, lt, lte, in
nvarchar, varchar, nchar, char, text	eq, neq, contains, in
bit	eq, neq
date, datetime, datetime2, datetimeoffset	eq, neq, gt, gte, lt, lte
uniqueidentifier	eq, neq, in

Security notes

• Identifiers are wrapped in [square brackets] to handle reserved words safely.
• Values bound via request.input('pN', value) — never string-concatenated.
• sp_* and system schemas (sys.*, INFORMATION_SCHEMA.*) are not reachable unless you explicitly grant SELECT. Don’t.

Troubleshooting

• Login failed for user → typo in credentials, or the user/login hasn’t been mapped to the database (CREATE USER step).
• Cannot open server "X" requested by the login → firewall blocking our egress. Add our IPs.
• The TCP/IP connection to the host has failed → SQL Browser disabled or wrong port. Use the IP + 1433.
• SSL handshake failure → set trustServerCertificate: true if your cert is self-signed.

More: Database connection errors.

Connect a different data source

Overview Postgres MySQL MongoDB Supabase Firebase OAuth setup Airtable Google Sheets Shopify WooCommerce REST API Webhook TS adapter Schema introspection