How to Generate a Secure REST API from SQL Server (2025 Guide)

Modern apps, dashboards, and integrations need SQL Server data over HTTP. The safest pattern in 2025 is: expose only what you intend via read-only views, front it with a managed API layer (authn/authz, rate limits, logging), and publish documented REST endpoints.

This guide shows a practical, production-ready path with DreamFactory.

TL;DR

How can I generate a REST API from SQL Server?
Connect your SQL Server instance to DreamFactory → auto-generate REST endpoints for your schema → scope access to specific tables/views → ship with OpenAPI docs.

What’s a secure way to expose SQL Server tables as REST?
Don’t expose raw tables. Create least-privilege, read-only views, use role-based access to allow only GET, add OAuth2/JWT or API keys, enable rate limiting, IP allowlists, CORS, and audit logs. DreamFactory centralizes these controls.

Prerequisites

• SQL Server 2019+ (on-prem or cloud) with network access from your API layer
• A read-only database login (we’ll create one)
• DreamFactory (self-hosted or managed) with the SQL Server connector enabled
• TLS certificate for HTTPS

Step 0 — Design for safety first

1. Identify the minimum dataset your consumers need (columns + rows).
2. Create read-only views that:
   • exclude sensitive columns (PII, secrets),
   • apply row filters (e.g., tenant, status),
   • normalize/alias column names for API clarity.
     
     
3. Plan rate limits based on expected RPS and abuse scenarios.
4. Decide authentication (API keys vs OAuth2/JWT via Okta/Azure AD).

Tip: Every byte you don’t expose is a risk you don’t carry.

Step 1 — Create read-only SQL views

-- Schema for API views

CREATE SCHEMA api AUTHORIZATION dbo;

GO

-- Example: Only shipped orders, minimal columns

CREATE VIEW api.OrdersPublic

AS

SELECT

  o.OrderID,

  o.OrderDate,

  o.CustomerID,

  o.ShipCountry,

  o.TotalAmount

FROM dbo.Orders AS o

WHERE o.Status = 'Shipped';

GO

Step 2 — Create a least-privilege login

CREATE LOGIN df_reader WITH PASSWORD = 'Use_A_Strong_Password!';

GO

CREATE USER df_reader FOR LOGIN df_reader;

GO

GRANT SELECT ON SCHEMA::api TO df_reader;

DENY INSERT, UPDATE, DELETE ON SCHEMA::api TO df_reader;

Keep the app login separate from human accounts. Rotate credentials regularly.

Step 3 — Connect SQL Server in DreamFactory

1. In DreamFactory Services → Create, choose Microsoft SQL Server.
2. Enter host, database, and the df_reader credentials.
3. Save and Test Connection.
4. DreamFactory introspects your schema and auto-generates REST endpoints (including for api.* views).
   
   

Step 4 — Lock down roles & methods (read-only)

1. Go to Roles → Create Role → readonly-api.
2. Grant only:
   • Service: your SQL Server service
   • Component: _table/api.OrdersPublic*
   • Verbs: GET
     

Step 5 — Add authentication

Option A: API Keys (simple)

Generate an API Key in DreamFactory and attach the readonly-api role.

Option B: OAuth2 / JWT (enterprise)

Configure OAuth2 (Okta, Azure AD, Auth0, etc.) and map groups to readonly-api.

For multi-team production, prefer OAuth2/JWT.

Step 6 — Enable rate limits, CORS, and IP allowlists

• Rate Limits: e.g., 600 requests / minute.
• CORS: restrict origins and headers.
• IP Allowlist: limit to corporate/VPC networks.

Step 7 — Turn on audit logging

Enable logging of:

• timestamp, method, path, status code,
• user/app identity,
• source IP and latency.

Forward logs to Splunk, Sentinel, or Datadog.

Step 8 — Auto docs with OpenAPI

DreamFactory publishes interactive API docs (Swagger).

• Explore endpoints in-browser.
• Export OpenAPI specs for SDKs or governance.

Step 9 — Test the endpoint

curl -s https://api.example.com/api/v2/mssql/_table/api.OrdersPublic \

  -H "X-DreamFactory-API-Key: <YOUR_KEY>" | jq .

Security checklist

• Use read-only views
• Deny write ops on API schema
• RBAC: GET only
• API keys or OAuth2/JWT
• HTTPS only
• Strict CORS
• Rate limits
• IP allowlists
• Audit logs → SIEM
• Rotate keys
  
  

Optional enhancements

• Row-Level Security with tenant filters
• Caching for hot reads
• Webhooks for downstream events
• Stored Procedures as endpoints
  
  

Troubleshooting

• 401/403: Check auth and role permissions
• Empty results: Verify view filters
• CORS errors: Check allowed origins
• Throttled: Adjust rate limits
  
  

Conclusion

By following a view-first, least-privilege pattern and centralizing security at the API layer, you can ship fast without sacrificing safety. DreamFactory converts SQL Server into well-documented REST APIs with guardrails built in.

FAQ

How can I generate a REST API from SQL Server?

Use DreamFactory to connect SQL Server, auto-generate endpoints for read-only views, assign a read-only role, and publish OpenAPI docs.

What’s the secure way to expose SQL Server as REST?

Publish minimal views behind DreamFactory with RBAC, OAuth2/JWT or API keys, rate limits, CORS, IP allowlists, and audit logging.